Create Button In Google Sheets Mobile

James MacAdam
Apr 20, 2020 · 3 min read
Image for post
Image for post
Image for post
Image for post

Part II of this series describes the very simple process of creating a clickable button in a Google Sheets workbook. Although it is a very useful and easy to use feature of Apps Script used with Google Sheets, the clickable buttons only work in the browser version of Google Sheets and not in the mobile app versions. Luckily, there is a workaround that allows users to create a “clickable” button thats usable in the Sheets mobile app. You can do this by creating the desired function to be triggered, set up the onEdit() trigger, and creating a dropdown menu which will serve as the “button”.

Just like creating a button in the browser version, the first step is to create the function to be triggered by the button. For this example, I created two simple functions that can be triggered by the button that allows the user to input the text “Hello World” into cell C1 or turn the cell blue.

function c1SayHelloWorld() { 
var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange(1,3).setValue('Hello World');
}
function c1TurnBlue() {
var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange(1,3).setBackground("blue");
}

There is a built in onEdit() trigger in Apps Script that allows users to trigger a function any time a cell is edited. In this specific example we will create an onEdit() trigger for when a specific cell (A1) is edited to the name of either of the two created functions, that function is triggered.

function onEdit(e) {
if (e.range.getA1Notation() == 'A1') {
if (/^\w+$/.test(e.value)) {
eval(e.value)();
e.range.clear();
}
}
}

The above code snippet contains a simple IF statement that if cell A1 is edited (and is a valid input), to execute the input string, and then clear the content in cell A1. In this case, if the input string is the name of either of the created functions, the function will be executed.

In this method of creating a button, a dropdown menu will serve as the clickable button. A dropdown menu is created in Google Sheets by highlighting the cell to contain the dropdown, navigating to the Data toolbar, and clicking Data validation. A dropdown menu is created by selecting the data validation criteria to be a List of items, and typing in each list item, separated by commas. Each list item should be the name of the functions you want to be able to trigger with the button.

Dropdown menu in Google Sheets
Dropdown menu in Google Sheets

Now the dropdown menu in cell A1 will serve as a button and when the cell is changed to the desired function, the function will execute. This button works in both the browser version and the mobile app version of Google Sheets. You also can use this method to create one button capable of executing a large number of different functions.

Image for post
Image for post

The dropdown menu cell can be formatted and customized to look and function more like the clickable button as described in Part II of this series, and can also but used in the mobile app versions of Google Sheets. This allows users to submit data, perform functions, and utilize the power of Apps Script on the go using their mobile phones.

Please feel free to try out the code snippets above and customize them to reference functions throughout your workbooks!

Go to Part II to see the alternative way to create a clickable button in Google Sheets that uses the Google Sheets user interface. Click HERE to see the Apps Script tutorial publication.

macadamScripts

Sharing Ideas One at a Time

James MacAdam

Written by

A Civil Engineer by day who is passionate about others, sharing ideas, building relationships, and learning new things. Columbus, OH. macadamscripts.com

macadamScripts

Exploring Google Apps Script

James MacAdam

Written by

A Civil Engineer by day who is passionate about others, sharing ideas, building relationships, and learning new things. Columbus, OH. macadamscripts.com

macadamScripts

Exploring Google Apps Script

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store