Connecting Google Sheets and Google Calendar

With Automate.io, you can connect Google Sheets to Google Calendar or vice versa. Do not confuse this with two-way syncing where data flows back and forth between two apps. 

This article will show you both ways to set up an integration between these two apps. 

Now, before working with Google Sheets, it’s important to set up your Google spreadsheet properly. Check out the 'Setting up your Google Spreadsheet' section in this article to learn how to do that

Once that is set up, you can proceed with the integration. 

Create Google Calendar events from Google Sheets

This integration comes in handy when you want to create Google Calendar events in bulk. Click on the button below to get started with the integration immediately.

Connect the Trigger app - Google Sheets:

  • Click on Create a Bot at the top of your dashboard. You will land on this page.
  • Click ‘Select Trigger app’ and search for ‘Google Sheets’. A window opens which asks you to sign in to your Google Sheets account. 
  • Enter the sign-in details. Click ‘Allow’ and then click ‘Save’. You’ve connected Google Sheets to Automate.io.

Select the Trigger:

  • Select the Trigger, ‘New Row’ from the drop-down.
  • Select the spreadsheet and worksheet. The column names in the worksheet appear in the ‘Output fields’ section.

Connect the Action app - Google Calendar:

  • Click ‘Select Action app’ and search for ‘Google Calendar’. A window appears which asks you to sign in to your Google Calendar account. 
  • Enter the sign-in details. Click Allow and then click Save. You’ve connected Google Calendar to Automate.io.

Select the Action:

  • Select the Action, ‘Add an Event’.
  • Choose the calendar from the Calendar drop-down.
  • Map the fields that you want from Google Sheets to Google Calendar.

Save & test:

  • Click ‘Save’ on the top-right.
  • Turn the Bot ON.
  • Now perform the test using any of the two ways shown here.

You can learn how to test your Bot here.

Important: Please note that the date and time fields on Google Sheets have to be in this format: DD-MMM-YYYY HH:MM:SS. Otherwise, your Bot will show the following error:

Error: Failed to parse date and time

To resolve this error, you need to convert the date & time fields in Google Sheets to the format mentioned above. You can do that conversion using Automate.io’s Formatter app.

How to convert date & time using Automate.io’s Formatter?

Follow the steps below to learn how to convert date & time using Formatter.

  • Click the + button between Google Sheets’ Trigger and Google Calendar’s Action. A popup appears asking you if you want to insert a new Action. Click ‘Proceed’.
  • Search for ‘Formatter’, select it, and choose the ‘Format Date’ trigger from the drop-down.
  • Drag the ‘Start time’ field (the name can be different on your spreadsheet) from Google Sheets’ ‘Output Fields’ to the ‘Input Date/Time’ field in the Formatter app. Under ‘Input Format’, select the format of the ‘Start time’ field. If you are unsure about its format or you don’t find the option, choose ‘Auto Detect’.
  • Under ‘Target Format’, from the drop-down, select the format you wish to convert it to. In most cases, what you may want is to convert it into Automate.io's standard format (which is provided in the drop-down), and then select the time zone you wish to convert it to. The converted ‘Start time’ field will be stored in ‘Formatted Date/Time’ in Formatter’s Output Fields.
  • Now, drag the ‘Formatted Date/Time’ field to the relevant field in your Google spreadsheet.
  • To convert the ‘End time’ field, repeat the above five steps.

Update events in Google Calendar on new rows in Google Sheets

This integration comes in handy when you want to make changes or update existing Google Calendar events. To get started immediately, click on the ‘Connect Now’ button below.

Connect the Trigger app - Google Sheets:

  • Click on Create a Bot at the top of your dashboard. You will land on this page.
  • Click ‘Select Trigger app’ and search for ‘Google Sheets’. A window opens which asks you to sign in to your Google Sheets account. 
  • Enter the sign-in details. Click ‘Allow’ and then click ‘Save’. You’ve connected Google Sheets to Automate.io.

Select the Trigger:

  • Select the Trigger, ‘New or Updated Row’ from the drop-down.
  • Select the spreadsheet and worksheet. The column names in the worksheet appear in the ‘Output fields’ section.

Connect the Action app - Google Calendar:

  • Click ‘Select Action app’ and search for ‘Google Calendar’. A window appears which asks you to sign in to your Google Calendar account. 
  • Enter the sign-in details. Click Allow and then click Save. You’ve connected Google Calendar to Automate.io.

Select the Action:

  • Select the Action, ‘Update an Event.’
  • Choose the calendar from the Calendar drop-down.
  • Map the Event ID field with the ID data from Google Sheets. Usually, this data is not available. So, the alternate way is to use the Search feature next to it.
  • Click Search. Select ‘Proceed’.
  • Select the Calendar from the drop-down. Enter or map the search term from Google Sheets. It can be the event title or description or location or any event data from Google Sheets. Remember that this value should match exactly with the event data that is in Google Calendar.
  • Select the Time Range if needed. This tells the Bot the period when the event is being held. 
  • This process fetches the matching event data into the Output Fields including the Event ID.
  • Additionally, it is recommended to add a Filter here to make sure your Bot works as expected. This ensures that the event data you’re searching for matches exactly with the event data in Google Calendar. You can do that by providing a few conditions in the filter. The Bot continues only if it satisfies a certain condition or a series of conditions mentioned in the Filter. You can visit this page to learn more about how to use a Filter in your Bot.

Note: The operations performed by the Filter are not counted as actions.

  • Now, map the fields from Google Calendar’s ‘Search an Event’ action Output Fields to Google Calendar’s ‘Update an Event’.

Save & test:

  • Click ‘Save’ on the top-right.
  • Turn the Bot ON.
  • Now perform the test using any of the two ways shown here.

You can learn how to test your Bot here.

Create rows in Google Sheets on new Google Calendar events

This integration will be useful when you want to catalog all of your Google Calendar events. Click on the ‘Connect Now’ button below to get started immediately.

Connect the Trigger app - Google Calendar:

  • Click on Create a Bot at the top of your dashboard. You will land on this page.
  • Click ‘Select Trigger app’ and search for ‘Google Calendar’. A window opens which asks you to sign in to your Google Calendar account. 
  • Enter the sign-in details. Click ‘Allow’ and then click ‘Save’. You’ve connected Google Calendar to Automate.io.

Select the Trigger:

  • Select the Trigger that you want from the drop-down.
  • Select the Calendar. The event details will appear in the ‘Output fields’ section.

Connect the Action app - Google Sheets:

  • Click ‘Select Action app’ and search for ‘Google Sheets’. A window appears which asks you to sign in to your Google Sheets account. 
  • Enter the sign-in details. Click ‘Allow’ and then click ‘Save’. You’ve connected Google Sheets to Automate.io.

Select the Action:

  • Select your desired Action.
  • Choose the spreadsheet and worksheet from the drop-down menu.
  • Map the fields that you want from Google Calendar to Google Sheets.

Save & test:

  • Click ‘Save’ on the top-right.
  • Turn the Bot ON.
  • Now perform the test using any of the two ways shown here.

You can learn how to test your Bot here.

Did this answer your question?