Google Sheets – Use Negative DURATION Formatted Cell in Google Scripts

google sheetsgoogle-apps-script

I'm using Google Spreadsheets and I have a sheet in the following format:

Start | End     | Partial result | Expected | Total
08:00 | 12:00   | 04:00          | 05:00    | -01:00
14:00 | 18:00   | 04:00          | 05:00    | -01:00
----------------------------------------------------
                                 | Month tot| -02:00

As you can see, it allows for negative duration stamps. This is causing me a lot of trouble in Google Scripts.

I want to email everyone a monthly summary.

Here's a simple snippet:

var sheet = SpreadsheetApp.getActive().getSheetByName(...);
var monthlyTotal = sheet.getRange(1, 1).getValue(); //The total is at cell A1

Now, when I debug, it shows my monthlyTotal as a Date object, set to Tue Dec 26 1899 14:50:28 GMT-0300 (BRT) (this is quite correct, my result is -81:16:00, it subtracted 81:16 from 1900-01-01 00:00:00, no issues there).

Now, I have no idea how to get back to a duration format (-81:16). I have tried converting this date object to a negative timestamp, but +monthlyTotal returns what I believe to be a wrong value (-2212132172000).

I have no idea how to turn a Date object from before 1900 to a timestamp and, as a consequence, I don't know how to proceed.

Best Answer

Short answer

Don't use .getValue() on date/time cells. Instead, put =TO_TEXT(A1) somewhere (or already have A1 containing something like TO_TEXT(SUM(E2:E)), and use .getValue() to get that string. Numbers and strings pass between sheets and scripts reliably, unlike dates and times.

Explanation

Unlike the Unix operating systems for which the epoch begins at 1970-01-01, Google Sheets use December 30, 1899 0:00:00 as 0 time, which you can see by entering =TO_DATE(0) anywhere in your spreadsheet.

In principle, you could apply .valueOf() to the duration value returned by .getValue(): this returns the JavaScript timestamp, which is the number of milliseconds from the beginning of 1970. Dividing by 1000 gives the Unix timestamp.

However, this is problematic because Unix timestamp and consequently JavaScript timestamp are measured from the moment 1970-01-01 00:00:00 in UTC, while the 0 mark of Google Sheets is in the local timezone.

If you try to adjust for timezones using new Date().getTimezoneOffset(); be warned that script time zone may be different from sheet time zone: documentation says this is "a frequent source of scripting bugs". The method Spreadsheet.getSpreadsheetTimeZone() gives the timezone of Spreadsheet itself, but it's returned as a string like 'America/New York', which does not directly translate to an offset.

Related Topic