Skip to main content

I am having trouble following a vague outline of how to add/update events in a Google Calendar with data added/edited in Google Sheets from this post: 

I started a simple two-step Zap to update a shared calendar when employees submitted a leave request via Google Forms. My Zap just added a calendar event from the original leave request entry, which was one row in a spreadsheet. I quickly realized that supervisor approval, changes to leave dates, and other modifications would need to be updated, too. So, I move to a paid subscription and started exploring the above three-step solution, the essence of which is 1) Google Sheets - New or Updated Spreadsheet Row, 2) Google Calendar - Find event, 3) Google Calendar - Update event.  

I am now stuck on a problem I cannot seem to solve. I get this error message at Test in the third step:

Failed to create an event in Google Calendar 

The app returned "Invalid start time.".

Click on the Troubleshoot tab below or learn more in our help center.

This is what I see with the sample data being passed: 

The dates are formatted as datetime, but Google Sheets uses / as a delimiter rather than - so maybe that is the issue? If so, it seems crazy that I would need to create custom number formatting for the spreadhseet to be able to pass a date via Zapier, so I suspect there is something else going on. 

Any suggestions?  

 

Hi @LloydTravisSmith 

Check the Field description (click the field name to see the field tooltip) for guidance about expected field values.

For us to have more info, post screenshots showing how your Zap steps are outlined and configured in EDIT mode for the ‘Configure’ tab.

TIP: Add a column to your GSheet for “Calendar Event ID” and after you first create the event, update the GSheet row to set the Calendar Event ID in the new column, that way for updates to the GSheet row you have the Calendar Event ID to use for this Zap action ‘GCal - Update Event’, which expects the Calendar Event ID.

 


I went back to try to add the calendar event id field, but I see that the sample data used in testing the Sap steps is not updated no matter what I do. So I started over completely with a new Zap, and I can now see that field, but I am still not figuring this out. 

Here is what I have so far:

First, I use Google Sheets: New or Updated Spreadsheet Row to monitor for new/updated data and trigger the Zap..Second, I use Google Calendar: Find Event with the search term Calendar Event ID. In that second step, I have checked the option to Create Google Calendar Event If It Doesn’t Exist Yet. I think you are saying that I need to add at least one more step to poke the calendar event ID back into the spreadsheet, but I am not sure whether to place it before or after the Google Calendar: Update Event step. 

 

Here are my configure tab snapshots:

 

 

 

 

If I try to add a Google Sheets: Update Spreadsheet Row(s) after the third step, I cannot:

 

Zapier tells me that the third step is incomplete. 

If I try to insert it before the third step, Zapier tells me that I have created a loop.

Any hints? Feeling pretty lost. 


@LloydTravisSmith 

FEEDBACK

  • LOGIC
    • You may want to add another column to use as the trigger column for updates
      • Set this new “update” column as the Trigger Column in the GSheets Zap trigger step
      • Concept being your side would add a unique value to the “update” trigger column to get the Zap to run for updates
        • When you make updates to the “update” trigger column, it needs to be a unique value each time
          • e.g. A > B > C
            • If you use the value A again the Zap won’t retrigger
      • As part of the GCal Update Event PATH (see more below), you’ll want to add a GSheet Update Row step that resets the “update” trigger column back to a common value. (e.g. RESET)
  • PATHS
    • You’ll want to add Paths as Step 2: https://help.zapier.com/hc/en-us/articles/8496288555917
      • Path 1: Create
        • Filter conditions check that the Calendar Event ID column value is empty
      • Path 2: Update
        • Filter conditions checks that the Calendar Event ID column value is not empty
  • In Step 2, your Search Term should not be the Calendar Event ID because that searches the Event Title, which won’t contain your Calendar Event ID
  • Your Start Date & Time can’t be the same as your End Date & Time
    • If you are trying to set an all day event, then the End Date & Time should be the next day to make it a full day

@LloydTravisSmith 

As an alternative, you can use Airtable instead of GSheets.

Airtable has Automations that have native integrations for GCal to:

  • Create Event
  • Update Event

Airtable Automations can be configured to watch for updates to specific fields. (see options below)

 

Airtable Automation triggers:

 


Reply