Quickbase Pipelines to Google Calendar: What to Put in the Time Zone Field

A question in the Quickbase community asked something that trips up a lot of people the first time they connect Quickbase to Google Calendar (original thread here). The asker already had a pipeline adding Google Calendar events whenever a record was added or changed. What they couldn't work out was what to put in the step's Time Zone field.

The short answer: Google wants an IANA time zone name, like America/New_York, America/Chicago, America/Denver or America/Los_Angeles. Abbreviations like EST and offsets like -05:00 are the wrong format.

Below is the full build, from the table fields to the pipeline to testing, including how to handle people in different time zones without hard-coding anything.

What the Time Zone field actually wants

Google's Calendar API describes an event's time zone as "an IANA Time Zone Database name, e.g. Europe/Zurich." The Pipelines step hands your value to Google, so it has to be in that form. These are the US ones you'll use most:

For anywhere else, look up the "TZ identifier" column in Wikipedia's list of tz database time zones. Quickbase's own Pipelines help points to that same list.

A few things to know about these names:

• They're case sensitive and use underscores. It's America/Los_Angeles, not america/los angeles.
• They pick the right offset for you. America/Chicago is UTC-6 in winter and UTC-5 in summer, so you never have to change anything when the clocks change. A fixed offset like -06:00 would be wrong for half the year. That's the main reason Google wants a name.
• The city is just the label for the zone. Use America/Chicago for anyone on Central time, even if they're in Dallas.

Why the time zone matters if the times are already right

Pipelines treats Date/Time values as exact moments. The Quickbase help on dates says it plainly: "All date and times in Pipelines are in UTC. In general, you won't need to do any time zone conversion yourself if you map between Date/Time fields." So if you map a Date/Time field into the event's start, the event lands at the correct moment whatever you put in Time Zone.

What the Time Zone value controls is which zone the event belongs to. Google's docs say it "indicates a custom time zone for the event start/end" on a single event, and that it's required for recurring events, because it decides how the repeats line up across daylight saving changes. It's also the zone Google shows when you open the event's time zone settings, so it's what someone sees when they check "what time is this meeting where it's happening?"

So: get the start and end right with Date/Time fields, and use Time Zone to say where the event happens.

Step 1: Set up the table

I built this in a sandbox app with an Appointments table. You need:

• Subject (Text): becomes the event title.
• Start (Date/Time) and End (Date/Time): use Date/Time fields, not text or plain Date fields, so Pipelines can pass real moments to Google.
• Event Time Zone (Text - Multiple Choice): the zone for this event. More on this in Step 3.

While you're in there, check your app's time zone. Quickbase shows and stores Date/Time entries using the app's time zone (or the account's, if the app doesn't set its own). An app manager can change it in Settings > App properties > Advanced Settings, under Date/Time Options, with the Use this time zone checkbox. If your app is set to Mountain and a Chicago user types 9:00 AM, Quickbase saves 9:00 AM Mountain. That's an app setup issue, not a calendar issue, but it shows up as "the event is an hour off," so it's worth knowing.

If people regularly enter times for other zones, turn on Show time zone in the Start and End field settings (Properties tab, display section). That adds Quickbase's time zone helper next to the field, so someone can type "9:00 AM Central" and Quickbase converts it to app time.

Step 2: Build the pipeline

1. In Pipelines, create a new pipeline.
2. Trigger: add a Quickbase trigger on your Appointments table for when a record is created. Add a filter so it only runs when Start and End aren't empty, so half-filled records don't throw errors.
3. Action: from the Google Calendar channel, add Add an Event. If this is your first Google step, connect your Google account in the step's connection section and allow the calendar permissions when Google asks.
4. Pick the calendar the events should go on.
5. Map the fields. Drag your trigger record's fields into the step:
   • Subject into the event's title
   • Start into the start date/time
   • End into the end date/time
   • Anything else you want (description, location, attendees) into the matching inputs
6. Time Zone: either type a zone name or map a field into it. That's the next step.

