1

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.

4
  • 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. Commented Jun 18, 2022 at 15:34
  • 1
    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. Commented Jun 18, 2022 at 15:34
  • Duplicate of stackoverflow.com/questions/63605833/… Commented Jun 18, 2022 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. Commented Jun 18, 2022 at 18:46

3 Answers 3

1

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.

Sign up to request clarification or add additional context in comments.

1 Comment

Thanks and took your advice and also Master above and getting much closer.
0

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

Comments

0

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.