Python with Google Sheets Service Account: Step by Step

A couple of days ago I had my first experience with Google Sheets API. I thought it would be an easy task but well… not.

I've been through the official Google API docs and ok, all the methods can be found there but they're lacking one big and important thing: a good walkthrough. After my disappointment with the official website, I did what pretty much everyone would do, Googled it =D. I found much more valuable information and finally after a few hours, lots of sites, lots of errors, code, and some coffee, I was able to get my simple script to work.

After all this trouble I thought it would be interesting to share a simple step-by-step guide to make things happen and so, here I am.

In this case, we will be using a service account, meaning that the authentication will be validated on the back end.

Please make sure you have Python and pip installed. If you don't know how to do that you should look here and here.

Ok, let's begin \o/

The first thing you need to do is to create a project. You know when you authenticate on some app using your Google Account and then you get the message: "some app needs your permission to…"? It's the same thing, we will be creating this "app".

To do that, simply go to Google Console and create a new project. In this case, I named my project Inserting Data Through API.

The next step is to enable the APIs we are going to use. You'll see at the top of this very same page a link called: Enable APIs and Services.

Search for the Google Drive API and Enable it.

By doing this, Google Sheets API should be enabled automatically but it's good to have a second check, just in case.

The next step is to create our credentials. For that, click on Credentials on the left panel and then Create Credentials.

When asked about what kind of credential select Service Account Key.

Click on the Service Account combo and select New Service Account. Give it a name and select a role (Project -> Editor should be more than enough for all purposes we are trying to achieve here).

Select JSON for the key type then click Create. A JSON file will be downloaded. Move this file to the directory where your python script will be created and rename it to client_secret.json.

Good, we are halfway through.

Now we are good to create our spreadsheet in Google Sheets and share it with our credentials. To do that, open your client_secret.json file, copy the client_email information, and share your spreadsheet with this email. Please be certain to share it with the "can edit" option.

Ok, now that all the bureaucratic needs have been attended to, we can finally start writing our script to insert data into our spreadsheet.

The first thing to do is to install the libraries from Google API and that’s why we need pip ;-)

Go to your environment’s terminal app and run pip install google-auth-httplib2 and pip install google-api-python-client. These are both necessary to authenticate our Google’s secret credentials and to connect with the spreadsheet so make sure you have them installed.

As with any Python script, we will begin importing the necessary stuff we need. In this case that would be the Google libraries we've installed before:

The scopes are needed for the type of authorisation you need. More information can be found here.

With the secret file and the scopes, we can authenticate on Google and create a service to list the methods we need.

A very important piece of information that we need now is the spreadsheet_id and this can be found in the URL of your spreadsheet.

Now you can go to infinity and beyond. All you have to do is to get your data, determine the range of the spreadsheet you want to update, and execute the method.

Your full script should look something like this:

And that's all. Simple right? Why complicate so much?

In this case, we have just inserted data into the spreadsheet but if you need all other methods, these can be found here. All you have to do is to change the data, the range and the method.

I truly hope that this tutorial helped you in some way (especially if it saved you hours of search and work \o/)

That's all folks.