Google Spreadsheet API returning error "Cannot call method "batchUpdate" of undefined."

Question

I'm trying to use a script given in answer to this question google spreadsheet script - Enable filter with a range by script

The aim is to create a basic filter on a spreadsheet.

This is the script

function applyFilter() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ssId = ss.getId();
var dataSheet = ss.getActiveSheet();
var lastRow = dataSheet.getLastRow();
var lastColumn = dataSheet.getLastColumn();
var sheetId = dataSheet.getSheetId();

var filterSettings = {
"range": {
  "sheetId": sheetId,
  "startRowIndex": 0,
  "endRowIndex": lastRow,
  "startColumnIndex": 0,
  "endColumnIndex": lastColumn
}
};
 var requests = [{
"setBasicFilter": {
  "filter": filterSettings
}
}];

Logger.log(requests );
         Logger.log(" ssid: " + ssId);
try{
Sheets.Spreadsheets.batchUpdate({'requests': requests}, ssId);
}
catch(e){Logger.log(e.message)}
}

These are the logs for my last attempt at running it the final line is the the error I'm getting

[17-09-17 10:32:57:700 BST] [{setBasicFilter={filter={range={endColumnIndex=26, endRowIndex=3756, sheetId=519417536, startColumnIndex=0.0, startRowIndex=0.0}}}}] [17-09-17 10:32:57:701 BST] ssid: 1-05tNvAddY6gwzK9GYC4aMG347XARy_yUXZRcXxgHIk [17-09-17 10:32:57:702 BST] Cannot call method "batchUpdate" of undefined.

The ssid is correct, the sheetId is correct. I have switched on the Google Sheets API in resources - I have enabled it in the Google API console

I'd be grateful if someone could tell me what I'm doing wrong

Tanaike · Accepted Answer · 2017-09-19T22:40:13.627

0

From your question, it is found that you have already enabled Sheets API at API console. But the error says Cannot call method "batchUpdate" of undefined.. So I think that Sheets API may have not been enabled at Advanced Google services. Please confirm it as follows.

In the script editor, select Resources > Advanced Google services
In the dialog that appears, click the on/off switch for Google Sheets API v4. Please turn on.
Click OK button.

If this was not useful for you, I'm sorry.

Edit :

In this sample, Sheets API v4 is used by UrlFetchApp.fetch() instead of Advanced Google services.

function applyFilter() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssId = ss.getId();
  var dataSheet = ss.getActiveSheet();
  var lastRow = dataSheet.getLastRow();
  var lastColumn = dataSheet.getLastColumn();
  var sheetId = dataSheet.getSheetId();

  var filterSettings = {
  "range": {
    "sheetId": sheetId,
    "startRowIndex": 0,
    "endRowIndex": lastRow,
    "startColumnIndex": 0,
    "endColumnIndex": lastColumn
  }
  };
   var requests = [{
  "setBasicFilter": {
    "filter": filterSettings
  }
  }];
  Logger.log(requests );
  Logger.log(" ssid: " + ssId);

  var url = "https://sheets.googleapis.com/v4/spreadsheets/" + ssId + ":batchUpdate"
  var params = {
    method:"post",
    contentType: "application/json",
    headers: {"Authorization": "Bearer " + ScriptApp.getOAuthToken()},
    payload: JSON.stringify({"requests": requests}),
    muteHttpExceptions: true,
  };
  var res = UrlFetchApp.fetch(url, params).getContentText();
  Logger.log(res)
}

edited Sep 19 '17 at 22:40

answered Sep 17 '17 at 23:22

Tanaike

181,128
11
97
165

thanks for your support but the Sheets API is definitely showing as ON and it is enabled in the Google API console – Martin Molloy Sep 18 '17 at 20:32
@Martin Molloy I'm sorry for the inconvenience. In my environment, your script works fine under enabling Sheet API at API console and Advanced Google services. Is the spreadsheet you use yours or shared one? If it's the later, can you try it to yours? – Tanaike Sep 18 '17 at 22:18
I've copied this script into a new project with no other scripts - itworks exactly as it should. If I turn the sheets API off I getthe error message "Sheets" is not defined. If I turn Sheets API on or off in the original project it makes no difference. The error message is always "Cannot call method "batchUpdate" of undefined". I can't work out why I'm getting a different error message. – Martin Molloy Sep 19 '17 at 15:42
Could the other APIs in the project be causing a problem? – Martin Molloy Sep 19 '17 at 15:45
@Martin Molloy I updated my answer. Please confirm it. If your Advanced Google services cannot be used (I don't know this reason. I'm sorry.), how about the use of ``UrlFetchApp.fetch()``? In the sample, Sheets API v4 is used by ``UrlFetchApp.fetch()`` instead of Advanced Google services. If the error occurs, you can see it using ``Logger.log(res)``. – Tanaike Sep 19 '17 at 22:41
thank you ever so much that sorted it - don't know why the Advanced Google Service doesn't work – Martin Molloy Sep 20 '17 at 19:40
@Martin Molloy Yes. I don't know the reason, too. If you found the reason, I'm glad if you let me know. Thank you, too. – Tanaike Sep 20 '17 at 22:00

Google Spreadsheet API returning error "Cannot call method "batchUpdate" of undefined."

1 Answers1

Edit :