Update or create new row based on Unique ID Google Sheets

Question

I have found a few options that come close to what I want to do, but nothing matching it exactly.

---

The request is simple enough.

"Sheet A" - Master Sheet (has 1 header row)

"Sheet B" - Input Sheet (has 1 header row)

"Column C" - Unique ID (same column on both sheets)

---

Trigger

• Sheet B is edited

Actions

• Script finds the Unique ID from Sheet B in Column C and looks for it in Sheet A in Column C.
• If it finds it, the entire respective row on Sheet A is replaced with the entire respective row from Sheet B.
• If it does not find it, a new row is added at the bottom of Sheet A and the entire respective row from Sheet B is added as a new record into the new row at the bottom of Sheet A.
• The entire respective row on Sheet B is Deleted.

Actions repeat until there are no populated rows in Sheet B from row 2 on (i.e. excluding the row 1 header).

Thanks

Edits

1. For clarification on why I am looking to do this. I have a Form that is being submitted and sending the data through to Google Sheets (Cognito -> Zapier -> Google Sheets). Part of this form involves repeating sections (line items). The current method that is importing the responses has no issue with adding new responses correctly, however when a response is updated, it cannot find/update the existing row(s) correctly for the repeating sections. So I had the intention of using Sheet A as my master sheet and then using Sheet B to simply be a receiving sheet. This way I can just submit every entry (including updated ones) as a "new" entry on Sheet B, and then have my script do the updating.
2. Sheet B will be edited automatically every time a new form entry is submitted or updated. The "edit" is basically a new row being added and data being populated into that row. It may be a good idea to add a 1 minute timer to the trigger so that if there is lots of data being added that it gives time for that to happen.
3. I am not even remotely close to a script expert. I just browse around different scripts other people have made and try to combine them to get them to work for what I need. I have found scripts that will move a row over and then delete it, but it does not check for matching values to update. I have found other scripts that check for unique values and copy over, but they do not delete the original row on the other sheet. I have tried to combine them, but since I don't have the base knowledge, I can't seem to get it to work.

Answer 1

As a workaround I'd use the onEdit simple trigger and a O(n) search

Here's my approach:

function onEdit(e) {
  // If it's not the Sheet B it won't make changes
  if (e.range.getSheet().getName() !== "Sheet B") {
    return;
  }

  var range = e.range;
  var numberRow = range.getA1Notation().slice(1);
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetA = ss.getSheetByName("Sheet A");
  var sheetB = ss.getSheetByName("Sheet B");

  var currentRowB = sheetB.getRange(`A${numberRow}:D${numberRow}`);
  var id = currentRowB.getValues()[0][2];
  // There's to be 4 values in the row (no empty values)
  if(currentRowB.getValues()[0].filter(value => value !== '').length === 4) {

    // Get all the values in Sheet A
    var rows = sheetA.getDataRange().getValues();
    for (row=1; row < rows.length; row++) {
      // If column C matches the ID replace the row
      if(rows[row][2] === id) {
        var currentRowA = sheetA.getRange(`A${row+1}:D${row+1}`);
        currentRowA.setValues(currentRowB.getValues());
        currentRowB.deleteCells(SpreadsheetApp.Dimension.COLUMNS);
        return;
      }
    }

    // If the ID doesn't match then insert a new row
    var newRow = sheetA.getRange(`A${rows.length+1}:D${rows.length+1}`);
    newRow.setValues(currentRowB.getValues());
    currentRowB.deleteCells(SpreadsheetApp.Dimension.COLUMNS);
  }
}

Which meets the requirements you asked:

• Script finds the Unique ID from Sheet B in Column C and looks for it in Sheet A in Column C. (line 19-28)
• If it finds it, the entire respective row on Sheet A is replaced with the entire respective row from Sheet B. (line 22-24)
• If it does not find it, a new row is added at the bottom of Sheet A and the entire respective row from Sheet B is added as a new record into the new row at the bottom of Sheet A. (line 31-33)
• The entire respective row on Sheet B is Deleted. (line 22 and 33)

I used this Sheet format as example:

Both Sheets have the same format. Keep in mind that this script checks if there's a valid row (in this specific case 4 columns which compose a row) before replacing it.

As a different approach (handling blank data)

In a summary this script should run every X minutes or the time you want it doesn't matter if there's new data incoming because this code will handle all the data given a certain time.

I edited the code in order to use the Z1 cell as a blocker cell and a time based trigger:

Trigger:

Code

function processCells() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetA = ss.getSheetByName("Sheet A");
  var sheetB = ss.getSheetByName("Sheet B");

  // If it's not the Sheet B or if there's a process running it won't make changes
  if (sheetB.getName() !== "Sheet B" || sheetB.getRange("Z1") === "Running") {
    return;
  }

  // Use the Z1 cell in order to block or unblock this sheet
  sheetB.getRange("Z1").setValue('Running');

  // Process all the rows
  var numCells = sheetB.getDataRange().getValues().length + 1;
  for (numberRow = 2; numberRow <= numCells; numberRow++) {

    var currentRowB = sheetB.getRange(`A${numberRow}:D${numberRow}`);
    var id = currentRowB.getValues()[0][2];

    // Get all the values in Sheet A
    var rows = sheetA.getDataRange().getValues();
    var match = false;
    for (row=1; row < rows.length; row++) {
      // If column C matches the ID replace the row
      if(rows[row][2] === id) {
        var currentRowA = sheetA.getRange(`A${row+1}:D${row+1}`);
        currentRowA.setValues(currentRowB.getValues());
        currentRowB.deleteCells(SpreadsheetApp.Dimension.COLUMNS);
        match = true;
        break;
      }
    }

    if(!match) {
      // If the ID doesn't match then insert a new row
      var newRow = sheetA.getRange(`A${rows.length+1}:D${rows.length+1}`);
      newRow.setValues(currentRowB.getValues());
      currentRowB.deleteCells(SpreadsheetApp.Dimension.COLUMNS);
    }
  }

  sheetB.getRange("Z1").setValue('');
}

Note that every time the script runs it'll check if there's another one processing the rows by using Z1.

References

• onEdit
• Event Object