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?
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)
7 Likes
There is a Module called “Perform Function” or something similar 
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)
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
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)
Lol how did i miss this?
you actually just blew my mind @JimTheMondayMan
gonna be a big change in how I build sheets scenarios.
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
1 Like
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:
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?
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