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

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.

CodePudding user response：

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);

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.

CodePudding user response：

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;      
}