TypeError: Method "getRange" of null when using multiple scripts (Libraries) in Google scripts

Question

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:

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?
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.

Wicket · Accepted Answer · 2019-08-16 22:52:46Z

2

From the OP's question

My question is:

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".

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:

Reduce the variable declarations at the global scope and when you declare variables at the global scope limit them to assign literals.
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.

answered Aug 16, 2019 at 22:47

Wicket

39.4k9 gold badges81 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Erosennin93 Over a year ago

Thanks Rubén!! Now I' get it. I need to read a lot of how Google Scripts works.

Collectives™ on Stack Overflow

TypeError: Method "getRange" of null when using multiple scripts (Libraries) in Google scripts

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related