Clear google sheet before adding new rows

[Martin_Gauthier]

When writing to the google sheet is there a way to clear the sheet (Except header row) in one operation before the write so its a fresh view of the data each time?
I may have 500+ rows and i hope i don’t have to iterate though each as this will be costly in operations on a daily basis!

Thanks

[AnthonyatXRay]

I believe you can do that using the “Clear Values from a Range” module in GSheets

Select the module and enter the range of cells you would like to clear. Probably start with A2 and go down all the way to the bottom or atleast more than the longest likely sheet you will have at the end.

[Olly_Ats]

Hey @Martin_Gauthier

You can use the ‘Clear values from a range’ module:

You would need to set a range that is large enough to cover the sheet. I hope that helps you.

[samliew]

Personally I prefer to delete all rows and unused columns from the sheet.

After adding a new sheet to your workbook, there will be a thousand rows.

Here’s the batchUpdate endpoint you can call using the “Make an API call” module, that will delete everything except the first row:

(module [5] is my “Add a Sheet” module)

POST — spreadsheets/{{5.spreadsheetId}}:batchUpdate

{
  "requests": [
    {
      "deleteDimension": {
        "range": {
          "sheetId": {{5.replies[].addSheet.properties.sheetId}},
          "dimension": "ROWS",
          "startIndex": 1,
          "endIndex": {{5.replies[].addSheet.properties.gridProperties.rowCount}}
        }
      }
    },
    {
      "deleteDimension": {
        "range": {
          "sheetId": {{5.replies[].addSheet.properties.sheetId}},
          "dimension": "COLUMNS",
          "startIndex": 4,
          "endIndex": {{5.replies[].addSheet.properties.gridProperties.columnCount}}
        }
      }
    }
  ]
}

[samliew]

Glad I could help!

Don’t forget to like and bookmark this very useful answer so you can get back to it easily in future!