Google script - call library function with .getActiveSpreadsheet()

Question

I need to have a centralized script which is executed by many different sheets. I did some research here and figured out how to do it by using the library method:

  test.Submit();

Where "test" is the library identifier, and Submit() is the function within the library.

I have the issue that Submit() is trying to get the active spreadsheet (the "client" sheet from where the function is called):

function Submit() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1'); 
  
  sheet.getRange(1,1).setValue("OK!"); }

It seems that getActiveSpreadsheet() does not work (ss is Undefined)

How can I let the main function Submit() know which spreadsheet needs to be edited?

I think "ss" is undefined. I get an error that cannot getRange of undefined, and I believe the root cause is that "ss" is not defined. — Filippo, Jul 22 '20 at 22:13

score 0 · Accepted Answer · answered Jul 22 '20 at 22:38

I was able to make it work by passing the sheet id as parameter.

(initially this was not working because I was not updating the script version. Remember to update the script version when saving!)

"local" sheet script:

  var id = SpreadsheetApp.getActiveSpreadsheet().getId();
  test.Submit(id);

Centralized script:

function Submit(id) {
  
  var ss = SpreadsheetApp.openById(id);
  var sheet = ss.getSheetByName('Sheet1'); 
  
  sheet.getRange(1,1).setValue("OK!");

Google script - call library function with .getActiveSpreadsheet()

1 Answers1

Linked