CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Kate Stewart
Kate Stewart

Reputation: 1

Google Sheet Apps Script Help - Moving a row over to a new sheet after a "completed" status + 30 day

i have the following code for when a line on my first tab get's marked as completed. Where i need help is to move it over to the "+ 30 day tab when it's marked as completed AND it's been more than 30 days.** How do i add that +30 days into the code?**

Header for date is in column H = Date of Signature (move to tab after 30 days from this date AND) Header for status is in column I = Status

`function onEdit(e) { const src = e.source.getActiveSheet(); const r = e.range;

// If the current cell value is in the correct column and it starts with Completed, // then move it to the +30 days tab.

if (r.columnStart != 9 || r.rowStart == 1 || ! r.getValue().startsWith("Completed")) { return; }

const dest = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("+ 30 days"); src.getRange(r.rowStart,1,1,23).moveTo(dest.getRange(dest.getLastRow()+1,1,1,23)); src.deleteRow(r.rowStart); }`

nothing yet! i'm not a coder and hoping that this code will do what i need it to do!

Upvotes: 0

Views: 44

Answers (1)

Cooper
Cooper

Reputation: 64032

Try this:

function onEdit(e) {
  const sh = e.range.getSheet();
  if (e.range.columnStart == 9 && e.range.rowStart > 1 && e.value.startsWith("Completed")) {
    let dt1 = new Date(e.range.offset(0, -1).getValue());
    let dv1 = new Date(dt1.getFullYear(), dt1.getMonth(), dt1.getDate()).valueOf();
    let dv2 = new Date(new Date().getFullYear(), new Date().getMonth(), new Date().getDate()).valueOf();
    if (dv1 > dv2) {
      const dest = e.source.getSheetByName("+ 30 days");
      sh.getRange(e.range.rowStart, 1, 1, 23).moveTo(dest.getRange(dest.getLastRow() + 1, 1, 1, 23));
      sh.deleteRow(e.range.rowStart);
    }
  }
}

Upvotes: 0

Related Questions