Business
Jobs
  • About Us
  • Solutions
    • Job Postings
      Post your job and receive qualified candidates in 48h.
    • Candidate Assessments
      500+ technical and psychological tests, plus anti-fraud.
    • Headhunting
      Tailor-made executive search from start to finish.
    • Payroll + EOR
      Payroll dispersal and EOR across 15+ LATAM countries.
  • Pricing
  • Jobs

0

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

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.

about 4 years ago · Juan Pablo Isaza
3 answers
Answer question

0

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.

about 4 years ago · Juan Pablo Isaza Report

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;      
}
about 4 years ago · Juan Pablo Isaza Report

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

about 4 years ago · Juan Pablo Isaza Report
Answer question
Find remote jobs

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post vacancy Pricing Sales
Legal
Terms and conditions Privacy policy
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Show me some job opportunities
There's an error!