使用GAS从/向电子表格读取和写入时间值 [英] Reading and writing time values from/to a spreadsheet using GAS

问题描述

我真的以为我开始理解这是如何工作的，直到我试图为这个问题提炼一个测试用例并且我再次感到困惑。

I really thought I was starting to understand how this works until I tried to distil a test case for this question and am totally confused again.

使用谷歌电子表格你可以编写这样的自定义函数：

With a google spreadsheet you can write a custom function like this:

function dateToSeconds(date) {
  var hours = date.getHours();
  var minutes = date.getMinutes();
  var seconds = date.getSeconds();
  return (hours*3600) + (minutes*60) + seconds;
}

并通过例如

=dateToSeconds(A1)

它符合您的期望。但是，如果你这样称呼它：

and it does what you expect. However, if you call it like so:

function test() {
    var sheet = SpreadsheetApp.getActiveSheet();

    sheet.getRange("A2").setValue(dateToSeconds(sheet.getRange("A1").getValue()));
}

存在25m 21s的差异。哎呀？

there's a discrepancy of 25m 21s. What the heck?

另外，如果你想从脚本中返回一个日期对象，以便将其格式化为时间，你可以这样做，例如

Also, if you want to return a date object from a script so that it is formatted as a time, you can do e.g.

function newTime(hours, minutes, seconds) {
    // 1899-12-30 is the epoch for time values, it seems
    // http://stackoverflow.com/questions/4051239/how-to-merge-date-and-time-as-a-datetime-in-google-apps-spreadsheet-script
   return new Date(1899, 11, 30, hours, minutes, seconds, 0);
 }

并通过执行以下方式调用：

and call it by doing:

=newTime(A1, A2, A3)

它工作正常。如果你这样做：

and it works fine. If you do:

function test() {
    var sheet = SpreadsheetApp.getActiveSheet();
    var values = sheet.getRange("A1:C3").getValues();
    sheet.getRange("D3").setValue(newTime(values[0], values[1], values[2]));
}

然后（给定11:00:00的输入）它被格式化为日期如：

then (given an input of 11:00:00) it gets formatted as a date like:

01/01/1970 01:00:00

01/01/1970 01:00:00

看起来它被解释为更传统的1970年代后一个小时？我认为之前看到的行为有点对称，上面的25m 21s偏移，我会看到：

which looks like it's being interpreted as an hour after the more traditional 1970 epoch? I thought the behaviour I had seen before was a bit symmetrical with the 25m 21s offset above and I'd see:

10:34： 39

10:34:39

我的测试用例是这里

推荐答案

似乎有一些奇怪的行为发生在your = dateToSeconds（）与test（）函数的对比。看起来有一个错误导致两者之间的时间差异。请在问题跟踪器中针对该问题提出错误。

There does seem to be something strange going on with the behavior of your =dateToSeconds() versus your test() function. It looks like there is a bug that's causing the time discrepancy between the two. Please raise a bug for that in the issue tracker.

然而，我能够通过在单元格上使用数字格式来实现这一点。请查看我的修改后的电子表格副本。在单元格A2中，我输入了29/04/2012 11:00:00，然后设置格式>数字> 15:59:00时间。您也可以使用 Range.setNumberFormat （HH：mm：）以编程方式执行此操作SS）;这导致单元格A2显示为11:00:00。

I was however able to get this working by using number formatting on the cells. Have a look at my modified copy of your spreadsheet. In cell A2 I entered "29/04/2012 11:00:00" and then set Format > Number > 15:59:00 Time. You can also do this programmatically by using Range.setNumberFormat("HH:mm:ss"); This results in cell A2 showing 11:00:00.

然后，我修改了您的测试函数以获得此代码：

Then, I modified your test function to have this code:

function test() {
  var sheet = SpreadsheetApp.getActiveSheet();

  sheet.getRange("E2").setValue(dateToSeconds(sheet.getRange("A2").getValue()));
  Logger.log(sheet.getRange("A2").getValue());
  Logger.log(sheet.getRange("A2").getNumberFormat());

  var values = sheet.getRange("A4:C4").getValues();
  Logger.log(values[0][0] + " " + values[0][1] + " " + values[0][2]);
  sheet.getRange("E4").setValue(newTime2(values[0][0], values[0][1], values[0][2])).setNumberFormat("HH:mm:ss");
}

而不是newTime函数，我用这段代码创建了一个newTime2： / p>

and instead of a newTime function, I created a newTime2 with this code:

function newTime2(hours, minutes, seconds) {
  var date = new Date();
  date.setHours(hours);
  date.setMinutes(minutes);
  date.setSeconds(seconds);
  Logger.log(date);
  return date;
}

我希望有所帮助。

这篇关于使用GAS从/向电子表格读取和写入时间值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！