Edit Google Sheets from a Python console with few lines of code

Available at https://github.com/opsabarsec/editGoogleSheets

A common request that you may get from colleagues not used to programming “could you please pass me these results saved to Excel format?”. Or “we write data every day into this spreadsheet, we would like to automate an analysis every x days”. Having to download, save and send csv files every time at this point it is not efficient, but you may work with people that want spreadsheets only, no code. In such a case, a couple of Python libraries can really become handy as you use them to read from and write into the spreadsheet directly on Google Drive.

The procedure can be broken into two steps: acquiring the credentials and actual coding to convert spreadsheets into Pandas dataframes and viceversa.

• Get the access token using your Google account

This step is rather long and describing it would make this post a bit too wordy. But the good new is that you do it only once. My tip is to follow the instructions on one of the many Youtube tutorials that guide you through the creation of a Json file containing the access strings to spreadsheets in a google drive account. For example, this one. Start from Google Developer Console and once you complete the procedure get back to this post. Together with the token in the Google Developer Console you will have created a service account of the type

somename@project-x-python.iam.gserviceaccount.com

It is important that you share to this email address each spreadsheet you would like to modify later on. When you share it make sure that you set the access as “editor”.

• Edit the spreadsheet from your Jupyter notebook

First of all you will need to import the Python libraries “gspread” and “oauth2client”. Eventually install them if they are not installed yet

!pip install gspread
!pip install oauth2client

import gspread
from oauth2client.service_account import ServiceAccountCredentials

At this point you can create a function to read the data contained in the first sheet of your Google Sheets file. It is important to note the file ID beforehand and paste it into the function from your browser navigation bar

Sheet ID copied from the browser

Then you can insert it in the decorator as shown below

Decorator read data from a worksheet in Google Sheets

By entering the command df_hist = get_historicalData() in the next cell you obtain a dataframe containing a time series with various columns corresponding to COVID data recorded in 2020. We can then write data directly into this G-Drive Sheet.

• appending data into a worksheet

Let’s append to these some data from a csv file on your local machine. Firstly you read these data using Pandas, with the command

df_recentdata = pd.read_csv(‘logfile.csv’).fillna(0)

To append the data of this dataframe to the worksheet in Google drive you need again gspread and oauth2client. The decorator I used is shown below.

This time not only the file ID but also the worksheet name is specified

Decorator to append data into a worksheet in Google Sheets

You may not only want to append data from a dataframe but to

• write a dataframe into a worksheet

eventually overwriting already existing data. For example, you created some new stats and you want them saved as a separate dataframe.

By simply changing “append” to “update” the new dataframe is written to the desired worksheet.

Decorator to add and overwrite worksheet data in Google Sheets

It is worth noting that one more line has to be added in order to include the dataframe headers. The values we obtain with the command below are a list of lists where each corresponds to one row

Values object from the decorator

“df.columns.values.tolist” corresponds to the headers. By placing those at the first position of the list of lists “values” (values[0]) we make sure that every time the dataframe headers are written into the desired worksheet.