How to Use a Spreadsheet to Batch-Upload Events to Your Calendar

The other day I posted...

One of my favourite things I've discovered this year is using a spreadsheet to batch-add things to my calendar. Such a timesaver!

— Tommaths (@TeaKayB) June 1, 2020

... and a couple of people asked me how to do it.

To some of you this may be the most obvious thing on the planet, but I only discovered it earlier this year and - my oh my - has it saved me some time. My day* job sees me visiting various places around the country and I keep track of it all in my calendar. As any self-respecting nerd is driven by unknown forces to do, I often put a bit of effort into finding out how I can get the tech around me to do some of the more boring jobs so that I have more time available for doing the interesting things. I'd list some potential use-cases for this spreadsheet-to-calendar trick but I figure that if you've read this far you already know what you want it for.

Before we get started, a disclaimer: Yes, there are plenty of other articles detailing how to do this and I visited many of them. I haven't referenced any of them because it was about six months ago so I've forgotten which ones I used, none of them gave me every piece of information I needed, and there were a few important stumbling blocks that weren't mentioned at all.

Ingredients

You will need:

• 1 spreadsheet. Any flavour (Excel, Google Sheets, Calc, etc) will do as long as it can save/export as a .csv** file type.
• A calendar, such as Outlook or Google Calendar. Others may work but I haven't tried it with them. It won't work with the paper one hanging on your wall.
• Some Things that are Planned.

Method

1. 

Get the things that are planned and put them in your spreadsheet. You'll need a column dedicated to each aspect of the Thing that is Planned: Start date, Start time, End time and Subject are what I consider the basics and should be used as the column headings in any spreadsheet with aspirations of becoming a series of calendar entries. Just like this:

You may want to put in an End Date column if any of your Things that are Planned span multiple dates, you party animal.

Some Potential Stumbling Blocks That Nobody Told Me About:

• Things are slightly different depending on whether you're uploading to an Outlook or Google calendar: Outlook is less fussy with regards to the column headings: when you upload (we'll get to that) it'll take a guess at which column is which then give you the option to check and correct if necessary. There's no such luxury with Google: if you haven't used the correct column headings it'll just fail with an unhelpful error.
• There are other column headings you can use but I'll leave that as a web-search exercise for the reader: anything that you can put in its own field in a calendar entry needs its own column in the spreadsheet.
• You'll want to make sure that the cell formats in your spreadsheet are correct: times need to be formatted as times, and dates formatted as dates, or else things may get confused.
• Similarly, you'll want to make sure that your default date formats in both your spreadsheet software and your calendar are the same: US format in one and UK  in the other and you'll have events all over the place and lots of cleaning up to do.

2.

Save your spreadsheet as a ".csv" file.

The details are slightly different for each format of spreadsheet, but the process is generally similar:

Click the File menu, choose Save As... and in the dialogue that pops up choose the option with CSV in it in the Save as type: list before clicking Save (see the red ring in the screenshot below***).

If you're doing this in a Google sheet you'll need to choose File 🠒 Download 🠒 Comma-separated values.

3.

Upload! It's a bit different for each service:

Upload to Google Calendar:

1. Load your calendar (duh) and open up the settings (the gear icon towards the top right of the window)
2. Choose Settings
3. In the menu bar down the left-hand side of the window, click Import & export, and then Import.
4. Click Select file from your computer, use the dialogue box that pops up to navigate to the file you saved in the previous step, click Open.
5. Choose which of your calendars you want to upload to using the drop-down box below that.
6. Click Import.

Job done! If it doesn't work, chances are you've hit one of the stumbling blocks mentioned previously.

Upload to Outlook Calendar:

Note: I don't use Outlook at home so I'm doing this from memory. Let me know if I miss anything out... Also, I haven't done it via the web version so if the instructions are markedly different please feel free to write them down and send them to me!

1. Open Outlook (duh) and click File 🠒 Open 🠒 Import.
2. A wizard will pop up! Not the Arthurian kind, unfortunately. Choose Import from another program or file and click Next.
3. Choose Comma Separated Values and (you guessed it) click Next.
4. Click Browse and in the window that pops up navigate to, select and open the file you saved earlier. Click Next.
5. Select Calendar then click Next.
6. If you're feeling lucky you can Finish here, but I would recommend clicking Map Custom Fields to make sure Outlook has properly worked out which column you want to map to each field in your calendar entries. You can make some adjustments if necessary. When you're happy that all is right with this little piece of the world, click OK and then Finish.
7. A progress bar will pop up and then...

Job done!

4.

Presto! Your Things that are Planned are now in your calendar!

Final notes

• This is really easy, so if any of the steps above make it seem less so, please get in touch and we'll work out where I've made a boo-boo in my instructions.
• Having said that, if your calendar gets messed up, or you delete the entire internet, or damned-near set off World War III by accident it's nothing to do with me, OK?
• If you're thinking "but this will take longer than just putting them in my calendar manually!" then just put them in your calendar manually. 

* In reality it's quite often an evening job, and a weekend job as well.

** The two asterisks aren't part of the file type. They're indicating a footnote. This one. Anyway, what I wanted to say was that "csv" stands for "comma separated values". Put simply, it saves your spreadsheet as a plain text file in which the contents of cells are separated by commas (obvious when I put it like thtat, no?) instead of the cell borders that you see in an actual spreadsheet. You won't ever see it in that form though.

*** You'll see that I, dirty, stinking, disorganised wretch that I am, saved it to my desktop. You don't have to do this; you could be a better person. Just remember where you put it because you'll need it again in a minute.

---

Learn the basics of Google Sheets in this free course from Alison.