Function NOW() + Setting Timezone (Google Sheets)

Question

I live in Brazil (UTC offset of - 3:00) and here we have something called daylight saving time, where at some times of the year the clocks are advanced by 1 hour.

What happens is that I have a whole spreadsheet ready to filter data in the England time zone.

I had a big problem today because Google's times have changed and my data has been filtered the wrong way.

My original formula in script was:

spreadsheet.getCurrentCell().setFormula('=text(now()+"5:00","yyyy/mm/dd hh:mm")');

And now many hours later I realized the failure and needed to switch to:

spreadsheet.getCurrentCell().setFormula('=text(now()+"4:00","yyyy/mm/dd hh:mm")');

So that this no longer happens, I would like to know how I could add the England time zone within the NOW () function.

I tried:

spreadsheet.getCurrentCell().setFormula('=text(now("UK"),"yyyy/mm/dd hh:mm")');

But failed to return. I use a trigger every 1 minute to update the time in the spreadsheet, I would like to know what I could do to solve this problem.

Detail: I could change the time in the spreadsheet settings, but I need it saved in local time.

Answer 1

• You want to use the different timezone without changing the local timezone of Spreadsheet.
• You want to put the time converted to the different timezone to the Spreadsheet as a text.
• You want to achieve this using Google Apps Script.

If my understanding is correct, how about this answer?

Issue and Workaround:

Unfortunately, in the current stage, NOW() which is the built-in function has no options for changing the timezone. If the timezone of NOW() is changed, it is required to change the timezone of Spreadsheet. But in your case, the timezone of Spreadsheet cannot be changed. So as a workaround, I would like to propose to use Utilities.formatDate().

Sample script:

The sample script is as follows.

function NOW2(timeZone, format) {
  return Utilities.formatDate(new Date(), timeZone, format);
}

function myFunction() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  spreadsheet.getCurrentCell().setFormula('=NOW2("Europe/London", "yyyy/mm/dd hh:mm")');
}

• NOW2() is used as the custom function. At the sample script, when myFunction() is run, =NOW2("Europe/London", "yyyy/mm/dd hh:mm") is put to the current cell. By this, the current time is put by converting the timezone to Europe/London as the format of yyyy/mm/dd hh:mm.

Note:

• If you want to refresh the custom function of =NOW2("Europe/London", "yyyy/mm/dd hh:mm") when the Spreadsheet is opened, please use the following script as the simple trigger.

  function onOpen(e) {
    e.source.createTextFinder("=NOW2").matchFormulaText(true).replaceAllWith("temp");
    e.source.createTextFinder("temp").matchFormulaText(true).replaceAllWith("=NOW2");
  }
  

• If you want to use above using the time-driven trigger, please use the following script. In this case, the current total runtime of triggers and the current runtime of custom function are 90 min / day and 30 sec / execution, respectively. Ref Please be careful this.

  function myFunction() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    ss.createTextFinder("=NOW2").matchFormulaText(true).replaceAllWith("temp");
    ss.createTextFinder("temp").matchFormulaText(true).replaceAllWith("=NOW2");
  }
  

References:

• NOW
• formatDate()

If I misunderstood your question and this was not the result you want, I apologize.