2-way Sync: Non-row ID

[@rmtgbesds1]

Thank you. I'll be waiting for the next one. Great script tutorial. It will improve my shared google sheets at my workplace (Portuguese school)

[@SpencerFarris]

Que bom! Obrigado e de nada.

[@khkgkhkg]

This is amazing. Trying to adapt it to run with multiple spreadsheets (to prevent Mary or John from seeing each others work, or the database) but running into problems. Any advice?

[@SpencerFarris]

What problems are you running into? That setup should work fine

[@seabreezebeach93]

Hi Spencer, what should we modify in the script if the unique id columns is at C?

[@SpencerFarris]

if (src.getName() == "Database" || r.rowStart == 1 || r.columnStart == 3) return; and let id = src.getRange(r.rowStart,3).getValue(); Those are the two that actually mark the ID column

[@m2mhung]

@@SpencerFarris how could we change the code when I filter with 2 or or 3 col, ? docs.google.com/spreadsheets/d/19gwkCX22NmDAsWwihSIyOL14GpodKu_HOhv8jamvtN8/edit#gid=0

[@eliasnogueira1286]

How do I do when the filter formula is not in A2 but in A10 for example? and if I change the column of the filter formula? How do I do this without the header disappearing?

[@SpencerFarris]

Sorry it's been SO LONG. Do you still need help?

[@yannhuet]

Hey Spencer, awesome tips here! Does this work between 2 different google sheets, instead of 2 tabs in the same sheet? And if so, does all users need read/edit permission to the master google sheet in order for the script to sync data from a child google sheet to a master google sheet?

[@SpencerFarris]

It does work between workbooks! kzbin.info/www/bejne/q5SvgGeffbVlgdk Only the user who creates the trigger needs to have edit permissions on both.

[@nssdesigns]

If you could get this to work with Checkboxes -that would be useful. Saves typing TRUe and FALSE or using data validation [TRUE,FALSE] dropdown on the MARY tab Database allows Checkboxes but the filtered views don't activate (only show the Database state TRUE or FALSE). But thank you this is truly a game changer in data collection within Sheets.

[@SpencerFarris]

Yes, I'll be looking into that issue. Thank you for pointing it out!

[@mauryhernandez2297]

function onEdit(e) { if (!e) throw "Do not run onEdit from script editor"; const src = e.source.getActiveSheet(); const r = e.range; moveProcessed(src,r); syncNonRow(src,r); } function moveProcessed(src, r) { if (src.getName() != "NEW VEHICLES IN" || r.columnStart != 11) return; const dest = SpreadsheetApp.getActive().getSheetByName("ACTIVE PRODUCTION"); r.offset(0, -10, 1, 10).moveTo(dest.getRange(dest.getLastRow() + 1, 1, 1, 10)); src.deleteRow(r.rowStart); } function syncNonRow(src,r){ if (src.getName() == "ACTIVE PRODUCTION" || r.rowStart == 3 || r.columnStart == 2) return; r.clear(); let id = src.getRange(r.rowStart,1).getValue(); const db = SpreadsheetApp.getActive().getSheetByName("ACTIVE PRODUCTION"); const ids = db.getRange("B:B").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); } function syncWithRow(e){ if (src.getName() == "ACTIVE PRODUCTION" || r.rowStart == 3 || r.columnStart == 2) return; r.clear(); let id = src.getRange(r.rowStart,2).getValue(); let row = id + 1; let db = SpreadsheetApp.getActive().getSheetByName("ACTIVE PRODUCTION"); db.getRange(row,r.columnStart).setValue(e.value); } Im using the combine 2 scripts code, works except that the value to sync returns the original one... is not overwriting it... any idea?

[@SpencerFarris]

I'm sorry, what's the issue?

[@mauryhernandez2297]

@@SpencerFarris I can make it work the two scrips, but I used a way around so, I might not need anymore... however would be nice make it work the script for use more than one actions or trigger