Map the Date/Time fields directly. Don't turn them into text with date filters first. Quickbase's help warns against using those filters "to transform dates between date fields in two different channels," because Pipelines already handles the format.

Step 3: Fill in the Time Zone field

If every event is in one zone

Type the name straight into the step's Time Zone field, for example America/Denver. You're done.

If events happen in different zones

Don't hard-code it. Map a field instead.

1. On the Appointments table, make Event Time Zone a Text - Multiple Choice field.
2. Add the IANA names you need as the choices (for example America/New_York, America/Chicago, America/Denver, America/Phoenix, America/Los_Angeles), and turn off letting users add their own choices. A pick list means nobody can type Eastern or EST and break the pipeline.
3. In the Add an Event step, map Event Time Zone into the Time Zone input.

If you want friendlier labels for users, you can keep a separate choice field with labels like "Eastern" and use a formula to turn them into the IANA names. For a handful of zones, though, the IANA names are readable enough to use as the choices directly.

Optional: Get the zone from the office or the person

Often the zone isn't really a property of the appointment. It comes from whoever or wherever the appointment belongs to. In that case, store it once on the parent and look it up.

In my sandbox I added an Offices table with an Office Time Zone multiple choice field using the same IANA choices, related Offices to Appointments, and brought Office Time Zone over as a lookup field. Then I added a Formula - Text field on Appointments called Calendar Time Zone:

If(
  Trim([Event Time Zone]) <> "", [Event Time Zone],
  Trim([Office Time Zone]) <> "", [Office Time Zone],
  "America/Denver"
)

It uses the appointment's own zone if someone set one, falls back to the office's zone, and ends with a default so the value is never blank. Here's what it returned for three test records:

Map Calendar Time Zone into the step's Time Zone input instead of Event Time Zone.

The same idea works with people. If each appointment has an assigned employee, put a Time Zone field on the Employees table and look it up through that relationship.

Updating events when the record changes

The asker's pipeline also updated events. You need to know which Google event belongs to which record, so add a text field such as Google Event ID to the table. After the Add an Event step, add a Quickbase step that updates the triggering record and writes the new event's ID (from the Add an Event step's output) into that field.

When the record changes later, a second pipeline (triggered on update) can use that stored ID to find the event with the Google Calendar steps (Look Up an Event, Move Event, Delete Event and so on) and change it. The step list on your account may be a little different, so check the Google Calendar channel in the step picker. Whatever you use, map the same Time Zone value there too, so an edited event doesn't drift to a different zone from the one it was created in.

Common errors and how to fix them

• Google rejects the time zone. Usually the value is an abbreviation (EST, CST), an offset (-05:00), a typo, or has the wrong case or spaces. Switch to an exact IANA name from a multiple choice list.
• The step fails or uses a default when the zone field is blank. Make sure the field always has a value, with a default in the formula like the one above, or a filter on the trigger.
• The event is exactly one or more hours off. That's almost always the app time zone or how the time was entered, not the Time Zone input. Check the app's time zone setting and look at the Start value in Quickbase. If it's wrong there, it will be wrong in Google.
• Recurring events drift by an hour after daylight saving changes. Recurring events need a named zone. A fixed offset can't follow the clock change.
• The end time is before the start. Google won't accept an empty or backwards time range. Add a data rule or a trigger filter so End is after Start.
• Arizona events are an hour off for half the year. Use America/Phoenix, not America/Denver, since most of Arizona doesn't observe daylight saving.

Testing it

1. Make a test calendar in Google so you're not filling a real one with junk events.
2. Add two or three records in different zones, like the Sarah Lee, Mike Chen and Priya Patel records above.
3. Open the pipeline's activity log and confirm each run succeeded. If one failed, the Google step's error message usually names the field it didn't like.
4. In Google Calendar, open each event and check its time zone in the event details. The time should match the moment in Quickbase, and the zone should be the one you mapped.
5. Edit a record's time and confirm the update pipeline moves the event instead of making a second one.

Once it's working, the setup is simple: Date/Time fields for when, an IANA name for where, and a pick list so nobody can enter the zone wrong.