10

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?

  • 1
    This is really an issue with the native Sheets app for Android, isn't it? Your question is probably better off at Android Enthusiasts. – ale Dec 4 '15 at 15:34
  • 1
    @AlE. That's one way to look at it, but to overcome the issue one needs the knowledge of Google Sheets, not the knowledge of Android. – user79865 Dec 5 '15 at 0:44
  • @Normal: That's why I asked the question (and didn't vote to close). I'm just trying to help the Asker attract the expert(ise) needed to get an answer. – ale Dec 5 '15 at 4:51
25

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)) {        
      eval(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 eval(e.value)(); 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

  • This is a very simple, clean, and effective solution. Nicely done. – StephenH Nov 16 '16 at 19:45
  • This works as long as the function being called isn't used to send an email. Emails cannot be triggered by an edit listener, so the button/custom menu issue is really limiting. – 10klines Jun 13 '17 at 11:52
  • 2
    @10klines In that case, change the simple onEdit trigger to an installable one, which runs with the authorization of the user who installed the trigger. – user135384 Aug 2 '17 at 21:53
  • If you want you can use checkboxes to trigger functions instead of pictures or Data Validation. With my tablet the eval function didn't work so I set a bunch of checkboxes for each function one. I adjusted the code accordingly and works fine. Hint: If you have a tablet don't use Message Box - doesn't work for some reason. – Tobias Sarnow Jan 30 at 6:26
  • This is nice solution, however it is not working if the function is called to make calls to API and get the response from it as it is asking for google approval. Any work around? – J.B. Oct 4 at 11:01

Not the answer you're looking for? Browse other questions tagged or ask your own question.