Use pygsheets to create a brand-new sheet using the service_file authorization method

Question

I can already edit an existing sheet by using the open_by_key() function but I would like to be able to create a brand-new sheet.

When I try the following code:

import pygsheets

creds_file = "/Users/jerome/audible2googlesheet.json"

gc = pygsheets.authorize(service_file=creds_file)
sh = gc.open("my_1st_public_sheet")
wks = sh.sheet1

# Update a single cell.                                                                                                                                                                                                                                                                                                               
wks.update_value('A1', "some value")

sh.share('', role='reader', type='anyone')

I get this exception/error:

raise SpreadsheetNotFound('Could not find a spreadsheet with title %s.' % title) pygsheets.exceptions.SpreadsheetNotFound: Could not find a spreadsheet with title my_1st_public_sheet.

What am I missing?

Answer 1

• You want to create new sheet using pygsheets with python.

If my understanding is correct, how about this answer? From your question, I could understand as following 2 patterns.

Pattern 1:

• You want to add new sheet in an existing Spreadsheet.

In this case, the sample script is as follows.

Sample script:

import pygsheets

creds_file = "/Users/jerome/audible2googlesheet.json"

spreadsheet_key = '###'  # Please set the Spreadsheet ID.
gc = pygsheets.authorize(service_file=creds_file)
sh = gc.open("my_1st_public_sheet")
sh.add_worksheet("sampleTitle")  # Please set the new sheet name.

Pattern 2:

• You want to create new Spreadsheet.

In this case, the sample script is as follows.

Sample script:

import pygsheets

creds_file = "/Users/jerome/audible2googlesheet.json"

gc = pygsheets.authorize(service_file=creds_file)
res = gc.sheet.create("sampleTitle")  # Please set the new Spreadsheet name.
print(res)

• In this case, the new Spreadsheet is created to the Drive of sercice account. So if you want to see the created Spreadsheet in your Google Drive, please share the Spreadsheet with your Google account. Please be careful this.

Note:

• These sample script supposes that you have already been able to get and put values for Spreadsheet using Sheets API.

References:

• add_worksheet
• create

If I misunderstood your question, I apologize.

Added:

In the following sample script, new Spreadsheet is created and the created Spreadsheet is shared with your account.

Sample script:

Before you run the script, please set your email address of Google account to ### your email address ###.

import pygsheets

creds_file = "/Users/jerome/audible2googlesheet.json"

gc = pygsheets.authorize(service_file=creds_file)
res = gc.sheet.create("sampleTitle")  # Please set the new Spreadsheet name.
createdSpreadsheet = gc.open_by_key(res['spreadsheetId'])
createdSpreadsheet.share('### your email address ###', role='writer', type='user')

• About the detail settings of share, please check the document.