Why is null passed to my function, when called from the editor?

Question

In following example playgroundSheet is globally defined. When run as below with the BH2, BL2:

function testDates(DateLow, DateHigh) {
  var now = playgroundSheet.getRange('BH2').getValue();;
  var nowMilli = Number(now.getTime()).toFixed(0);
  var targetDate = playgroundSheet.getRange('BL2').getValue();
  var targetDateMilli = Number(targetDate.getTime()).toFixed(0);
  var durationMilli = targetDateMilli - nowMilli;
 // return numberOfDays
 
  Logger.log(now); 
  Logger.log(nowMilli);
  Logger.log(targetDate); 
  Logger.log(targetDateMilli);
  Logger.log(durationMilli/day);
  
}

I get the results I desire:

11:00:18 AM Notice  Execution started
11:00:20 AM Info    Wed Jun 08 00:00:00 GMT-04:00 2022
11:00:20 AM Info    1654660800000
11:00:20 AM Info    Sun Jun 12 00:00:00 GMT-04:00 2022
11:00:20 AM Info    1655006400000
11:00:20 AM Info    4.0
11:00:20 AM Info    null
11:00:20 AM Info    Wed Jun 08 00:00:00 GMT-04:00 2022
11:00:20 AM Info    1654660800000
11:00:20 AM Info    Sun Jun 12 00:00:00 GMT-04:00 2022
11:00:20 AM Info    1655006400000
11:00:20 AM Info    4.0
11:00:19 AM Notice  Execution completed

But run by call to the function:

var numberOfDays = testDates('BH2','BL2');
Logger.log(numberOfDays);
function testDates(DateLow, DateHigh) {
  var now = playgroundSheet.getRange(DateLow).getValue();;
  var nowMilli = Number(now.getTime()).toFixed(0);
  var targetDate = playgroundSheet.getRange(DateHigh).getValue();
  var targetDateMilli = Number(targetDate.getTime()).toFixed(0);
  var durationMilli = targetDateMilli - nowMilli;
  return numberOfDays;

I get the following:

10:28:03 AM Notice  Execution started
10:28:03 AM Info    null
10:28:03 AM Error   
Exception: Argument cannot be null: a1Notation
testDates   @ Code.gs:13

where line 13 is

var now = playgroundSheet.getRange(DateLow).getValue();```

I have tried passing the (row, col, #row, #col) syntax with no joy and am out of ideas.

You might want to checkout how to debug your code and watch the variables. This would be similar to running vba with breakpoints and then using the immediate window or "watching" a variable. Google app scripts allows you to watch your defined variables to the right of the screen if you click `debug` instead of `run`. A lot easier than depending on using `logger.log` to follow your variables. — pgSystemTester, Jun 18 '22 at 15:34
From your function `testDates` you are return `numberOfDays` which is the variable you are trying to set the value of `testDates`. Try just returning `durationMilli`. — TheWizEd, Jun 18 '22 at 15:34
Duplicate of https://stackoverflow.com/questions/63605833/when-passing-variable-to-function-i-get-invalid-argument-but-when-i-hard-code/63608780#63608780 — TheMaster, Jun 18 '22 at 17:08
Thanks for making the title more informative and for noting my poor return value. That helped a lot. The comment from Rubén at 1 to call my function from another function was also invaluable. Still did not work the same but was able to figure it out so the final seems to work fine. I just added two more lines: `` var durationMilli = (targetDateMilli - nowMilli).toFixed(0); return durationMilli/day; ``` Thanks to all for the prompt responses. — David, Jun 18 '22 at 18:46

score 1 · Answer 1 · answered Jun 18 '22 at 16:48

Regarding

var numberOfDays = testDates('BH2','BL2');
Logger.log(numberOfDays);
function testDates(DateLow, DateHigh) {
  var now = playgroundSheet.getRange(DateLow).getValue();;
  var nowMilli = Number(now.getTime()).toFixed(0);
  var targetDate = playgroundSheet.getRange(DateHigh).getValue();
  var targetDateMilli = Number(targetDate.getTime()).toFixed(0);
  var durationMilli = targetDateMilli - nowMilli;
  return numberOfDays;
}

replace

var numberOfDays = testDates('BH2','BL2');
Logger.log(numberOfDays);

by

function myFunction(){
  var numberOfDays = testDates('BH2','BL2');
  Logger.log(numberOfDays);
}

Then run myFunction instead of testDates.

The above because calling testDates or any other function from the script editor, a custom menu, etc. will cause that the statments in the global scope be executed before running the function that was called, in this case, it ran two times, the first is caused by var numberOfDays = testDates('BH2','BL2'); the second by the UI/trigger used to call the function. The error occurs because the call from the UI doesn't pass parameters to the function.

Thanks and took your advice and also Master above and getting much closer. — David, Jun 18 '22 at 18:26

score 0 · Answer 2 · answered Jun 18 '22 at 15:27

Try it this way:

function testDates(DateLow, DateHigh) {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('pg');
  var now = new Date(sh.getRange('BH2').getValue());
  var nowMilli = now.valueOf();
  var targetDate = new Date(sh.getRange('BL2').getValue());
  var targetDateMilli = targetDate.valueOf()
  var durationMilli = targetDateMilli - nowMilli;      
}

score 0 · Answer 3 · edited Jun 18 '22 at 18:53

Thanks to TheMaster and Rubén. The final function called from another function returns the number of days between two dates:

function testDates(DateLow, DateHigh) {
  var now = playgroundSheet.getRange('BH2').getValue();;
  var nowMilli = Number(now.getTime()).toFixed(0);
  var targetDate = playgroundSheet.getRange('BL2').getValue();
  var targetDateMilli = Number(targetDate.getTime()).toFixed(0);
  var durationMilli = (targetDateMilli - nowMilli).toFixed(0);
  return durationMilli/day;
}

I have been reading here a lot as I try to learn the language and this is my first question. Absolutely amazed at the quick and helpful responses. Forever learning, David

Why is null passed to my function, when called from the editor?

3 Answers3