This article will focus on examples of how to write Apps Script to clear different ranges of your sheet contents.
In order to run this script you’ll need to pick from the one of 3 ways to make a button on Google Sheets. You can create a clickable drawing that runs script, you can add a custom menu item or you can add a tick box to a cell and respond whenever someone clicks it. Each method has drawbacks and benefits. But whichever one you use, it will have to run some Apps Script to clear the contents of your cells.
If you want to jump straight in please open our example sheet for this article called Clear Contents Buttons! NOTE: Clickable drawing buttons don’t work on mobile sheets, so be sure to open the example on a desktop. It has a button to clear the entire sheet, a single column, a single row, a range of data and another button at the end to generate some data to clear!
How to clear the entire sheet contents
Once you’ve created a button to run your script, clearing a sheet is a simple task. The Sheet object has different methods that allow you to clear either just the contents of a sheet (the values), just the formatting, just the conditional format rules or just the notes. Or you can call the clear() method that clears the content and formatting (but not the notes).
Here’s some example code that does this and shows how to clear only certain types of sheet data.
function clearEntireSheet() {
var sheet = SpreadsheetApp.getActiveSheet();
sheet.clear(); //Clears the sheet of content and formatting (but NOT notes)
//Alternatively or Optionally
sheet.clearNotes(); //Clears the sheet notes
sheet.clearConditionalFormatRules(); //Removes only conditional format rules from the sheet
sheet.clearContents(); //Clears the sheet contents, but keep formatting information
sheet.clearFormats(); // Clears the sheet formatting, but keep the contents
}Code language: JavaScript (javascript)How to clear only a certain sheet range
To clear a certain range of cells in a sheet, you first need to get a Range object from the Sheet. You can do this using simple R1C1 notation by passing a range to the Sheets getRange() function.
If you’re not familiar with how R1C1 (Row and Column) notation works, there’s an easy way of finding out a range by simply selecting the cells you want in your Google Sheet and looking in the top left corner next to the formula bar. There you’ll see your selected range. In the example below, we selected 4 cells, and we can see A2:B3 displayed.
Once you have the range you want to clear, you just need to pass it to Sheet.getRange() and then call the clear method appropriate to what you want to do.
Much like the Sheet object, Range has different methods to allow you to clear content, formatting, notes, separately. And a clear() method that clears content and formatting together.
Range also gives you the ability to clear data validation rules using clearDataValidations().
Here’s an example of how to use it:
function clearRange(r1c1) {
var range = SpreadsheetApp.getActiveSheet().getRange("A5:G10");
range.clear(); // Clears the content and formatting (but NOT notes)
//Alternatively or Optionally
range.clearNote(); // Clears the notes in the range
range.clearContent(); // Clears the content of the range, leaving the formatting alone
range.clearDataValidations(); // Clears the data validation rules for the range
range.clearFormat(); // Clears formatting for this range, keeps the contents
}Code language: JavaScript (javascript)How to clear a single row
To clear a single row, we call the clear() function on a range that relates to a single row. In our example, “15:15” will clear the entire of row 15.
function clearRow() {
SpreadsheetApp.getActiveSheet().getRange("15:15").clear();
}Code language: JavaScript (javascript)How to clear a single column
To clear a single column, we call the clear() function on a range that indicates a single column. Here, “B:B” will clear the entire of column B.
function clearColumn() {
SpreadsheetApp.getActiveSheet().getRange("B:B").clear();
}Code language: JavaScript (javascript)