Google Spreadsheets and Python

Google Spreadsheets and Python

This post is inspired by Patrick McKenzie’s reminder that sometimes you don’t need a database:

In this tutorial, we’ll use Anton Burnashev’s excellent gspread Python package to read, write, and delete data from a Google Spreadsheet with just a few lines of code. 

Google Drive API and Service Accounts

At the risk of being Captain Obvious, you’re going to need a spreadsheet if you want to follow along with this post. If you don’t have one on hand that’s full of juicy data, might I suggest you make a copy of this spreadsheet with contact information for all United States legislators(Side note: Ian Webster uses this data in conjunction with Twilio to make it easy for citizens to call congress).

 

To programmatically access your spreadsheet, you’ll need to create a service account and OAuth2 credentials from the Google API Console. If you’ve been traumatized by OAuth2 development before, don’t worry; service accounts are way easier to use.

Follow along with the steps and GIF below. You’ll be in and out of the console in 60 seconds (much like Nic Cage in your favorite Nic Cage movie).

  1. Go to the Google APIs Console.
  2. Create a new project.
  3. Click Enable API. Search for and enable the Google Drive API.
  4. Create credentials for a Web Server to access Application Data.
  5. Name the service account and grant it a Project Role of Editor.
  6. Download the JSON file.
  7. Copy the JSON file to your code directory and rename it to client_secret.json

 

There is one last required step to authorize your app, and it’s easy to miss!

Find the  client_email inside client_secret.json. Back in your spreadsheet, click the Share button in the top right, and paste the client email into the People field to give it edit rights. Hit Send.

If you skip this step, you’ll get a gspread.exceptions.SpreadsheetNotFound error when you try to access the spreadsheet from Python.

We’re done with the boring part! Now onto the code.

Read Data from a Spreadsheet with Python

With credentials in place (you did copy them to your code directory, right?) accessing a Google Spreadsheet in Python requires just two packages:

  1. oauth2client – to authorize with the Google Drive API using OAuth 2.0
  2. gspread – to interact with Google Spreadsheets

Install these packages with:

Then paste this code into a new file called spreadsheet.py:

Run python spreadsheet.py and marvel at the glorious, well-formatted data.

 

Insert, Update, and Delete from a Spreadsheet with Python

We’ve just scratched the surface of gspreads’ well documented and comprehensive functionality. 

For instance, we extracted the data into a list of hashes, but you can get a list of lists if you’d prefer:

Or you could just pull the data from a single row, column, or cell:

You can write to the spreadsheet by changing a specific cell:

Or you can insert a row in the spreadsheet:

You can also delete a row from the spreadsheet:

And find out the total number of rows:

Check the gspread API reference for the full details on these functions along with a few dozen others. 

Using Google Spreadsheets with Python opens possibilities like building a Flask app with a spreadsheet as the persistence layer, or importing data from a Google spreadsheet into Jupyter Notebooks and doing analysis in Pandas. If you want to start playing with Python and Twilio, check out our Python quickstarts.

If you build something cool, please let me know. You can find me at gb@twilio.com or @greggyb. And if this post was helpful, please share it with someone else who might dig it. 

