Introduction

People naturally want to access Google Spread Sheets with python. It is an article that can easily satisfy such desires.

―― 1. Preparation for accessing Google Spread Sheets using Python --1-1. Create a new project --1-2. Enable Google Drive API --1-3. Enable Google Spread Sheets API --1-4. Set authentication information --1-5. Generate a private key ―― 2. Access Google Spread Sheets using Python --2-1. Set up sharing of Google Spread Sheet ―― 2-2. Get the key of Google Spread Sheet --2-3. Program execution!

1. Preparation for accessing Google Spread Sheets from Python

This is the most annoying.

To access Google Spread Sheets from the outside, It seems best to use Google Cloud Platform.

--The initial setup procedure on Google Cloud Platform is the following 5 steps --1-1. Create a new project --1-2. Enable Google Drive API --1-3. Enable Google Spread Sheets API --1-4. Set authentication information --1-5. Generate a private key

1-1. Create a new project

--First, you need to prepare a project to get the API. --Access Google Cloud Platform API Library -Click ** Select Project **

-Click ** New Project **

--Create by entering an appropriate project name

--End

1-2. Enable Google Drive API

--If you do not enable the API of Google Drive, it is impossible to access Google Spread Sheets from the outside such as Python. ――There is no help for it, so enable it while fluttering. --Find and click on the Google Drive API

--Click Enabled

--Select the project created in 1-1

--End

1-3. Enable Google Spread Sheets API

--Enable the Google Spread Sheets API as in 1-2.

1-4. Set authentication information

――Preparation is almost over. --Here, when accessing from the outside (Python), the information used for authentication is acquired. --Go to Credentials, click ** Create Credentials ** and select a service account.

--Enter the service account name appropriately and click ** Create **. ――It seems that you should write what the service account name does

--For roles, select ** Project ** → ** Owner **.

--Click Finish to finish.

1-5. Generate a private key

--Generate a private key from the authentication information created in 1-4. -What is a private key? ――In other words, don't give the private key created here to anyone and keep it in a safe place.

--Click the service account created in 1-4

-Click ** New Key ** under ** Key **

--Select ** json ** as the key type and click Create.

--This saves the json file.

2. Access Google Spread Sheets using Python

There seem to be several ways to access Google Spread Sheets from Python. This time, I will introduce the method using gspread, which seems to be the easiest.

The procedure is the following 3 steps 2-1. Set up sharing of Google Spread Sheet 2-2. Get the key of Google Spread Sheet 2-3. Program execution!

2-1. Set up sharing of Google Spread Sheet

First, create Google Spread Sheets and set sharing settings.

--Open the json downloaded in 1-5 and copy the address written next to ** "client_email" **. --XXXXXX [at] gspread-sheets-It should be an address like python.YY.gserviceaccount.com.

--Click "Share" in the upper right

--Add the copied address to users and groups.

This completes the sharing settings for Google Spread Sheets.

2-2. Get the key of Google Spread Sheets

--You can get the key from the Google Spread Sheets link prepared in 2-1.

https://docs.google.com/spreadsheets/d/aaaaaaaaaaaaaa/edit#gid=0

Aaaaaaaaaaaaaa is the key. You will need it in 2-3, so make a note of it.

2-3. Program execution!

――Prepare the environment before doing. --If you don't have Python environment on your PC, please refer to here (preparing to post)

--The following three modules are required - json - gspread - oauth2client

Since json is a standard Python module, get the other two with pip.

pip install gspread

pip install oauth2client

――The program is finally executed. --When executed, the value will be written like this.

Reference code

`gspread_simple.py`


import gspread
import json
from oauth2client.service_account import ServiceAccountCredentials

# (1)Visit Google Spread Sheets
def connect_gspread(jsonf,key):
    scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
    credentials = ServiceAccountCredentials.from_json_keyfile_name(jsonf, scope)
    gc = gspread.authorize(credentials)
    SPREADSHEET_KEY = key
    worksheet = gc.open_by_key(SPREADSHEET_KEY).sheet1
    return worksheet

#Here jsonfile name and 2-Enter the key prepared in 2
jsonf = "~~~~~~~.json"
spread_sheet_key = "aaaaaaaaaaaaaa"
ws = connect_gspread(jsonf,spread_sheet_key)

#(2)Updated values on Google Spread Sheets
#(２−１)Update the value of a cell (specify row and column)
ws.update_cell(1,1,"test1")
ws.update_cell(2,1,1)
ws.update_cell(3,1,2)

#(２−２)Update the value of a cell (specify the label)
ws.update_acell('C1','test2')
ws.update_acell('C2',1)
ws.update_acell('C3',2)

#(2-3)Update values for a range of cells
ds= ws.range('E1:G3')
ds[0].value = 1
ds[1].value = 2
ds[2].value = 3
ds[3].value = 4
ds[4].value = 5
ds[5].value = 6
ds[6].value = 7
ds[7].value = 8
ds[8].value = 9
ws.update_cells(ds)

Reference code explanation

(1) Access Google Spread Sheets

def connect_gspread(jsonf,key):
    #Specify two APIs, spreadsheets and drive
    scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
    #Set credentials
    credentials = ServiceAccountCredentials.from_json_keyfile_name(jsonf, scope)
    gc = gspread.authorize(credentials)
    #Access sheet1 using a spreadsheet key
    SPREADSHEET_KEY = key
    worksheet = gc.open_by_key(SPREADSHEET_KEY).sheet1
    return worksheet

#Specify jsonfile name
jsonf = "~~~~~~~.json"
#Specify the shared spreadsheet key
spread_sheet_key = "aaaaaaaaaaaaaa"
ws = connect_gspread(jsonf,spread_sheet_key)

(2-1) Update the value of a certain cell (specify the row and column)

ws.update_cell(line,Column,value)

It has become. In other words

ws.update_cell(2,4,100)

If so, 100 will be written in the 4th column (D column) of the 2nd row.

(2-2) Update the value of a certain cell (specify the label)

ws.update_acell(label,value)

It has become. In other words

ws.update_acell("E4",200)

If so, 200 will be written in the 5th column (E column) of the 4th row.

(2-3) Update the value of cells in a certain range

--This is recommended if you want to write to many cells.

――By the way, access to Google Spread Sheets is limited to ** up to 100 times in 100 seconds **, and if you access more than this, an error will occur. ――If you use this method, you only need to access twice.

#Specify the range of cells and store in a one-dimensional array.
ds = ws.range('A1:C3') #Access occurs
#Specify the value of each cell
ds[0].value = 1
ds[1].value = 2
ds[2].value = 3
ds[3].value = 4
ds[4].value = 5
ds[5].value = 6
ds[6].value = 7
ds[7].value = 8
ds[8].value = 9
#Update value
ws.update_cells(ds) #Access occurs

--If you specify A1: C3, the order in which they are stored in the one-dimensional array is as follows.

	A	B	C
1	①	②	③
2	④	⑤	⑥
3	⑦	⑧	⑨

Reference site

-[Don't hesitate anymore] Summary of initial settings for reading and writing spreadsheets with Python -Summary of how to use the gspread library! Working with spreadsheets in Python

I tried to access Google Spread Sheets using Python