How do I access (read, write) Google Sheets spreadsheets with Python?

Question

I am wondering if you can point me to an example of reading/writing to/from a google doc/spreadsheet using python.

I did look at google docs API here https://developers.google.com/google-apps/spreadsheets/ but not sure if I hit the right link. Also an example will be of great help.

What I am trying to do is query spreadsheets based on the different columns more like a SQL query and then do some downstream parsing with the data and put it into another spreadsheet or doc at google docs.

Best, -Abhi

Answer 1

(Jun-Dec 2016) Most answers here are now out-of-date as: 1) GData APIs are the previous generation of Google APIs, and that's why it was hard for @Josh Brown to find that old GData Docs API documentation. While not all GData APIs have been deprecated, all newer Google APIs do not use the Google Data protocol; and 2) Google released a new Google Sheets API (not GData). In order to use the new API, you need to get the Google APIs Client Library for Python (it's as easy as pip install -U google-api-python-client [or pip3 for Python 3]) and use the latest Sheets API v4+, which is much more powerful & flexible than older API releases.

Here's one code sample from the official docs to help get you kickstarted. However, here are slightly longer, more "real-world" examples of using the API you can learn from (videos plus blog posts):

• Migrating SQL data to a Sheet plus code deep dive post
• Formatting text using the Sheets API plus code deep dive post
• Generating slides from spreadsheet data plus code deep dive post
• Those and others in the Sheets API video library

The latest Sheets API provides features not available in older releases, namely giving developers programmatic access to a Sheet as if you were using the user interface (create frozen rows, perform cell formatting, resizing rows/columns, adding pivot tables, creating charts, etc.), but NOT as if it was some database that you could perform searches on and get selected rows from. You'd basically have to build a querying layer on top of the API that does this. One alternative is to use the Google Charts Visualization API query language, which does support SQL-like querying. You can also query from within the Sheet itself. Be aware that this functionality existed before the v4 API, and that the security model was updated in Aug 2016. To learn more, check my G+ reshare to a full write-up from a Google Developer Expert.

Also note that the Sheets API is primarily for programmatically accessing spreadsheet operations & functionality as described above, but to perform file-level access such as imports/exports, copy, move, rename, etc., use the Google Drive API instead. Examples of using the Drive API:

• Listing your files in Google Drive and code deep dive post
• Google Drive: Uploading & Downloading Files plus "Poor man's plain text to PDF converter" code deep dive post (*)
• Exporting a Google Sheet as CSV blog post only

(*) - TL;DR: upload plain text file to Drive, import/convert to Google Docs format, then export that Doc as PDF. Post above uses Drive API v2; this follow-up post describes migrating it to Drive API v3, and here's a developer video combining both "poor man's converter" posts.

To learn more about how to use Google APIs with Python in general, check out my blog as well as a variety of Google developer videos (series 1 and series 2) I'm producing.

ps. As far as Google Docs goes, there isn't a REST API available at this time, so the only way to programmatically access a Doc is by using Google Apps Script (which like Node.js is JavaScript outside of the browser, but instead of running on a Node server, these apps run in Google's cloud; also check out my intro video.) With Apps Script, you can build a Docs app or an add-on for Docs (and other things like Sheets & Forms).

UPDATE Jul 2018: The above "ps." is no longer true. The G Suite developer team pre-announced a new Google Docs REST API at Google Cloud NEXT '18. Developers interested in getting into the early access program for the new API should register at https://developers.google.com/docs.

UPDATE Feb 2019: The Docs API launched to preview last July is now available generally to all... read the launch post for more details.

UPDATE Nov 2019: In an effort to bring G Suite and GCP APIs more inline with each other, earlier this year, all G Suite code samples were partially integrated with GCP's newer (lower-level not product) Python client libraries. The way auth is done is similar but (currently) requires a tiny bit more code to manage token storage, meaning rather than our libraries manage storage.json, you'll store them using pickle (token.pickle or whatever name you prefer) instead, or choose your own form of persistent storage. For you readers here, take a look at the updated Python quickstart example.

Answer 2

Have a look at GitHub - gspread.

I found it to be very easy to use and since you can retrieve a whole column by

first_col = worksheet.col_values(1)

and a whole row by

second_row = worksheet.row_values(2)

you can more or less build some basic select ... where ... = ... easily.

Answer 3

I know this thread is old now, but here is some decent documentation on Google Docs API. It was ridiculously hard to find, but useful, so maybe it will help you some. http://pythonhosted.org/gdata/docs/api.html.

I used gspread recently for a project to graph employee time data. I don't know how much it might help you, but here's a link to the code: https://github.com/lightcastle/employee-timecards

Gspread made things pretty easy for me. I was also able to add logic in to check for various conditions to create month-to-date and year-to-date results. But I just imported the whole dang spreadsheet and parsed it from there, so I'm not 100% sure that it is exactly what you're looking for. Best of luck.

