Startec

Startec

Google Sheets ‚Äď How to Automatically Post Events to Google Calendar with Apps Script

Mai 24, às 21:44

·

5 min de leitura

·

0 leituras

In this article we'll link two Google services -> Google Sheets and Google Calendar. By using a very short custom function in Google Apps Script, we can add a list of events from a Google Sheet to a Google Calendar. ūü§Į And we'll even have it email our guests as
Google Sheets ‚Äď How to Automatically Post Events to Google Calendar with Apps Script
Google Sheets ‚Äď How to Automatically Post Events to Google Calendar with Apps Script

In this article we'll link two Google services -> Google Sheets and Google Calendar.

By using a very short custom function in Google Apps Script, we can add a list of events from a Google Sheet to a Google Calendar. ūü§Į

And we'll even have it email our guests as well. ūüĒ•

Here's the video walkthrough to accompany the article:

Google Sheets Setup

Our sheet is quite straightforward. We have event names, dates, start times, end times and guest emails.

The only curious thing is the formatting of our dates and times - I'll cover this as we go on, but you can see that columns B and C are repeating information from columns D, E and F...

No alt text provided for this image
screenshot of Google Sheet event information

Google Calendar needs to receive the start and end times in the form of a full date/time object. But in the Google Sheet, there's not an easy way to create a dropdown data validation for users to select a date/time object.

In column D, I've put data validation to select a valid date.

No alt text provided for this image
screenshot of data validation for a valid date

And in columns E and F, I've created a dropdown list of valid times.

No alt text provided for this image
screenshot of data validation for a valid time

Columns B and C combine these together into a format that's useable to send to Google Calendar by using the =TEXT() function to concatenate the date and times together.

No alt text provided for this image
screenshot of google sheets text concatenation

I promise it'll make more sense in a second! ūüėÉ

Calendar Setup

Let's make a new calendar in Google Calendar.

No alt text provided for this image
Screenshot of new calendar options

Underneath your calendars on the left sidebar of Google Calendar, click the plus icon to add a new one.

Give it a name and a description if you want, and then we're ready to roll.

No alt text provided for this image
Screenshot of creating a new calendar

Scroll down a bit in the calendar's settings to the Integrate Calendar section. Copy the calendar ID. This is how we'll get Apps Script talking to Calendar!

No alt text provided for this image
screenshot of calendar ID

Apps Script + CalendarApp

Apps Script is awesome. ūüĎŹ

The Class CalendarApp allows a script to access a user's Google Calendar and make changes to it.

Here is the full script, and we'll walk through what's going on below.

// Creates an events variable which is an array of arrays
function createCalendarEvent() {
	let events = SpreadsheetApp.getActiveSpreadsheet().getRangeByName("events").getValues();
	// Creates an event for each item in events array
	events.forEach(function(e){
 	CalendarApp.getCalendarById("f7574e7b4d1ad00c9ecd7f1eba5bed329e8600e317cd387a400748d67f301d06@group.calendar.google.com").createEvent(
 	e[0],
 	new Date(e[1]),
 	new Date(e[2]),
 	{guests: e[6],sendInvites: true}
 );
 })
}

I've named the range A3:B8 as "events". Then in Apps Script, we create a variable named events that grabs all the values in that whole range. We used a small range, but you could make this as many rows long as need be.

let events = SpreadsheetApp.getActiveSpreadsheet().getRangeByName("events").getValues();

Then, we loop through each item and add the events to our calendar.

The first part is where we use that calendar ID string we grabbed from Google Calendar for the getCalendarById method.

Then we use the createEvent method to pull data from each row in our Google Sheet and make new events.

Here's the createEvent description from the developers page:

No alt text provided for this image
screenshot of createEvent method

You can think of each row of data in the Google Sheet as an array of values. In the zero position is the event name, in the one position is the event date and start time, and so on.

No alt text provided for this image
screenshot of array of arrays illustrated

By using e[0] we can access the element that is in the zero position for every time we loop through the forEach loop...effectively looping through each row of data.

