Using Python to Create a New Google Sheet

Question

Background

Hi All...still pretty new to python. I'm on a Mac (Sierra) running Jupyter Notebook in Firefox (87.0). I'm trying to use my python script to build a dataframe, create a new Google Sheet (a new workbook, not a new sheet in an existing workbook) on my personal Google Drive in a specific folder, and then write my dataframe to that new Google Sheet.

What I've Tried

I've been able to get my service account to open an existing Google Sheet and then later read/write using code like this:

import gspread
from oauth2client.service_account import ServiceAccountCredentials
from gspread_dataframe import set_with_dataframe

scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name(json_path, scope)
client = gspread.authorize(creds)
workbook = client.open_by_key(my_existing_google_sheet_key)

There's tons of documentation on how to read/write from existing Google Sheets, but as I said I'd like to create a new Google Sheet to write to. This has been surprisingly hard to find. The one reference I did find was this related post but can't get the OP's code to work (what is 'discovery'?). I feel like I'm missing something obvious.

Questions

How do I use python to have my service account create a new Google Sheet on my personal Google Drive in a specific folder so that I can write to it?

Tanaike · Accepted Answer · 2021-05-15 23:42:16Z

4

I believe your goal as follows.

You want to create new Google Spreadsheet to the specific folder of your own Google Drive using the service account.
You want to achieve this using gspread with python.

Modification points:

When I saw the document of gspread, it seems that the create method of Class Client can be used. And, in this method, the folder ID can be used for putting to the specific folder.

When these are reflected to your script, it becomes as follows.

Modified script:

import gspread
from oauth2client.service_account import ServiceAccountCredentials
from gspread_dataframe import set_with_dataframe

json_path = '###' # Please set the file for using service account.

scope = ['https://www.googleapis.com/auth/spreadsheets', 'https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name(json_path, scope)
client = gspread.authorize(creds)

spreadsheetTitle = 'new spreadsheet title'
folderId = '###' # Please set the folder ID of the folder in your Google Drive.

workbook = client.create(spreadsheetTitle, folder_id=folderId)

Note:

In this case, it is required to share the specific folder of your Google Drive with the email of the service account. By this, the Spreadsheet can be created to the folder with the service account. Please be careful this.

Reference:

create method of Class Client

edited May 15, 2021 at 23:42

answered May 15, 2021 at 23:27

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tanaike Over a year ago

@Fist Pump Cat Now, I noticed that the create method of Class Client has the argument for putting to the specific folder when the new Spreadsheet is created. I apologize for this. When this is used, the script becomes simpler. So I updated my answer. Could you please confirm it?

Alex Over a year ago

this does work for creating the sheet in a folder in the user account, but technically the sheet is still owned by the service account.

Collectives™ on Stack Overflow

Using Python to Create a New Google Sheet

Background

What I've Tried

Questions

1 Answer 1

Modification points:

Modified script:

Note:

Reference:

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Background

What I've Tried

Questions

1 Answer 1

Modification points:

Modified script:

Note:

Reference:

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related