When I combine 2 onEdit scripts, I keep getting a range error on Google sheets-Script Editor

Question

I have a shared google sheet that I use as a to-do list. I am using Script Editor. I originally had everything moving to an empty row on sheet 1. As more was added I found that the done items need to move to a second sheet. My original code moved the row from any input in column 6/F. I have tried adding a trigger to move column 7/G to sheet 2. Both codes work by themselves, but I cannot seem to combine them.

After researching this site and others, I have tried renaming onEdit, Nesting, using “my function”, recording macros. I receive the same error but the line changes depending on how I edit the code. "TypeError: Cannot read property 'range' of undefined onEdit @ Done.gs:2"

What I want to achieve,

When the task is marked complete it moves to sheet2 (Column 7/G type “yes”) When the status of a task is updated it moves it to an empty row on sheet 1 (Column 6/F words used – ongoing, pending review, later) I am also looking for a basic course to start understanding scripts and macros for google sheets. I realize that I am starting in the middle and making it harder on myself. Thank you I appreciate the feedback and recommendations!

function onEdit(event) {
// assumes source data in sheet named Sheet1
// target sheet of move to named Sheet2
// test column with yes is col 7 or G
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = event.source.getActiveSheet();
var r = event.source.getActiveRange();

if(s.getName() == "Sheet1" && r.getColumn() == 7 && r.getValue() == "yes") {
var row = r.getRow();
var numColumns = s.getLastColumn();
var targetSheet = ss.getSheetByName("Sheet2");
var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
s.getRange(row, 1, 1, numColumns).moveTo(target);
s.deleteRow(row);
}
}
function onEdit(e) {

const row = e.range.getRow();
const col = e.range.getColumn();
const as = e.source.getActiveSheet();
if(as.getName() == "Sheet1" && col == 6  && row > 1 && !as.getRange(row,col).getValue()=='') {  
const row_new = as.getRange(row,1,1,col);
row_new.copyTo(as.getRange(as.getLastRow()+1,1,1,col));
as.deleteRow(row);

 }
}

Cooper Cooper · Accepted Answer · 2021-06-08T13:12:30

Try this:

function onEdit(e) {
  e.source.toast('entry');//debug
  var s = e.range.getSheet();
  s.getRange('P1').setValue(JSON.stringify(e));//debug
  if (s.getName() == "Sheet1" && e.range.columnStart == 7 && e.value == "yes") {
    e.source.toast('cond1');//debug
    var tsh = e.source.getSheetByName("Sheet2");
    var trg = tsh.getRange(tsh.getLastRow() + 1, 1);
    s.getRange(e.range.rowStart, 1, 1, s.getLastColumn()).copyTo(trg);
    s.deleteRow(e.range.rowStart);
  }
  if (s.getName() == "Sheet1" && e.range.columnStart == 6 && e.range.rowStart > 1 && !e.value) {
    e.source.toast('cond2');//debug
    const row_new = s.getRange(e.range.rowStart, 1, 1, e.range.columnStart);
    row_new.copyTo(s.getRange(s.getLastRow() + 1, e.range.columnStart));
    s.deleteRow(e.range.rowStart);
  }
}

I added the tools that I generally use to debug these scripts. The toasts help me to be able to determine where the code is stopping when I edit the page. And the line that include JSON.stringify(e) I usually put in some unused cell near the top of the page so that one can see what's inside of the event object.

When I combine 2 onEdit scripts, I keep getting a range error on Google sheets-Script Editor

2 Answers