Run a function with onEdit on Google Sheets mobile app

Question

I made my function that it's working good for me now, but i have the issue to make it work on mobile so i tried another way to use the function OnEdit, so when it will be the value that i want to lunch the function that i created before, but for now it's not working and i don't know why it's not, i'm asking for you help with this small issue ;) thank you

function onEdit(e) { 
  var range = e.range;
  var spreadSheet = e.source;
  var sheetName = spreadSheet.getActiveSheet().getName();
  var column = range.getColumn();
  var row = range.getRow();
  var value = SpreadsheetApp.getActiveSheet().getRange(row, column).getValue();
  
  if(sheetName == 'New Orders' && column == 12 && value=='COMMANDE VALIDER')
  {  
    VALIDERCOMMANDE();   
  } 
}

function VALIDERCOMMANDE() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var copySheet = ss.getSheetByName("New Orders");

  var url = "https://docs.google.com/spreadsheets/d/1eTWG_XZt-3CMzkxgKM4pCvD41deGMdka37eQkHM9oDg/edit#gid=0";
  var ss2 = SpreadsheetApp.openByUrl(url);
  var pasteSheet = ss2.getSheetByName("Order Pull");

// get source range
var max = copySheet.getMaxRows().toString();
var range = copySheet.getRange(2, 1, max, 12);
var dataValues = range.getValues();

for (i = 1; i < dataValues.length; i++) {
    if (dataValues[i][11] === 'COMMANDE VALIDER') {
        pasteSheet.appendRow([dataValues[i][0],
            dataValues[i][1],
            dataValues[i][2],
            dataValues[i][3],
            dataValues[i][4],
            dataValues[i][5],
            dataValues[i][6],
            dataValues[i][7],
            dataValues[i][8],
            dataValues[i][9],
            dataValues[i][10],
            dataValues[i][11]]);

        var clearRow = i + 2;
        copySheet.getRange('D' + clearRow + ':L' + clearRow).clearContent();
    }
}

// get destination range
var destination = pasteSheet.getRange(pasteSheet.getLastRow() + 1, 1, max, 1);

// clear source values
Browser.msgBox('Commande Confirmer');
}

Refer https://stackoverflow.com/questions/33373826/executing-google-apps-script-functions-from-mobile-app/47207643#47207643 — TheMaster, Aug 14 '22 at 15:57

Rubén · Answer 1 · 2022-08-14T20:56:13.810

Class Browser, Class UI and SpreadsheetApp.toast don't work in the Google Sheets mobile apps (iOS and Android). By the other hand, instead of using a simple trigger you should use an installable trigger because SpreasheetApp.openByUrl method requires authorization to run.

Change the name of the onEdit function, remove Browser.msgBox('Commande Confirmer'); and create an installable on edit function calling the renamed function should make your script work on the mobile apps

If you really need to have a custom notification when the onEdit function finish, you might send write a message or image on certain range. If you use on edit installable trigger you might also send an email or call an external API.

Cooper · Answer 2 · 2022-08-14T20:32:31.243

1

Try this way

function onMyEdit(e) {
  const sh = e.range.getSheet();
  if (sh.getName() == 'New Orders' && e.range.columnStart == 12 && e.value == 'COMMANDE VALIDER') {
    VALIDERCOMMANDE();
  }
}
function VALIDERCOMMANDE() {
  var ss = SpreadsheetApp.getActive();
  var csh = ss.getSheetByName("New Orders");
  var id = "1eTWG_XZt-3CMzkxgKM4pCvD41deGMdka37eQkHM9oDg";
  var ss2 = SpreadsheetApp.openById(id);
  var psh = ss2.getSheetByName("Order Pull");
  var vs = csh.getRange(2,1,csh.getLastRow() - 1, 12).getValues().filter(r => r[11] == 'COMMANDE VALIDER').filter(e => e);
  psh.getRange(psh.getLastRow() + 1, 1, vs.length, 12).setValues(vs);
}

I often find that onEdits are not reliable on mobile and sometimes the trigger process has to be repeated

edited Aug 14 '22 at 20:32

answered Aug 14 '22 at 17:02

Cooper

59,616
6
23
54

3

`ss.toast` won't work in mobile – TheMaster Aug 14 '22 at 17:22
3

`SpreadsheetApp.openById()` is not available in a simple trigger context such as `onEdit(e)`. Use an [installable trigger](https://developers.google.com/apps-script/guides/triggers/installable) to make it work. – doubleunary Aug 14 '22 at 20:29

Run a function with onEdit on Google Sheets mobile app

2 Answers2

Try this way

Linked