December 4, 2020

How to add Button in Google Sheet

In this lesson we will learn how to add Button in Google Sheet. Below are the steps to follow for adding a clickable button in Google Sheet that will run the script functions assigned to it when clicked.

  • First click ‘Drawing’ under the ‘Insert’ menu of Google Sheet

  • One Drawing pane will open. There click the icon for ‘Shape’. Then go to ‘Shapes’ and select the desired shape for your button. We have selected rectangle here. 
  • Then Click ‘Save and Close’.

  • Now the Drawing pane will be closed and your button will appear on your Google sheet.
  • Now let’s prepare one simple script which will be triggered by clicking the button. Since the buttons in Google sheet are usually used to run the scripts of your need.
  • To create a script click ‘Script Editor’ under the ‘Tools’ menu of the Google Sheet.

  • The editor will be opened in a different tab. You can enter the name of your project now or later. In the body of Code.gs, we have added the below script to open a message box with the text “Hey! It Works ..” upon clicking the button.
function myFirstScript(){
  Browser.msgBox("Hey! It Works ..");
}

  • Now save the script from the File menu.

  • There are icons to save, run and debug which you can also use to save, test and validate your script for errors respectively.

  • While saving, the popup will come to enter a new project name if not added earlier. Provide the name of your choice and click OK.

  • Now your script is ready. You need to assign it with your button. To do that click the three vertical dots on the top right corner of your button on the google sheet and then select ‘Assign a Script’.

  • Now manually type the name of your function in the pop up and click OK.

  • Now click your button. 
  • Here for the first time a pop up can appear asking for the permission to run the script. Click Continue and in subsequent screens you may require to select your Google account to provide the authorization.

  • Finally once you complete the authorization process the message box will appear as follows containing the text as you have added in your script. The message box will disappear if you click OK. You can click your button any number of times and every time you will see the same set of actions repeats.

  • Now let’s see some tricks to format your button. First click the three vertical dots on the top right corner of your button and select Edit. In case there is already any script assigned to the button and upon clicking on the button the script is getting triggered and the three vertical dots won’t appear then you can right click on the button and then the three vertical dots will appear.

  • It will open the Drawing pane.Now click the icon for ‘Text Box’ and then you can create a text box on top of your button in the drawing pane. Add a name of your button inside the text box as here we entered ‘My Button’.

  • With the ‘Fill Colour’ icon in Drawing pane you can select the desired colour for your button. There are other options to select border color or images. Click ‘Save and Close’ once you are done with editing the button.

  • Finally below is how our button looks with the colour and text as we provided.

Now let’s see some more examples of different kind of useful scripting in Google Sheet.

Firstly we will learn the script to increment and decrement the value in two different cells by clicking the button.

Below is the script we have added in the script editor. With this function we are incrementing the value in cell D2 and decrementing the value in cell D3.

function inc_dec(){

  // defining the cell to be incremented
  var cell_inc = SpreadsheetApp.getActiveSheet().getRange("D2");
  // getting and setting the cell value
  var cellIncValue = cell_inc.getValue();
  cell_inc.setValue(cellIncValue + 1);  // To increment by 1 but can be any value
 
  // defining the cell to be decremented
  var cell_dec = SpreadsheetApp.getActiveSheet().getRange("D3");
  // getting and setting the cell value
  var cellDecValue = cell_dec.getValue();
  cell_dec.setValue(cellDecValue - 1);  // To decrement by 1 but can be any value
}

As you can see below in script editor in the same Code.gs we can add multiple functions like here we added the below one after our previous function. Save the script.

Now we have entered the function name(inc_dec) manually to assign the script to our button with the same process as demonstrated earlier.

Then we have added the value 10 in both cells D2 & D3.

Upon clicking the button the value in cell D2 has been incremented by 1 and the value in cell D3 has been decremented by 1 as follows.

Now we will learn another example of scripting in Google sheet to add current timestamp to the selected cell.

To do that we have added the below function in the code.gs of our project in the script Editor.

function setTimeStamp(){ 
var range = SpreadsheetApp.getActiveRange();
var date = new Date(); 
var timeStamp = date.getTime(); 
var currentTime = date.toLocaleTimeString();
range.setValue(currentTime);
}

Then save the script and assign the script to the button by manually adding the function name(setTimeStamp) with the same process as demonstrated earlier.

Now we have selected the cell E4 and then clicked the button. As you can see below the current time stamp has been added in cell E4 by running the script.

Here if you change the format of the cell(E4) to Date Time as follows then with the next click to the button, entire Date Time stamp will be displayed there.

Now here is the final example of a script to show the content of the selected cell upon clicking the button.

To do that we have added the below function in the code.gs of our project in the script Editor.

function showCellContent() {
  var show = SpreadsheetApp.getUi();
  var cellContent = SpreadsheetApp.getCurrentCell().getValue();
  show.alert("You clicked: \n"+cellContent+"!!!");
}

Then save the script and assign the script to the button by manually adding the function name(showCellContent) with the same process as demonstrated earlier.

Now in the cell E4 we have added the text “This is Great” and then clicked the button. As you can see below the when running the script the message “Running script” appears on top of the sheet and the alert box comes up with the text in cell E4 as per the script and once you click OK in the alert box, the script ends with the message “Finished” on top of the sheet.

This is all about creation of a button and using it with some simple basic scripting in Google sheet. The last point to mention here is, the button you created will be scrolled left/right or up/down with the movement of horizontal and vertical scroll bars of the Google sheet. So if you need to fix your button on the Google sheet, you can lock the rows and columns in the area where you have placed your button.

Leave a Reply

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