This article shows how to automatically create Google Calendar events for each row in a Google Sheet, including when new rows are added.
This automation saves time and reduces missed appointments. It's particularly useful for sales teams, marketing teams, and any business that wants to schedule appointments directly from a Google Sheet.
We will use Lido, a new spreadsheet built to automate repetitive tasks, to connect our Google Sheets data to Google Calendar. There is a free tier with paid plans as well. You can create a new account here: https://www.lido.app/go/signup
Step 1. Click Create new File
Step 2. In the upper left corner of the new file, click Connect Data and select Google Sheets.
Next, paste in your Google Sheet URL. If it's your first time connecting a Google Sheet to Lido, you'll need to authenticate into a Google account that has access to the sheet you're connecting.
Select all of the columns that you want to connect to Lido.
Click Add Data.
Your Lido spreadsheet should now have connected data from your Google Sheet in a green table. When new data is added to your Google Sheet, click Refresh to update it in Lido.
Next, we will add another column to the right of your data that has Lido's ADDCALENDAREVENT formula. This formula will create a calendar event in Google Calendar based on values from each Google Sheets row that you specify.
The formula is:
=ADDCALENDAREVENT(calendar_credential, calendar_id, title, description, start_date, end_date, status_cell, [attendees])
Start typing =ADDCALENDAREVENT in the first row of data (Row 2 in this example). This will bring up a dropdown to add a Google Calendar Credential.
Click the "Add Credential" button, then click the "Connect to Google Calendar" button. A new tab or window will open in your browser allowing you to authenticate with Google. Connect to the account where you want to send the calendar events from.
Once you've finished the Google authentication, you will be brought back to Lido, and you can click the "Update" button to automatically place your new Google Calendar credential into the ADDCALENDAREVENT formula.
Next in the formula we need to add your Google Calendar ID.
1. Go to Google Calendar and find the calendar you want to use with Lido on the left sidebar. Mouse over the name of the calendar, click the three dots that appear, then click "Settings and sharing".
2. Then, on the navigation for the settings page on the left-hand side, click "Integrate calendar" (you may need to click the name of your calendar again to make it appear).
Your browser should scroll down the page to that section. The first item you should see is the Calendar ID. Copy this text, then go back to Lido. Select one of the cells you were editing before (it may have an error, this is fine!) and hit Enter to edit it.
3. Finally, paste the Calendar ID between double-quotes for the second argument. Your Calendar is now connected to the ADDCALENDAREVENT formula.
Next, we will enter the details for our calendar event. The title can be anything you want. For this example, we want "Meeting with <customer's name>". We can use the concatenation operator & to assemble this title. For the third argument (title), we'll use:
```="Meeting with " & A2.```
This will combine the "Meeting with " part with the values in the first column, which are our customer names.
For the description, we'll just use the "meeting agenda" column, so we can enter C2.
We have a "meeting time" column that we can use for the start_date argument (D2).
To calculate the ending time for each meeting, we'll assume for this example that all the meetings should last an hour. Then we can just add an hour to the start_date, and use that for the end_date argument: D2 + TIME(1, 0, 0). Alternatively, if you have a column with end times, you can just reference that as well.
The last required argument is the status_cell. This is where ADDCALENDAREVENT will output a "success" message if it runs successfully, or output error information otherwise.
For status cell, just use the cell directly to the right of the one we're editing, which in this case is F2.
Then hit Enter, and your ADDCALENDAREVENT Computed Column is finished!
For this example, our formula looks like this:
```=ADDCALENDAREVENT(<calendar-credential>, "ben@trylido.com", "Meeting with "&A2, C2, D2, D2+TIME(1, 1, 0), F2)```
Our formula will work as is, but it'll be better to have the status cell as part of a Linked Column.
To create the Linked Column for the status values:
1. Hover your mouse over the right of the customers table, click the Add Column icon, and select "Add Linked Column".
2. Select a unique ID column to use for the Linked Column (in this case "email") and click "Save". The Linked Column will keep track of the status values and make sure they always stay with their associated rows, even if some get re-ordered or removed.
You can also rename the Computed and Linked columns if you'd like. Just edit the header cells and change the name in the window that appears.
To try it out, right-click one of the ADDCALENDAREVENT cells and click "Run action".
You should see a "success" message appear in the Status column and a new event appear in your Google Calendar.
If something went wrong, you'll see an error message appear in the status column instead.
We can also optionally invite other people to our event.
In our example meeting use case, we can use this to automatically send them an email about the event (and if we also use Google Meet, they can even join the meeting from the email). Suppose we also want to add our head of sales to every meeting, just in case they want to meet with the customer as well.
All we have to do is add the attendees argument to ADDCALENDAREVENT. We already have the customer emails, and we'll assume that the email for the head of sales is sales@lido.app.
Then for the second argument, we can use the ARRAY function to join the two emails together into a single argument:
ARRAY(B2, "sales@lido.app").
We can add as many attendees as we want by just adding more emails to the ARRAY function. So the final formula will look like:
```=ADDCALENDAREVENT(<calendar-credential>, "ben@trylido.com", "Meeting with "&A2, C2, D2, D2+TIME(1, 1, 0), F2, ARRAY(B2,"sales@lido.app"))```
If you have many events you want to create at once, choose "Run Column Now" from the Add Cal Event column menu. For every cell where there is an ADDCALENDAREVENT formula displaying, it will run the formula.
To set it up so that a Calendar event is automatically created for each new Forms submission, we want to automate the running of the "Add Cal Event" column on a regular schedule.
Do the following:
IF(F2 <> "success",ADDCALENDAREVENT(...etc))
You'll notice for the test event you have already created, the value FALSE will be there instead of displaying the action formula. Otherwise, it would create a new event for every row each time the column is run.
