1

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.

Digital Farmer
  • 1,705
  • 5
  • 17
  • 67
  • 1
    `I can't change the time zone in spreadsheet settings,`. Why not? – TheMaster Oct 27 '19 at 13:07
  • Forgive my English, I am Brazilian and sometimes mistake in deciding the words to use ... In fact I wanted to say that: ```I could change the time in the spreadsheet settings, but I need it saved in local time.``` – Digital Farmer Oct 27 '19 at 13:50
  • So, changing to England's timezone in your spreadsheet will solve the issue right? – TheMaster Oct 27 '19 at 14:55
  • @TheMaster Yes ... It would work, but I can't change because I need the records all in my local time. But partner Tanaike managed to make a great choice for my need. Now it's made it a lot easier to use. – Digital Farmer Oct 27 '19 at 15:21
  • This is great. Btw, 'mm' for months needs to be 'MM' since 'mm' represents minute. – MoneyHulk Feb 26 '21 at 20:41

2 Answers2

8
  • 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:

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

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Thank you very much for your answer ... I would just like to ask you a question: As it is a function created by me, it has a limitation of use, correct? Do you think that because I need to update every minute, it could cause me to reach the limit of use and disrupt the worksheet? – Digital Farmer Oct 27 '19 at 13:52
  • 1
    @Frederico Mattos Thank you for replying. Current the total runtime of triggers and the runtime of custom function are 90 min / day and 30 sec / execution, respectively. [Ref](https://developers.google.com/apps-script/guides/services/quotas) I think that in your situation, these become the limitation. In this case, when the custom functions are refreshed by a script with the time-driven trigger, the total runtime of the script by the trigger is required to be less than 90 min / day. If this information was not useful for your situation, I have to apologize. – Tanaike Oct 27 '19 at 21:53
  • I realize this is a 4-year-old thread, but I'm hoping to get a little help from Tanaike, or anyone else who understands how scripts work in Google Sheets. I just went to the Extensions menu in Sheets, selected Apps Script, and pasted in the script. It works perfectly. But how do I use the script to force updates? I have zero experience with Apps Scripts. Where do I put the 2nd script to force updates? – Steven Klein Mar 10 '23 at 16:15
  • @Steven Klein About your question, I would like to support you. But, this question is not your question. So, can you post it as a new question by including more information? By this, it will help users including me think of the solution. If you can cooperate to resolve your question, I'm glad. Can you cooperate to do it? – Tanaike Mar 10 '23 at 22:24
0

Just a suggestion.... I use a "Data" sheet, which holds lookup lists, and various named ranges. I'm in AUS which also has daylight savings time.

I just added a Named Range 'AU_TZ' which I change from 11 (hours) to 10 when our time changes. So to check against UK time, I can use

now() - AU_TZ

Just have to remember to change this one value in my spreadsheet. :)

maxhugen
  • 1,870
  • 4
  • 22
  • 44