create a button for google sheets mobile app/ range not found-CodePudding

I am trying to create a dropdown 'button' which will trigger my 'function main' in Google Sheets' mobile app, but the error says 'range cannot be found'. Could you please advise how the code should be adjusted? Can it be because the spreadsheet has multiple sheets and I need to define the sheet name? I tried this way but still not working. The error says range not found.

function onEdit(e) {

var ss = SpreadsheetApp.getActive();
var sheet = ss.getSheetByName("Թերթ9");
  if (e.range.getA1Notation() == 'A1') {
    if (/^\w $/.test(e.value)) {        
      eval(e.value)();
      e.range.clear();
    }
  }
}


function main(e) {moveData(); autoSort();}

function moveData(e) 
{
  // 1. Retrieve values from the source and target sheets.
  var ss = SpreadsheetApp.openById("1w5AtGaZbUWpmeWXv9S8anNXPvOw1xqbU_VEj3LRewMM");
  var [srcSheet, targetSheet] = ['Գնումներ', 'Թերթ9'].map(s => ss.getSheetByName(s));
  var [srcValues, targetValues] = [[srcSheet, "AB2:AF"], [targetSheet, "A3:E"]].map(s => s[0].getLastRow() == 1 ? [] : s[0].getRange(s[1]   s[0].getLastRow()).getValues());

  // 2. Create objects for searching values of the column "A".
  var [srcObj, targetObj] = [srcValues, targetValues].map(e => e.reduce((o, [a, ...b]) => (o[a] = b, o), {}));

  // 3. Check update values at the target sheet.
  var updatedValues = targetValues.map(([a, ...b]) => [a, ...(srcObj[a] || b)]);
  // 4. Check append values.
  var appendValues = srcValues.reduce((ar, [a, ...b]) => {
    if (!targetObj[a]) ar.push([a, ...b]);
    return ar;
  }, []);

  // 5. Update the target sheet.
  var values = [...updatedValues, ...appendValues];
  targetSheet.getRange(3, 1, values.length, values[0].length).setValues(values);
}

function autoSort(){

const ss = SpreadsheetApp.getActiveSpreadsheet()
const ws = ss.getSheetByName("Թերթ9")
const range = ws.getRange(3,1,ws.getLastRow()-1,12)
range.sort({column: 3, descending: false})
}

CodePudding user response：

Put a checkbox in A1

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") {
    e.range.setValue("FALSE");//reset the checkbox
    main(e);
  }
}

CodePudding user response：

The function works on mobile with this script.

function createEditTrigger() {
 ScriptApp.newTrigger("main")
   .forSpreadsheet(SpreadsheetApp.getActive())
   .onEdit()
   .create();
}


function main(e) {moveData(); autoSort();}

function moveData(e) 
{
  // 1. Retrieve values from the source and target sheets.
  var ss = SpreadsheetApp.openById("1w5AtGaZbUWpmeWXv9S8anNXPvOw1xqbU_VEj3LRewMM");
  var [srcSheet, targetSheet] = ['Պատվերներ', 'Գնումների ցանկ'].map(s => ss.getSheetByName(s));
  var [srcValues, targetValues] = [[srcSheet, "AB2:AF"], [targetSheet, "A2:E"]].map(s => s[0].getLastRow() == 1 ? [] : s[0].getRange(s[1]   s[0].getLastRow()).getValues());

  // 2. Create objects for searching values of the column "A".
  var [srcObj, targetObj] = [srcValues, targetValues].map(e => e.reduce((o, [a, ...b]) => (o[a] = b, o), {}));

  // 3. Check update values at the target sheet.
  var updatedValues = targetValues.map(([a, ...b]) => [a, ...(srcObj[a] || b)]);
  // 4. Check append values.
  var appendValues = srcValues.reduce((ar, [a, ...b]) => {
    if (!targetObj[a]) ar.push([a, ...b]);
    return ar;
  }, []);

  // 5. Update the target sheet.
  var values = [...updatedValues, ...appendValues];
  targetSheet.getRange(3, 1, values.length, values[0].length).setValues(values);
}

function autoSort(){

const ss = SpreadsheetApp.getActiveSpreadsheet()
const ws = ss.getSheetByName("Գնումների ցանկ")
const range = ws.getRange(3,1,ws.getLastRow()-1,12)
range.sort({column: 3, descending: false});
}

I just need to further define it so that the function will work only if Cell A is edited (for example if a checkbox value True). Could you please have a look how the code could be adjusted for working the script only if cel A is edited? Thanks in advance!