remove rows from google spreadsheet using ajax only

user3369120 0
Viewed 30

i want to remove entire rows from Gsheet using simple jquery my code is:

 var arr = {
            "deleteDimension": {
                "range": {
                    "sheetId": sheetId,
                    "dimension": "ROWS",
                    "startIndex": 276,
                    "endIndex": 277
                }
            }
        };
        $.ajax({
            type: 'delete',
            headers: { Authorization: "Bearer " + auth, 'content-type': 'application/json' },
          //  data: JSON.stringify(arr),
            url: 'https://sheets.googleapis.com/v4/spreadsheets/' + sheetId +'/sheetname!A276:A277' ,
            success: function (r) {
                console.log(r)
               ;
            }, error: function (r) {
                console.log(r)
                
            }
        });

I think the url or post data param is not correct. any help will appreciate.

google-sheets-api ajax google-sheets
1 Answers

Modification points:

  • In your script, sheetId of range property is the sheet ID. And, at the endpoint, please use Spreadsheet ID.
  • In order to use "deleteDimension", please use "Method: spreadsheets.batchUpdate".
  • In this case, the POST method is used.
  • Please use data: JSON.stringify(arr) for including the request body.

When these points are reflected in your script, it becomes as follows.

Modified script:

var spreadsheetId = "###"; // Please set your Spreadsheet ID here.
var sheetId = "###"; // Please set your sheet ID here.
var auth = "###"; // Please set your access token.

var arr = {
  "requests": [{
    "deleteDimension": {
      "range": {
        "sheetId": sheetId,
        "dimension": "ROWS",
        "startIndex": 276,
        "endIndex": 277
      }
    }
  }]
};
$.ajax({
  type: 'POST',
  headers: { Authorization: "Bearer " + auth, 'content-type': 'application/json' },
  data: JSON.stringify(arr),
  url: 'https://sheets.googleapis.com/v4/spreadsheets/' + spreadsheetId + ':batchUpdate',
  success: function (r) {
    console.log(r);
  }, error: function (r) {
    console.log(r);
  }
});
  • When this script is run, row 277 of the sheet of Spreadsheet is deleted using "Method: spreadsheets.batchUpdate".

Note:

  • This modified script supposes that your access token can be used for using "Method: spreadsheets.batchUpdate". Please be careful about this.

References:

Tanaike0
Related