Unattended operation of Google Spreadsheets (etc.) in Python

TL; DR

--I wrote how to unattended Google Spreadsheets tables using a Python program. -Official Quick Start is a human-mediated OAuth2 method, but it's an unmanned version of this. --Create a service account, give it the required permissions, and use the account's private key to access it.

Spreadsheet preparation

This is nothing special. Create a suitable sheet in Google Drive and make a note of the file ID.

Project and service accounts

Of course, you need permission to operate various documents. When operating from a program, there are roughly two methods for acquiring authority.

--Temporarily obtain permission from a human and operate with that human account --Operate with a privileged ** machine account **

Roughly speaking, the former is the method used in interactive software, and the latter is the method used in automated systems. This time we will use the latter method. Here, the "machine account" is called ** service account **. In other words, you first need to create a service account and give it the required permissions. The procedure is roughly as follows.

1. Go to the GCP Console (https://console.cloud.google.com) and create a project.
2. GCP's top left hamburger menu> IAM & Administration (https://console.cloud.google.com/iam-admin)> Service Accounts> Create Service Account
3. I think you only need to enter the required items. You will have an account called [email protected].
4. Once you have an account, you will be able to download your private key. Please download in JSON format.
5. ** Give this account the permission to operate the file (spreadsheet) you want to operate (= share). The way of giving is the same as the normal authority operation for humans.
6. Go to Hamburger Menu> APIs & Services> Dashboard> + Enable APIs & Services and enable Google Sheets API.

Package installation

Maybe this is all you need.

$ pip3 install google-api-python-client google-auth-oauthlib oauth2client

code

The code to write test in cell A1 of the sheet. Only this!

from googleapiclient.discovery import build
from oauth2client.service_account import ServiceAccountCredentials

PRIVKEY_FILE = "projectname-*********.json"  #Downloaded private key
SPREADSHEET_ID = "********"                  #Sheet file ID
SCOPE = ["https://www.googleapis.com/auth/spreadsheets"]

creds = ServiceAccountCredentials.from_json_keyfile_name(PRIVKEY_FILE, SCOPE)
service = build("sheets", "v4", credentials=creds)
sheet = service.spreadsheets()

result = sheet.values().update(
    spreadsheetId=SPREADSHEET_ID,
    range="a1",
    valueInputOption="RAW", # USER_You can enter an expression as an expression with ENTERED(Example: "=sum(a1:a100)")
    body={"values": [["test"]]}).execute()

in conclusion

It was just as easy with Ruby.