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.

Recommended Posts

Unattended operation of Google Spreadsheets (etc.) in Python
[Python] Operation of enumerate
Equivalence of objects in Python
Implementation of quicksort in Python
Check the operation of Python for .NET in each environment
Google search for the last line of the file in Python
Pixel manipulation of images in Python
[Python] Operation memo of pandas DataFrame
Division of timedelta in Python 2.7 series
MySQL-automatic escape of parameters in python
Handling of JSON files in Python
Download Google Drive files in Python
Implementation of life game in Python
Samples of Python getters, setters, etc.
Waveform display of audio in Python
Law of large numbers in python
Implementation of original sorting in Python
Reversible scrambling of integers in Python
Trial of writing the configuration file in Python instead of .ini etc.
Mouse operation using Windows API in Python
[Python] Explore the characteristics of the titles of the top sites in Google search results
Check the behavior of destructor in Python
(Bad) practice of using this in Python
General Theory of Relativity in Python: Introduction
Output tree structure of files in Python
Display a list of alphabets in Python 3
Comparison of Japanese conversion module in Python3
Summary of various for statements in Python
Get Google Fit API data in Python
The result of installing python in Anaconda
Memo of pixel position operation for image data in Python (numpy, cv2)
Gang of Four (GoF) Patterns in Python
Create and edit spreadsheets in any folder on Google Drive with python
The basics of running NoxPlayer in Python
Bulk replacement of strings in Python arrays
Project Euler # 16 "Sum of Powers" in Python
Traffic Safety-kun: Recognition of traffic signs in Python
Summary of built-in methods in Python list
Non-logical operator usage of or in python
In search of the fastest FizzBuzz in Python
Practical example of Hexagonal Architecture in Python
Project Euler # 17 "Number of Characters" in Python
Double pendulum equation of motion in python
Get rid of DICOM images in Python
Status of each Python processing system in 2020
Project Euler # 1 "Multiples of 3 and 5" in Python
Meaning of using DI framework in Python
Output the number of CPU cores in Python
Draw a graph of a quadratic function in Python
First steps to try Google CloudVision in Python
Correspondence summary of array operation of ruby and python
[Python] Sort the list of pathlib.Path in natural sort
Play with Google Spread Sheets in python (OAuth)
Automatic operation of Chrome with Python + Selenium + pandas
Summary of how to import files in Python 3
Project Euler # 10 "sum of prime numbers" in Python
Get the caller of a function in Python
Match the distribution of each group in Python
Put text scraped in Python into Google Sheets
Run Google Analytics API (core v3) in python
View the result of geometry processing in Python