Setting the Timezone for timed triggers

问题:

I've written a script that utilizes Google's timed triggers. However, I'm having problems getting the script to fire at the correct time. I reside in Sydney (GMT +10) and need the script to fire at noon each day.

I've gone in and set the timezone in the Spreadsheets settings, the Project Properties as well as the global settings for Google Drive but despite all this the script doesn't fire at the right time. It's off by about 6-5 hrs. What is even more frustrating is when I go into the triggers admin panel the timezone is listed as Pacific time (-8hrs) and as that wasn't enough every other time I log on it seem to change to some other random time soon like Eastern standard etc. (within the triggers panel that is).

I've even tried manually compensating for the time difference but that hasn't worked either and it's ideally not the path I want to go down as it affects the day which the script is triggered (it will be Monday in EST time and Tuesday here in Sydney) which has impacts on the DB query I'm running which is date sensitive.

Either way, I've now drawn a blank can't think of any further solutions. How can I address this?

回答1:

Manually adding the trigger does not set the correct Time Zone but adding it through code does work:

1. Set the appropriate Time Zone to the Script in File > Project Properties > Time Zone.
2. Add the trigger programmatically:

function addTrigger() {
  // main() will be called every day at 7am in your selected time zone
  var everyDay = ScriptApp.newTrigger("main")
      .timeBased()
      .everyDays(1)
      .atHour(7)
      .create();
}

You should see the correct time zone on the "Current project's triggers" view.

回答2:

I had this problem as well. Possibly because I was traveling. Setting the spreadsheet's timezone didn't help. Setting the script's timezone didn't help either.

What did help was using a modified version of @Rodrigo's script:

function addTrigger() {
  // main() will be called weekly on Monday at 16:00 in the specified time zone
  var everyWeek = ScriptApp.newTrigger("main")
      .timeBased()
      .everyWeeks(1)
      .onWeekDay(ScriptApp.WeekDay.MONDAY)
      .inTimezone("America/Los_Angeles")
      .atHour(16)
      .create();
}

You can specify the timezone using .inTimezone() -- see description here.

Once you run this script, you can edit the trigger using the GUI editor. Also, now the timezone is correct for new triggers I add using the GUI -- not sure if that's because of the script or because I'm back home now.

回答3:

I've been caught out by the timezone difference before so I've spent a bit of time thinking about this, but it does make my head hurt sometimes!

Looking at one of my triggers: my Google Account and spreadsheet are set to London time (GMT+0) and I've got triggers set to go off at daily at 4am GMT, so looking at Wednesday (Jan 8th): according to the revision history on the spreadsheet the last triggers went off at 8pm PT (GMT-8) on Tuesday (Jan 7th). Which ties in with them going off as expected at 4am GMT on the 8th (and in my case finding the calendar events for the 8th as they are stored in GMT/UTC).

If you've got your spreadsheet time set to Sydney (GMT+10) the triggers should be going off at the local time you set them too.

So maybe you just need to double-check that, or you could create a test Google account and set everything up from scratch in case you've missed a timezone setting somewhere.

Don't feel like I've given you much, but hope this helps.