How do you create a new sheet in a specific folder/directory using PyGSheets v2?

Question

I want to use PyGSheets to create a Spreadsheet in my Google Drive folder. I also want to be able to set the directory/folder where the sheet is created using the code. Specifically, I would like to type a string similar to a URL or even just the folder's name.
I have already looked at the PyGSheets documentation and the "Spreadsheet" model. I have not found any classes that accept a folder name or directory address. There is also no class used to move a sheet from one folder to another. Is either operation possible using PyGSheets?

As of May 5, I have used a work-around for this problem. To get the ID of a folder in my Drive, I right-click the target folder and select "Get shareable link" from the menu that appears. I copy the link and paste it on any text editor. The link looks like this: https://drive.google.com/open?id=9JHS74hgls049J50. I copy the random string of characters after the "id = " keywords. That is what I supply as a value when I create a folder using PyGSheets:

shtTargetedCreate = con.create("Test Folder",folder="1GwA4W8iv-26BvG48nKnEigYqDL8SpUGK")

Is there any more efficient way to do this?

rherron · Accepted Answer · 2019-06-09T22:47:09.153

Well it looks like you figured out how to create a new spreadsheet in a folder by file id, but haven't found how to get folder names / IDs from within pygsheets or how to move sheets. (You said you created a "folder using PyGSheets, but that just creates a spreadsheet, right?)

These can both be done by using the DriveAPIWrapper functions - https://pygsheets.readthedocs.io/en/stable/drive_api.html

Folder names / IDs:

pygsheets.drive.list(), in your case con.drive.list(), will give a list of metadata dictionaries for all files and folders in the drive. I've made a simple function to extract just the folder names (keys) and ids (values) into a dictionary for simpler lookup and use with the create method:

def folder_id_dict(client):
    folders = {}
    meta_list = client.drive.list()
    for file_meta in meta_list:
        if file_meta['mimeType'] == 'application/vnd.google-apps.folder':
            folders[file_meta['name']] = file_meta['id']
    return folders

#your use:
names = folder_id_dict(con)

Moving files between folders:

https://pygsheets.readthedocs.io/en/stable/drive_api.html#pygsheets.drive.DriveAPIWrapper.move_file

con.drive.move_file(file_id, old_folder, new_folder, **kwargs)

This works great! Only additional thing I had to do was share the folder with my Google service account's client_email in order to be able to see it. — Thomas Burke, Jul 20 '20 at 05:01

How do you create a new sheet in a specific folder/directory using PyGSheets v2?

1 Answers1