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


Answers

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.

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




You might try using the AuthSub method described in the Exporting Spreadsheets section of the documentation.

Get a separate login token for the spreadsheets service and substitue that for the export. Adding this to the get_spreadsheet code worked for me:

import gdata.spreadsheet.service

def get_spreadsheet(key, gid=0):
    # ...
    spreadsheets_client = gdata.spreadsheet.service.SpreadsheetsService()
    spreadsheets_client.email = gd_client.email
    spreadsheets_client.password = gd_client.password
    spreadsheets_client.source = "My Fancy Spreadsheet Downloader"
    spreadsheets_client.ProgrammaticLogin()

    # ...
    entry = gd_client.GetDocumentListEntry(uri)
    docs_auth_token = gd_client.GetClientLoginToken()
    gd_client.SetClientLoginToken(spreadsheets_client.GetClientLoginToken())
    gd_client.Export(entry, file_path)
    gd_client.SetClientLoginToken(docs_auth_token) # reset the DocList auth token

Notice I also used Export, as Download seems to give only PDF files.




I may be misinterpreting the question, but if you just want to copy a file from Excel to Google Sheets you can just upload it with their web interface and it will be automatically converted.

If you need the file to be synchronized, you can use the Google Drive sync client, or save the Excel sheet to OneDrive and use Zapier to synchronize the file.

If, for whatever reason, you need to upload the file programmatically, it will be easier to just automate the user inputs with a program like AutoHotkey than to create a Python program to do this.

If you really need the program in Python (which is after all one of the most useful languages for data processing), you can use xlwings to read the sheet and the Google Sheets API for output.

Another thing you could do to speed up the file read-in would be to add some simple VBA code to the Excel file to output the entire contents to a format more easily readable by python, or exporting the whole sheet as csv and then using the python csv library to read it in.

Another question is why you need it in Google Sheets format anyway. If you just need the file to be accessible via a link, you could upload the sheet to OneDrive and make it accessible. If you need it to be accessible by other programs over the internet, you could transfer it directly or make the file accessible over FTP.




Download a spreadsheet from Google Docs using Python

In case anyone comes across this looking for a quick fix, here's another (currently) working solution that doesn't rely on the gdata client library:

#!/usr/bin/python

import re, urllib, urllib2

class Spreadsheet(object):
    def __init__(self, key):
        super(Spreadsheet, self).__init__()
        self.key = key

class Client(object):
    def __init__(self, email, password):
        super(Client, self).__init__()
        self.email = email
        self.password = password

    def _get_auth_token(self, email, password, source, service):
        url = "https://www.google.com/accounts/ClientLogin"
        params = {
            "Email": email, "Passwd": password,
            "service": service,
            "accountType": "HOSTED_OR_GOOGLE",
            "source": source
        }
        req = urllib2.Request(url, urllib.urlencode(params))
        return re.findall(r"Auth=(.*)", urllib2.urlopen(req).read())[0]

    def get_auth_token(self):
        source = type(self).__name__
        return self._get_auth_token(self.email, self.password, source, service="wise")

    def download(self, spreadsheet, gid=0, format="csv"):
        url_format = "https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key=%s&exportFormat=%s&gid=%i"
        headers = {
            "Authorization": "GoogleLogin auth=" + self.get_auth_token(),
            "GData-Version": "3.0"
        }
        req = urllib2.Request(url_format % (spreadsheet.key, format, gid), headers=headers)
        return urllib2.urlopen(req)

if __name__ == "__main__":
    import getpass
    import csv

    email = "" # (your email here)
    password = getpass.getpass()
    spreadsheet_id = "" # (spreadsheet id here)

    # Create client and spreadsheet objects
    gs = Client(email, password)
    ss = Spreadsheet(spreadsheet_id)

    # Request a file-like object containing the spreadsheet's contents
    csv_file = gs.download(ss)

    # Parse as CSV and print the rows
    for row in csv.reader(csv_file):
        print ", ".join(row)



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()



Python 3 - Saving a google spreadsheet as a csv

In Python 3, open the file in text mode, and set newline to '' to let the CSV writer control what newlines are written:

with open(filename, 'w', newline='') as f:
    writer = csv.writer(f)
    writer.writerows(worksheet.get_all_values())