script to copy a row of data from one sheet to a specific sheet based on the value of a cell

Question

I'm trying to create a script to copy a row of data from one sheet to a another sheet based on the value of a cell.

But essentially when a new row of information is input in the main sheet it'll copy the row and place that row of data into a another sheet based off the value of a specific cell. In this case it's the utility company. If, for example, the customer is created and the utility company they have is United Power I want to have the sheet copy that customers information over and add it to the specified United Power sheet and so on and so forth with the other utility company's (3 total)

I keep getting the below error: TypeError: Cannot read property 'source' of undefined (line 4, file "Code")

I'll paste below what I have so far. (I only have the code for one so far) I can also link the spreadsheet if need be.

 function onEdit(event) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  if(s.getName() == "Contracts" && r.getColumn() == S && r.getValue() == "United Power") {
  
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("United Power");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
  } 
}

That probably is happening because you are trying to run the function without the event object. — Cooper

Cooper Cooper · Accepted Answer · 2020-06-29T21:14:57

You could have written like this:

You need to change S to a number

function onEdit(e) {
  const sh=e.range.getSheet();
  if(sh.getName()=="Contracts" && e.range.columnStart==19 && e.value=="United Power") {
    const tsh = e.source.getSheetByName("United Power");
    const target = tsh.getRange(tsh.getLastRow() + 1, 1);
    sh.getRange(e.range.rowStart,1,1,sh.getLastColumn()).moveTo(target);
  } 
}

script to copy a row of data from one sheet to a specific sheet based on the value of a cell

1 Answers