Setting up 2-way sync within the same spreadsheet where the ID and the row have a mathematical relation.
Important notes:
* This version only works when the ID and the row have a mathematical relation such that the row number can be derived from the ID number;
* This assumes the FILTER()/QUERY()/IMPORTRANGE() etc. formula is already setup and functioning properly.
Sheet (make a copy from the File menu)
https://docs.google.com/spreadsheets/d/1XgDMx8hh6Od2ShRbkyyAa8PyXQsC4XGfzBDVlfFfTeg/edit#gid=0
Script:
function onEdit(e){
if (!e) throw “Do not run manually”;
syncWithRow(e);
}
function syncWithRow(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();
let row = id + 1;
let db = SpreadsheetApp.getActive().getSheetByName(“Database”);
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
#GoogleSheets #GoogleAppsScript #appsscript