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