Many thanks to Devin and Sam for the reviews, to Google for Sheets, and most of all, to Anton for gspread. 

  • Jed

    Keep an eye on https://github.com/nithinmurali/pygsheets which supports v4 of the sheets API. Might already help speed up inserts into large sheets, for example.

  • pizzapanther

    The big downside I’ve found using this a lot in the past is that Google has no SLA for sheets. Sure its Google so it has high availability but things can quite often fail with no support from Google. So just be careful of building anything mission critical with this. The API can be wonky every once in a while. At one point it even took them like 6 months to fix an issue I had with the Apps Script API. If my business relied on that functionality, I would have been screwed.

    The Jupyter Notebook use case sounds great but if you move to something that is more critical use a real data store.

    • ZoubIWah

      same. you need to handle a lot of random error conditions in your scripts because google can go from basically cutting you off (“connection lost”) to random 500 errors and even 404 for the exact same request.

      And of course you then need to pool until the API works again.

      In addition to that some of the queries are extremely slow.

      Basically it feels like a 3rd class citizen at best. Now then again I fully use it for years and do not intend to stop.

    • Andrey Fedorov

      > just be careful of building anything mission critical with [Google Sheets as a backend]

      wat

      • Well, you know, sometime you build an MVP, PoC, or prototype, and it gets deployed to production by management fiat (also known as protoduction).

  • Dilshod

    Thank you Greg for the great tutorial. I would like to know what program did you use for gif animations. It would be useful if you share it too. Thanks again.

    • @cosecantt:disqus I would love to share because Camtasia is perhaps my favorite piece of software installed on my machine! It’s the “it just works” of screencasting. https://www.techsmith.com/camtasia.html

      • James Abela

        I second that! Its an amazing piece of software… Once you learn it, you can knock out screencasts for any lesson you please with very little notice.

  • Excellion

    Hello, great tutorial. Thanks. Is there any API to download an entire spreadsheet as an MS Excel?

    • Alan

      import pandas as pd

      your_data = sheet.get_all_values()
      your_data = pd.DataFrame(your_data)
      your_data.to_csv(‘filename.csv’)

      Open csv in Excel and convert it to .xlsx if you like

      • Dustin

        Or use Pandas to save it as Excel.

  • Chris Emery

    It seems it maybe a good use case for distributed development troubleshooting.
    Maybe using hexagonal programming structure would allow a development app. to switch between sample, problem and production data in a simplified and distributed fashion.

  • IssaKnife

    For some reason I am unable to install gspread and oauth2client. I get an error that says Exception:
    Traceback
    then lists a bunch of file paths followed by:
    Permission denied: ‘/usr/local/lib/python2.7/dist-packages/httplib2-0.9.2-py2.7.egg/EGG-INFO/PKG-INFO’

    Any advice?

    • James Abela

      This is a wonderful tutorial and thanks for making it.

      To address some of the comments, if you want to be less dependent on Google Sheets, but still want access to data on it. You can publish to CSV file. Python can then pull this data in from the web. (Or of course you can download a CSV file, which I’ve done in the past for static data)

  • Jur

    If you just want to read data in as a CSV and don’t mind your data being public, your life can be a lot easier if you use this URL format to download a public Google Sheet as CSV:

    I find this the easiest way to get Google Spreadsheet data into a jupyter notebook for data analysis with pandas.

    • Jan Oglop

      Let me add that line separators are “rn” … I’ve been fighting with it whole day

  • zhangjingqiang

    I also write a blog about write data to google spreadsheet. All of them using raw google v4 api. — http://qiang-blog.herokuapp.com/blogs/use-google-api-write-local-file-into-google-spreadsheet

  • Bruno

    Thanks for this quick tutorial, very useful!

    > At the risk of being Captain Obvious, you’re going to need a spreadsheet if you want to follow along with this post

    I looks like it’s possible to create a spreadsheet with gspread: https://github.com/burnash/gspread#creating-a-spreadsheet
    Was it omitted on purpose? Is it because it needs to be shared by the script if done that way?

  • Lee

    I’ve used Sheets for some simple data hosting before, but gave Fieldbook a try as the backend when knocking up a simple static site I found the API much simpler and the interface and functionality slightly better than Sheets (for my purpose at least). There’s some details here: http://viewfinderdesign.co.uk/archive/791/using-fieldbook-as-a-static-site-data-source/

  • Hello, I run into the following error ;

    python spreadsheet.py
    Traceback (most recent call last):
    File “spreadsheet.py”, line 12, in
    sheet = client.open(“Cost of Foods In Kampala”).sheet1
    File “/usr/local/lib/python2.7/dist-packages/gspread/client.py”, line 82, in open
    feed = self.get_spreadsheets_feed()
    File “/usr/local/lib/python2.7/dist-packages/gspread/client.py”, line 155, in get_spreadsheets_feed
    r = self.session.get(url)
    File “/usr/local/lib/python2.7/dist-packages/gspread/httpsession.py”, line 73, in get
    return self.request(‘GET’, url, params=params, **kwargs)
    File “/usr/local/lib/python2.7/dist-packages/gspread/httpsession.py”, line 65, in request
    response = func(url, data=data, params=params, headers=request_headers, files=files, json=json)
    File “/usr/lib/python2.7/dist-packages/requests/sessions.py”, line 467, in get
    return self.request(‘GET’, url, **kwargs)
    TypeError: request() got an unexpected keyword argument ‘json’

    • Von Foerster

      I’ve got the same one.
      Did you solve it? Can you help?
      Thanks for the tutorial!

      • I had to upgrade the requests library which meant moving to Python 3.x from 2.7. That’s how I solve the issue.

  • Thanks for the awesome tutorial!

    I’m mainly into motion graphic design, and a really a newbie to coding and stuff like APIs. I started learning Python some 2 weeks ago primarily to make automated data visualization animations in Cinema 4D, and now it’s a huge step forward that I can use Google Spreadsheet data. I really apreciate this tutorial :)

    My only problem is that while this stuff works like a charm on my standalone Python 3.6 installation, but I can’t find out how to install oauth2client and gspread modules into the Python of Cinema 4D. Anyone has some ideas?

  • Thanks for this. Super useful and clear!

  • Ron Serruya

    Thanks ! This helped me a lot

  • Manikandan K

    hello..i want a webapp with google spreadsheet database where one can search a data and results mst be displayed as webpage

  • Brandon Moore

    I still get the gspread.exceptions.SpreadsheetNotFound error even though I hit send on the client email. <

    My gmail inbox had this message,

    Your message wasn't delivered to gspread@gspreadupdater-169815.iam.gserviceaccount.com because the domain gspreadupdater-169815.iam.gserviceaccount.com couldn't be found. Check for typos or unnecessary spaces and try again.

    any advice?

  • Wenhao Xuan

    Will I need to create a new project in the Google APIs Console for a new spreadsheet?

  • Sunny ke

    Thank you for sharing. This is very helpful.
    I tried to convert it to executable from py2exe. When I ran, I got error:

    Traceback (most recent call last):
    File “spreadsheet.py”, line 8, in
    File “gspreadclient.pyc”, line 402, in authorize
    File “gspreadclient.pyc”, line 61, in login
    File “oauth2clientclient.pyc”, line 545, in refresh
    File “oauth2clientclient.pyc”, line 749, in _refresh
    File “oauth2clientclient.pyc”, line 780, in _do_refresh_request
    File “oauth2clienttransport.pyc”, line 282, in request
    File “httplib2__init__.pyc”, line 1659, in request
    File “httplib2__init__.pyc”, line 1399, in _request
    File “httplib2__init__.pyc”, line 1319, in _conn_request
    File “httplib2__init__.pyc”, line 1069, in connect
    File “httplib2__init__.pyc”, line 96, in _ssl_wrap_socket
    IOError: [Errno 2] No such file or directory

    • gif

      did you find out something? I’m still suffering from the same bug

  • Fernando Simao

    Thanks a lot! Very good job!! Fernando Simão from Rio de Janeiro, Brazil.

  • Erik

    Get an error when trying to import ServiceAccountCredentials:

    TypeError: unsupported operand type(s) for |: ‘frozenset’ and ‘list’

    • Curtis Salisbury

      I am getting the same error as well.

  • omer

    Hi thank for this tutorial. But I get the next error:

    Traceback (most recent call last):
    File “C:/Users/Drew/PycharmProjects/Real Estate/googlesheetstest.py”, line 6, in
    creds = ServiceAccountCredentials.from_json_keyfile_name(‘client_secret.json’, scope)
    File “C:Python27libsite-packagesoauth2clientservice_account.py”, line 219, in from_json_keyfile_name
    with open(filename, ‘r’) as file_obj:
    IOError: [Errno 2] No such file or directory: ‘client_secret.json’

    • Charles Cuthbert

      Omer: I have the same problem. Did you manage to fix it?

      I saved the ‘client_secret.json’ file in oauth2client directory, but it does not work:
      C:UserscharlesAppDataLocalProgramsPythonPython36-32libsite-packagesoauth2client”

      Charles

      • Oliv698

        With iPython, my json file is located in the same repository as my ipynb file. with a Python script, it should be the same, no?

        • Oliv698

          sorry, in the root repository of my iPython installation (with Anaconda, my user folder with W7

  • Alan Torres

    I think this is a great tutorial. Something not super clear is why did you add Editor to your service account? I didn’t notice the account being used for anything that requires Editor access (other than sheets permissions, but those are handled independently).

  • Steve Shaw

    Can I use this to access all files in a specified folder as opposed to just a single sheet? Also how would I access a file within a folder in drive?

  • Razvan Zaharia

    Following your youtube tutorial I have been able to upload sensors data from an Arduino to Google Sheets. Thank you! I have however encountered the following problem: after one hour of logging the authorization is no longer valid. If I restart it works like a charm. I tried to reauthorize at a shorter time interval but I don’t seem to get it right!
    Any advice?
    Thanks again for the tutorial

    • Razvan Zaharia

      Forget that. It’s not about one hour or any other time interval, it’s just Google not responding some time. I got it working for 7 hours straight and then I got this – File “C:Python27libsite-packagesrequestsadapters.py”, line 490, in send raise ConnectionError(err, request=request) requests.exceptions.ConnectionError: (‘Connection aborted.’, error(10060, ‘A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond’))
      I guess I just have to ignore these errors and try to connect again.
      ???

  • Kyaw Thura Maung

    Hello!