Working with Google Sheets API

This article covers how to update a Google Sheet automatically with Python, focusing on my most used methods:

• Create a new sheet
• Update an existing sheet
• Append to an existing sheet

To begin with, follow the guide to get your credentials and install the library. In the credential file, you will find an email address. To edit an existing spreadsheet, it is required to grant edit permission to the client email. Then we can start to play with the API.

Connect to Google API

from oauth2client.service_account import ServiceAccountCredentials
from googleapiclient import discovery
SCOPES = ['https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name(
    'credentials.json', SCOPES)
service = discovery.build('sheets', 'v4', credentials=creds)

For the first time executing the piece of code, it will direct users to an authorization page. Log into your Google account to get authorized.

Create a New Sheet

This method will create a new sheet in an existing spreadsheet. The spreadsheetId can be found in the URL.

URL Format: https://docs.google.com/spreadsheets/d/spreadsheetId/edit#gid=0

from pprint import pprint
# fill in spreadsheetId
spreadsheetId = '1xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
# name your new sheet
title = 'MySheet'
# call the Sheets API
body = {'requests': [
    {
        'addSheet': {
            'properties': {
                'title': title
            }
        }
    }]}
request = service.spreadsheets().batchUpdate(
    spreadsheetId=spreadsheetId, 
    body=body)
response = request.execute()
pprint(response)

Update an Existing Sheet

This method will write data from the first row or any row specified in range_.

spreadsheetId = '1xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
range_ = 'MySheet!A1:C'
values = [
    ['date', 'event', 'count'],
    ['6/1', 'login', 100]
]
body = {
    'range': range_, 
    'values': values, 
    'majorDimension': 'ROWS'
}
request = service.spreadsheets().values().update(
    spreadsheetId=spreadsheetId, 
    range=range_, 
    valueInputOption='USER_ENTERED',
    body=body)
response = request.execute()
pprint(response)

Append to an Existing Sheet

This method will append data to an existing data set in a spreadsheet.

spreadsheetId = '1xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
range_ = 'MySheet!A:C'
values = [
    ['6/2', 'login', 200],
    ['6/2', 'complain', 1]
]
body = {
    'range': range_, 
    'values': values, 
    'majorDimension': 'ROWS'
}
request = service.spreadsheets().values().append(
    spreadsheetId=spreadsheetId, 
    range=range_, 
    valueInputOption='USER_ENTERED',
    insertDataOption='INSERT_ROWS',
    body=body)
response = request.execute()
pprint(response)

References:

Google Sheets API | Google Developers

Thanks for reading!