I'm trying to create an apps script that when a checkbox is checked, it fills in the current date, then moves the row to a separate sheet I've named as "Done/Closed"

While the code fills in the date, it doesn't move the row to my Done/Closed sheet.

The code is shown below;

function onEdit(e) {
  let range=e.range;
  let activeRow = range.getRow();
  let activeColumn = range.getColumn();
  let cellValue = range.getValue();
  let sheet = SpreadsheetApp.getActiveSheet();
  var activeSheet = e.source.getActiveSheet();
  var activeRange = e.source.getActiveRange();
  
  


    if (activeColumn == 22) {
      if (cellValue == false) {
          sheet.getRange(activeRow,23).clearContent();
      } else {
          sheet.getRange(activeRow,23).setValue(new Date());
          //move row to archived sheet
          if (activeSheet.getName() == "Working" && activeRange.getColumn == 22 && activeRange.getValue() == true) {
            var row = activeRow.getRow();
            var numColumns = activeSheet.getLastColumn();
            var targetSheet = sheet.getSheetByName("Done/Closed");
            var target = targetSheet.getRange(targetSheet.getLastRow()   1, 1);
            activeSheet.getRange(row, 1, 1, numColumns).moveTo(target);
            activeSheet.deleteRow(row)
          }
          


      }
    }
}

What am I doing wrong here?

CodePudding user response：

Try it this way:

function onEdit(e) {
  let sh = e.range.getSheet();
  if (sh.getName() == "Working" && e.range.columnStart == 22 && e.value == "FALSE") {
    sh.getRange(e.range.rowStart, 23).clearContent();
  } else {
    sh.getRange(e.range.rowStart, 23).setValue(new Date());
    if (sh.getName() == "Working" && e.range.columnStart == 22 && e.value == "TRUE") {
      var targetSheet = e.source.getSheetByName("Done/Closed");
      var target = targetSheet.getRange(targetSheet.getLastRow()   1, 1);
      sh.getRange(e.range.rowStart, 1, 1, sh.getLastColumn()).moveTo(target);
      sh.deleteRow(e.range.rowStart)
    }
  }
}

CodePudding user response：

I believe your goal is as follows.

• From What am I doing wrong here?, you want to know the reason for this.
• By modifying your script, you want to achieve your goal.
  • When the checkbox of column "V" of "Working" sheet is checked, you want to move the row.

Modification points:

• When I saw your script, at the line of if (activeSheet.getName() == "Working" && activeRange.getColumn == 22 && activeRange.getValue() == true) {, activeRange.getColumn == 22 is required to be used. In this case, it's activeRange.getColumn() == 22.
• And, even when activeRange.getColumn == 22 is modified to activeRange.getColumn() == 22, I think that an error occurs at var row = activeRow.getRow();. Because in your script, activeRow is not Range object by let activeRow = range.getRow();.
• And, even when activeRow is modified, I think that an error occurs at var targetSheet = sheet.getSheetByName("Done/Closed");. Because in your script, sheet is not Spreadsheet object by let sheet = SpreadsheetApp.getActiveSheet();.

When these points are reflected in your script, it becomes as follows.

Modified script:

function onEdit(e) {
  let range = e.range;
  let activeRow = range.getRow();
  let activeColumn = range.getColumn();
  let cellValue = range.getValue();
  let sheet = SpreadsheetApp.getActiveSheet();
  var activeSheet = e.source.getActiveSheet();
  var activeRange = e.source.getActiveRange();
  if (activeColumn == 22) {
    if (cellValue == false) {
      sheet.getRange(activeRow, 23).clearContent();
    } else {
      sheet.getRange(activeRow, 23).setValue(new Date());
      if (activeSheet.getName() == "Working" && activeRange.getColumn() == 22 && activeRange.getValue() == true) {
        var row = activeRow;
        var numColumns = activeSheet.getLastColumn();
        var targetSheet = e.source.getSheetByName("Done/Closed");
        var target = targetSheet.getRange(targetSheet.getLastRow()   1, 1);
        activeSheet.getRange(row, 1, 1, numColumns).moveTo(target);
        activeSheet.deleteRow(row)
      }
    }
  }
}

• When this modified script is used, when the checkbox of column "V" of "Working" sheet is checked, the row is moved to "Done/Closed" sheet.

Note:

• In the case of your script, I thought that the script might be able to be simpler a little. So, as one more modified script, I would like to propose the following sample script.

  function onEdit(e) {
    const srcSheetName = "Working";
    const dstSheetName = "Done/Closed";
  
    const {range, source} = e;
    const sheet = range.getSheet();
    if (sheet.getSheetName() != srcSheetName || range.columnStart != 22) return;
    if (range.isChecked()) {
      range.offset(0, 1).setValue(new Date());
      const dstSheet = source.getSheetByName(dstSheetName);
      const row = range.rowStart;
      sheet.getRange(row, 1, 1, sheet.getLastColumn()).moveTo(dstSheet.getRange(dstSheet.getLastRow()   1, 1));
      sheet.deleteRow(row);
      return;
    }
    range.offset(0, 1).clearContent();
  }
  

References:

• Simple Triggers
• getColumn()
• getSheetByName(name)