2-Way Sync: Non-row ID

Setting up 2-way sync within the same spreadsheet where the ID and the row DO NOT have a mathematical relation.
Important notes:
* Remember to increment the /row/ variable after the loop
* This assumes the FILTER()/QUERY()/IMPORTRANGE() etc. formula is already setup and functioning properly.

Sheet (to copy from the File menu)
https://docs.google.com/spreadsheets/d/1v3UiZydfvj7P3ZQkQqCcofmy2amXCP8zpKaYZ52x4To/edit#gid=1438556100

Script:
function onEdit(e){
if (!e) throw “Do not run manually”;

syncNonRow(e);
}

function syncNonRow(e){
const src = e.source.getActiveSheet();
const r = e.range;
if (src.getName() == “Database” || r.rowStart == 1 || r.columnStart == 1) return;
r.clear();
let id = src.getRange(r.rowStart,1).getValue();
const db = SpreadsheetApp.getActive().getSheetByName(“Database”);
const ids = db.getRange(“A:A”).getValues();
let row = 0;
for (row; row < ids.length; row++){
if (ids[row][0] === id)
break;
}
row++;
db.getRange(row,r.columnStart).setValue(e.value);
}

Connect with me:
• spencer.farris@gmail.com
• spencerfarris.me
• www.linkedin.com/in/spencer-farris/
• Twitter @FarrisSpencer
• Google Product Expert support.google.com/docs/profile/12305

Donate on Paypal: spencer.farris@gmail.com

Leave a Reply

Your email address will not be published. Required fields are marked *