Practice English Speaking&Listening with: How to automatically add a schedule from Google Sheets into Calendar

Difficulty: 0


SPEAKER: Hey, Joanna, for Friday's event,

I'm trying to make sure our volunteers know where to go.

Is there a way for me to export schedules from our spreadsheet

into our team calendar?

JOANNA SMITH: Oh, so like putting all these

shifts into the event calendar automatically?

SPEAKER: Yeah, exactly.

JOANNA SMITH: Absolutely.

SPEAKER: Oh, awesome.

JOANNA SMITH: You can use Apps Script.

Apps Script is a really easy way to customize your apps

with really simple code.

SPEAKER: Awesome.

So what do we do first?

JOANNA SMITH: Give me one minute.

OK, so I went ahead and set some things up

in the spreadsheet to make it a little easier for us.

First, I reformatted the dates, you can see,

to make them easier for the code to understand.

And I just did this by clicking Format, Number, Date and Time.

Trust me, you do not want to have to learn

how dates work in programming.

And then second, I went ahead and wrote some skeleton code

for you in the script editor.

So if you want to open it up, it's Tools, Script Editor.


So what do I do now?

JOANNA SMITH: Well, any coding task

is much simpler if you break it into smaller tasks first.

And you wanted help syncing a sheet to a calendar.


JOANNA SMITH: OK, so task one, what calendar?

SPEAKER: Our team calendar.

JOANNA SMITH: We need to open that.

Task two, what part of the sheet?

SPEAKER: The part with the dates and shifts.

JOANNA SMITH: OK, so we need those cell numbers.

Task three, we need to actually take that information

and make an event.

And then task four is bonus, and I did it for you

to make it easy for everyone to use.

SPEAKER: Oh, thank you.


So now what?

JOANNA SMITH: Now we begin.

Task number one, opening the calendar.

It's really simple.

The call is CalendarApp.getCalendarByID.

Now, to get the ID, we're going to take it from the public URL.

Go ahead and open this, Calendar Settings, and scroll

down to where it is, because it's not always

the URL in your view.

But if you get that and copy it into the cell

I created in the spreadsheet, our script

can find it every time.

SPEAKER: So what's next?

JOANNA SMITH: Task number two, we

want to pull each shift into a form

that the computer can understand.

To do that, we'll make a call to the spreadsheet, getRange,

and that'll be the cell range, and then getValues,

to turn that into the form that I created for you.

And that's that matrix.

It's a list of lists.

And that way, we can go through each one.

SPEAKER: OK, so what's the next step?

JOANNA SMITH: Task number three, where we do the actual work.

We have our matrix, and now every shift in that matrix,

we're going to call with our Calendar.createEvent,

with a name, a start time, and an end time.

And this is the advantage of Apps Script.

It is that simple.

SPEAKER: So let's say I actually want someone else on my team

to add the shifts.


This is that bonus part that I actually built for you.

I created a custom menu.

As easily as you can click File, Print in Docs,

you can now click up here on our custom menu

and run the script with a click.

SPEAKER: Oh, that's so simple now.


If you have any questions on this pro tip,

be sure to leave them in the YouTube comments below,

or check out some of my other posts.

And for more tips, follow us on Twitter @gsuite.


The Description of How to automatically add a schedule from Google Sheets into Calendar