Google-sheets – Add a script trigger to Google Sheet that will work in Android mobile app

androidgoogle sheetsgoogle-apps-scriptgoogle-apps-script-triggers

I have a Google sheet that includes a script that should be run when clicking a button. The recommended way to do this (as far as I can tell) is to insert an image and then attach a script to the image in question.

This works just fine as long as I open the sheet on my desktop. However, if I open the sheet in the Android Google sheet app, the image simply doesn't appear. As far as I can tell this (lack of) behavior is not documented anywhere, but I've found a number of people with the same issue.

I was wondering how I circumvent this problem. Did I miss something obvious that will make my images visible in the Android app? Or is there another simple way to add a script trigger to a sheet that will work on mobile?

Best Answer

It seems that neither images nor custom menu items work in Sheets Android app at present. I suggest creating a "function menu" within the spreadsheet. For example:

  1. Cell A1 says "Select a function"
  2. Cell B1 has a data validation rule that restricts the content to the names of functions you have. In my example, they are "insertSomething" and "convertSomething". (Don't check "show help" in data validation dialog, the "help" popup is an annoyance on mobile.)
  3. A script function onEdit (simple trigger), running on every edit, checks whether the content of B1 has changed. If so, it runs the appropriate function.

Here is my code, with two functions included for demonstration purpose: 

function onEdit(e) {
  if (e.range.getA1Notation() == 'B1') {
    if (/^\w+$/.test(e.value)) {        
      this[e.value]();
      e.range.clear();
    }
  }
}

function insertSomething() { 
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(2,3).setValue('inserted something');
}  

function convertSomething() { 
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(3,3).setValue('converted something');
}

The condition /^\w+$/.test(e.value) is just to check we have a nonempty string and are not executing malicious code that someone somehow put in cell B1. After the function is envoked with this[e.value]();(this refers to the global object and contains function names) the content of B1 is cleared. One can choose to run the same function again, or another one.

As a proof of concept, here are screenshots from the app. Screenshot 1: selection a function

choose

Screenshot 2: after the function has run

after

References

Related Topic