How can I make my submit button work on sheets mobile?-CodePudding

I have a spreadsheet that my company uses to submit and track orders. There is a submit button on the order form and it uses the code below to execute. The problem is that it does not work on mobile. Instead of launching the code it just selects the image. I have read I may be able to do a work around with a checkbox but I'm having trouble figuring that out. Please advise. I just want to be able to submit and clear the form on mobile as well. I cannot do onedit() because the form submits to one line and if it submitted each entry at a time it would mess things up.

function Submit() {
  var ss        =SpreadsheetApp.getActiveSpreadsheet();
  var formS    =ss.getSheetByName('Order Form'); //data entry sheet
  var dataS  = ss.getSheetByName('Events Summary'); //data sheet


  var values = [[formS.getRange("B3").getValue(),
                 formS.getRange("B5").getValue(),
                 formS.getRange("B6").getValue(),
                 formS.getRange("B7").getValue(),
                 formS.getRange("B8").getValue(),
                 formS.getRange("B10").getValue(),
                 formS.getRange("B11").getValue(),
                 formS.getRange("B12").getValue(),
                 formS.getRange("B13").getValue(),
                 formS.getRange("B14").getValue(),
                 formS.getRange("B16").getValue(),
                 formS.getRange("D3").getValue(),
                 formS.getRange("D4").getValue(),
                 formS.getRange("D5").getValue(),
                 formS.getRange("D6").getValue(),
                 formS.getRange("E6").getValue(),
                 formS.getRange("D7").getValue(),
                 formS.getRange("E7").getValue(),
                 formS.getRange("D8").getValue(),
                 formS.getRange("E8").getValue(),
                 formS.getRange("D9").getValue(),
                 formS.getRange("E9").getValue(),
                 formS.getRange("D10").getValue(),
                 formS.getRange("E10").getValue(),
                 formS.getRange("D11").getValue(),
                 formS.getRange("E11").getValue(),
                 formS.getRange("D12").getValue(),
                 formS.getRange("E12").getValue(),
                 formS.getRange("D13").getValue(),
                 formS.getRange("B19").getValue(),
                 formS.getRange("B20").getValue(),
                 formS.getRange("B21").getValue(),
                 formS.getRange("B22").getValue(),
                 formS.getRange("B23").getValue(),
                 formS.getRange("B24").getValue(),
                 formS.getRange("B25").getValue(),
                 formS.getRange("B26").getValue(),
                 formS.getRange("B27").getValue(),
                 formS.getRange("B28").getValue(),
                 formS.getRange("B29").getValue(),
                 formS.getRange("B30").getValue(),
                 formS.getRange("D18").getValue(),
                 formS.getRange("D19").getValue(),
                 formS.getRange("D20").getValue(),
                 formS.getRange("D21").getValue(),
                 formS.getRange("D22").getValue(),
                 formS.getRange("D23").getValue(),
                 formS.getRange("D24").getValue(),
                 formS.getRange("D25").getValue(),
                 formS.getRange("D26").getValue(),
                 formS.getRange("D27").getValue(),
                 formS.getRange("D28").getValue(),
                 formS.getRange("D29").getValue(),
                 formS.getRange("D30").getValue(),
                 formS.getRange("D31").getValue(),
                 formS.getRange("F9").getValue(),
                 formS.getRange("F11").getValue(),
                 formS.getRange("F13").getValue(),
                 formS.getRange("F15").getValue(),
                 formS.getRange("F17").getValue(),
                 formS.getRange("F7").getValue(),
                 formS.getRange("F19").getValue(),
                 formS.getRange("F21").getValue(),
                 formS.getRange("F23").getValue(),
                 formS.getRange("F25").getValue(),
                 formS.getRange("F27").getValue(),
                 formS.getRange("F29").getValue(),
                 formS.getRange("F31").getValue(),
                 formS.getRange("B50").getValue(),
                 formS.getRange("C50").getValue(),
                 formS.getRange("E50").getValue(),
                 formS.getRange("F2").getValue(),
                 formS.getRange("F3").getValue(),
                 formS.getRange("F4").getValue()]];
  dataS.getRange(dataS.getLastRow() 1,1,1,74).setValues(values);
  ClearCell();
}

CodePudding user response：

You can do it onEdit, perhaps adding a checkbox for example or a cell with data validation. Let's say you have it in A1, then you can say in your function:

function onEdit(e) { 

    if(e.range.getA1Notation() == 'A1') {
      Submit()
      e.range.setValue(False)
    }
   }

CodePudding user response：

Using a dialog which could easily become a webapp or a sidebar

gs:

function Submit() {
  const ss        =SpreadsheetApp.getActiveSpreadsheet();
  const fsh    =ss.getSheetByName('Order Form'); //data entry sheet
  const dsh  = ss.getSheetByName('Events Summary'); //data sheet
  const rgl = fsh.getRangeList(["B3","B5","B6","B7","B8","B10","B11","B12","B13","B14","B16","D3","D4","D5","D6","E6","D7","E7","D8","E8","D9","E9","D10","E10","D11","E11","D12","E12","D13","B19","B20","B21","B22","B23","B24","B25","B26","B27","B28","B29","B30","D18","D19","D20","D21","D22","D23","D24","D25","D26","D27","D28","D29","D30","D31","F9","F11","F13","F15","F17","F7","F19","F21","F23","F25","F27","F29","F31","B50","C50","E50","F2","F3","F4"]);
  const vs = rgl.getRanges().map(r => fsh.getValue())
  dsh.getRange(dsh.getLastRow() 1,1,1,vs.length).setValues([vs]);
  ClearCell();
}

function launchDialog() {
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutputFromFile('index'),"Submit Form");
}

html:(index.html)

<!DOCTYPE html>
<html>

<head>
    <base target="_top">
</head>
<body>
  <input type="button" value="Submit" onClick="submit();" />
</body>
<script>
  function submit() {
    google.script.run.Submit();
  }
</script>
</html>

Or using onEdit()

function onEdit(e) {
  const sh = e.range.getSheet();
  if (sh.getName() == "Your Sheet Name" && e.range.columnStart == 1 && e.range.rowStart == 1 && e.value == "TRUE") {
    Submit();
    e.range.setValue("FALSE")
  }
}