1

I have a serious problem when using Libraries in Google Sheets.

I have two Spreadsheets: Dashboard1 and Dashboard2. In both spreadsheets, I have multiple scripts and Triggers.

There is one trigger in specific that triggers on event OnEdit in Dashboard2. Here is the code:

Dashboard2

Code.gs

function onEdit(e){
  Dashboard1.updateReferral(e.oldValue, e.value);
}

Dashboard1

Code.gs

function updateReferral(evaluatedEmail, newReferral){
  var employee = returnEmployeeRow(evaluatedEmail);
  PAYROLL_SHEET.getRange(REFERRAL_COLUMN + employee[2]).setValue(newReferral);
}

Constants.gs

var SHEET_CATEGORIES = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Categorias");
var RANGE_CATEGORIES = SHEET_CATEGORIES.getRange("A2:G");
var CATEGORIES = RANGE_CATEGORIES.getValues();

What I want to do is when someone edit in Dashboard2, it updates a single row in Dashboard1.

When I tried to trigger the event, it throws the error

TypeError: Cannot call method "getRange" of null. (line 2, file "Constants", project "XXXXXX")

I figured out that this error exist because there is no sheet "Categorias" in Dashboard2. If I create a sheet "Categorias" in Dashboard2, it works fine.

My question is:

  1. This is a normal behavior? Why when I run the code, it tries to initialize the variable from Constants.gs in Dashboard1 as they exist in Dashboard2?

  2. There is a solution for this? Or just I can't do this?

I've tried to use different name in the .gs files but it didn't work. Also tried to delete the file Constants.gs and have all the variables in Code.gs.

Rubén
  • 34,714
  • 9
  • 70
  • 166
Erosennin93
  • 11
  • 2

1 Answers1

2

From the OP's question

My question is:

  1. This is a normal behavior? Why when I run the code, it tries to initialize the variable from Constants.gs in Dashboard1 as they exist in Dashboard2?

Yes, this behavior is "normal".

  1. There is a solution for this? Or just I can't do this?

There are many solutions. Some rules of "thumb" for Google Apps Script projects that could help you decide how to procede:

  1. Reduce the variable declarations at the global scope and when you declare variables at the global scope limit them to assign literals.
  2. Only use stand-alone projects for libraries but if you really need to use a bounded project as a library, don't use "getActive" methods on the library functions, instead add the "active" object as an argument on the library functions that require them.

Related

Rubén
  • 34,714
  • 9
  • 70
  • 166