This article shows how to automatically create Google Calendar events when a new row is added to a Google Sheet or a new response is submitted to a Google Form.
This automation saves time and reduces missed appointments. It's particularly useful for sales teams, consultants, and any business that wants to schedule an appointment automatically when a form is filled out.
First, we need to connect our Google Form to a Google Sheet, so that new form responses are automatically added to the sheet. In your Google Form, go to the responses tab. Then click on the Link to Sheets button.
You will be prompted to select a destination for responses. Select Create a new spreadsheet. Then click Create.
We will use Lido, a new spreadsheet built to automate repetitive tasks, to connect our Google Sheet 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. From the Lido Files page, click Create a new file
Step 2. then 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 trying to connect.
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. It will look like this:
Our next step is to add another column to the right of your data that has the ADDCALENDAREVENT formula. This formula will add a calendar event in Google Calendar based on values from each row that you specify. The formula syntax 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. If you have already added one, feel free to use it.
Click the "Add Credential" button, then click the "Connect to Google Calendar" button in the new window. A new tab or window will open in your browser allowing you to authenticate with Google.
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.
Get your Google Calendar ID
The next argument we need to add is the 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 can enter the parameters for the 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), but how do we get the ending time?
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).
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. This is going to be a Linked Column to the right of the ADDCALENDAREVENT Computed Column, which we'll set up shortly.
For now, 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)```
Add the "status" Linked Column
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. Hoever 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.
