How to remove a blank row in google sheet using google sheets api v4 javascript?

user_15790364 0
Viewed 687
var params1 = {
      spreadsheetId: '1g9y32IkyujOupw6O6eRhtlCcwhn5vv9mM_Yr4peRRmo', 
      range: str,
};
    
var clearValuesRequestBody = {
};
    
var request = await gapi.client.sheets.spreadsheets.values.clear(params1, clearValuesRequestBody);

Above code is to delete a row from the google sheet. Now, my question is that how to remove that blank row from the google sheet by moving rest of the rows?

google-sheets-api javascript
1 Answers

Answer:

You need to use a batchUpdate to do this.

Request Example:

{
  "requests": [
    {
      "deleteDimension": {
        "range": {
          "sheetId": sheetId,
          "dimension": "ROWS",
          "startIndex": 0,
          "endIndex": 1
        }
      }
    }
  ]
}

The above request will delete the first row from a sheet with given gid.

The rows are 0-indexed so starting at 0 and ending at 1 will delete the first row. Likewise, starting at 8 and ending at 18 will delete rows 9-18.

The method is as follows:

gapi.client.sheets.spreadsheets.batchUpdate(resource)

Note: This is spreadsheets.batchUpdate and NOT spreadsheets.values.batchUpdate.

All together now:

const resource = {
  "requests": [
    {
      "deleteDimension": {
        "range": {
          "sheetId": sheetId,
          "dimension": "ROWS",
          "startIndex": 0,
          "endIndex": 1
        }
      }
    }
  ]
}

const response = gapi.client.sheets.spreadsheets.batchUpdate({
  "spreadsheetId": "your-spreadsheet-id",
  "resource": resource
})
  .then(function(response) {
    console.log("Response is: \n", response)
  }, function(err) { 
       console.error("Execute error", err)
     })

In order to get which rows are blank, you will have to use spreadsheets.values.get and manually loop through the response.

The response will be of the following form; you will need to loop through response.values:

{
  "range": "Sheet1!A1:Z1000",
  "majorDimension": "ROWS",
  "values": [
    [] // each row is an array
  ]
}

In this example I am using a Spreadsheet that looks like this:

enter image description here

For this spreadsheet, response will be:

{
  "range": "Sheet1!A1:Z1000",
  "majorDimension": "ROWS",
  "values": [
    [ "1", "1", "1", "1", "1", "1", "1", "1", "1", "1" ],
    [ "2", "2", "2", "2", "2", "2", "2", "2", "2", "2" ],
    [ "3", "3", "3", "3", "3", "3", "3", "3", "3", "3" ],
    [ "4", "4", "4", "4", "4", "4", "4", "4", "4", "4" ],
    [],
    [ "6", "6", "6", "6", "6", "6", "6", "6", "6", "6" ],
    [],
    [],
    [ "9", "9", "9", "9", "9", "9", "9", "9", "9", "9" ],
    [ "10", "10", "10", "10", "10", "10", "10", "10", "10", "10" ],
  ]
}

so we just need to check what elements of response.result.values are empty arrays: Assume that the Spreadsheet has columns up to ZZZ so to encapulate the whole range, you can make the request:

const emptyRows = []

gapi.client.sheets.spreadsheets.values.get({
  "spreadsheetId": "your-spreadsheet-id",
  "range": "A:ZZZ"
})
  .then(function(response) {
    response.result.values.forEach(function(row, index) {
      if (row.length == 0) emptyRows.push(index)
    })  
  }, function(err) { 
       console.error("Execute error", err)
     })

and then loop through this array backwards, passing the values into the previous batchUpdate request to completely remove them from the sheet. It's important delete from bottom up so to not change row numbers and accidentally delete rows with data in them.

Note: this will not delete empty rows past the last data row, only empty rows within the data set.

References:

user_115514680
Related