How to Move a Row to Two Different Locations on a Different Google Sheets Tab Based off Two Different Criteria

Question

I would like to copy and paste a row from one google sheet tab titled "New Projects" to another google sheet tab titled "Project Tab" if column H says "Approved" and then clear the row that was copied. Additionally, I would like the destination of the copied row on the tab titled "Project Tab" to be conditional on column G on the tab "New Projects" before the copy and paste function is made. If column G says either "4" or "5" I would like to copy and past the row to row 8 on the tab called "Project Tab", else copy and paste the row to row 60 the tab called "Project Tab". So in summary: If column H says "Approved" in the "New Projects" tab check to see if column G has either a "4" or a "5". If it does move to row 8 on the tab called "Project Tab", else move to row 60. Below is a pictures of:

"New Projects" Tab

Top of "Project Tab" Tab

Bottom of "Project Tab" Tab"

I have created separate working function for sorting the rows once they are copied to the "Project Tab" tab. These functions are listed below in the picture:

Sorting Functions

The code below represents what I currently have. Right now it is copying the rows from "New Projects" tab and pasting in the "Project Tab" tab at row 8 regardless of what column G says on the "New Projects" tab. This is where I need help. How can I create a code that copies and pastes to a specific location based on column G, but the function does not run until it reads "Approved" in column H?

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

  newprojectstoProjecttab(ss,s,r);
}

   function newprojectstoProjecttab(ss,s,r){
       if(s.getName() == "New Projects" && r.getColumn() == 8 && r.getValue() == "Approved") {
         var row = r.getRow();
         var targetSheet = ss.getSheetByName("Project Tab");
         var target = targetSheet.getRange(60,1,1);
         var prioritytarget = targetSheet.getRange(8,1,1);   
            if(r.getColumn() == 7 && r.getValue() == "4" || "5") {
              s.getRange(row, 1, 1, 7).copyTo(prioritytarget);
              var clearRange = s.getRange(row,1,1,8);
              clearRange.clearContent();
           } else {
              s.getRange(row, 1, 1, 7).copyTo(target);
              var clearRange = s.getRange(row,1,1,8);
              clearRange.clearContent();
           }
       }   
   }

Any help would be much appreciated! If you need more information please let me know!

Answer 1

• the first IF is sheet="New Products" and Column H = "Approved"
• the second IF is the value of Column G: if (ColGValue == 4 || ColGValue == 5){
  • if true, then the target is column 8: var target = targetSheet.getRange(8,1,1)
  • if false, then the target is Column 60: var target = targetSheet.getRange(60,1,1)
• thereafter, the commands are identical
  • copy the data to the target: s.getRange(editedRow, 1, 1, 7).copyTo(target)
  • erase the source: s.getRange(editedRow,1,1,8).clearContent()

---

    var ss = spreadsheetApp.getActiveSpreadsheet()
    var s = event.source.getActiveSheet()
    
    // get the edited row, and the values of Column G and H
    var editedRow = event.range.rowStart
    var ColHValue = s.getRange(editedRow,8).getValue()
    var colGValue = s.getRange(editedRow,7).getValue()

    
    if(s.getName() == "New Projects" && ColHValue == "Approved") {
        var targetSheet = ss.getSheetByName("Project Tab")
        if (ColGValue == 4 || ColGValue == 5){
            // if Column G =4 or 5, copy to row 8
            var target = targetSheet.getRange(8,1,1)
        }
        else 
        {
            // if Column G <> 4 or 5, then copy to row 60
            var target = targetSheet.getRange(60,1,1)
        }
        s.getRange(editedRow, 1, 1, 7).copyTo(target)
        s.getRange(editedRow,1,1,8).clearContent()
    }