Error "Expected OAuth 2 access token" on container-bound script

Question

I want to filter a google sheet for one value only, as in this question/answer.

I have activated sheets-api and looked through documentation, how to add oauth2 verification. Concluded, that it should happen automatically, since the script is container-bound.

var ss = SpreadsheetApp.getActiveSpreadsheet();

SpreadsheetApp.setActiveSheet(ss.getSheetByName('Aufgaben')); // change active sheet

var filterCriteria = {"type":"TEXT_CONTAINS","values":[{"userEnteredValue": "0001" }]}; // set filter

var filterSettings = {};
filterSettings.range = {sheetId: ss.getSheetByName('Aufgaben').getSheetId(), startRowIndex: 1};
filterSettings.criteria = {};
var columnIndex = 4;

filterSettings['criteria'][columnIndex] = {'condition': filterCriteria}; // Modified

var request = {setBasicFilter: {filter: filterSettings}};
Browser.msgBox(JSON.stringify({'requests': [request]})); //only works, when this line is added!
Sheets.Spreadsheets.batchUpdate({'requests': [request]}, ss.getId()); `

The code works, but ONLY if I add Browser.msgBoxone line above Sheets.Spreadsheets.batchUpdate(). Otherwise I get this error: Request is missing required authentication credential. Expected OAuth 2 access token, login cookie or other valid authentication credential. See https://developers.google.com/identity/sign-in/web/devconsole-project. at onEdit(test:127)

Since I've already spent hours trying to solve this. Thank you.

I cannot understand about The code works, but ONLY if I add Browser.msgBoxone line above Sheets.Spreadsheets.batchUpdate(). When the script is run by script editor, it works. But if it's run by the simple trigger and custom function, such error occurs. Custom function cannot use Browser.msgBox. So if you run it with the simple trigger, how about modifying to the installable trigger? Ref At that time, please modify the function name in order to prevent the double running. If I misunderstood your situation, I apologize. — Tanaike
My code is triggered by onEdit(). I inserted Browser.msgBox...as a simple way to check the parameters and all of a sudden, the code did't produce the error, that was chance. I just don't understand, how this could prevent the error? If I comment out or delete Browser.msgBox...I get the oauth-error. Even works with Browser.msgBox('hello'). Very strange. — hariioom
You need "installable" edit trigger instead of a simple one. — TheMaster
Thank you for replying. At first, I have to apologize for my poor English skill. When your script is used, TheMaster`s comment will resolve your issue. — Tanaike
@Tanaike I appreciate your help. I don't know why, but with sheets-api I did not get it to work. But ADW 's answer worked for me – Google Apps Script Spreadsheet Service instead. — hariioom

ADW ADW · Accepted Answer · 2019-08-04T09:23:22

Perhaps the

// set filter

is causing problems?

This worked for me without oauth2 verification:

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  SpreadsheetApp.setActiveSheet(ss.getSheetByName('Aufgaben')); // change active sheet

  var filterCriteria = {"type":"TEXT_CONTAINS","values":[{"userEnteredValue": "4210"}]}; // Modified

  var filterSettings = {};
  filterSettings.range = {sheetId: ss.getSheetByName('Aufgaben').getSheetId(), startRowIndex: 1};
  filterSettings.criteria = {};
  var columnIndex = 4;

  filterSettings['criteria'][columnIndex] = {'condition': filterCriteria}; // Modified

  var request = {setBasicFilter: {filter: filterSettings}};
  Sheets.Spreadsheets.batchUpdate({'requests': [request]}, ss.getId());

Edit

I'm was not able to replicate the issue with the code you provided. Even with the message box, I got an error.

If you are triggering the filtering function when the sheet is edited, you may want to try a version of the Google Apps Script Spreadsheet Service:

function onEdit(e) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var criteria = SpreadsheetApp.newFilterCriteria().whenTextContains("0001").build();
  var lastRow = sheet.getLastRow();
  var lastCol = sheet.getLastColumn();
  var range = sheet.getRange(2, 1, lastRow - 1, lastCol);
  range.createFilter().setColumnFilterCriteria(4, criteria);
}

Refs

newFilterCriteria()

whenTextContains(text)

createFilter()

setColumnFilterCriteria(columnPosition, filterCriteria)

Error "Expected OAuth 2 access token" on container-bound script

1 Answers