Google Apps Script get bounded sheet data from imported package

Question

I am trying to figure out how to get data from a Spreadsheet bounded to a script that is referenced as a package in another script.

I have a Spreadsheet with some data and a bounded script with this function:

function getIt()
{
  return SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange("A1").getValue();
}

The script works as expected.

Then I have a standalone script/project where I have added the above script as a library. In this standalone script, if I try library.getIt() I get an error:

TypeError: Cannot call method "getSheetByName" of null. (line 8, file "Code", project "Untitled project")

What I want to do is create some functions in the script that is bounded to the Sheet that gets/returns data from the sheet. Then I want to be able to add the script as a library to other scripts and get said data by calling those functions.

Is this possible?

Although I'm not sure whether this is the direction you want, for example, when Apps Script API is used, the file ID of Google Docs can be retrieved from the bound script. How about retrieving the file ID of the Google Docs (Spreadsheet ID) using this? By this, Spreadsheet of bound script which is the library can be used from the script which installed the library. If this was not the direction you want, I apologize. — Tanaike

ross ross · Accepted Answer · 2019-05-28T15:00:52

You won't be able to getActiveSpreadsheet() from a standalone script, try using openById() or openByUrl() instead.

Although your getIt() script is bound to the spreadsheet, I believe it would try and run getActiveSpreadsheet() for the current script file you're running this from, which returns null because it's not bound to any spreadsheet.

Google Apps Script get bounded sheet data from imported package

1 Answers