unable to invoke bound script from sheets on iPad

1
votes

Unable to manually invoke bound script in Google sheet when accessing from iPad using Sheets app

I created a bound script in a Google spreadsheet that I want to access from different devices. The main sheet contains raw data. The script summarizes the raw data and puts the results in a second sheet. I created custom menu (ui.createMenu) to allow the user to select one of several extract options.

This worked fine when I load the spreadsheet in Chrome on a PC but the custom menu doesn’t show when you open the spreadsheet in the Sheets app on an iPad. So I tried adding a Dashboard sheet with several buttons linked to the functions for the different extract options. Again this works fine on a PC, but when I open the Dashboard sheet on my iPad and tap one of the buttons it acts like I’m trying to edit the sheet instead of running the script.

2
google-apps-scriptmobilegoogle-sheets
Running scripts on mobiles is not supported as far as I know. You might be able to accomplish what you need using a WebApp. – Cooper
Search – TheMaster

2 Answers

0
votes

It isn't possible to edit the UI elements in mobile versions of Sheets, and add-ons are not available for iPhone and iPad so a workaround can't be done this way either.

For mobile sheets I use the functionality of onEdit(e) and the event object to workaround this:

I freeze the first row of the sheet so that it's always viewable, with a dropdown menu in B1 like so:

enter image description here

My Apps Script code then has validation in the onEdit(e) trigger which first checks to see if the edit has been made in the cell B1, and executes the code I need it to based on the selection:

function onEdit(e) {
  if (e.range.getA1Notation !== 'B1'){
    return;
  }
  else{
    var selectedFunction = e.range.getValue();

    if (selectedFunction == 'myFunction()'){
      myFunction();
    }
    else if (selectedFunction == 'otherFunction()'){
      otherFunction();
    }
    else if (selectedFunction == 'thirdFunction()'){
      thirdFunction();
    }
  }
}

function myFunction(){
  // my code
}
function otherFunction(){
  // other code
}
function thirdFunction(){
  // third code
}

I know it's not a perfect solution but I hope that it could be useful for your case.

0
votes

I like R.G.'s solution since the only alternative I've found to kick off a custom menu item is creating a "button" drawing and attaching a function trigger to it. (I found the drawing part a little difficult to control inside a cell.)

Below is my adaption of R.G.'s solution which I found worked well in a situation where there already were other onEdit(e) routines. In this example the dropdown is in B2

// IOS/ANDROID MENU ROUTINE
var ss = SpreadsheetApp.getActiveSpreadsheet(); 

var sheet = SpreadsheetApp.getActiveSheet(); 
var sheetNameToWatch = "Entry sheet";
var columnNumberToWatch = 2;
var rowNumberToWatch = 1; 
var menuItem1 = "Update";
var menuItem2 = "Clear Staging";
var range = sheet.getActiveCell();

if (sheet.getName() == sheetNameToWatch && range.getColumn() == columnNumberToWatch && range.getRow() == rowNumberToWatch && range.getValue() == menuItem1) {
       [your_function_no_1]();
       }
else if (sheet.getName() == sheetNameToWatch && range.getColumn() == columnNumberToWatch && range.getRow() == rowNumberToWatch && range.getValue() == menuItem2) {
       [your_function_no_2]();
  }