How to make a better Google Sheets looping script for multiple checkboxes that write data using appe-CodePudding

I want to find out if there's a more efficient coding option than what I have cobbled together from other S.O. posts. I'm pretty inexperienced with Sheets coding, so I'm sure I've got the least-efficient solution possible. I don't really understand much about Cache use or other practices.

I have a page of data search results (called the GammaPage below) that are populated based on user searches. When the user finds a row of data that they want to incorporate into a separate report, they press a checkbox, and that checkbox triggers an onEdit(e) command which appends the chosen row of data -- and only that row -- to a separate page for data collection and processing (the EpsilonPage below).

What works: The script runs correctly, exporting the user-selected data when prompted by the checkbox.

What makes me wonder: The script takes a long time to run (about 8 seconds). I feel like there has to be a more efficient way to write it while doing the same tasks. To be clear, I'm not asking for a brand-new script. I'm just asking if the one I have should be rewritten in some way that loops more cleanly.

Why it is the way it is:

The script needs to append each new row of selected data one row below the previous selection.
The data needs to be static values, not something that can be changed, so any code-based solutions like QUERY or IMPORTRANGE won't work.
There needs to be support for 30 separate checkboxes, allowing users to choose any one of 30 search results (when they examine the raw data) to export.
The execution of the code needs to happen when the user clicks the checkbox; a time-based trigger will not suffice.

The code (condensed for clarity): I've appended the function below, or at least the first four loops of it, to demonstrate how it's working. The whole function is currently 341 lines long. I feel like I'm missing an obvious wildcard somehow.

function onEdit(e) {
if (e.source.getActiveSheet().getName() != 'AlphaSheet' && != 'BetaSheet' && e.source.getActiveSheet().getName() != 'EpsilonSheet') {
  var spreadsheet = SpreadsheetApp.getActive();
  var rr = e.range;
  var ss = e.range.getSheet();
  var headerRows = 5;  // # header rows to ignore
  if (rr.getRow() <= headerRows) return;
  var checkBoxLocation6 = "A6";
  const values6 = spreadsheet.getRange('GammaSheet!B6:D6').getValues().flat();
  var checkBoxLocation7 = "A7";
  const values7 = spreadsheet.getRange('GammaSheet!B7:D7').getValues().flat();
  var checkBoxLocation8 = "A8";
  const values8 = spreadsheet.getRange('GammaSheet!B8:D8').getValues().flat();
  var checkBoxLocation9 = "A9";
  const values9 = spreadsheet.getRange('GammaSheet!B9:D9').getValues().flat();
  var checkBoxCondition = true;
  var result = SpreadsheetApp.getUi().alert("Do you wish to extract this data?", SpreadsheetApp.getUi().ButtonSet.OK_CANCEL);
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('GammaSheet'), true);
  if (e.range.getA1Notation() == checkBoxLocation6) {
    if (e.range.getValue() == checkBoxCondition) {
    if(result === SpreadsheetApp.getUi().Button.OK) {
    spreadsheet.getSheetByName('EpsilonSheet').appendRow(values6);
    SpreadsheetApp.getActive().toast("Request initiated.");
    spreadsheet.getRange('B3').clearContent();
    } else {
    SpreadsheetApp.getActive().toast("Request canceled.");}
    spreadsheet.getRange('A6').setValue('FALSE');}}
  if (e.range.getA1Notation() == checkBoxLocation7) {
    if (e.range.getValue() == checkBoxCondition) {
    if(result === SpreadsheetApp.getUi().Button.OK) {
    spreadsheet.getSheetByName('EpsilonSheet').appendRow(values7);
    SpreadsheetApp.getActive().toast("Request initiated.");
    spreadsheet.getRange('B3').clearContent();
    } else {
    SpreadsheetApp.getActive().toast("Request canceled.");}
    spreadsheet.getRange('A7').setValue('FALSE');}}
  if (e.range.getA1Notation() == checkBoxLocation8) {
    if (e.range.getValue() == checkBoxCondition) {
    if(result === SpreadsheetApp.getUi().Button.OK) {
    spreadsheet.getSheetByName('EpsilonSheet').appendRow(values8);
    SpreadsheetApp.getActive().toast("Request initiated.");
    spreadsheet.getRange('B3').clearContent();
    } else {
    SpreadsheetApp.getActive().toast("Request canceled.");}
    spreadsheet.getRange('A8').setValue('FALSE');}}
  if (e.range.getA1Notation() == checkBoxLocation9) {
    if (e.range.getValue() == checkBoxCondition) {
    if(result === SpreadsheetApp.getUi().Button.OK) {
    spreadsheet.getSheetByName('EpsilonSheet').appendRow(values9);
    SpreadsheetApp.getActive().toast("Request initiated.");
    spreadsheet.getRange('B3').clearContent();
    } else {
    SpreadsheetApp.getActive().toast("Request canceled.");}
    spreadsheet.getRange('A9').setValue('FALSE');}}
}};

And so on for another 26 loops.

Screenshot for reference:

So, can I be less clunky in my coding, or is this as good as it gets, given what I want it to do?

CodePudding user response：

Sorry, it's really hard to resist the temptation to transform the 314 lines into 25 lines (without comments). So, here we go:

function onEdit(e) {
  // check if it's first column
  if (e.range.columnStart != 1) return;

  // check if checkbox is true
  if (e.value != 'TRUE') return;

  // check if the sheet is the right sheet
  var sheet = SpreadsheetApp.getActive();
  var ignored_sheets = ['BetaSheet', 'EpsilonSheet', 'Imported master list'];
  if (ignored_sheets.includes(sheet.getName())) return;

  // get data from current row
  var row_index = e.range.rowStart;
  var row = sheet.getRange('B'   row_index   ':D'   row_index).getDisplayValues().flat();
 
  // check if cell 'C' of current row is not empty, just to be sure
  if (row[1] == '') return; 
  
  // show the prompt message
  var result = SpreadsheetApp.getUi()
    .alert("Do you wish to request this student?", SpreadsheetApp.getUi().ButtonSet.OK_CANCEL);
  if (result != SpreadsheetApp.getUi().Button.OK) {
    sheet.toast("Request canceled.");
    return;
  }

  // do stuff
  sheet.toast("Request initiated.");
  sheet.getActiveCell().setValue(false);
  sheet.getRange('B3').clearContent();
  sheet.getSheetByName('EpsilonSheet').appendRow(row);
}