使用GAS获取持续时间的等号 [英] Getting the number equalent of duration using GAS

问题描述

我有一个单元格，其值类型为duration， 我已将值设置为00:10:00，即10分钟， 当我将单元格的格式更改为数字时，它将显示0.01.

I have a cell that has the value of type duration, I have set the value to 00:10:00 which is 10 minutes, When I change the format of the cell to number it will show 0.01.

我如何以编程方式使用应用程序脚本获得等同于使用应用程序脚本代码的十进制数?

How will I get programmically using apps script the decimal equivalent of using apps script code?

00:10:00是0.01 10:00:00是0.42

00:10:00 is 0.01 10:00:00 is 0.42

用于转换此持续时间字符串的计算是什么?

What is the calculation used to convert this duration string?

推荐答案

此答案如何?

0.01和0.42的值是序列号.但是在这种情况下，当看到每个单元格时，0.01和0.42分别是0.00694444444444444和0.416666666666667.您也可以确认这一点.

Values of 0.01 and 0.42 are the serial number. But in this case, when each cell is seen, 0.01 and 0.42 are 0.00694444444444444 and 0.416666666666667. You can also confirm this.

当您要将0.00694444444444444和0.416666666666667分别转换为00:10:00和10:00:00时，请执行以下流程.

When you want to convert 0.00694444444444444 and 0.416666666666667 to 00:10:00 and 10:00:00, respectively, please do the following flow.

1. 将序列号转换为unix时间.
2. 将unix时间转换为日期对象.
3. 检索类似00:10:00和10:00:00的值.

1. Convert serial number to unix time.
2. Convert unix time to date object.
3. Retrieve the value like 00:10:00 and 10:00:00.

示例脚本:

var serialNumbers = [0.00694444444444444, 0.416666666666667];

for (var i = 0; i < serialNumbers.length; i++) {
  var unixTime = (serialNumbers[i] - 25569) * 86400 * 1000; // Reference: https://stackoverflow.com/a/6154953
  var dateObject = new Date(unixTime);
  var value = dateObject.toISOString().split("T")[1].split(".")[0];
  console.log(value); // 00:10:00 and 10:00:00
}

• Unix时间
• DATEVALUE函数
• 关于var unixTime = (serialNumbers[i] - 25569) * 86400 * 1000

• Unix time
• DATEVALUE function
• About var unixTime = (serialNumbers[i] - 25569) * 86400 * 1000

如果这不是您想要的结果，我表示歉意.

If this was not the result you want, I apologize.

将类似"00:10:00", "10:00:00"的字符串转换为序列号时，以下脚本如何处理?在此脚本中，流程如下.

When the string like "00:10:00", "10:00:00" is converted to the serial number, how about the following script? In this script, the flow is as follows.

1. 将字符串转换为日期对象.
2. 将日期对象转换为unix时间.
3. 将Unix时间转换为序列号，例如0.006944444445252884和0.4166666666678793.

1. Convert string to date object.
2. Convert date object to unix time.
3. Convert unix time to serial number like 0.006944444445252884 and 0.4166666666678793.

var durations = ["00:10:00", "10:00:00"];
for (var i = 0; i < durations.length; i++) {
  var ISO8601 = "1899-12-30T" + durations[i] + "Z";
  var dateObject = new Date(ISO8601);
  var serialNumber = (dateObject.getTime() / 1000 / 86400) + 25569; // Reference: https://stackoverflow.com/a/6154953
  console.log(serialNumber); // 0.006944444445252884 and 0.4166666666678793
}

• 00:10:00和10:00:00放在电子表格的单元格中.
• 使用getValues()检索上述单元格中的值.
• 要从检索到的值中检索序列号.

• 00:10:00 and 10:00:00 are put in cells of Spreadsheet.
• Values from above cells are retrieved using getValues().
• From the retrieved values, you want to retrieve the serial number.

我可以理解您的其他问题.如果我的理解是正确的，那么该示例脚本怎么样?

I could understand about your additional question. If my understanding is correct, how about this sample script?

就这种情况而言，我认为需要考虑时差.因为getValues()检索的值已经是具有时间差的日期对象.因此，在这种情况下，需要删除此偏移量.

As the point of this situation, I think that the time difference is required to be considered. Because the values retrieved by getValues() have already been the date object with the time difference. So in this case, this offset is required to be removed.

在此示例脚本中，假定单元格"A1"和"A2"的持续时间为00:10:00和10:00:00.该示例脚本的流程如下.

In this sample script, it supposes that the cells "A1" and "A2" has 00:10:00 and 10:00:00 as the duration. The flow of this sample script is as follows.

1. 使用getValues()从单元格中检索值.
2. 获取时差.
3. 从获取的值中删除时间差.
4. 获取序列号.

1. Retrieve values from cells using getValues().
2. Retrieve the time difference.
3. Remove the time difference from the retrieved values.
4. Retrieve serial number.

示例脚本:

var sheet = SpreadsheetApp.getActiveSheet();
var values = sheet.getRange("A1:A2").getValues();
var offset = (new Date()).getTimezoneOffset(); // Retrieve the time difference.
for (var i = 0; i < values.length; i++) {
  var dateObject = new Date(values[i][0].getTime() - (1000 * 60 * offset));
  var serialNumber = (dateObject.getTime() / 1000 / 86400) + 25569; // Reference: https://stackoverflow.com/a/6154953
  Logger.log(serialNumber) // 0.006944444445252884 and 0.4166666666678793
}

这篇关于使用GAS获取持续时间的等号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！