I am creating a web app for my organization using google apps script. The apps functionality boils down to reading data from different sheets and displaying it to the user.

The docs for using the SpreadsheetApp service seems to require that you provide the full r/w scope just to open a sheet; either by ID or URL i.e. openById() or openByURL() requires:

    https://www.googleapis.com/auth/spreadsheets

However I would like to only use the following scope.

    https://www.googleapis.com/auth/spreadsheets.readonly

I feel like it should be easy to do this in Apps Script but I cannot find anything in the documentation regarding it.

I have found something related to what I want here. However this seems very complicated requiring to manage API keys.

Is there a 'simple' read-only interface Google Apps Script provides or am I required to port my project over to a standard google cloud project, enable the Sheets API, figure out API keys, and understand how to apply oauth scopes separate from the appsscript manifest?

CodePudding user response：

Issue and workaround:

Unfortunately, in the current stage, when the Spreadsheet service (SpreadsheetApp) is used, the scope of https://www.googleapis.com/auth/spreadsheets is required to be used. It seems that this is the current specification. For example, when DriveApp is used before, the scope of https://www.googleapis.com/auth/drive was required to be used. But, by the update on the Google side, when DriveApp.getFiles() is used, https://www.googleapis.com/auth/drive.readonly can be used. So, I think that the situation of the Spreadsheet service might be changed in the future update.

But, in the current stage, it is required to use the current specification. So, it is required to use a workaround. In this answer, I would like to propose a workaround for using Spreadsheet with the scope of https://www.googleapis.com/auth/spreadsheets.readonly.

Usage:

1. Prepare a Google Apps Script project.

Please create a new Google Apps Script project. In this case, you can use both the standalone type and the container-bound script type. In this answer, I would like to use the standalone type. If you want to directly create it, please access https://script.new. By this, you can see the script editor of Google Apps Script. Please set the filename.

2. Enable Sheets API.

In order to retrieve the values from Spreadsheet using the scope of https://www.googleapis.com/auth/spreadsheets.readonly, Sheets API is used. So, please enable Sheets API at Advanced Google services. Ref

3. Prepare manifest file.

Before the script is used, please set the manifest file (appsscript.json). So, please show the manifest file. Ref

And, please add your expected scope as follows. You can see that Sheets API has already been included in enabledAdvancedServices. Please add "oauthScopes": ["https://www.googleapis.com/auth/spreadsheets.readonly"] as follows.

{
  "timeZone": "###",
  "dependencies": {
    "enabledAdvancedServices": [
      {
        "userSymbol": "Sheets",
        "version": "v4",
        "serviceId": "sheets"
      }
    ]
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": ["https://www.googleapis.com/auth/spreadsheets.readonly"]
}

4. Prepare sample script.

Please copy and paste the following script to your script editor and please set spreadsheetId, and save the script.

function myFunction() {
  const spreadsheetId = "###"; // Please set your Spreadsheet ID.

  const res = Sheets.Spreadsheets.get(spreadsheetId);
  console.log(res);
}

• About "Method: spreadsheets.get", this is from your question.

5. Testing.

When you run myFunction, the script is run. And, the values of the Spreadsheet are returned. By this, you can retrieve the values from Spreadsheet using Sheets API with the scope of https://www.googleapis.com/auth/spreadsheets.readonly.

Note:

• The scope of https://www.googleapis.com/auth/spreadsheets.readonly can be used for retrieving the values from Spreadsheet. When you want to update the Spreadsheet, it is required to use the scope of https://www.googleapis.com/auth/spreadsheets. Please be careful about this.

Reference:

• Method: spreadsheets.get