Write to a Google Sheets with a Python script

We will go through the following steps:

• Create a Google Cloud project
• Enable Google Sheet API for this project
• Create a service account associated with this project
• Create a key for this service account
• Create a Google Sheets to receive the data
• Share Google Sheets with service account
• Write data with a python script configured for this sheet and this service account

First, we will have to create a Google Cloud project which will allow us to provide access to a spreadsheet. You must go to console.cloud.google.com and create a new project, here WineMonitor:

In the search engine we type Google Sheets API which sends us here https://console.cloud.google.com/marketplace/product/google/sheets.googleapis.com. We then choose to activate this API by checking that the correct project is selected:

We then search for “service account” in the search engine which sends us here: https://console.cloud.google.com/iam-admin/serviceaccounts. We describe the account and pass the optional options. Note the email address of this account.

By clicking on the account, you can then generate a key in JSON format. The key is immediately downloaded. We bring it back into our Python project and note the file name.

We then create a new Google Sheets here: https://docs.google.com/spreadsheets/u/0/

Give it the name you want and carefully note the name of the first sheet (Sheet1 or Feuille 1 depending on your location). We share the sheet with the service account email and “editor” rights on the sheet. And we note the sheet ID present in the URL:

We then prepare the following Python script (courtesy of https://denisluiz.medium.com/python-with-google-sheets-service-account-step-by-step-8f74c26ed28e). This scritp may require the following packages to be installed first:

pip3 install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib

import httplib2
import os
from apiclient import discovery
from google.oauth2 import service_account

try:
    scopes = ["https://www.googleapis.com/auth/drive", "https://www.googleapis.com/auth/drive.file",
              "https://www.googleapis.com/auth/spreadsheets"]
    secret_file = os.path.join(os.getcwd(), "filename of JSON keys")
    spreadsheet_id = 'Sheet Id'
    range_name = 'Sheet name!A1:D2'
    credentials = service_account.Credentials.from_service_account_file(secret_file, scopes=scopes)
    service = discovery.build('sheets', 'v4', credentials=credentials)

    values = [
        ['a1', 'b1', 'c1', 123],
        ['a2', 'b2', 'c2', 456],
    ]
    data = {'values': values}
    service.spreadsheets().values().update(spreadsheetId=spreadsheet_id, body=data, range=range_name,
                                           valueInputOption='USER_ENTERED').execute()

except OSError as e:
    print(e)

And we replace:

• the name of the JSON key file
• the sheet id,
• the name of the first sheet

We execute the code and voilà, the values are written in the Google Sheets by our Python script!