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.

14 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?

    1. Hi sanu if you remove some code from your code then I wish its work ,
      please use only function body contents.
      and then run may be its work

      thanking you

  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

  5. I cannot get this to work at all. I have a simple script that basically reads the value from the active cell, and sets it as the value in another, on which a query is based. This works perfectly when I run it from the script editor, but when I assign this to a button as described, I get an error saying the script cannot be found. To ensure I don’t have typos, I copied the name of the function and pasted it into the button. I’m quite familiar with javascript and don’t believe it is a typo issue or syntax issue. I think the script is not connected to the sheet despite the script editor having been opened from the sheet. I cannot find anything describing how to link the two.

  6. I was having the same trouble with the button linking to the script, but it worked after I refreshed the age, and typed the exact name of the macro as it was in the script editor, with no spaces.

  7. Hello i am write a code for filter a table data but I can’t write successfully so now I want to help for this problam , my problem is I write code for a table data filter on specific column ,when I run it then its return whole table to me,my code is below

    var sheet = SpreadsheetApp.getActive().getSheetByName(‘Sheet2’);
    sheet.getRange(‘A:D’).clearContent();
    function myFunction() {
    var sss = SpreadsheetApp.openById(‘1NWUmvz6WxkMtwjginWEDwJitrdRm7kzDcRTZoYQi9uM’); //replace with source ID
    var ss = sss.getSheetByName(‘Sheet1’); //replace with source Sheet tab name
    var range = ss.getRange(‘A:D’); //assign the range you want to copy
    var data = range.getValues();

    var tss = SpreadsheetApp.openById(‘1NWUmvz6WxkMtwjginWEDwJitrdRm7kzDcRTZoYQi9uM’); //replace with destination ID
    var ts = tss.getSheetByName(‘Sheet2’); //replace with destination Sheet tab name
    ts.getRange(ts.getLastRow()+1, 1, data.length, data[0].length).setValues(data);

    var range = ts.getRange(ts.getLastRow()+1, 1, data.length, data[0].length)
    var rawData = range.getValues() // get value from spreadsheet 2
    var data = [] // Filtered Data will be stored in this array
    for (var i = 0; i< rawData.length ; i++){
    if(rawData[i][3] == "Akash") // Check to see if column K says ipad if not skip it
    {
    data.push(rawData[i])
    }
    }
    }

    and table is

    Date Amount Descriptions Given By
    4/4/2020 1800 Dinesh ko Ashok ke paise de bache hue lockdown mein transfer karvate the Akash
    4/14/2020 3000 bablu bhai ko transfer lockdown mein chacha ke kahane per Akash
    4/17/2020 3000 Shankar ko transfer Kare chacha ke kahane per lockdown mein Akash
    4/29/2020 600 chacha ka recharge kara jio ka Akash
    5/21/2020 1000 1000 rupaye bablu bhai ko diye rohini depot mein Raj
    5/21/2020 1000 1000 rupaye bablu bhai ki lebor ko diya jo ghas kaatne I they depo mein Akash

Leave a Reply

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