Specify a folder when creating a google spreadsheet

Feature Requests
1

I want Retool to create a spreadsheet shared with someone who is not myself.

Currently, if I create a resource Google Sheet, I can connect it to my account via OAuth2 (I haven't explored service accounts yet, and maybe that's the way to go).

Then when I use the "create a spreadsheet" function, I can only specify the title, and the fill will be placed in my own personal Google Drive root folder.

I cannot afford to share my entire drive for obvious reasons. Not even organization level.

If there was a way to create a spreadsheet into a specific Shared Drive or also Shared Folder, then it'd be easy peasy to have google taking care about sharing setting...

Currently my process is broken because the user of my Retool app, will create documents in my own personal drive with no way to access them right away. This will create chaos in my root of google drive which is a pain. And will block my entire workflow

2

Hello @Nicolo_Paternoster!

Interesting use case, I completely understand the need to have the sheets either created in the correct Drive/Folder.

I was just going over the Google Drive and Google Sheets API, and it is definitely possible to create a file and then move it programatically.

Although the process is not necessarily 'easy' as it requires syncing up permissions and using OAuth 2.0 to prove that the user has access to update the file's parent folder. And requires specifying the file/folder path location which you would need to get from the user and pass into the code as well.

My suggestion for the easiest work around would be to have users OAuth 2.0 into their own Google accounts where they want the created spreadsheet to end up.

3

I can make a feature request to our eng team to look at adding in a option to Google Sheets queries to specify a drive which the user has access to.

This might take some time so here are steps for a possible work around in the meantime!

To change the drive location of a Google Sheet using the API, you need to utilize the Google Drive API's "files.update" method, specifying the desired new parent folder ID in the request body, as the primary way to move a Google Sheet (considered a file in the Drive API) to a different folder within your Google Drive; essentially, you're "updating" the file's location by changing its parent folder ID.

Key points to remember:

  • Access the Drive API: Ensure you have enabled the Google Drive API in your Google Cloud project.
  • Identify the sheet file ID: You'll need the unique ID of the Google Sheet file you want to move.
  • Find the new parent folder ID: Get the ID of the target folder where you want to move the sheet.

How to do it:

  1. Make an API request: Use the "files.update" method from the Drive API.
  2. Set the request body:
  • "fileId": Paste the ID of the Google Sheet file you want to move.
  • "parent": Set the new parent folder ID.

Example code snippet (using the Google Apps Script language):

Code

function moveSheetToNewFolder(sheetId, newFolderId) {
  var sheetFile = DriveApp.getFileById(sheetId);
  sheetFile.setParents([DriveApp.getFolderById(newFolderId)]);
}

Important considerations:

  • Permissions:

Make sure your application has the necessary permissions to modify files in the user's Google Drive.

  • File type check:

Always verify that the file you are trying to move is a Google Sheet (using its mimeType "application/vnd.google-apps.spreadsheet").

4

I am using google scripts API to achieve this. I have a shared folder on my google drive which contains all files and subfolders. I have a script that moves a file. Then Retool calls that script via the API and voila...the file can be viewed by everyone having access to the shared folder. The link to that file is stored in my retool database to access via a Retool app as well...

1 Like