Using Google Sheets API from within Apps Script for same Sheet

Question

Using an Apps Script that's bound to a Spreadsheet to effect the sheet is very straight forward, via the SpreadsheetApp global. However, there's some functionality, such as getting/setting filters on a sheet, that can only be accessed from the Google Sheets REST API.

I've seen an example that uses UrlFetchApp from within an Apps Script, to call the Google Sheet API, but it's written as if the Apps Script is not actually bound to a particular Spreadsheet.

function getSheetBasicFilter(ssId, sheetId) {
  var params = {
    headers: {
      "Authorization": "Bearer " + ScriptApp.getOAuthToken()
    }
  };
  var base = "https://sheets.googleapis.com/v4/spreadsheets/";
  var partialRespParams = "?fields=sheets%2FbasicFilter";
  var resp = UrlFetchApp.fetch(base + ssId + partialRespParams, params);
  var sheets = JSON.parse(resp.getContentText()).sheets;
  for (var i = 0; i < sheets.length; i++) {
    if (sheets[i].basicFilter 
        && (sheets[i].basicFilter.range.sheetId == sheetId 
            || (!sheets[i].basicFilter.range.sheetId && sheetId == 0))) {
      return sheets[i].basicFilter;
    }
  }
  return null;
}

When I attempt to call this function from within a Spreadsheet Bound Apps Script, I get an error 'Request had insufficient authentication scopes'.

Since the script is running as the user that owns the document, and ScriptApp.getOAuthToken() is being used, it's not clear why this error is occurring.

Any help would be appreciated.

Community · Accepted Answer · 2017-05-23 12:13:50Z

1

First, make sure you enable the Sheets API in your Developers Console.

The error "Request had insufficient authentication scopes" is an error in the OAuth 2.0 token provided in the request specifies scopes that are insufficient for accessing the requested data.

Make sure you use the correct and all necessary scopes, and check this Authorizing requests with OAuth 2.0 if you properly follow the steps here.

You can also try to revoke the access and try to redo it.

For more information, check the documentation of ScriptApp.getOAuthToken() and this thread

edited May 23, 2017 at 12:13

CommunityBot

11 silver badge

answered Oct 4, 2016 at 10:00

KENdi

7,7942 gold badges18 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2672083 Over a year ago

Thank you for your reply. Before submitting this question, I had explored all the recommendations you made. Regarding the use of 'all necessary scopes', there does not appear to be a way to specify the scopes, from within the Apps Script, necessary to access the google sheets API. The thread you reference passively uses 'DriveApp' which implicitly adds the necessary scopes to Apps Script (which I believe can be seen in the Apps Script properties dialog); How is this done for the Google Sheets API?

KENdi Over a year ago

Did you already try to use muteHttpExceptions option to examine full response? Maybe this issue is not all about scopes. Check this link on how to use UrlFetchApp.

user2672083 Over a year ago

Yes, tried that as well. The 'full' raw text response is { "error": { "code": 403, "message": "Request had insufficient authentication scopes.", "status": "PERMISSION_DENIED" } }

Collectives™ on Stack Overflow

Using Google Sheets API from within Apps Script for same Sheet

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related