Use the Advanced Sheets Service with permissions only for current sheet

Question

I want to use the Advanced Sheets Service, i.e., "Sheets.Spreadsheets.Values..." in my Google Apps Script add-on (to write to multiple ranges at once) only for the current sheet. But when I add the service to my Apps Script, it requires permission to

"See, edit, create, and delete all your Google Sheets spreadsheets".

Which is rather broad. I only want "https://www.googleapis.com/auth/spreadsheets.currentonly":

View and manage spreadsheets that this application has been installed on

I've tried using the service via UrlFetchApp HTTP requests to the corresponding REST API, passing the ID of the current spreadsheet. But that workaround failed with this error:

Request had insufficient authentication scopes. ([{"@type":"type.googleapis.com/google.rpc.ErrorInfo","reason":"ACCESS_TOKEN_SCOPE_INSUFFICIENT","domain":"googleapis.com","metadata":{"method":"google.apps.sheets.v4.SpreadsheetsService.GetSpreadsheet","service":"sheets.googleapis.com"}}])

After relaxing the scope in "appscript.json" to "https://www.googleapis.com/auth/spreadsheets" (which does not accomplish my goal) and retrying, I get another error:

PERMISSION_DENIED: Google Sheets API has not been used in project 719095722315 before or it is disabled. Enable it by visiting https://console.developers.google.com/apis/api/sheets.googleapis.com/overview?project=719095722315 then retry

When I visit that URL, it shows the error below and I can't enable any API:

You need additional access to the project: 719095722315

So, my questions:

1. Is there a way to use the Advanced Sheets Service with permissions just for the current sheet?
2. Is there any point in using UrlFetchApp requests instead of the Service itself from Apps Script?

Answer 1

Answer for 1st question

Is there a way to use the Advanced Sheets Service with permissions just for the current sheet?

I think that it might be able to say "Yes". In your situation, how about using a scope of https://www.googleapis.com/auth/drive.file? The official document of this says as follows.

https://www.googleapis.com/auth/drive.file: See, edit, create, and delete only the specific Google Drive files you use with this app

When this scope is used, only the files created by the client of the Google Apps Script project can be accessed. For example, when this scope is used, the script of the Google Apps Script project cannot access the files including the Spreadsheets you manually created. I thought that this might be close to your expected result.

When you want to test this situation, please do the following flow.

1. Create a new Google Apps Script project (for example, it's a standalone type.).

2. Please set the scope of https://www.googleapis.com/auth/drive.file to appsscript.json file. Namely, please add "oauthScopes": [ "https://www.googleapis.com/auth/drive.file" ] to it.

3. Please enable Sheets API at Advanced Google services.

4. As a test, please run the following script by inputting your sample Spreadsheet. When this script is run, an error like Requested entity was not found occurs. From this error, it is found that this Google Apps Script project cannot access your Spreadsheet.

   function sample1() {
     const ssId = "###"; // Please set the Spreadsheet ID of your Spreadsheet.
     Sheets.Spreadsheets.Values.batchUpdate({ valueInputOption: "USER_ENTERED", data: [{ range: "Sheet1", values: [["sample text"]] }] }, ssId);
   }
   

5. In order to access the Spreadsheet, please run the following script. By this script, a new Spreadsheet is created by this Google Apps Script project with the scope of https://www.googleapis.com/auth/drive.file. Please copy the spreadsheet ID.

   function sample2() {
     const ssId = Sheets.Spreadsheets.create({ properties: { title: "sample" }, sheets: [{ properties: { title: "Sheet1" } }] }).spreadsheetId;
     console.log(ssId);
   }
   

6. Please copy and paste the Spreadsheet ID created by sample2 to ssId of sample1. And, run the script. By this, "sample" is put into the cell "A1" of "Sheet1" of the created Spreadsheet.

From this flow, I guessed that you might be able to understand the scope of https://www.googleapis.com/auth/drive.file.

Answer for 2nd question

Is there any point in using UrlFetchApp requests instead of the Service itself from Apps Script?

When Sheets API is used with UrlFetchApp, it is required to use 2 scopes of https://www.googleapis.com/auth/script.external_request and https://www.googleapis.com/auth/drive.file. On the other hand, when Sheets API is used with Advanced Google services, Sheets API can be used with only https://www.googleapis.com/auth/drive.file. If you don't want to use other scopes, how about using Sheets API with Advanced Google services?

Note:

• From Do you know if you can use sheets api using spreadsheets.currentonly scope? mentioned by TheMaster in a comment, as additional information,
  • When a scope of https://www.googleapis.com/auth/spreadsheets.currentonly is used, the Spreadsheet service (SpreadsheetApp) can be restricted.
  • But, unfortunately, when Sheets API is used, an error like Request had insufficient authentication scopes. occurs. In this case, the scopes for using Sheets API are required to be used. For example, when the scopes of https://www.googleapis.com/auth/spreadsheets.currentonly and https://www.googleapis.com/auth/spreadsheets are used, Sheets API can access other Spreadsheet. It seems that Sheets API cannot be restricted with the scope of https://www.googleapis.com/auth/spreadsheets.currentonly.
• From this situation, how about the following patterns?
  • When only the Sheets API is used, only the scope of https://www.googleapis.com/auth/drive.file might be suitable.
  • When only the Spreadsheet service is used, only the scope of https://www.googleapis.com/auth/spreadsheets.currentonly might be suitable.
  • When both Sheets API and Spreadsheet service are used, the scopes of https://www.googleapis.com/auth/drive.file and https://www.googleapis.com/auth/spreadsheets.currentonly might be suitable.

References:

• OAuth 2.0 Scopes for Google APIs
• Method: spreadsheets.create
• Method: spreadsheets.values.batchUpdate