0

I have two scripts, each one bounded to different spreadsheets. Just script one is bounded to a cloud project while script two is not. I saved the first script version with identifier: "scriptOne"

This is the function in script 1 (which does return the value I want when using the log):

var ss = SpreadsheetApp.getActive().getSheetByName("someSheet");
var one = ss.getRange(2, 1).getValue();

function scriptOneFunction() {
  return one;
}

I have a second script and i made sure to select scriptOne.v1 within libraries (development mode "on") and now I am trying to call this function from the second script as follows:

function callScriptOne() {
  var two = scriptOne.scriptOneFunction();
  Logger.log(two);
}

The error I get:

TypeError: Cannot read property 'getRange' of null
    at [scriptOneFunction](callScriptOne:2:17)

What am i doing wrong?

Felipe Lara
  • 1
  • 1

1 Answers1

1

There are two possible issues with your approach:

  1. Your second script is not bound to a google spreadsheet.

  2. If 1) is not the issue, then check if the spreadsheet has indeed a sheet with the name someSheet.

Related:

TypeError: Cannot call method "getRange" of null. (line 9, file "Code")

TypeError: Cannot call method "getRange" of null. (line 4, file "Code"

Marios
  • 26,333
  • 8
  • 32
  • 52
  • 1
    If it was `1`, the error would be `TypeError: Cannot read property 'getSheetByName' of null` (`getActive()` would return null), so I think it has to be `2`. – Iamblichus Dec 03 '20 at 13:46
  • True thanks ! Just wanted to be sure I cover all the possibilities @Iamblichus – Marios Dec 03 '20 at 13:48
  • @Iamblichus the sheet name is not the problem. Could it be that one of the scripts is not bounded to the same cloud project as the other? – Felipe Lara Dec 03 '20 at 17:31
  • @FelipeLara the second script needs to be bounded to a spreadsheet, not as a cloud project. Open a new spreadsheet, go to to Tools => Script editor on the top menu and then use your second script there. Think about it, in your code you use active sheets and spreadsheets. How is the script supposed to know which spreadsheet are you referring to? What is the active spreadsheet of a cloud based script? Follow the instructions I just mentioned and let me know if it worked. – Marios Dec 03 '20 at 17:34
  • Thanks @Marios for your comment. Both scripts were already bounded to spreadsheets. I made sure `scriptOneFunction()` works within script one. It just does not work when I call it from script two. Would it be easier to publish as API executable? – Felipe Lara Dec 03 '20 at 17:46
  • Does script 2 have a sheet with the name `someSheet`? @FelipeLara – Marios Dec 03 '20 at 17:47
  • No, just script 1 from which i am extracting the data. Script 2 has different sheet names @Marios – Felipe Lara Dec 03 '20 at 17:54
  • 1
    @FelipeLara this is exactly what we are saying. The **second** spreadsheet needs to have a sheet with the name `someSheet` because that is the name you use in the code. It has to be exactly that name, no difference in capital letters or extra spaces etc. – Marios Dec 03 '20 at 18:13
  • @FelipeLara is your issue solved? – Marios Dec 04 '20 at 02:35
  • Not really @Marios. I also do not get why i should have two sheets with the same name to make the `callScriptOne()` work. – Felipe Lara Dec 04 '20 at 12:51
  • I don't know why suddenly my question was closed but it turns out the answer was i had to use the method `openById()` in script 1 otherwise if i use method `callScriptOne()` in script 2 it wouldn't go through `getActive()` in script 1 @Marios – Felipe Lara Dec 04 '20 at 13:47