Creating A Spreadsheet In A Folder With GSpread

Question

I am having trouble finding any documentation on how to create a GSheet in a certain Google Drive directory using GSpread.

I have checked the documentation and had a look around some of the back end code.

I am currently using the code below to create the spreadsheet:

worksheet = sh.add_worksheet(title='Overview', rows='100', cols='9')

I want to be able to create the spreadsheet in a directory on a google drive, for example:

X > Y > Spreadsheet

Any help would be greatly appreciated,

Cheers.

Answer 1

• You want to create new Spreadsheet to the specific folder.
• You want to achieve this using Python.

If my understanding is correct, how about this answer?

Modification points:

• Unfortunately, Sheets API cannot achieve this. In this case, it is required to use Drive API.
• In your script, I think that you use gspread.authorize() like gc = gspread.authorize(credentials). In this modification, credentials is used.
• The script in your question of worksheet = sh.add_worksheet(title='Overview', rows='100', cols='9') is used for adding a sheet to the existing Spreadsheet. When you create new Spreadsheet using gspread, please use sh = gc.create('A new spreadsheet').
  • In this case, the new Spreadsheet is created to the root folder.

Preparation:

Before you use the following script, please enable Drive API at API console, and please add the scope of https://www.googleapis.com/auth/drive. If you are using the scope of https://www.googleapis.com/auth/drive.file, please use this and the scope is not required to be modified to https://www.googleapis.com/auth/drive.

• If you are using OAuth2, please remove the file including the refresh token. And then, please run the script and reauthorize again. By this, the added scope is reflected to the access token.

• If you are using Service account, it is not required to remove the file.

Pattern 1:

The following sample script creates new Spreadsheet to the specific folder.

Sample script:

from apiclient import discovery

destFolderId = '###'  # Please set the destination folder ID.
title = '###'  # Please set the Spreadsheet name.

drive_service = discovery.build('drive', 'v3', credentials=credentials)  # Use "credentials" of "gspread.authorize(credentials)".
file_metadata = {
    'name': title,
    'mimeType': 'application/vnd.google-apps.spreadsheet',
    'parents': [destFolderId]
}
file = drive_service.files().create(body=file_metadata).execute()
print(file)

Pattern 2:

If you want to move the existing Spreadsheet to the specific folder, please use the following script.

Sample script:

from apiclient import discovery

spreadsheetId = '###'  # Please set the Spreadsheet ID.
destFolderId = '###'  # Please set the destination folder ID.

drive_service = discovery.build('drive', 'v3', credentials=credentials)  # Use "credentials" of "gspread.authorize(credentials)".
# Retrieve the existing parents to remove
file = drive_service.files().get(fileId=spreadsheetId,
                                 fields='parents').execute()
previous_parents = ",".join(file.get('parents'))
# Move the file to the new folder
file = drive_service.files().update(fileId=spreadsheetId,
                                    addParents=destFolderId,
                                    removeParents=previous_parents,
                                    fields='id, parents').execute()

References:

• Files: create
• Files: update
• Moving files between folders

If I misunderstood your question and this was not the direction you want, I apologize.

Edit:

When you want to share the folder, please use the following script.

Sample script:

drive_service = discovery.build('drive', 'v3', credentials=credentials)  # Use "credentials" of "gspread.authorize(credentials)".
folderId = "###"  # Please set the folder ID.
permission = {
    'type': 'user',
    'role': 'writer',
    'emailAddress': '###',  # Please set the email address of the user that you want to share.
}
res = drive_service.permissions().create(fileId=folderId, body=permission).execute()
print(res)

Reference:

• Permissions: create