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.
Basically, I will write while referring to the official document of gspread
.
gspread(Docs)
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.)
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.
verification code
will be displayed, so copy it.It's easy and wonderful.
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.)
This is a method to open directly by file name.
ss_name = "Spreadsheet file name"
workbook = gc.open(ss_name)
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)
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)
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
For the spreadsheet obtained above, you can get a specific sheet by the following method.
ss_name = "Spreadsheet file name"
workbook = gc.open(ss_name)
worksheet = workbook.worksheet("Sheet 1")
ss_name = "Spreadsheet file name"
workbook = gc.open(ss_name)
worksheet = workbook.get_worksheet(0)
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
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)
By the way, the cell acquired here has the following three properties.
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))
There are the following methods to get the value in the specified sheet.
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))
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'], ...
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 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)
If you want to use the latest gspread
, delete_rows is prepared, so please use that. If you use it, it seems possible to delete all the specified line range at once.
If you want to delete the target sheet itself, pass the target sheet itself to del_worksheet
.
ss_name = "Spreadsheet file name"
workbook = gc.open(ss_name)
worksheet = workbook.worksheet("Sheet name you want to delete")
workbook.del_worksheet(worksheet)
That's all.
I haven't tried gspread
yet, so I hope I can add it to this post little by little.
Recommended Posts