javascript - running a script on google sheet on mobile not working

i have a script that record scores and export it to another sheet. however as i understand, google mobile sheet does not allow the pressing of button, and thus i created a checkbox to run it. When i try this, the script doesnt run. is it because the script calls other google sheet into play?

Here's my script for reference.

function recordScores() {
  var ss1 = SpreadsheetApp.openByUrl("Unique URL 1").getSheetByName("PC 1-3");
  var ss2 = SpreadsheetApp.openByUrl("Unique URL 2");
  var ss3 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");

  var aspireLevel = Number(ss3.getRange("B6").getDisplayValue());
  var element = Number(ss3.getRange("B7").getDisplayValue());
  var judgeNumber = Number(ss3.getRange("B8").getDisplayValue());
  var athleteNumber = ss3.getRange("B11").getDisplayValue();
  var b10Value = ss3.getRange("B10").getDisplayValue();

  var r1 = ss3.getRange("B18").getDisplayValue();
  var r2 = ss3.getRange("B19").getDisplayValue();
  var r3 = ss3.getRange("B20").getDisplayValue();
  var AComments = ss3.getRange("A26").getDisplayValue();
  var scores = ss3.getRange("E18").getDisplayValue();

  if (b10Value.trim() === "") {
    SpreadsheetApp.getUi().alert("Error: No judge assigned for this level");
    return;
  }

  var sheet = (aspireLevel >= 1 && aspireLevel <= 3) ? ss1 : ss2;
  var rowToInsert = findRow(sheet, athleteNumber, aspireLevel);
  if (rowToInsert === -1) {
    SpreadsheetApp.getUi().alert("Error: Athlete number and Aspire Level not found");
    return;
  }

  var columnMap = getColumnMapping(aspireLevel, element, judgeNumber);
  if (!columnMap) {
    SpreadsheetApp.getUi().alert("Error: Invalid Element or Judge Number");
    return;
  }

  var columnMap2 = getColumnMapping2(aspireLevel, element, judgeNumber);
  if (!columnMap2) {
    SpreadsheetApp.getUi().alert("Error: Invalid Element or Judge Number");
    return;
  }

  if (aspireLevel <= 3) {
    sheet.getRange(rowToInsert, columnMap[0]).setValue(r1);
    sheet.getRange(rowToInsert, columnMap[1]).setValue(r2);
    sheet.getRange(rowToInsert, columnMap[2]).setValue(r3);
    sheet.getRange(rowToInsert, columnMap[3]).setValue(AComments);
  } else {
    sheet.getRange(rowToInsert, columnMap2[0]).setValue(scores);
    sheet.getRange(rowToInsert, columnMap2[1]).setValue(AComments);
  }

  SpreadsheetApp.getUi().alert("Scores have been recorded");
  resetValues(ss3);
}

function findRow(sheet, athleteNumber, aspireLevel) {
  var data = sheet.getDataRange().getValues();
  for (var i = 1; i < data.length; i++) {
    if (data[i][1] == athleteNumber && data[i][4] == aspireLevel) {
      return i + 1;
    }
  }
  return -1;
}

function getColumnMapping(aspireLevel, element, judgeNumber) {
  var columnMappings = {
    1: { 1: [20, 23, 26, 29], 2: [21, 24, 27, 30], 3: [22, 25, 28, 31] },
    2: { 1: [32, 35, 38, 41], 2: [33, 36, 39, 42], 3: [34, 37, 40, 43] },
    3: { 1: [44, 47, 50, 53], 2: [45, 48, 51, 54], 3: [46, 49, 52, 55] },
    4: { 1: [56, 59, 62, 65], 2: [57, 60, 63, 66], 3: [58, 61, 64, 67] },
    5: { 1: [68, 71, 74, 77], 2: [69, 72, 75, 78], 3: [70, 73, 76, 79] },
    6: { 1: [80, 83, 86, 89], 2: [81, 84, 87, 90], 3: [82, 85, 88, 91] },
    7: { 1: [92, 95, 98, 101], 2: [93, 96, 99, 102], 3: [94, 97, 100, 103] },
    8: { 1: [104, 107, 110, 113], 2: [105, 108, 111, 114], 3: [106, 109, 112, 115] }
  };
  return columnMappings[element] ? columnMappings[element][judgeNumber] : null;
}
function getColumnMapping2(aspireLevel, element, judgeNumber) {
  var columnMappings = {
    1: { 1: [20, 23], 2: [21, 24], 3: [22, 25] },
    2: { 1: [26, 29], 2: [27, 30], 3: [28, 31] },
    3: { 1: [32, 35], 2: [33, 36], 3: [34, 37] },
    4: { 1: [38, 41], 2: [39, 42], 3: [40, 43] },
    5: { 1: [44, 47], 2: [45, 48], 3: [46, 49] },
    6: { 1: [50, 53], 2: [51, 54], 3: [52, 55] },
    7: { 1: [56, 59], 2: [57, 60], 3: [58, 61] },
    8: { 1: [62, 65], 2: [63, 66], 3: [64, 67] },
    9: { 1: [68, 71], 2: [69, 72], 3: [70, 71] },
    10: { 1: [74, 77], 2: [75, 78], 3: [76, 79] },
    11: { 1: [80, 83], 2: [81, 84], 3: [82, 85] },
    12: { 1: [86, 89], 2: [87, 90], 3: [88, 91] },
  };
  return columnMappings[element] ? columnMappings[element][judgeNumber] : null;
}

function resetValues(ss3) {
  ss3.getRange("B18").setFormula("=IF(C18=TRUE,\"C\",IF(D18=TRUE,\"NYC\",\"\"))");
  ss3.getRange("B19").setFormula("=IF(C19=TRUE,\"C\",IF(D19=TRUE,\"NYC\",\"\"))");
  ss3.getRange("B20").setFormula("=IF(C20=TRUE,\"C\",IF(D20=TRUE,\"NYC\",\"\"))");
  var cellsToReset = ["C18", "C19", "C20", "D18", "D19", "D20"];
  cellsToReset.forEach(cell => ss3.getRange(cell).setValue('FALSE'));
  ss3.getRange("A26").setValue('');
  ss3.getRange("E18").setValue('');
}


function onEdit(e) {
  var sheet = e.source.getSheetByName("Sheet1"); // Change to your actual sheet name
  var range = e.range;

  if (!sheet || range.getA1Notation() !== "B26") return;

  var checked = range.getValue();
  if (checked === true) {
    recordScores();
    // Optional: Uncheck after running the script
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange("B26").setValue(false);
  }
}

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu("Scoring") 
    .addItem("Record Scores", "recordScores") 
    .addToUi();
}

Any help is much appreciated

科技改变生活-雨落星辰 - 所有的伟大,都源于一个勇敢的开始

javascript - running a script on google sheet on mobile not working - Stack Overflow

与本文相关的文章

评论列表(0)