Add A Google Sheets Button To Run Scripts

Learn how to add a Google Sheets button to run your Google Apps Script functions.

Let’s see how this works with a simple example.

Imagine you have an invoice template you use on a regular basis, but it’s a pain to clear out all the values each time you need to start over. Well, you can add a button to Google Sheets so you can run scripts and clear your invoice with a single button click.

Google Sheets button

Let’s start by creating a basic invoice template with placeholders to hold information:

Add button to Google Sheets invoice example

The user can enter information into cells B5, B8, E5 and E6 (shown in yellow).

In the script editor, accessed through Tools > Script Editor, add a very simple script to clear these specific cells out:

function clearInvoice() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var invoiceNumber = sheet.getRange("B5").clearContent();
  var invoiceAmount = sheet.getRange("B8").clearContent();
  var invoiceTo = sheet.getRange("E5").clearContent();
  var invoiceFrom = sheet.getRange("E6").clearContent(); 

You can run this function from the script editor and it will clear out the contents of the invoice.

But that’s a pain.

You don’t want to have to open up the script editor every time. You want to do that directly from your Google Sheet.

To do that, add a Google Sheets button.

You add a button via the Insert > Drawing menu.

This brings up the drawing editor where you can easily add a box and style it to look like a button:

Google Sheets button drawing

When you click Save and Close, this drawing gets added to your Google Sheet. You can click on it to resize it or drag it around to reposition it.

To assign a script, click the three little dots in the top right of the drawing and select Assign Script:

Google Sheets button assign script

Then type in the name of the function you want to run from your Apps Script code. In this example, choose the clearInvoice function (i.e. like the code above!).

Now, when you click the button it will clear out the invoice for you!

Button with apps script in google sheets

Note: to edit or move the button after you’ve assigned it to a script, you now need to right-click on it.

See Create, insert & edit drawings in the Google Documentation for more info on the Drawing feature.

9 thoughts on “Add A Google Sheets Button To Run Scripts”

  1. Hi, I had a similar script to clear the contents but keep the formula.

    function myFunction() {
    var sheet = SpreadsheetApp.getActiveSheet();
    var rangeList = sheet.getRangeList([‘A8’, ‘E24’]);
    rangeList.clear({contentsOnly: true});

    This is working, when I/someone logged in to google account. If i am not logged in the script won’t work and not even asking permission to run, getting error message “Script myFunction experienced an error Details Dismiss”… Is there a way that I can use this function as anonymous?

  2. Hello, I’m having an issue with the button not connecting to my script. It will clear fields if I click the run function on the script page but the button I created and assigned the code to will not clear it. Here is the code and a look at the sheet. Any help would be greatly appreciated.
    function myFunction() {
    var sheet = SpreadsheetApp.getActive().getSheetByName(‘Adwords’);
    var Campaign = sheet.getRange(“B1”).clearContent();
    var adgroup1 = sheet.getRange(“B3”).clearContent();
    var adgroup2 = sheet.getRange(“D3”).clearContent();
    var adgroup3 = sheet.getRange(“F3”).clearContent();
    var adgroup4 = sheet.getRange(“H3”).clearContent();
    var adgroup5 = sheet.getRange(“J3”).clearContent();
    var startdate = sheet.getRange(“C7”).clearContent();
    var enddate = sheet.getRange(“E7”).clearContent();
    var radius = sheet.getRange(“B9”).clearContent();
    var location1 = sheet.getRange(“B11”).clearContent();
    var location2 = sheet.getRange(“B12”).clearContent();
    var location3 = sheet.getRange(“B13”).clearContent();
    var location4 = sheet.getRange(“B14”).clearContent();
    var location5 = sheet.getRange(“B15”).clearContent();
    var location6 = sheet.getRange(“B16”).clearContent();
    var location7 = sheet.getRange(“B17”).clearContent();
    var location8 = sheet.getRange(“B18”).clearContent();

    1. Sorry, I can’t seem to add the image here but the name of the sheet is “Adwords” but as you can see in the code the name of the action is “myFunction” but that doesn’t work either. Am I supposed to “Publish” this as well?

  3. Is it possible to make the new invoice number after clear ,for example invoice number is 10001 after clear contents number go 10002??
    Thank you!

  4. Can I run a script without assigning it to a button?
    Example when in a cell G3 I type “y” to reset
    Can I have the script clear B5, B8, then changing G3 to N

    Kinda like DOS

Leave a Reply

Your email address will not be published. Required fields are marked *