I have a standalone script that uses the Sheets API. There are only two calls:

SpreadsheetApp.openById(spreadsheetID).getSheetByName("Answers")
SpreadsheetApp.openById(otherspreadsheetID).getSheetByName("Questions").getDataRange().getValues()

So it reads from one file and writes to a different file. The script is set to run as a webapp as me, so on initial run, by default, this triggers a broad scope to view/edit/delete all sheets. I want to limit that. I see this is a scope I could manually set: https://www.googleapis.com/auth/drive.file (docs).

But I don't get how to then set my two spreadsheets as files that have been "opened or created" by the app so that the scope is valid for those files. I tried creating a function that creates two new sheets (I figured this counts as "a drive file that you created with this app") but even the Spreadsheetapp.create() function throws a "do not have permission" error.

I might be misunderstanding how this scoping works?

CodePudding user response：

Issue and workaround:

When SpreadsheetApp.openById() is used, the scope of https://www.googleapis.com/auth/spreadsheets is used. It seems that this is the current specification.

So, as a workaround, how about using Sheets API? When the Sheets API is used, you can use the scope of https://www.googleapis.com/auth/drive.file.

As an important point, the official document of the scope of https://www.googleapis.com/auth/drive.file is as follows.

Per-file access to files created or opened by the app. File authorization is granted on a per-user basis and is revoked when the user deauthorizes the app.

So, when you want to retrieve the data from the Spreadsheet using the scope of https://www.googleapis.com/auth/drive.file, at first, the Spreadsheet is required to be created by the scope. In this answer, I would like to introduce the following flow.

1. Set the scope of https://www.googleapis.com/auth/drive.file to Google Apps Script project.
2. Create a new Spreadsheet.
3. Retrieve values from the Spreadsheet.

Usage:

1. Set scope.

Please create a new Google Apps Script. From your question, I understood that you are using the standalone type. For this, please set the scope of https://www.googleapis.com/auth/drive.file to the manifest file. Ref Please add "oauthScopes": ["https://www.googleapis.com/auth/drive.file"] to the file of appsscript.json. By this, the specific scope can be used.

2. Create a new Spreadsheet.

Before you use this script, please enable Sheets API at Advanced Google services. When you run the following script, a new Spreadsheet is created and you can see the spreadsheet ID at the log. Please copy the ID. This ID is used in the next script. By this, a new Spreadsheet is created by the scope of https://www.googleapis.com/auth/drive.file.

function createNewSpreadsheet() {
  const id = Sheets.Spreadsheets.create({properties: {title: "sample"}}).spreadsheetId;
  console.log(id)
}

• When you try to retrieve the values from the existing Spreadsheet which is not created by this Google Apps Script project, an error of Requested entity was not found. occurs. So in this section, a new Spreadsheet is created by this Google Apps Script project.

3. Get values from Spreadsheet.

Before you use this script, please open the created Spreadsheet and check the sheet name. And, as a sample, please put the sample values to the cells. When you run the following script, the values are retrieved from the Spreadsheet and you can see them at the log.

function getValues() {
  const spreadsheetId = "###"; // Please set the spreadsheet ID.
  const obj = Sheets.Spreadsheets.Values.get(spreadsheetId, "Sheet1");
  const values = obj.values;
  console.log(values)
}

References:

• Manifest structure
• Method: spreadsheets.create
• Method: spreadsheets.values.get