Create Button In Google Sheets Mobile

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”.

Create a Function

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");
}

onEdit() Trigger

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.

Create the Button

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.

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.

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.