How to set formatting for just a time duration in a Google Sheet using appScript?-CodePudding

In another program, I calculate the duration of an event. The result is in seconds. In the outside program (using LuaScript), I convert this seconds result into a string as "hh:mm:ss.SSS" format. I then POST this to GoogleSheets via appScript, and I want to write this into the next available row (ss.appendRow). It mostly appears correctly, except it is rounded to the nearest whole second, instead of keeping the milliseconds. GoogleSheets correctly identifies this string as a date/time, but thinks it is an actual time instead of a duration. For example, if the event took 5.75 seconds, the output in the cell shows "00:00:05", and when I select the cell, it actually shows "12:00:05 AM".

Following recommendations I found in this forum, I have tried this code:

function doPost(e){
  var s = SpreadsheetApp.openById(myID);
  var ss = s.getSheetByName("TimeResults");
  var params = JSON.parse(e.postData.contents);

  ss.appendRow([Utilities.formatDate(new Date(params.draftDuration), s.getSpreadsheetTimeZone(), 'hh:mm:ss.SSS'));
  
  return ContentService.createTextOutput('{"status":"ok"}').setMimeType(ContentService.MimeType.JSON);
}

But this resulted in unexpected output, showing "5:00:00 AM". Clearly, I'm using the Date object incorrectly. What should I do instead to make this work as intended, to show a duration, such as "00:00:05.75"?

EDIT Here is a snippet of the JSON that is contained in e.postData.contents:

  "gameDate": 05/07/2022
  "draftDuration": 0:0:3.75
  "gameDuration": 0:0:5.323

CodePudding user response：

If the original value is in seconds as in 5.323 for five seconds and 323 milliseconds, you can convert it to a dateserial value by dividing it like this:

dataserial = seconds / 24 / 60 / 60

Append the resulting value to the spreadsheet and format the cell or the whole column as duration, say [h]:mm:ss.SSS where [h] signifies elapsed hours.

The hours field needs to be expressed as elapsed hours to correctly handle durations that exceed 24 hours. Minutes and seconds never exceed 60 and can be handled the same way regardless of whether they express calendar time or elapsed time.

See this answer for an explanation of how date and time values work in spreadsheets.