0

I want an automated method of cutting and pasting completed projects from one sheet to another sheet. I want to have a column where users can go in and type "DONE" at the end of a row to state that that particular project is complete and then I want a script to recognize that and cut the whole row and paste it into another sheet. It would be even better if the script went ahead and deleted the row from the sheet after it is done cutting the data and pasting it so that there aren't blank rows left over.

1 Answer 1

1

Here is a script that does the following: if a user enters 'DONE' in any cell in sheet named 'Sheet1', the row with that cell is copied over to Sheet2, and deleted from Sheet1. To use, just enter in Tools > Script Editor, and change sheet names if needed.

The script does not check that 'DONE' is in the last nonempty cell of a row: it can be anywhere.

function onEdit(e) { 
  if (e.value === 'DONE') {
    var sheet = e.range.getSheet();
    if (sheet.getSheetName() == 'Sheet1') {    // Name of the sheet with projects
      logSheet = e.source.getSheetByName('Sheet2');   // Name of the archive sheet 
      var row = e.range.getRow(); 
      var lastRow = logSheet.getLastRow();
      var range = sheet.getRange(row, 1, 1, sheet.getLastColumn()); 
      range.copyTo(logSheet.getRange(lastRow + 1, 1));
      sheet.deleteRow(row);
    }
  }
}

Depending on the contents of your spreadsheet, you may want to replace the copying command with

range.copyTo(logSheet.getRange(lastRow + 1, 1), {contentsOnly: true});

so that only static values are copied, not formulas. This makes sense if the row contains formulas that would stop working properly if moved to another sheet.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.