当编辑器从编辑器打电话时，为什么null传递给我的功能？

Question

在下面的示例中，Playgroundsheet是全球定义的。当按照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);
  
}

我得到了我想要的结果：

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

但是通过致电函数运行：

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;

我得到以下内容：

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

第13行是

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

我尝试通过（row，col，col，#row，#col）语法没有欢乐，没有想法。

Answer 1

关于

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

替换

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

，

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

然后运行myFunction而不是testdates。

以上是因为调用testdates或脚本编辑器中的任何其他函数，自定义菜单等都会导致全局范围中的陈述在运行所谓的函数之前，在这种情况下，它运行了两次，第一个是由var numberOfdays = testDates（'bh2'，'bl2'）;第二次由用户/触发器用于调用该函数的第二次。发生错误是因为UI的调用不会将参数传递给函数。

Answer 2

这样尝试：

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

Answer 3

感谢Themaster和Rubén。从另一个功能调用的最终功能返回两个日期之间的天数：

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

当我尝试学习语言时，我在这里阅读了很多，这是我的第一个问题。对快速而有用的回应感到惊讶。
永远学习，
大卫