CODEWITHSUNDEEP
pythongoogle-sheetsgoogle-drive-apigoogle-sheets-api
Fist Pump Cat
Fist Pump Cat

Reputation: 55

Using Python to Create a New Google Sheet

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

  1. 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?

Upvotes: 1

Views: 7612

Answers (1)

Tanaike
Tanaike

Reputation: 201378

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:

Upvotes: 4

Related Questions