Answer 4

Take a look at gspread port for api v4 - pygsheets. It should be very easy to use rather than the google client.

Sample example

import pygsheets

gc = pygsheets.authorize()

# Open spreadsheet and then workseet
sh = gc.open('my new ssheet')
wks = sh.sheet1

# Update a cell with value (just to let him know values is updated ;) )
wks.update_cell('A1', "Hey yank this numpy array")

# update the sheet with array
wks.update_cells('A2', my_nparray.to_list())

# share the sheet with your friend
sh.share("[email protected]")

See the docs here.

Author here.

Answer 5

The latest google api docs document how to write to a spreadsheet with python but it's a little difficult to navigate to. Here is a link to an example of how to append.

The following code is my first successful attempt at appending to a google spreadsheet.

import httplib2
import os

from apiclient import discovery
import oauth2client
from oauth2client import client
from oauth2client import tools

try:
    import argparse
    flags = argparse.ArgumentParser(parents=[tools.argparser]).parse_args()
except ImportError:
    flags = None

# If modifying these scopes, delete your previously saved credentials
# at ~/.credentials/sheets.googleapis.com-python-quickstart.json
SCOPES = 'https://www.googleapis.com/auth/spreadsheets'
CLIENT_SECRET_FILE = 'client_secret.json'
APPLICATION_NAME = 'Google Sheets API Python Quickstart'


def get_credentials():
    """Gets valid user credentials from storage.

    If nothing has been stored, or if the stored credentials are invalid,
    the OAuth2 flow is completed to obtain the new credentials.

    Returns:
        Credentials, the obtained credential.
    """
    home_dir = os.path.expanduser('~')
    credential_dir = os.path.join(home_dir, '.credentials')
    if not os.path.exists(credential_dir):
        os.makedirs(credential_dir)
    credential_path = os.path.join(credential_dir,
                                   'mail_to_g_app.json')

    store = oauth2client.file.Storage(credential_path)
    credentials = store.get()
    if not credentials or credentials.invalid:
        flow = client.flow_from_clientsecrets(CLIENT_SECRET_FILE, SCOPES)
        flow.user_agent = APPLICATION_NAME
        if flags:
            credentials = tools.run_flow(flow, store, flags)
        else: # Needed only for compatibility with Python 2.6
            credentials = tools.run(flow, store)
        print('Storing credentials to ' + credential_path)
    return credentials

def add_todo():
    credentials = get_credentials()
    http = credentials.authorize(httplib2.Http())
    discoveryUrl = ('https://sheets.googleapis.com/$discovery/rest?'
                    'version=v4')
    service = discovery.build('sheets', 'v4', http=http,
                              discoveryServiceUrl=discoveryUrl)

    spreadsheetId = 'PUT YOUR SPREADSHEET ID HERE'
    rangeName = 'A1:A'

    # https://developers.google.com/sheets/guides/values#appending_values
    values = {'values':[['Hello Saturn',],]}
    result = service.spreadsheets().values().append(
        spreadsheetId=spreadsheetId, range=rangeName,
        valueInputOption='RAW',
        body=values).execute()

if __name__ == '__main__':
    add_todo()

Answer 6

You could have a look at Sheetfu. The following is an example from the README. It gives a super easy syntax to interact with spreadsheets as if it was a database table.

from sheetfu import Table

spreadsheet = SpreadsheetApp('path/to/secret.json').open_by_id('<insert spreadsheet id here>')
data_range = spreadsheet.get_sheet_by_name('people').get_data_range()

table = Table(data_range, backgrounds=True)

for item in table:
    if item.get_field_value('name') == 'foo':
        item.set_field_value('surname', 'bar')              # this set the surname field value
    age = item.get_field_value('age')
    item.set_field_value('age', age + 1)
    item.set_field_background('age', '#ff0000')             # this set the field 'age' to red color

# Every set functions are batched for speed performance.
# To send the batch update of every set requests you made,
# you need to commit the table object as follow.
table.commit()

Disclaimer: I'm the author of this library.

Answer 7

This thread seems to be quite old. If anyone's still looking, the steps mentioned here : https://github.com/burnash/gspread work very well.

import gspread
from oauth2client.service_account import ServiceAccountCredentials
import os

os.chdir(r'your_path')

scope = ['https://spreadsheets.google.com/feeds',
     'https://www.googleapis.com/auth/drive']

creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope)
gc = gspread.authorize(creds)
wks = gc.open("Trial_Sheet").sheet1
wks.update_acell('H3', "I'm here!")

Make sure to drop your credentials json file in your current directory. Rename it as client_secret.json.

You might run into errors if you don't enable Google Sheet API with your current credentials.

Answer 8

I think you're looking at the cell-based feeds section in that API doc page. Then you can just use the PUT/ GET requests within your Python script, using either commands.getstatusoutput or subprocess.