Working with Google Sheets

So it is not always the case that you can in Excel, even now at work I have a Google Drive spreadsheet that I need to get some information from. Up until now this has been a manual task as I have not had the chance to look into connecting to Google Drive. 

So here is the data for the speadsheet I am trying to connect to (my daughter chose the colours): 

The data itself is not import, it is just some analysis to see which runners over / under performed at a shorter raced based on every ones times in that race and a longer race. 

Connecting to Google Drive / Sheets:

Now I won't go into too much detail on how you actually connect but basically:

1. Set up or use your Google Developer Account and go to the Google Api Console 
2. Set up access for the Google Drive and Google Spreadsheet Api 
3. This will allow you generate a JSON file that you download 

You then use this file to connect to Google Drive. Note that in this file there is an e-mail and you will need to share your target document with the e-mail address. 

I haven't covered the above in much details as there is Google to find out how to do it! 

GSpread

This is the python package I am using to connect to the Google Drive Sheets, there are others but this is just the one that I came across the instructions for. Although I am not using it here you can use this write to Google sheets as well as reading.

So the code below connect to the Google Drive sheet and extracts the data from the above sheet (the 4th sheet in the wordbook) and then imports it into a pandas dataframe which looks like the below. 

So that is job done, now I can use this data how I like. Put it into a internal report to more likely for me use the outcome for a machine learning algorithm to predict people times next time (yes I know this is OTT given the volume of data and I could just use the equation for the trend line in the graph to calculate it, but where is the fun in that?).

The code: