It is as the title….

I wanted to write the scraped data to a spreadsheet (personally) Even if you often see articles on how to edit an existing spreadsheet, I couldn't find an article to create a new spreadsheet to edit, so I'll share it ...!

In this article, I will briefly introduce how to handle Google Drive and spreadsheets at the same time.

Target audience

--I want to work with google spreadsheets in python --I want to create a new spreadsheet by specifying an arbitrary folder

flow

--Get service account key (json file) --Share the folder where you want to create a spreadsheet with your service account --Programming! !!

Get service account key (json file)

In order to work with Drive and Sheets in programming, you need to have a service account with Google Drive Api and Google Sheets Api enabled.

The following article was very helpful, so please refer to this to get the service account key. Edit Google Sheets in Python

Share the folder where you want to create the spreadsheet with your service account

Share the folder where you want to create the spreadsheet. In Share> Share with others, enter the email address of your service account. (You can also check the service account email address in the service account key file client_email)

Also, check the folder ID here. The folder ID is part of the url, as shown below.

https://drive.google.com/drive/folders/<Folder ID>

programming!!

environment

macOS 10.15.4
python 3.7.3
pip 19.3.1

Environment

pip install gspread oauth2client google-api-python-client google-auth-httplib2 google-auth-oauthlib pydrive

Program creation

Preparations are complete! From now on, I will write it in write_spread.py. Create a spreadsheet titled sample_spread in a specific folder.

Directory structure

app/
 ├ write_spread.py
└ Service account key.json

`write_spread.py`


import gspread
from oauth2client.service_account import ServiceAccountCredentials
from pydrive.auth import GoogleAuth
from pydrive.drive import GoogleDrive
import pprint

scope = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']
json_keyfile_path = 'Service account key.json'

#Read the service account key
credentials = ServiceAccountCredentials.from_json_keyfile_name(
    json_keyfile_path, scope)

#Perform OAuth authentication for pydrive
gauth = GoogleAuth()
gauth.credentials = credentials
drive = GoogleDrive(gauth)

folder_id = '<Folder ID>'
f = drive.CreateFile({
    'title': 'sample_spread',
    'mimeType': 'application/vnd.google-apps.spreadsheet',
    "parents": [{"id": folder_id}]})
f.Upload()

#Output the information of the created spreadsheet
pprint.pprint(f)

#Authenticate for gspread
gc = gspread.authorize(credentials)

#Select a workbook by specifying the spreadsheet ID
workbook = gc.open_by_key(f['id'])
worksheet = workbook.sheet1

#Enter in cell A1
worksheet.update_acell('A1', 'Hello World!')

#1 on the second line~Enter in the third column
cell_list = worksheet.range(2, 1, 2, 3)
cell_list[0].value = 'Serial number'
cell_list[1].value = 'name'
cell_list[2].value = 'phone number'

#Update spreadsheet
worksheet.update_cells(cell_list)