Is there a way in google sheets to move an entire row automatically based on drop down options inside of one of the columns and change dates

0
votes

What I'm looking to do is take my google sheet that contains columns A through N. What I want to do is take the rows, except for row 1 which is a header for all the column, and have them automatically move to the bottom of the same spreadsheet based on the drop down options in column E. Once the option has been selected that signifies it's done I want the entire row to automatically move to the bottom of the spreadsheet. In addition to that I also want column D, which is the due date for the event, to update by adding one year to the date in that column as the new due date will be one year from the previous. Is this possible? Thanks for the help.

function onEdit(e) {
  const row = e.range.getRow();
  const col = e.range.getColumn();
  const as = e.source.getActiveSheet();
  if(as.getName() == "Sheet1" && col == 5 && row >1 && !as.getRange(row,col).getValue()=='MPS')
  {
    const row_new = as.getRange(row,1,1,col);
    row_new.copyTo(as.getRange(as.getLastRow()+1,1,1,col));
    as.deleteRow(row)
  }
}
1
google-sheetsspreadsheet
I strongly recommend you to take a look into StackOverflow: How to ask a question. In order to get a prompt response you can summarize the title and what's actually your expected spreadsheet behavior. It's helpful for the community. What have you tried so far? – Jose Vasquez
My apologies, I'll have to look at that. In the meantime I have edited the code I'm trying from another question on here, however we had slightly different goals and I changed it slightly, but I might not be doing it right. – BlackBird
By I want the entire row to automatically move to the bottom of the spreadsheet. you mean sort your selected range? also In addition to that I also want column D, which is the due date for the event, to update by adding one year to the date in that column as the new due date will be one year from the previous that will need a little bit of context due to the nature of the question e.g. an illustration or an image. – Jose Vasquez
The contents of the entire row should move to the bottom of the spreadsheet once it completes until the next time It needs to come due. However, I can sort it everytime but I would rather it does it automatically. So currently column D is setup to have a bunch of dates when something is due. Everytime it is complete it needs to change to the following year. So for example. It's due tomorrow march 23 2021 once it's completed the row should move to the bottom and the date should then read March 24 2021. – BlackBird

1 Answers

0
votes

You should change the way you are comparing the column 5

I've used the following example:

Sheet example

Instead of '==' use '===' comparison because double '=' is not comparing the value but the type of the object. Having said that here's my approach:

function onEdit(e) {
  const row = e.range.getRow();
  const col = e.range.getColumn();
  const as = e.source.getActiveSheet();
  const newDate = new Date();

  // No due date
  if (as.getRange(row,3).getValue() > newDate) {
    return;
  }

  if(as.getName() === "Sheet1" && col === 5 && row >1 && as.getRange(row,col).getValue()!=='MPS')
  {
    let row_new = as.getRange(row,1,1,col);

    // Add 1 day to your due date
    let oldValues = row_new.getValues();
    oldValues[0][3] = new Date(oldValues[0][3].setDate(newDate.getDate() + 1));

    // Set the new date
    row_new.setValues(oldValues);

    row_new.copyTo(as.getRange(as.getLastRow()+1,1,1,col));
    as.deleteRow(row);
  }
}

Where at the very beginning there's a comparison between dates in order to determine if the column D (Date) is greater than the current date or not, then in order to set the new Date I make use of the Class Date and added 1 day to the date extracted from the column D.

References