How to Make a Clear Contents Button in Google Sheets

The 2 simplest ways to make a button in Google Sheets are by adding a clickable drawing that looks like a button and runs some Apps Script, or adding a custom item to the menu. This article will take you through how to do both and how to write some simple apps script that clears your sheet contents.

How to create a clear contents button

Here’s a step-by-step list of things to do to create a clear contents button in Google Sheets:

1. From the menu, select Insert > Drawing to create a new drawing on your sheet
2. Select text-box from the drawing toolbar and draw a small button sized box on your drawing canvas
3. Type ‘Clear Sheet‘ into your text box
4. Click on fill color and set a color for your button
5. Use the border weight and border color options to give your button a border (this makes it look more clickable)
6. Click on more options to open the text options
7. Set a text color and text alignment (centre) for your button text to distinguish it as a button
8. Click Save and Close to save your drawing
9. On your sheet, right-click your button and click the three dot menu that appears in the top right of it
10. Select Assign a script from the menu
11. Enter clearSheet as the name of the function in your sheet Apps script you want to execute whenever this button gets clicked
12. From the menu, select Extensions > Apps Script
13. In the Apps Script editor window that opens, replace what is there with the code below:

function clearSheet() {
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.clear(); //Clears the sheet of content and formatting (but NOT notes)
}Code language: JavaScript (javascript)

Now, whenever you left-click on your Clear sheet button, it will clear the contents and formatting from all the cells on the sheet!

If you need to clear just notes, or just formatting or just values and not formatting, there are other methods on the sheet object that let you do this. Either look at the code in our example sheet for this article or our article where we go into more depth on how to clear a sheet, a column, a row or just a range of cells.

If you’re stuck, here are a few screens of the process to help you along. When you create a new drawing, you’ll see a drawing canvas window like the one below. We’ve highlighted the text-box button on the top row of the toolbar. To get to the text color and alignment options, click the three dots (highlighted also) on the right side of the toolbar.

When you’ve added a button drawing, you’ll need to assign a script. To do this you need to right-click on the button and open the menu in the top right corner by left-clicking on the three dots.

When you open the Apps Script editor it will open a code.gs file with a default function called myFunction() added. You can replace this with the clearSheet function we gave you above.

How to create a clear contents menu item

Creating custom menu items in Google Sheets done within Apps Script. Here is some example code that creates a new menu called ‘My Menu’ with a ‘Clear Sheet’ option that does exactly that. To use this code, open Extensions > Apps Script and paste this code into the editor.

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  var myMenu = ui.createMenu('My menu!');
  myMenu.addItem('Clear sheet', 'clearSheet');
  myMenu.addToUi();
}

function clearSheet() {
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.clear(); //Clears the sheet of content and formatting (but NOT notes)
}Code language: JavaScript (javascript)

You can easily make your menu item clear a range of cells, a single row, an entire column or just everything on the sheet. You can also choose to clear notes, just values, or just formatting. If you want to know how to that read this article on how to write clear contents apps scripts.