0

I want to record when an App Script run inside a spreadsheet. This time will be used by other formulas in the spreadsheet, to calculate the time between now and the last time the app script has run.

The formulas on the spreadsheet use the NOW() function, which returns a decimal number of days since 30/12/1899. The timezone of this epoch is dependent on the timezone of the sheet, see 18596933.

I need to use App Script to insert the current time into a spreadsheet cell using the same format as NOW(). I have been trying to use Javascript Date's to do this, however these dates are based on the App timezone, which may be different from that of the spreadsheet.

How can I create a App Script version of NOW(), which returns the same value as NOW() regardless of spreadsheet or script timezone differences?

Community
  • 1
  • 1
Daniel
  • 1,239
  • 1
  • 13
  • 24

1 Answers1

0

My workaround doesn't return the number of days, with fractional part, as NOW() does. Instead, it returns a string, which App Script appears to handle correctly in formulas which also contain NOW().

function getSpreadsheetTime() {
  var spreadsheetTimeZone = SpreadsheetApp.getActive().getSpreadsheetTimeZone();
  var spreadsheetDateTime = Utilities.formatDate(new Date(), spreadsheetTimeZone, "dd/MM/yyyy' 'HH:mm:ss.S");

  return spreadsheetDateTime;
}

This is based on the answer from: Google Apps Script formatDate using user's time zone instead of GMT

Community
  • 1
  • 1
Daniel
  • 1,239
  • 1
  • 13
  • 24
  • Note that in the newer version of Google Sheets, the results of Apps Script functions can be cached, so it won't always update in a predictable manner. – Steve Lieberman Apr 14 '14 at 14:54