Google表格脚本中的日期(持续时间)

本文介绍了Google表格脚本中的日期(持续时间)的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个包含三列的Google表格:-日期和时间(时间戳)- 期间-说明

I have a Google Sheet with three columns: - Date and time (timestamp) - Duration - Description

我有一个脚本，当我在描述"中编写内容时，此刻将在此日期和时间以及持续时间"中插入日期":

I have an script that when I write something in 'Description', inserts in 'Date' the date and time at this moment, and the 'Duration':

function onEdit(e) { if(e.source.getActiveSheet().getName() == "Sheet2" ) { var col = e.source.getActiveCell().getColumn(); if(col == 3 ) { // I'm in column three var cellTimeStamp = e.range.offset(0,-2); // First column of the same row var cellTimeDiff = e.range.offset(0,-1); // Second column of the same row var cellTimePrev = e.range.offset(-1,-2); // First column of the previous row var timeTimeStamp = new Date(); var iniTime = cellTimePrev.getValue().getTime(); var finTime = timeTimeStamp.getTime() ; var timeDiff = String(finTime - iniTime) ; cellTimeStamp.setValue(timeTimeStamp); cellTimeDiff.setValue(timeDiff); // [***] } } }

在持续时间"(Duration)列中执行此操作(作为事件)时，格式为"HH:mm:ss".

When this executes (as an event) in the column of 'Duration' there is NOT something in the format of 'HH:mm:ss'.

但是，如果我删除此脚本中的最后一行并将此公式添加到工作表中，则:= A3-A2(在第3行中)= A4-A3(在第4行中)...那就可以了.

But if I remove the last line in this script and adds this formulae in the sheet: =A3-A2 (in row 3) =A4-A3 (in row 4) ... then it works ok.

我想知道如何使用脚本来达到相同的结果.

I'd like to know how to meet the same result but with a script.

谢谢.

推荐答案

timeDiff是 finTime-iniTime 的结果，它们都是本地日期对象值，这意味着我们有毫秒数.

timeDiff is the result of finTime - iniTime which are both native date object values, which means we have milliseconds .

将其转换为hh:mm:ss是简单的数学运算...:一分钟60秒，一小时60分钟...一个简单的代码可能是这样的:

converting that in hh:mm:ss is simple math... : 60 seconds in a minute and 60 minutes in an hour... A simple code could be like this :

function msToTime(s) { var ms = s % 1000; s = (s - ms) / 1000; var secs = s % 60; s = (s - secs) / 60; var mins = s % 60; var hrs = (s - mins) / 60; return hrs + ':' + mins + ':' + secs; // milliSecs are not shown but you can use ms if needed }

如果您更喜欢按常规格式设置字符串格式(每个值2位数字)，请不要忘记可以使用 Utilities.formatString()这样做.

If you prefer formating your string more conventionally (2 digits for each value) don't forget you can use Utilities.formatString() to do so.

以下示例:

return Utilities.formatString("%02d",hrs) + ':' + Utilities.formatString("%02d",mins) + ':' + Utilities.formatString("%02d",secs);

编辑

您的评论如下:

电子表格比您想象的要聪明，您可以尝试下面的代码，您会发现结果实际上是时间值.(双击检查)

Spreadsheets are smarter than you think, you can try the code below and you will see that the result is actually a time value.(check by double clicking on it)

function test() { var sh = SpreadsheetApp.getActiveSheet(); var t1 = sh.getRange('a1').getValue().getTime(); var t2 = sh.getRange('b1').getValue().getTime(); sh.getRange('c1').setValue(msToTime(t1-t2)).setNumberFormat('hh:mm:ss'); } function msToTime(s) { var ms = s % 1000; s = (s - ms) / 1000; var secs = s % 60; s = (s - secs) / 60; var mins = s % 60; var hrs = (s - mins) / 60; return hrs + ':' + mins + ':' + secs; // milliSecs are not shown but you can use ms if needed }

请注意，其中 setNumberFormat('hh:mm:ss')是可选的，仅用于强制电子表格显示 hour:min:sec 格式，但自动模式也可以.

note that setNumberFormat('hh:mm:ss') is optional, it's only there to force the spreadsheet to display hour:min:sec format but automatic mode works as well.