Save Google sheet data onEdit in other sheet update duplicate data

Question

I have two sheets one with entry and one with Data and script thet work onedit with Trigger.

function CopyRow() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSht = ss.getSheetByName("database");
  var entSht = ss.getSheetByName("entry")
  var lastrow = dataSht.getLastRow();
  var adata = entSht.getRange("C4:I4").getValues();
  var bdata = entSht.getRange("C7:I7").getValues();
    var transposedValues = [];
  
  for (var i = 0; i < adata[0].length; i++) {
    transposedValues.push([]);
    for (var j = 0; j < adata.length; j++) {
      transposedValues[i].push(adata[j][i]);
    }
  }
  
  dataSht.getRange(lastrow + 1, 1, transposedValues.length, transposedValues[0].length).setValues(transposedValues);
}

 // dataSht.getRange(lastrow + 1, 1, 1, 5).setValues(dataRng);

//}

function onEdit(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var entSht = ss.getSheetByName("entry")
  var cell = entSht.getRange("C4:G4")
if (cell.getA1Notation()){
    CopyRow();
  }
}

• How can i efect getA1Notation on multi Selected Cell ?
• copy adata to colum A and bdata to Colum B??
• before copy A data check value on same data just update Bcolum

code that work with e Objekt trigger and singel Cell

  function onEdit(e){ 
    if (e.range.getA1Notation() == "C4"){    
    CopyRow();   
    } 
    }

Answer 1

There are two sheets:

• "entry"
  • containing new transaction data in row 4 and row 7
• "database"
  • containing data in Columns A and B
  • Column A aligns to the data in row 4 of "entry"
  • Column B aligns to the data in row 7 of "entry"

The goal is:

• take the data in "entry"
  • check if an value in row 4 already appears in "database"
    • if yes, then update the value in Column B
    • if no, then append the row 4 and row 7 values to "database"

Consider the following script.

• "entry" values for Row 4 and Row 7 are obtained separately and converted to 1D arrays using flat()

• "database" values for Columns A and B are obtained separately and also converted to 1D arrays using flat()

• the script loops through the "entry" row 4 values looking for a match in "database" Column A

  • indexOf is used to find a match. a value of -1 indicated no match, a value >-1 indecated the array index value
    • if indexOf = -1 (no match is found) the "entry" row4 and corresponding row 7 values are added to a temporary array
    • if index <> -1 (match is found), the "database" Column B array value is updated

• at the conclusion of the loop

  • the "database" Column B array is converted back to a 2D array, and the "database" range is updated using setValues()
  • the temporary array is copied to the bottom of "database"

---

function copyEntry2Db() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var entrySheet = ss.getSheetByName("entry")
  var dbSheet = ss.getSheetByName("database")

  // get Entry sheet row 4 and 7 and flatten to 1D arrays
  var entryRow4 = entrySheet.getRange("C4:I4").getValues().flat()
  var entryRow7 = entrySheet.getRange("C7:I7").getValues().flat()
  // Logger.log(entryRow4) // DEBUG
  // Logger.log(entryRow7) // DEBUG

  // get database sheet columns A and B
  var dbSheetLR = dbSheet.getLastRow()
  // Logger.log("DEBUG: Last row of data on database = "+dbSheetLR+", so number of rows of data exlcuding header = "+(+dbSheetLR-1))
  var dbColAValues = dbSheet.getRange(2,1,dbSheetLR-1,1).getValues().flat()
  // Logger.log(dbColAValues) // DEBUG
   var dbColBValues = dbSheet.getRange(2,2,dbSheetLR-1,1).getValues().flat()
  // Logger.log(dbColBValues) // DEBUG

  // create a temporary array to hold values to add to database
  var tempArray = []

  // loop though the row4 entry values to find a match on db column A
  // if indexOf return value = -1, then no match, so add entry value to db
  // if indexOf returns value <. -1, then value is array index
  for (var i=0;i<entryRow4.length;i++){
    var idxMatch = dbColAValues.indexOf(entryRow4[i]);
    //Logger.log("DEBUG: The entry value = "+entryRow4[i]+", index match = "+idxMatch)
    if (idxMatch == -1 ){
      // no match, so add entry values to tempArray
      tempArray.push([entryRow4[i],entryRow7[i]])
    }else{
      // Logger.log("matches value = the index = "+idxMatch+""+", Column A = "+entryRow4[i]+", new value = "+entryRow7[i])
      // match, so update dbColB value
      dbColBValues[i] = entryRow7[i]
    }
  }
  // Logger.log(tempArray) // DEBUG
  // Logger.log(dbColBValues) // DEBUG

  // convert dbColBValues to 2d array
  var nArr = [];
  while(dbColBValues.length > 0) {
    nArr.push(dbColBValues.splice(0,1));
  }
  // Logger.log("DEBUG: the Column B range = "+dbSheet.getRange(2,2,nArr.length,1).getA1Notation())
  // update dbColBValues
  dbSheet.getRange(2,2,nArr.length,1).setValues(nArr)

  // copy new entry values to database Sheet
  // Logger.log("the target range = "+dbSheet.getRange(dbSheetLR+1,1,tempArray.length,2).getA1Notation())
  // format new "database" Column A values as "dd.mm.yyyy"
  dbSheet.getRange(dbSheetLR+1,1,tempArray.length,1).setNumberFormat('dd.MM.yyyy')
  
}

---

SAMPLE - Entries

---

SAMPLE - Database: Before

SAMPLE - Database:After