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.
Create a new Google Apps Script project (for example, it's a standalone type.).
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.
Please enable Sheets API at Advanced Google services.
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);
}
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);
}
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:
