Script Editor Error: Not moving row based on specific value

Question

I am trying to move a row of data from one sheet to another, when a specific value is recorded in a specific column.

I have found and adapted the following code to use in the Apps Script:

function onEdit(event) {
  // assumes source data in sheet named Responses
  // target sheet of move to named Completed
  // test column with yes/no is col Y or 25
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  if(s.getName() == "Responses" && r.getColumn() == 25 && r.getValue() == "yes") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Completed");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }
}

I have tested this on a copy of this data and it has worked fine. However, when using the original, nothing happens, when yes is entered in column Y. I have listed below some reasons why I think this may be occurring:

This is a shared sheet, I am not the owner. The owner has granted permissions
The responses sheet is connected with a Google Form

P.S. The following error appears when the script is executed from the editor, I don't believe this is an issue based on the type of trigger, it also appeared on my test and had no impact

TypeError: Cannot read property 'source' of undefined onEdit @ Code.gs:6

I would appreciate any suggestions on workarounds!

Welcome to [so]. This is a very frequent question... briefly, you should not run this function from the code editor directly b/c it requires an event object. You might test it by editing the spreadsheet or by creating a function that creates a mock event object and calls the onEdit function passing the required parameter. — Rubén, Mar 13 '22 at 21:59
Hi @Rubén. Thanks for the advice, I did know that. I have tested this directly in the sheet and faced it not working, hence asking the question. It obviously worked in my test sheet, so unsure why it doesn't work now. — M Singh, Mar 14 '22 at 00:58
Please clarify what you mean by "The following error appears when run, I don't believe this is an issue based on the type of trigger, it also appeared on my test and had no impact..." . Does the referred error message appears every time that the sheet is edited? — Rubén, Mar 14 '22 at 02:31
Hello @Rubén, no the error does not show when the sheet is edited. All I meant was, this error appears, but I don't think it is the issue. There is no error displayed when editing the sheet, but it does not cause the row to move. — M Singh, Mar 14 '22 at 07:54

Rubén · Answer 1 · 2022-03-14T15:37:31.603

In order to help you to debug your script, just after function onEdit(event) { add a the following line:

console.log(JSON.stringify(event,null,' '));

The above will print the event object "printable" properties on the execution logs. This will allow you to validate, first, that the function is actually triggered when the spreadsheet is edited by using the Google Sheets UI, second, the coordinates of the edited cell as well the value entered into edited cell.

The coordinates will be shown as properties rowStart, columnStart, rowEnd, columnEnd.

Also you might find helpful to add the following line before the if statement.

console.log(s.getName());

NOTE: The error "Cannot read property 'source' of undefined" occurs when the onEdit function is run from the script editor as the event object is undefined.

Script Editor Error: Not moving row based on specific value

1 Answers1