Create and edit spreadsheets in any folder on Google Drive with python

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


--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.<Folder ID>




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 Create a spreadsheet titled sample_spread in a specific folder.

Directory structure

└ Service account key.json

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

scope = ['',
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/',
    "parents": [{"id": folder_id}]})

#Output the information of the created spreadsheet

#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

Program execution

$ python

With the above, I think that a spreadsheet called `` `sample_spread``` has been created and written in the specified folder of Google Drive!


Edit Google Sheets in Python Summary of data manipulation between Google Drive and Colaboratory [PyDrive] Google Drive API + Python on the server [Introduction]

Recommended Posts

Create and edit spreadsheets in any folder on Google Drive with python
Create AtCoder Contest appointments on Google Calendar with Python and GAS
Access Google Drive with Python
Create a list in Python with all followers on twitter
How to extract any appointment in Google Calendar with Python
Shuffle the images in any directory with Python and save them in another folder with serial numbers.
Download Google Drive files in Python
Create and read messagepacks in Python
Edit Google Spread Sheets with Python (Low-cost RPA case with Google APIs and Python)
A memo with Python2.7 and Python3 on CentOS
Create and decrypt Caesar cipher with python
Dealing with "years and months" in Python
See file and folder information on python
Upload images to Google Drive with Python
Deploy a Python app on Google App Engine and integrate it with GitHub
Send email with SES in Python and short message with SMS on SNS
Edit Slide (PowerPoint for Google) with Python (Low-cost RPA case with Google API and Python)
Create a Python3 environment with pyenv on Mac and display a NetworkX graph
[Python] Get the files in a folder with Python
Play with Google Spread Sheets in python (OAuth)
Unattended operation of Google Spreadsheets (etc.) in Python
How to drop Google Docs in one folder in a .txt file with python
Automatically create word and excel reports in python
Create a virtual environment with conda in Python
Install OpenCV 4.0 and Python 3.7 on Windows 10 with Anaconda
Create an image with characters in python (Japanese)
Try working with Mongo in Python on Mac
Create a new page in confluence with Python
Upload files to Google Drive with Lambda (Python)
Save Twitter's tweets with Geo in CSV and plot them on Google Map.
Explanation of edit distance and implementation in Python
Until you create a machine learning environment with Python on Windows 7 and run it
Upload JPG file using Google Drive API in Python
Notes on HDR and RAW image processing with Python
Create Amazon Linux with AWS EC2 and log in
Install selenium on Mac and try it with python
Implement Sign In With Google on the backend side
How to load files in Google Drive with Google Colaboratory
Automatic follow on Twitter with python and selenium! (RPA)
Get comments on youtube Live with [python] and [pytchat]!
Calculate Pose and Transform differences in Python with ROS
PIL with Python on Windows 8 (for Google App Engine)
Ubuntu 20.04 on raspberry pi 4 with OpenCV and use with python
Find this week's date in any format with python
Start numerical calculation in Python (with Homebrew and pip)
Email hipchat with postfix, fluentd and python on Azure
Get stock prices and create candlestick charts in Python
Automate Chrome with Python and Selenium on your Chromebook
Create a decent shell and python environment on Windows
Create a fake Minecraft server in Python with Quarry
Edit fonts in Python
Create SpatiaLite in Python
Drive WebDriver with python
Recursively get the Excel list in a specific folder with python and write it to Excel.
Have Google Text-to-Speech create audio data (narration) for video material (with C # and Python samples)
Put Ubuntu in Raspi, put Docker on it, and control GPIO with python from the container
Until you create Python Virtualenv on Windows and launch Jupyter
[Python] Create a file & folder path specification screen with tkinter
[Python] Create a linebot that draws any date on a photo
Create a child account for connect with Stripe in Python
Let's create a script that registers with in Python.