Search code examples
google-apps-scriptpermissionsgoogle-oauthscopes

How to narrow scopes for Google Apps Script to specific files

Main Issue:

I have two Google spreadsheets: a template and a master.

The template is sent out to people to make a copy of, fill in, and then I have a script that copies their tab to the master spreadsheet.

Currently the authorization is very broad- to view, edit, delete all of the users' spreadsheets. Since only two files are involved, I'd like to narrow the scope to just those two, mainly because the authorization process looks sketchy to the users right now.

Is there a way to limit scope to specific spreadsheets vs all spreadsheets?

What I've found/researched so far:

It seems that you can easily only limit scope to the current file, or demand access to all spreadsheets.

I've found two methods:

1) current only Add this to the top of the script:
/** * @OnlyCurrentDoc */ This means that I can't copy the tab to the master spreadsheet.

2) Set Explicit Scopes Go into the manifest as described here: https://developers.google.com/apps-script/concepts/scopes Seems that this also only allows for current file only or full spreadsheet access.

Similar Questions:

Others have asked similar questions but haven't gotten an answer to this specific issue where there are multiple specific files in question.

How to use narrower Google Apps Script Authorization Scope when accessing file from own drive

How to narrow down the auth/drive scope for a google apps script?

Code and potential ideas:

I haven't tried whitelisting- could that help? Would I whitelist the master spreadsheet on the template?

The authorization when I tried '@OnlyCurrentDoc' defines the permission as 'View and manage spreadsheets that this application has been installed in'. Can I install this application in my master spreadsheet and have them talk? Any ideas?

//this is pretty much the only applicable code:

var admin_ss = SpreadsheetApp.openById([ID]);

var this_ss = SpreadsheetApp.getActiveSpreadsheet();
Solution

  • Possible approaches:

    • CurrentOnly scope: 

      https://www.googleapis.com/auth/spreadsheets.currentonly
      • You can install it in many sheets as a add-on by publishing your script OR
      • Publish a web-app in the master spreadsheet. You can create a doPost() function to receive and authorize any request to write to the master spreadsheet from the slave spreadsheets.

    • Drive.file scope: 

      https://www.googleapis.com/auth/drive.file
      • You can use this scope to access any file created/opened with this project.
      • You can create a web app to execute as the user and to create a slave spreadsheet from the master spreadsheet. Master needs to be shared to the user OR
      • Use Google picker/web app to make the user manually choose/open the master/slave spreadsheet to provide access to the app to only those sheets.

    References: