0

I have a script bound to my Google spreadsheet with a simple onEdit(e) trigger that isn't working.

The script is supposed to move the edited row to Closed Cases, then delete the original row, if the following conditions in the active sheet are all true:

  1. The active sheet is named 11th Grade
  2. The edited cell is in the checkbox Column 14 / N
  3. The checkbox is selected / true

When I make changes to 11th Grade the script always completes successfully but the row is never moved or deleted.

function onEdit(e) {

  var ss = e.source;
  var activatedSheetName = ss.getActiveSheet().getName();
  var activatedCell = ss.getActiveSelection();
  var activatedCellRow = activatedCell.getRow();
  var activatedCellColumn = activatedCell.getColumn();
  var activatedCellValue = activatedCell.getValue();
 
  var input = ss.getSheetByName("11th Grade"); // source sheet
  var closed = ss.getSheetByName("Closed Cases"); // target sheet

  if (activatedSheetName == input.getName() && activatedCellColumn == 14 && activatedCellValue == "True") {

    // insert a new row at the second row of the target sheet
    closedlosed.insertRows(2,1);

    // move the entire source row to the second row of target sheet
    var rangeToMove = input.getRange(/*startRow*/ activatedCellRow, /*startColumn*/ 1, /*numRows*/ 1, /*numColumns*/ input.getMaxColumns());
    rangeToMove.moveTo(closedlosed.getRange("A2"));
    
    // delete row from source sheet
    input.deleteRows(activatedCellRow,1);
  }
}
0

2 Answers 2

0

The code in the question calls nine SpreadsheetApp methods every time any value in the spreadsheet is edited, before actually getting to its business, and uses six API calls to move the row. All these API calls slow things down. The event queue only will only handle two events at a time, so if you tick checkboxes in quick succession, chances are that some rows do not get moved even if they meet the conditions.

You can speed it up by orders of magnitude by taking more advantage of the event object e, like this:

/**
* Simple trigger that runs each time the user manually edits the spreadsheet.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
  if (!e) {
    throw new Error(
      'Please do not run the onEdit(e) function in the script editor window. '
      + 'It runs automatically when you hand edit the spreadsheet. '
      + 'See https://stackoverflow.com/a/63851123/13045193.'
    );
  }
  moveRowConditionally_(e);
}


/**
* Moves a row from '11th Grade' to the top of 'Closed Cases' when a
* checkbox in column N is ticked and the row has 'Success' in column D.
*
* @param {Object} e The onEdit() event object.
*/
function moveRowConditionally_(e) {
  if (e.value !== 'TRUE' || e.range.columnStart !== 14) return;
  const sheet = e.range.getSheet();
  if (!sheet.getName().match(/^(11th Grade)$/i) || e.range.offset(0, -10).getValue() !== 'Success') return;
  try {
    const targetSheet = e.source.getSheetByName('Closed Cases');
    if (!targetSheet) throw new Error('Target sheet cannot be found.');
    targetSheet.insertRowBefore(2);
    sheet.getRange(`${e.range.rowStart}:${e.range.rowStart}`)
      .moveTo(e.source.getRange('Closed Cases!A2'));
    sheet.deleteRow(e.range.rowStart);
  } catch (error) {
    e.source.toast(error.message, 'moveRowConditionally_', 30);
    throw error;
  }
}

The version above makes no SpreadsheetApp calls unless the edit took place in column N and the new value in the cell is true, as in a ticked checkbox. Only when those two conditions are met, it will make two API calls to check whether the edit took place on '11th Grade'. If that condition is met as well, it will make another two calls to check whether the value in column D is Success. If that condition is met as well, it will move the row, using another five API calls.

See these onEdit(e) optimization tips.

14
  • I don't know where the success part came from for column D. That was notes from a script I copied. All I want to do is column N (14) when checked needs to move from 11th Grade to Closed Cases. Nothing seems to work. Not even the script above??
    – Kelly
    Commented Dec 5, 2023 at 12:52
  • 1
    Column D is specified in the question. You can remove the || e.range.offset(0, -10).getValue() !== 'Success' bit if needed. Please edit the question as appropriate. See How do I ask a good question? Commented Dec 5, 2023 at 13:02
  • Sadly this doesn't execute when I check the box?? Is there something I am missing?
    – Kelly
    Commented Dec 5, 2023 at 14:44
  • The onEdit(e) function will run automatically when you edit the spreadsheet. If you are using the script I gave you, it will execute. Perhaps you mean that no rows move when you tick a checkbox? That's not the same as "does not execute." Make sure you are editing a sheet whose name is spelled exactly 11th Grade with no leading or trailing spaces, letter case differences, or the like. Consider sharing a publicly editable sample spreadsheet. Commented Dec 5, 2023 at 14:57
  • Nothing copies and nothing deletes. It does nothing when the checkbox is checked.
    – Kelly
    Commented Dec 5, 2023 at 18:03
0

Here is a working version of your script that you can review to see how it differs from your approach.

Script

Sample Spreadsheet Link

function onEdit(e) {

  const ss = e.source;
  const range = e.range;
  const sheet = range.getSheet();
  const row = range.getRow();
    
  const input = "11th Grade";
  const output = "Closed Cases";
  const valueCol = 4;
  const checkCol = 14;
  
  if ((sheet.getName() == input) &&          //  was the right sheet edited?  
      (range.getColumn() == checkCol) &&     //  was the checkbox column edited?
      (range.getValue() == true) &&          //  is the checkbox selected?  
      (out = ss.getSheetByName(output))) {   //  does the destination sheet exist?

    out.insertRowAfter(1);
    sheet.getRange(row, 1, 1, sheet.getLastColumn()).moveTo(out.getRange("A2"));
    sheet.deleteRow(row);

  }

}

Example of Script in action with the source and target sheets displayed

Google Apps Script Simple onEdit(e) Trigger

Troubleshooting

If you have a script that is not working as intended, you can add logging with Logger.log() to determine what is working/not working and where.

If the conditions are passing but the move isn't being performed, you know that the issue is with the statements following the conditions.

Here is an example that logs the success or failure of the if statement conditions.

if (x) {
  Logger.log("success");
} else {
  Logger.log("failure");
}

Shows a portion of the script containing the if statement with the addition of an else statement and logging for success and failure.

3
  • That is not part of what I need to do. I removed Line 19 and it still is not completing (copying, pasting and deleting)
    – Kelly
    Commented Dec 6, 2023 at 14:26
  • @Kelly is there a failure in the execution log? If not you need to troubleshoot what you are doing differently. You could add some simple logging to see if the conditions pass. I can't tell from here what you are doing that is different but if you screenshot your code I'm sure I can point out the issue. I clarified some basic troubleshooting steps with an example in an edit to my answer.
    – Blindspots
    Commented Dec 6, 2023 at 19:09
  • @kelly I added a working sample spreadsheet using your source data.
    – Blindspots
    Commented Dec 6, 2023 at 20:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.