Google Sheet API guided walkthrough

I have been doing this for quite some years and witnessed the Google Sheets API became matured. This are the steps we needed to do from scratch to create a credential and write some Python code to drive up Google Sheet API.

First is to create a Google API project: On the developer console, we create a new project (such that we can apply access control on project basis) using the drop down at the left of the top search bar. While in the project created, click on the “Dashboard” on the sidebar and enable Google Sheet API.

Then, credential have to be created. Click on “Credentials” on the sidebar and create credentials. We need the API key and OAuth 2.0 client ID for this but Google will also generate service account. The OAuth ID shall be downloaded as JSON format, credentials.json.

Afterwards, we should find the Google Sheet ID. This is the hash key to be used by the API and the sheet shall be shared for access with the service account. Then, in Python, we will need gspread and oauth2client packages for the Sheets API and authentication, respectively. Alternative to gspread would be the googleapiclient, which however, is more generic and officially from Google.

Access using Python is as follows: Using gspread:

import gspread
from oauth2client.service_account import ServiceAccountCredentials

credential = ServiceAccountCredentials.from_json_keyfile_name(
    "credentials.json",
    ["https://spreadsheets.google.com/feeds"])
client = gspread.authorize(credentials)
sheet = client.open_by_key("___PutGoogleSheetKeyHere___").sheet1
sheet.append_row(["data", "data", "data", "data"], 1)

and using googleapiclient will be this:

from googleapiclient.discovery import build
from oauth2client import file, client, tools
from httplib2 import Http

SCOPES = 'https://www.googleapis.com/auth/spreadsheets'

store = file.Storage("token.json")
creds = store.get()
if not creds or creds.invalid:
    flow = client.flow_from_clientsecrets('credentials.json', SCOPES)
    creds = tools.run_flow(flow, store)
service = build('sheets', 'v4', http=creds.authorize(Http()))
sheet = service.spreadsheets()
sheet.values().append(
    spreadsheetId="___PutGoogleSheetKeyHere___",
    body={'values': ['data','data','data','data']},
    valueInputOption="RAW",
    range="'Sheet1'!A1:D"
).execute()

Surely, the gspread is simpler as many nuts and blots are hidden. Using the generic API needs to specify the exact API syntax for HTTP REST API parameters, such as the range have to be correct and matching the dimension of the value.

Also note that this is for remote access. If we write script (in JavaScript) inside Google Sheet, we do not need any of these.