Delete all Google Sheet rows except header

Is there a way in ONE operation to delete all rows in a Google Sheet except the first header row?

I don’t think that is possible with the tools Make provides (correct me if I am wrong), but you can add a script to your sheet like this one:

function deleteAllButHeader(){
  const app = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = app.getActiveSheet();
  sheet.getRange(2,1,sheet.getLastRow(), sheet.getLastColumn()).setValue("")

}

This delets everything except the first row (if you have more header rows just change the first argument of getRange() to the row number you want to start deleting)

1 Like

Interesting, but how would Make cause the function to execute?

@Dmitri_Martin

To clear a range, you can use the clear cell module:

To DELETE multiple rows, you would need to use the API call module:

Notes:
Replace “AAAA…AAAA” in the URL with your spreadsheet ID
In the body, replace “123456789” with your sheet ID
Rows are indexed from 0. The reference below refers to rows 2 through 1000001
“endRowIndex”: 1000000,
“startRowIndex”: 1


Jim - The Monday Man (YouTube Channel)
What is Make & How can it help you with monday?
We Create Custom Solutions - Your Make or Ours
Schedule a 1-on-1 Tutorial Session (for monday, Make or Excel)

6 Likes

There is a Module called “Perform Function” or something similar :slight_smile:

@Richard_Johannes

You are correct that there is a Google Sheets module called Perform a Function.

But it is basically provides webhook functionality to call a Make scenario from a Google Spreadsheet. It is not intended to call Sheet functions from Make.


Jim - The Monday Man (YouTube Channel)
What is Make & How can it help you with monday?
We Create Custom Solutions - Your Make or Ours
Schedule a 1-on-1 Tutorial Session (for monday, Make or Excel)

For bulk update, append and clear, we have built a tool where we can save tons of operations.

2 Likes

@JimTheMondayMan clear cell module works best for what Dimitri wants I think. It’s one operation. I never realized you can have a range in there. Maybe the wording should be looked at. It reads like it works only for one cell.

2 Likes

@OHA_Daniel

You might be right. But he did write DELETE. And, depending on the details, the actual delete is also only one operation.


Jim - The Monday Man (YouTube Channel)
What is Make & How can it help you with monday?
We Create Custom Solutions - Your Make or Ours
Schedule a 1-on-1 Tutorial Session (for monday, Make or Excel)

clear a cell

:question: Lol how did i miss this?

you actually just blew my mind @JimTheMondayMan
gonna be a big change in how I build sheets scenarios.

:brain: I know how though,

i just got in the habit of not having it, and never actually went back to check or verify.
neat to know


Submitting This to Support(clickme)

+^^this convo


I adapted years ago to clear a range not being an option… thus had no need to go back and check later. would have completely changed my make trajectory.

Hopefully they change the language for future makers.

1 Like

@JugaadiTech

The module wording certainly implies single cell only. I don’t remember what made me test it. I was probably trying to figure out how to just make the call to the API directly. But there you go.


Jim - The Monday Man (YouTube Channel)
What is Make & How can it help you with monday?
We Create Custom Solutions - Your Make or Ours
Schedule a 1-on-1 Tutorial Session (for monday, Make or Excel)

2 Likes

While the Clear a Cell module does allow clearing of a range, it still costs the same number of operations:

2

The same hold true for the API approach:

I’m not sure how to implement the function approach, can a function be called by an API? Would doing so hide the number of rows deleted from Make so it wouldn’t count them as operations?

@Dmitri_Martin

The problem there is not the method. This issue is the Search Rows module is returning 29 bundles. So, everything after that will execute 29 times.

If I am correctly understanding your objective, it is not needed at all. The range specified in the Cell parameter does not need to be exact. If you never have more than say 1,000,000 rows, and you want to clear through the last row, “A2:K1000000” will work just fine.


Jim - The Monday Man (YouTube Channel)
What is Make & How can it help you with monday?
We Create Custom Solutions - Your Make or Ours
Schedule a 1-on-1 Tutorial Session (for monday, Make or Excel)

1 Like

Of course! Sorry I missed that. You’ve solved the issue!

1 Like