0

I have two scripts that I need to work with on Google Sheets. The first one auto sorts all rows if I put a date on column 23. The other script moves the entire row to another sheet called completed when I check a box on row 28. Separate they work perfectly but when I add them to the same script one of them stops working. How can I tweak this?

enter image description here

function autoSort(){
  
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const ws = ss.getSheetByName("Installation")
  const range = ws.getRange(2,1,ws.getLastRow()-1,28)

  range.sort({column:23, descending:false})
}

function onEdit(e){

  const row = e.range.getRow()
  const column = e.range.getColumn()

  if(!(column === 23 && row >= 2)) return
  
  autoSort()
}

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

  if(s.getName() == "Installation" && r.getColumn() == 28 && r.getValue() == "Completed") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Complete");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }
}
Nimantha
  • 6,405
  • 6
  • 28
  • 69
Ronald Garcia
  • 1

1 Answers1

1

I believe your goal is as follows.

  • Your 2 functions of onEdit work fine for each run. You want to merge these functions.

In this case, how about the following modification?

Modified script:

function onEdit(e) {
  const r = e.range;
  if (r.columnStart === 23 && r.rowStart >= 2) {
    autoSort()
    return;
  }
  const s = r.getSheet();
  if (s.getSheetName() == "Installation" && r.columnStart == 28 && r.getValue() == "Completed") {
    var row = r.rowStart;
    var numColumns = s.getLastColumn();
    var targetSheet = e.source.getSheetByName("Complete");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }
}

Reference:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • I get an error when ran. TypeError: Cannot read property 'range' of undefined onEdit @ Move File.gs:2 – Ronald Garcia Jan 11 '22 at 07:10
  • @Ronald Garcia Thank you for replying. I apologize for the inconvenience. From your question, I had thought that you wanted to run the script by OnEdit trigger. So also, I used OnEdit trigger. So from `I get an error when ran. TypeError: Cannot read property 'range' of undefined onEdit @ Move File.gs:2`, I'm worried that you might directly run the function `onEdit` with the script editor. If my understanding is correct, such error occurs. In this case, please edit the cells. By this, the script is run by the OnEdit trigger. – Tanaike Jan 11 '22 at 07:28
  • @Ronald Garcia If I didn't want to run the script with the OnEdit trigger and you want to directly run the script with the script editor, I apologize for my misunderstanding. Also, you can see the official document about the trigger. [Ref](https://developers.google.com/apps-script/guides/triggers) – Tanaike Jan 11 '22 at 07:28