Preparing data for Google Calendar

So it seemed quite straightforward. My husband works irregular shifts at the BBC and his monthly shifts appear in a table on the BBC Intranet. But he would like to import those shift times into his Google Calendar. Here’s how I did it. (This may not be the most efficient way so do please add a comment if you can streamline the process). By working through this problem, I picked up a lot of useful little new tricks along the way which proves the general point that the best way to pick up new tech skills is to give yourself a project to work through.

Grab the data

The shifts are in a table. You need to copy and paste it directly into a spreadsheet. I’m using Google here. This isn’t the actual data from my husband’s timetable. For privacy purposes I’ve made it up but the format is the same as the original.

Spreadsheet pix

I’ve also reduced it to just 5 rows of data so it’s easier to see what I’m doing. In real life, for a data set this small, it would probably be quicker just to type the shifts into your calendar. But the original is much longer than this so the process is worth it.

At this point, you may want to do some tidying up. Sometimes the descriptions of the shift can run over to an adjacent cell or down to the next row so you might need to copy & cut & paste those so they’re in the right place.

Google Calendar is fussy

Very fussy! You have to get the column headings for each column absolutely right or it pretends it can’t see the data. The column headers you need are Subject, Start Date, Start Time, End Date, End Time with the capital letters in the correct place. So let’s put some of those in where we can. We can also delete those blank rows. You end up with this.

NewSpreadsheet pix

So our spreadsheet is looking pretty neat now BUT Google Calendar still won’t like it. We’ve got the start time and end time in the same cell which is no good. And the date format is wrong. Google Calendar only recognises dd/mm/yyyy so we need to get rid of the day abbreviation. 

Split cells in Google Spreadsheet

This is a useful function (which can be done in Google Refine too as explained in an earlier blog post) but we’re going to stick with the spreadsheet to save all that import/export nonsense.

Let’s deal with the Start Date column first. We need to get rid of the day of the week so we’re just left with dd/mm/yyyy. We’re going to use a Function to separate the data we don’t want from the data we do want. The function is called SPLIT. In column D2, write this function =SPLIT(B:B,” “). That means you’re selecting column B as your range of data. Then the separator (in this case a space) is in inverted comas. You might need to grab the bottom right-hand corner of the corrected cell and drag it down to apply the formula down the length of the column. You should end up with this.

Split pix

Delete the surplus columns. Duplicate the column with the correctly formatted dates because you can use that for your End Dates.

NB This SPLIT function is very useful when you want to split full names into separate columns for first name and surname, for example.

Format Times in Google Spreadsheet

Now we need to look at those times. We have two problems here. The start and end time are in the same column so we need to split them in much the same way as we split the date above. But there’s an additional problem. Google Calendar likes the time in the format hh:mm (I told you it was fussy).

We’re going to deal with the second problem first. Highlight two empty columns. We’re going to format these columns so that when numbers appear in them, the spreadsheet will automatically convert them into the format we want. So once the columns are highlighted, click on Format>number>more formats>custom number format.

In the text box at the top, type in 0#:00 (this gets round the problem that some times may have been input as just 3 digits instead of 4). You won’t notice anything happening at this stage. But wait…..!

At the top of the first of those columns you highlighted and formatted, write another SPLIT function like this =SPLIT(C:C,”-“). This means I want the spreadsheet to split column C (I’ve moved things around a bit so that’s where my times are now) at the separator – (hyphen). Not only will the times now be split into two columns (representing Start Time and End Time) but they will be automatically formatted as HH:MM as we wanted!! Again, to make this apply to the whole column, you may need to grab the bottom right-hand corner of the first cell and drag it down to the end of the column. Voila!

Cleaning messy spreadsheet

So everything should now be correctly formatted. But the spreadsheet probably looks a bit of a mess with surplus columns and columns in the wrong place (remember how fussy Google Calendar is). We can’t just start deleting and moving around though because a lot of the values we can see are dependent on functions we carried out on columns we’re going to delete. Once the column is deleted, the spreadsheet can’t perform the function to generate the values. So we need to do some copy&pasting into empty cells.

BUT we can’t just do an ordinary copy and paste of, say, the newly formatted times. (Try it and see what happens!). Instead we need to use the Special Paste option which you’ll find in the Edit tab. Select “Paste Value” and you’ll get the actual number. You might need to reformat it again to hh:mm.

Once you’ve done all that, deleted surplus columns, rewritten the headers in the way Google Calendar likes them, in the right order etc etc, your spreadsheet should look like this.


Finished pix

Pretty, huh?

Import spreadsheet into Google Calendar

We’ve finally made it. First, you need to download your spreadsheet as a .csv file.

Go to your Google Calendar. Go to Settings (Click on the wheel icon on the top right-hand side). Then select Calendars in the top left-hand corner and half way down, choose Import Calendar. Import your .csv file and, if the Google spiders are on your side, you should see all your events magically appear in your calendar.

ALL DATA IS DIRTY. So there are few good techniques here which are useful for all sorts of situations.

Related Posts

Data Journalism Classroom Activity - using google survey, spreadsheets, maps and Refine

Leave a Reply