How to Insert Button in Google Sheets (5 Quick Steps)

Often we need to use specific scripts or macros continually in Google Sheets or Excel. Inserting a button and assigning those scripts or macros to that button helps to get rid of the frequent use of those scripts or macros. In this article, we have demonstrated the easiest step-by-step procedure to insert a button in Google Sheets. The result of our example may look as follows eventually.

A Sample of Practice Spreadsheet

You can download the spreadsheet used to describe methods in this article from here.

What Is Button in Google Sheets?

A button in Google Sheets is basically used to perform a specific task by assigning a Script. Like, here we’ll create a button to calculate the average.

Uses of Button in Google Sheets

The adaptable controls known as Button comes in handy in a variety of situations. You can use button if you want-

Step-by-Step Guide to Insert Button in Google Sheets

We will be using the following worksheet to describe the procedure to insert button in Google Sheets. The dataset represents some students’ grades in Maths and Physics. What we want to do here is to add a button that will contain a script which will calculate the average marks in those 2 subjects with just a click on the button.

Step 1. Inserting Image or Shape

We can add a button in Google Sheets in two ways. By inserting an image then linking it with the script or by drawing a button manually using the drawing menu from the toolbar then linking it with the apps script.

1.1 Using Image Option

Let’s think, we want to add a button by inserting an image. You can add any image you want as a button. We will be using the following image here.

Steps:

• First, click on the Insert menu at the toolbar, go to Image and then select Insert image over cells.

• The following window will appear. Click on Browse.

• Insert the image file from the location you saved it.

• Or simply drag the image file thereby holding it with your left-mouse button. You can also copy the image file and paste it there.

• The image will appear on your worksheet as follows.

1.2 Using Drawing Menu

Instead of inserting an image, we can create a button by drawing it manually using the Drawing tool from the toolbar.

Steps:

• First, go to the Insert menu at the toolbar then choose Drawing.

• A new window as follows will appear on your screen.

• Assume, we want to create a rectangular-shaped button here.
• Click on the Shape ribbon, click on Shapes then select Rectangle.

• Draw a rectangle and then give it a name as you want by left-clicking twice on that rectangle.
• You can customize font size, font type, fill color of the drawing as well. Here, I have set the text size to 20, chosen the font type as “Lobster” and filled the rectangle with green color.
• After completing the drawing, click on Save and Close.

• Following this, the rectangular drawing for a button will appear on your screen.

Read More: How to Insert Video in Google Sheets (2 Easy Ways)

Step 2. Resizing Button

Resize the inserted image or the drawn button using the Square icons on the corner and the sides of the button. Here, I have illustrated the method using the image I have inserted.

Similar Readings

• How to Insert Serial Numbers in Google Sheets (7 Easy Ways)
• Insert Multiple Columns in Google Sheets (2 Quick Ways)
• How to Insert a Legend in Google Sheets (With Easy Steps)
• Insert Signature in Google Sheets (3 Easy Ways)
• How to Insert a Header in Google Sheets (2 Simple Scenarios)

Step 3. Inserting Script

• Now, at the top of your toolbar, select Extension then choose Apps Script.

• A new tab of Apps Script will come up in your browser.
• Insert the codes given below in the gs section there then click on the Save ribbon. We will use it to calculate our required average values then to get the output in Cell C17 and Cell D17.

function calculateAverage() {
var wannagetavg = SpreadsheetApp.getActiveSpreadsheet();
//var sheet = wannagetavg.getSheets()[0];
var feuille = wannagetavg.getActiveSheet();
var cell = feuille.getActiveCell();
//var columnwiseavg = 4;
var columnwiseavg = wannagetavg.getRange("C17");
columnwiseavg.setFormula("=AVERAGE(C5:C16)");
var columnwiseavg = wannagetavg.getRange("D17");
columnwiseavg.setFormula("=AVERAGE(D5:D16)");
for(var i = 1;i <= columnwiseavg;i++)
{
feuille.insertRowAfter(2);
}
}

• Click on Run at the toolbar there.

• Authorization will be required to follow the given codes. The following window will appear on your screen. Select Review permissions.

• You will be asked for an email. Here, I have selected mine.

• After selecting the email address, a safety page will appear on your screen. Select Advance from there.

• Next, select “Go to Untitled project (unsafe)” from the newly appeared window.

• Following this, press on Allow.

• Something as follows will appear in the Execution bar that is in the below of the Apps Script.

Read More: How to Insert Formula in Google Sheets for Entire Column

Step 4. Assigning Script

At this point, we have to assign the script previously inserted in the Apps Script menu to the button.

• Select the image then click on the 3 dots menu ribbon as shown below. After that, click on Assign script.

• Type down the function name “calculateAverage” we have given in our script then click on OK.

Read More: How to Insert an Exponent in Google Sheets (3 Easy Ways)

Step 5. Using the Button

• Simply left-click just once on the created button. First, it will return a message “Running Script” as follows.

• Finally, the message “Finished script” will appear and the output will return as follows in Cell C17 and Cell D17.

Read More: How to Insert Equation in Google Sheets (4 Tricky Ways)

How to Make a Hyperlink Button to Jump to a Specific Sheet in Google Sheets

We can add a hyperlink button to jump to a specific sheet in Google Sheets. Presume, we want to create a hyperlink button in the following dataset that will take us to the sheet titled “Sheet1” instantly.

Steps:

• Create a button by inserting an image or by drawing a shape using the Drawing tool from the toolbar then resize it using the Fill handle icons along its corners and sides just like previously.

• Next, at the toolbar, click on the Extension menu then select Apps Script.

• We will use the following script below that will take us to “Sheet1” after clicking on the button.

function JumptoSheet() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Sheet1'), true);
};

• But, if there exists a code in the gs section already, then click on the Add a file plus icon (+) ribbon then select Script.

• Insert the codes in the newly created “Untitled.gs” section then click on Save.

• Now, get back to your spreadsheet, select the button image/draw then click on the 3 dots menu ribbon as shown below. After that, click on Assign script.

• Type down the function name “JumptoSheet” we have given in our script then click on Ok.

• Now, simply left-click once on the created button. First, it will return a message “Running Script” as follows.

• Finally, following this will take you to Sheet1 and the message “Finished Script” will appear as follows.

Conclusion

The article contains the step-by-step process to insert button in Google Sheets. Hope, this will definitely help you with your task. Visit our site officewheel.com to see more related articles.

Related Articles

• How to Insert Superscript in Google Sheets (2 Simple Ways)
• Add Parentheses in Google Sheets (5 Ideal Scenarios)
• How to Insert Error Bars in Google Sheets (3 Practical Examples)
• Insert Blank Column Using QUERY in Google Sheets
• How to Insert Yes or No Box in Google Sheets (2 Easy Ways)
• Insert a Drop-Down List in Google Sheets (2 Easy Ways)
• How to Insert Sparklines in Google Sheets (4 Useful Examples)