CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsspreadsheet
G1U_9802
G1U_9802

Reputation: 1

Google Sheet Script to Copy and Paste Row

I am working on this spreadsheet and I want it to move the entire row to a separate tab once it is marked as "complete" and if it is marked anything else while on that tab, I want it to move BACK to the original tab.

I've written a script and it works. However the only problem with it is that every single time I write anything on the sheet, everything shifts around. Is there any way to avoid this? I am going to be writing on the sheet constantly and I would like it to ONLY move things around when that specific cell that shows the status (pending, In progress, Complete") changes.

This is what i have written on the script for the Spreadsheet so far: `

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

if(s.getName() == "Current Tasks" && r.getColumn() == 3 && r.getValue() == 'Complete') {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Archived Tasks");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  } else if(s.getName() == "Archived Tasks" && r.getColumn() == 3 && r.getValue() == 'In Progress', 'Pending') {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Current Tasks");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }
}

`

Upvotes: 0

Views: 51

Answers (1)

Martín
Martín

Reputation: 10217

Maybe it's not it, but could you try changing your second if statement?:

else if(s.getName() == "Archived Tasks" && r.getColumn() == 3 && (r.getValue() == 'In Progress' || r.getValue() == 'Pending')){

Upvotes: 1

Related Questions