CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsmovedelete-row
Twin K
Twin K

Reputation: 1

Google Sheets: Move Row to Other Sheet, Then Back Again, Based on Cell Value

Please please help! I have been researching this for weeks without a solution.

We use google forms to combine data onto google sheets. There are 2 sheets- "INCOMPLETE" which accepts all form responses, and "COMPLETE".

I was able move a row based on the condition "COMPLETE" from sheet name "INCOMPLETE" to sheet name "COMPLETE".

I need help with: (1) I can not for the life of me find a script that will reverse it if someone accidentally checked "COMPLETE", therefore, moving it back to the "INCOMPLETE" Sheet and have it sorted by Time Stamp.

(2) If possible I would also like another column that shows a timestamp for each time an update is made to that particular row.

(3) If possible, send a reminder email to the order person when a incomplete remains for more than 2 days (from the original timestamp).

This is the script that I have so far:

function onChange() {
  // moves a row from a sheet to another when a magic value is entered in a column
  // adjust the following variables to fit your needs
  // see https://productforums.google.com/d/topic/docs/ehoCZjFPBao/discussion
  var sheetNameToWatch = "INCOMPLETE";
  var sheetNameToWatch2 = "COMPLETE";
  var columnNumberToWatch = 7; // column A = 1, B = 2, etc.
  var valueToWatch = "COMPLETE";
  var valueToWatch2 = "INCOMPLETE";
  var sheetNameToMoveTheRowTo = "COMPLETE";
  var sheetNameToMoveTheRowTo2 = "INCOMPLETE";
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getActiveCell();
  
  if (sheet.getName() == sheetNameToWatch && range.getColumn() == columnNumberToWatch && range.getValue() == valueToWatch) {
    var targetSheet = ss.getSheetByName(sheetNameToMoveTheRowTo);
    var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    sheet.getRange(range.getRow(), 1, 1, sheet.getLastColumn()).copyTo(targetRange);
    sheet.deleteRow(range.getRow());
    
    } else if (sheet.getName() === sheetNameToWatch2 && range.getColumn() == columnNumberToWatch && range.getValue() == valueToWatch) {
    var targetSheet = ss.getSheetByName(sheetNameToMoveTheRowTo2);
    var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    sheet.getRange(range.getRow(), 1, 1, sheet.getLastColumn()).copyTo(targetRange);
    sheet.deleteRow(range.getRow());   
}
  }

Upvotes: 0

Views: 639

Answers (1)

Cooper
Cooper

Reputation: 64100

During the testing of this I found the bugs I left as a comment above. I also like to add a return to other sheets as quickly as possible so that the code returns on unwanted sheets as quickly as possible.

function onChange() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var rg=sh.getActiveRange();
  Logger.log('Head: Sheet: %s Range: %s col: %s row: %s value: %s',sh.getName(),rg.getA1Notation(),rg.getColumn(),rg.getRow(),rg.getValue());
  if(sh.getName()=='INCOMPLETE' || sh.getName()=='COMPLETE'){

    if(sh.getName()=='INCOMPLETE' && rg.getColumn()==7 && rg.getValue()=='COMPLETE') {
      var tsh=ss.getSheetByName('COMPLETE');
      var trg=tsh.getRange(tsh.getLastRow() + 1, 1);
      sh.getRange(rg.getRow(), 1, 1, sh.getLastColumn()).copyTo(trg);
      sh.deleteRow(rg.getRow());
      Logger.log('Top: Sheet: %s Range: %s col: %s row: %s',sh.getName(),rg.getA1Notation(),rg.getColumn(),rg.getRow());
    } 

    if (sh.getName()=='COMPLETE' && rg.getColumn()==7 && rg.getValue()=='INCOMPLETE') {
      var tsh=ss.getSheetByName('INCOMPLETE');
      var trg=tsh.getRange(tsh.getLastRow() + 1, 1);
      sh.getRange(rg.getRow(), 1, 1, sh.getLastColumn()).copyTo(trg);
      sh.deleteRow(rg.getRow());   
      Logger.log('Bot: Sheet: %s Range: %s col: %s row: %s',sh.getName(),rg.getA1Notation(),rg.getColumn(),rg.getRow());
    }
  }else{
    return;//quick return for other sheets
  }  
}

Upvotes: 0

Related Questions