And this is where the funky stuff we did with the start and end times comes into play.

Because the values in columns B and C are strings since we concatenated them together, we need to turn them back into complete date objects now.

That's why we're passing new Date(e[1]) and new Date (e[2]) into our createEvent function.

It's a bit of a cumbersome way to allow ourselves to use those dropdown selections in Google Sheets rather than painfully typing in a full date/time object.

User experience > code. ūüĎć

And lastly, we add an optional parameter to send invites to guests.

Send with Button

That's all there is to the Apps Script. ūüéČ

As an added feature, we've attached a script to the rounded rectangle drawing to make it function like a button. Any time this is pressed, the events in the Google Sheet will populate the Google Calendar.

No alt text provided for this image
Screenshot of assigning a script to a drawing in Google Sheets

I hope this has been useful for you!

Please come check out and subscribe to my YouTube channel where I'm making weekly videos on coding and spreadsheets.

If you'd like my newsletter in your inbox, check it out here.



Learn to code for free. freeCodeCamp's open source curriculum has helped more than 40,000 people get jobs as developers. Get started


Continue lendo

DEV

Authentication system using Golang and Sveltekit - Dockerization and deployments
Introduction Having built out all the features of our application, preparing it for deployment is the next step so that everyone around the world will easily access it. We will deploy our apps (backend and...

Hoje, às 19:52

DEV

LEARN API AND ITS MOST POPULAR TYPE
An API (Application Programming Interface) is a set of rules and protocols that allows different software applications to communicate and interact with each other. It defines the methods, data structures, and...

Hoje, às 19:26

AI | Techcrunch

Investors take note: Wildfire smoke will spark a surge in East Coast climate tech startups
As smoke from Canadian wildfires has enveloped large swathes of the East Coast, millions of people have found themselves trapped inside, gazing out on orange skies and hazy cityscapes. The air quality index ‚ÄĒ...

Hoje, às 18:08

DEV

A Plain English Guide to Reverse-Engineering the Twitter Algorithm with LangChain, Activeloop, and DeepInfra
Imagine writing a piece of software that could understand, assist, and even generate code, similar to how a seasoned developer would. Well, that’s possible with LangChain. Leveraging advanced models such as...

Hoje, às 18:08

DEV

Finding Harmony in Marketing and UX
When we think of teamwork in the world of user experience (UX), we often imagine design and engineering working together. However, the idea of design and marketing working together is not as common. While...

Hoje, às 17:02

DEV

ūüí°¬†Where to Find Inspiration for Building Your Next App
The first steps before turning your ideas into code. Whenever I’m trying to think of an idea to build a new application or website and I get stumped on what to do, there’s one phrase that always comes to...

Hoje, às 16:58

DEV

How to create 700+ SEO optimised pages for website in 1 h using Next.JS, OpenAI, Postgres
Small intro, I started learning coding couple of months before and since then experimenting with different small side projects. So this I show coding still looks for me:) What did I build this...

Hoje, às 16:37

DEV

Angular Project Mongodb database Connect | Angular Website Project | Angular App
Angular Project Mongodb database Connect | Angular Website Project | Angular App - YouTube ‚Äč @softwaretechit Download Our App:- https://blog.softwaretechit.com/p/download.htmlWhat will we Learn In This...

Hoje, às 16:10

AI | Techcrunch

Meta warned it faces 'heavy sanctions' in EU if it fails to fix child protection issues on Instagram
The European Union has fired a blunt warning at Meta, saying it must quickly clean up its act on child protection or face the risk of ‚Äúheavy sanctions‚ÄĚ. The warning follows a report by the Wall Street...

Hoje, às 16:03

DEV

Taking Control with PostgreSQL Functions: Closing the Gap to ORM Functionality
Unveiling the Disparity: Understanding the Divide Between Direct Driver and ORM Functionality When it comes to choosing the technologies for developing a backend and manipulating data in a database like...

Hoje, às 16:02