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() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const invoiceNumber = sheet.getRange("B5").clearContent();
  const invoiceAmount = sheet.getRange("B8").clearContent();
  const invoiceTo = sheet.getRange("E5").clearContent();
  const 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.

52 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

      1. Yes you can. Do this with the onEdit event – just check if the current cell (there’s a function for the current range – see if its top left corner is your cell) is the one you want and then if its contents are “y” and if so, do your thing.

        1. Sorry, it’s not always the top left corner. But there is a currently selected cell in any range, probably the one the user starts with, and that’s the one you can check for with getCurrentCell().

  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.

    1. Same problem here. I created my simple macro.gs with macro recording. Created a script code.gs. They have different function names and are inside a project which of course has a name. I have tried attaching macro.gs, code.gs, both of the function names, and all of these prefixed with the project name dot. I get an error saying the script cannot be found. I added this to both /*** @OnlyCurentDoc */ but that did not have any effect.

    2. I had the same problem. I renamed macro as it was in the script editor. It was “8”, but “_8” in the script editor. I renamed macro for “_8” and it started to work.

  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

  8. I am having trouble with merged cells and logos. They are not loading on the PDF.
    Is there a setting for those?

  9. Hello:

    Thank you for this code.

    I’m having issues with my version:

    function ClearFlash() {
    var GreecePC = SpreadsheetApp.getActiveSheet();
    var invoiceAmount = sheet.getRange(“i9”).clearContent();
    var invoiceAmount = sheet.getRange(“i11”).clearContent();
    var invoiceAmount = sheet.getRange(“i12”).clearContent();
    var invoiceAmount = sheet.getRange(“i14”).clearContent();
    var invoiceAmount = sheet.getRange(“i15”).clearContent();
    }

    But I get this message:

    ReferenceError: sheet is not defined (line 3, file “Code”)

  10. Hey Ben,

    Thank you for your ressourceful insights! I use this method in many files and – in general – it works fine.

    Lately I stumble over an issue I don’t know how to fix by myself:

    DESCRIPTION
    Without any action on my behalf an image loses its script assignment and is back to being only an image. One day the script is triggered by clicking the image like it should be and the other day the assignment is lost. Reloading the file doesn’t bring back the assignment – it has to be assigned anew.

    I can’t force this issue to happen on a script assigned image. It seems to occur randomly and got me to a point where I’m about to avoid this feature at all for future documents.

    Do you have any idea about a possible root cause of this error and is it familiar to you?

    Thank you very much in advance and have a nice day!

    Best regartds and keep up the awesome work,
    Daniel

    1. Hi Daniel,

      I have the same problem, and i cant see that anybody answered you here. Have u found a solution yet?

      Best
      Shy

    2. Daniel and Shy Van Alma, I’m having the same issue. Any luck with a solution? I have 3 buttons…each calling a different script. They work fine but randomly, one will lose its script assignment. I add it back and some time later, another button will lose its assignment. Very weird.

  11. hello All,

    would it be possible to use this idea to protect ranges in multiple cells at once? instead of clearing them

  12. hello, what I want to do is automate the click of the button I created. I want the system to push the button at that time by randomly choosing the time interval I will choose. Is an devlope on this possible? Thank you

  13. Hi, The button I have assigned the script/macro to keeps moving up slightly every time I click it.
    Is it possible to “Fix” it to a location so it doesn’t move?
    Thanks

  14. I’m trying to make a button within a cell to open a sheet corresponding to the respective cell. For example, if I am looking for data about certain material, I want to click in its cell and have it open a sheet with data about it. Does anyone have any idea how to make this work?

    1. Hi. Best workaround I have found is to create a drawing that’s transparent and put that on top of the cell, then assign the script as usual. I actually put a tiny “P” in the transparent drawing so I can make sure they are where they need to be.

  15. Is possible with Google Apps Scripts: block, hide or remove option from menu general of Google Sheets. The idea is block access to TOOLS menu.

    Regards,

    Juan Gonzalez

  16. I have a lovely drawing that has an assigned script which runs when the drawing is clicked but I cannot find a way to insert it into a cell. When I just had an image in a cell I could not find a way to assign a script. Are drawing and image totally different in Google land?

  17. It’s working. But I also need a solution. I shared my Spreadsheet to the others (by link). I made access “can only fill in certain cells”. But when she/he pressed the button (the pic), it didn’t work, just work from my account (a maker). What should I do to make all buttons work for everyone I link to?

  18. Hey there everyone.

    I am trying to use an “ADD NEW” button to copy a range of cells, and paste is below but also clear all the data that has been entered. This new copy has to retain the titles, and formulas.

  19. Hi Scott, I came here to create a button for a similar script. I got most of it from stackexchange (https://webapps.stackexchange.com/a/47204) and modified it to my needs:

    // global
    var ss = SpreadsheetApp.getActive();

    function addRow() {
    var sh = ss.getActiveSheet(), lRow = sh.getLastRow()-2; // -2 as the last two rows contain sums, remove or replace with -1
    var lCol = sh.getLastColumn(), range = sh.getRange(lRow,1,1,lCol);
    sh.insertRowsAfter(lRow, 1);
    range.copyTo(sh.getRange(lRow+1, 1, 1, lCol), {contentsOnly:false});
    // set default values
    sh.getRange(lRow+1,1).setValue(Utilities.formatDate(new Date(), “GMT+1”, “dd.MM.yyyy”)); // first col contains a date
    sh.getRange(lRow+1,2).setValue(‘0:00:00’); // second col contains a duration
    sh.getRange(lRow+1,4,1,4).setValue(”); // clear values of col 4-7
    }

  20. function clearDataform() {
    var sheet = SpreadsheetApp.getActiveSheet();
    var dataformJumiaagentname = sheet.getRange(“G12”).clearContent(“G14”);
    var dataformOrdercode = sheet.getRange(“J12”).clearContent(“J14”);
    var dataformDateandtime = sheet.getRange(“G17”).clearContent(“G19”);
    var dataformComplaintsource = sheet.getRange(“J17”).clearContent(“J19”);
    var dataformProblem = sheet.getRange(“G22”).clearContent(“G24”);
    var dataformCommentNecessaryfield = sheet.getRange(“J22”).clearContent(“G24”);
    var dataformSearchbox = sheet.getRange(“J9”).clearContent(G9);
    }
    }

    Mine working when applying it to button

  21. i’m create two button with different function in my form. 1st button to search part number and 2nd button to search serial number. i’m using below script for both but its not working. any idea how can 1 assign script or there have problem with my script. Tried so many time but it’s only working for one of each button. Please help

    const ss = SpreadsheetApp.getActive();
    const dataSheet = ss.getSheetByName(‘Data’);
    const formSheet = ss.getSheetByName(‘Form’);
    const formRanges = [‘B6’, ‘B8’, ‘B10’, ‘B12’, ‘B14’, ‘B16’, ‘B18’, ‘B20’, ‘B22’, ‘B24’].map(rangeA1 => formSheet.getRange(rangeA1));
    const searchKeyRange = formSheet.getRange(‘B3’);
    var SEARCH_COL_IDX = ‘0’;

    function Search() {
    const searchKey = searchKeyRange.getDisplayValue().trim().toLowerCase();
    if (!dataSheet.getDataRange().getDisplayValues().some(function (row) {
    if (searchKey === row[SEARCH_COL_IDX].trim().toLowerCase()) {
    formRanges.forEach((range, index) => range.setValue(row[index]));
    return true;
    }
    })) {
    ss.toast(‘Could not find a match for “‘ + searchKey + ‘” in column ‘ + (SEARCH_COL_IDX + 1) + ‘.’);

    }
    }

  22. Hi All – does anyone know how I could make a script to where someone clicking a ‘drawing’ button would input +1 (vote up) into the cell next to it?

  23. The code is:
    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();
    }

  24. Hi Ben, I did this and it worked amazingly!
    However, I use the spreadsheet most often on my iPhone (using the Google Sheets app), and when I tried to push the button on my phone, it doesn’t work. Any suggestions on how to make the button work on my mobile device?

  25. Hello, after exiting the sheet then logging back on, the buttons or images won’t stay in their assigned cell. Any suggestions on how to keep buttons/images from floating?

  26. I will make this button but i will make it so that if you push on the button the data will be placed on a other page or on the same page so i can find it back. How can i do that?

    (excuse me for my bad english i am not good in it but i hope you will understand what i mean)

  27. hi
    After insert button and “assign text” name function working also good.

    But if click right mouse on button after click “assign text” after close or “x(mark)” automatically assign text deleted.

    i am facing this could tell me any one

  28. it doesn’t work on the touch platform… is there an alternative solution to make it work on a tablet for example?!!!

  29. Years later, still a helpful post! What I’d like is to have one button clear ranges from many sheets within a workbook–example, Sheet1!B2:B80, Sheet2!B2:B80, so on. How would I code that?

    1. Try recording a Macro of you deleting all that cells and then assign that macro to a button.

Leave a Reply

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