google apps script - Creating a spreadsheet file in a folder without using auth/drive scope

Question

I had asked a question before on creating a file and moving it to a folder, but now as I'm moving to publish this simple add-on I'm having issues with the auth/drive scope needed to move a file. auth/drive is a sensitive scope and I'd have to go through a review process just to publish. I tried changing the scope to auth/drive.file but apparently that doesn't apply to the moveTo method.

I find it frustrating that I have to jump through hoops just to create a file in the correct location. Are there any other ways to move create a file in a folder that doesn't require sensitive or restricted scopes?

Here is the function

function createSpreadsheet(form){
  var spreadsheetName = [form.getTitle() + " Results"];
  var thisFileId = form.getId();
  var parentFolder = DriveApp.getFileById(thisFileId).getParents().next();
  var spreadsheet = SpreadsheetApp.create(spreadsheetName);
  var spreadsheetID = spreadsheet.getId();
  var spreasheetFile = DriveApp.getFileById(spreadsheetID);
  spreasheetFile.moveTo(parentFolder);
  return spreadsheet;
}

Answer 1

I believe your goal as follows.

• You want to narrow the scopes of your Google Apps Script.
  • You want to use https://www.googleapis.com/auth/drive.file for this.

Modification points:

In this case, when Drive service is used, the scope of https://www.googleapis.com/auth/drive is required to be used. So in order to narrow the scopes, I would like to propose to use Drive API instead of Drive service.

The flow of this modified script is as follows.

1. Retrieve the parent folder ID of "thisFileId".
   • In this case, the scope of https://www.googleapis.com/auth/drive.metadata.readonly is used.
2. Create new Spreadsheet to the specific folder.
   • In this case, the scope of https://www.googleapis.com/auth/drive.file is used.
   • The Spreadsheet is created and moved to the specific folder using the method of "Files: insert" by one API call.
3. Retrieve Spreadsheet object.
   • In this case, the scope of https://www.googleapis.com/auth/spreadsheets is used.

This flow is the same process with your script. When above flow is reflected to your script, it becomes as follows.

Modified script:

Before you use this script, please enable Drive API at Advanced Google services.

function createSpreadsheet(form){
  var spreadsheetName = [form.getTitle() + " Results"];
  var thisFileId = form.getId();
  
  // 1. Retrieve the parent folder ID of "thisFileId".
  // In this case, the scope of "https://www.googleapis.com/auth/drive.metadata.readonly" is used.
  var folderId = Drive.Files.get(thisFileId).parents[0].id;
  
  // 2. Create new Spreadsheet to the specific folder.
  // In this case, the scope of "https://www.googleapis.com/auth/drive.file" is used.
  var spreadsheetId = Drive.Files.insert({title: spreadsheetName, mimeType: MimeType.GOOGLE_SHEETS, parents: [{id: folderId}]}).id;
  
  // 3. Retrieve Spreadsheet object.
  // In this case, the scope of "https://www.googleapis.com/auth/spreadsheets" is used.
  return SpreadsheetApp.openById(spreadsheetId);
}

Note:

• This sample script is for the script in your question. So when you are using the methods for using the scope of https://www.googleapis.com/auth/drive in your other part, this script might not be useful. Please be careful this.
• From form.getTitle() and form.getId(), the scope of https://www.googleapis.com/auth/forms might be required to be included. Please be careful this. And also, when you are using other methods for using other scopes, please include them.
• The Spreadsheet is created with the scope of https://www.googleapis.com/auth/drive.file, the Spreadsheet can be used by the application using the scope. Please be careful this.

References:

• Files: get
• Files: insert