[PYTHON] Sample code summary when working with Google Spreadsheets from Google Colab

Recently, I often touch Google spreadsheets from Google Colab.

(I also made a video like this, so please take a look if you like! → [[Learning Python with Safa Colab -9] How to write values from Google Colab to Google Spreadsheet (YouTube)](https:: //www.youtube.com/watch?v=4RCWYQk9bfM))

When I touch it, I often type in the usual code, so I decided to put the frequently used descriptions together in Qiita as sample code.

Referenced document

Basically, I will write while referring to the official document of gspread. gspread(Docs)

Install gspread

The library gspread, which is essential for working with Google Spreadsheets. On Google Colab, when you hit a command, add ! At the beginning.

!pip install gspread

As I wrote so far, I noticed that Google Colab seemed to have gspread installed from the beginning. By the way, the installed version is 3.0.1.

import gspread
print(gspread.__version__)
# => 3.0.1

One thing to keep in mind here is that this 3.0.1, which is installed from the beginning, is not the latest version, so there are some features that cannot be used. burnash/gspread(Releases)

Therefore, if you want to use the latest gspread function, you need to install the latest gspread by hitting the following command.

!pip install --upgrade gspread

import gspread
print(gspread.__version__)
# => 3.6.0

** However, this time I thought about writing with Google Colab in mind to handle Google spreadsheets immediately, so I will write using 3.0.1 which is installed by default. .. ** ** (Since the functions added in the latest version are also attractive, I would like to write that as a separate post.)

Authentication process required to handle Google Spreadsheets from code

Essential processing for handling Google Spreadsheets from Google Colab. Running this code will give you the authentication required to work with Google Sheets from within Google Colab. (Almost like magic, I use it when writing code that handles Google spreadsheets. If you have trouble writing, you can paste this description on Colab with copy and execute it.)

from google.colab import auth
from oauth2client.client import GoogleCredentials
import gspread

auth.authenticate_user()
gc = gspread.authorize(GoogleCredentials.get_application_default())

I'm not sure if this authentication is because both Google Colab and Google Spreadsheets are Google products, but it's really smart, so it's always great without any hassle.

Specifically, the steps are as follows.

When you execute the above code, the URL for authentication will be displayed, so click it

You will be taken to the authentication screen on the Google side, so authenticate. When the authentication is completed, the verification code will be displayed, so copy it.

If you paste the code you copied earlier on the Colab side, authentication is completed.

It's easy and wonderful.

How to get Google Sheets

Regarding the method of obtaining the Google spreadsheet to be operated, gspread provides several methods, so I will introduce each of them. From here, I will write the code on the assumption that the authentication described above has been completed. (If you run it without being authenticated, you will get an authentication error.)

Get by file name

This is a method to open directly by file name.

ss_name = "Spreadsheet file name"
workbook = gc.open(ss_name)

Get by spreadsheet ID

How to open with the ID included in the spreadsheet URL. (As an aside, as far as I looked up the information in Japanese, I got the impression that the most common way to open it was this way.)

# https://docs.google.com/spreadsheets/d/{This is the spreadsheet ID}/edit#gid=0
ss_id = "Spreadsheet ID"
workbook = gc.open_by_key(ss_id)

Get by URL

It is a method to enter the URL of the spreadsheet as it is and open it

ss_url = "Spreadsheet URL"
workbook = gc.open_by_url(ss_url)

Get all spreadsheets

There was also a way to get all the spreadsheets in Google Drive. https://gspread.readthedocs.io/en/latest/api.html#gspread.Client.openall

When run, it returns a list of spreadsheets.

workbook_list = gc.openall()
for workbook in workbook_list:
  print(workbook.title) #Display the title of the acquired file

How to get a sheet

For the spreadsheet obtained above, you can get a specific sheet by the following method.

How to get by passing the sheet name to be acquired to worksheet ()

ss_name = "Spreadsheet file name"
workbook = gc.open(ss_name)
worksheet = workbook.worksheet("Sheet 1")

How to get by passing the index of the sheet to be acquired to get_worksheet ()

ss_name = "Spreadsheet file name"
workbook = gc.open(ss_name)
worksheet = workbook.get_worksheet(0)

How to get the value in the sheet

How to get by specifying a cell (acell)

ss_name = "Spreadsheet file name"
workbook = gc.open(ss_name)
worksheet = workbook.worksheet("Sheet 1")

print(worksheet.acell("A1").value)
# =>Value of cell A1

print(worksheet.acell("B2").value)
# =>B2 cell value

print(worksheet.cell(3,2).value)
# =>B3 cell value

How to get by specifying a cell (range)

ss_name = "Spreadsheet file name"
workbook = gc.open(ss_name)
worksheet = workbook.worksheet("Sheet 1")
print(worksheet.range(3,2)[0].value)
# =>For range, an array of cells is returned
# =>In the case of this specification, only one cell information of B3 is included in the array, so like this[0]The value is obtained by specifying with

#Get cell information in the range you want to specify. Since it changes with an array, all values are output in a loop
cell_list = worksheet.range("A1:B3")
for cell in cell_list:
  print(cell.value)

About Cell Object

By the way, the cell acquired here has the following three properties.

• value (cell value)
• row
• col

For example, the following is sample code to get and display the value of a cell in the specified range ("A1: B3 ").

ss_name = "Spreadsheet file name"
workbook = gc.open(ss_name)
worksheet = workbook.worksheet("Sheet 1")

#Get cell information in the range you want to specify. Since it changes with an array, all values are output in a loop
cell_list = worksheet.range("A1:B3")
for cell in cell_list:
  print('%s line%The value in column s is%s' % (cell.row, cell.col, cell.value))

How to get the values in the sheet (get all)

There are the following methods to get the value in the specified sheet.

get_all_records returns a list of dicts (dictionaries)

If you use get_all_records, the value of the target sheet will be returned as a list of dict (dictionary).

ss_name = "Spreadsheet file name"
workbook = gc.open(ss_name)
worksheet = workbook.worksheet("Sheet name you want to get")

print(worksheet.get_all_records())
# => [{'item1': 2, 'item2': 4, 'item3': 31}, {'item1': 3, 'item2': 6, 'item3': 32}, ...

If you call it without specifying any options, the first line will be treated as header. Please refer to here for how to specify options.

For example, if you want to convert the value in the sheet to json, you will get the following code.

#When converting to the specified sheet value json
import json
ss_name = "Spreadsheet file name"
workbook = gc.open(ss_name)
worksheet = workbook.worksheet("Target sheet name")

dict_list = worksheet.get_all_records()
print(json.dumps(dict_list))

get_all_values returns the values in the sheet as an array

Another option is to simply get it as an array of values by using get_all_values.

ss_name = "Spreadsheet file name"
workbook = gc.open(ss_name)
worksheet = workbook.worksheet("Sheet name you want to get")

print(worksheet.get_all_values())
# => [['item1', 'item2', 'item3'], ['2', '4', '31'], ['3', '6', '32'], ...

How to add a value to a sheet

Use ʻappend_row` to append values to the sheet with rows. The sample below is sample code that adds 100 lines with the value to the specified sheet. (For example, if there is already a description in the sheet, a new row will be added from there)

import random

ss_name = "Spreadsheet file name"
workbook = gc.open(ss_name)
worksheet = workbook.worksheet("Sheet 2")

for i in range(100):
  worksheet.append_row([i, i * 2, random.randint(1, 100)])

Below is a capture of the spreadsheet that was actually entered.

If you want to delete all the contents of the sheet (impractical)

If you want to delete all the contents of the specified sheet, it seems that you need to execute as follows.

However, since this is a very delicate code (the number of existing lines is deleted line by line), it is better to delete it at once by the process of deleting the sheet itself written below. Looks good. </ font>

#If you want to delete all the values in the specified sheet
ss_name = "Spreadsheet file name"
workbook = gc.open(ss_name)
worksheet = workbook.worksheet("Sheet name")

for i in range(1, len(worksheet.get_all_values())):
  worksheet.delete_row(1)

ss_name = "Spreadsheet file name"
workbook = gc.open(ss_name)
worksheet = workbook.worksheet("Sheet name you want to delete")

workbook.del_worksheet(worksheet)