2-Way Sync: Row-aligned ID

preview_player
Показать описание
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)

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

syncWithRow(e);
}

function syncWithRow(e){
let row = id + 1;
}

Connect with me:
• Twitter @FarrisSpencer

#GoogleSheets #GoogleAppsScript #appsscript
Рекомендации по теме
Комментарии
Автор

Thanks for this video Spencer. Is there a way to allow adding info in additional rows to propagate back to the database? For example, if Mary had another student join her class. Is there a way to make it so that if she were to add the student in a new row in her tab, this new student would show up in the database tab and assign an ID to the row?

RunninMiles
Автор

Agin thanks for a great work.
I have a sheet named 'Main Data' then i have created another sheet named 'Clint1' which is sync with Main Data.
Now i use Data validation in Clint1 sheet. in my case data validation is date input only. when I input a date in Clint1 sheet, It returns the value in Main Data but not that formate, it return a garbage value. is there any solution For data validation from client 1 sheet to Main Data sheet?

prosharmarketing
Автор

Hello! could you help me understand the script, from the code i understand that it only updates new edits to the "Database" spreadsheet, however when i try your example sheet it actually really works both ways from the database to the other sheets and vice-versa. When i try to copy the scipt and paste it to another sheet however it doesnt work both ways. is there something i am missing out?

prisylj
Автор

How do you edit the code to specify which sheets you want this code to run on

delannaleonard
Автор

Excellent, I always thought this issue has no solution.

AgendaBioBioCL
Автор

Hi Spencer. Thank you for making this video. The 2-way sync works but whenever I use the filter feature on my queried sheet, my filtered data disappears. I'm just new to app scripts and I've been trying to figure out what should be added to the script. Can you please help me?

candymabaquiao
Автор

What a great tutorial.
is it possible to sync two way for comment?
when i make a comment in one sheet this comment is not shifted in my database sheet.

prosharmarketing
Автор

How do you make the refresh rate faster 😅

filingbee
Автор

Hi Spencer. Thank you again for making another version of the 2 way sync row aligned ver. I am wondering, is it possible to have 2 or more databases in a single workbook or worksheet?

sosyalfang
Автор

Hi Spencer, thank you for the excellent explanation however i can't seem to get it to work in my sheets. I currently have a Master List Worksheet and a separate Worksheet for the other team members and i can't seem to get it to work so that whenever my team members edit their worksheet the new data will overwrite the existing data and appear in the Master List while corresponding to the unique ID :(

myrumahbaru