How can I maintain referential integrity between two google spreadsheets?
google-apps-script, google-sheets
Solution
Ive done this before more than once, in different ways. First, ill suggest the easiest one: if the roles that updates A B and C all have permission to see all data, you can keep all the info in a single master spreadsheet plus have a script that generates B and C (say every hour, or realtime with onChange). This way you can still have reader roles the way you had them already. Drawback: write roles have access to all.
Second, this one does cover all your reqs. Have an onChange in A that detects the rows changed and only updates/deletes those in B,C. Here integrity is little more difficult because many rows can be inserted/deleted at once, plus if your onChange fails (and we know script api calls fail sometimes) the integrity breaks. The easiest to fix that is to also call the first script (forced sync-all-rows) every few hours so its 'eventally consistent'.
This assumes that rows are only deleted/inserted on A.
Problem
Suppose I have three spreadsheets. One has a list of employees and their contact information, let's call this spreadsheet A. Another has a list of employees and their favorite colors (spreadsheet B), and the and a third has a list of employees and their birthdays, pet names and last review date (spreadsheet B). When we add an employee to spreadsheet A, there should be a new line in spreadsheets B and C with the employee's name present, but the rest of the row should be blank. When we update an employee's name (someone got married!) on spreadsheet A, that change should propagate to spreadsheets B and C. (Note that this is fairly easy to do with the import range function: https://support.google.com/drive/answer/3093340 ) When we delete an employee from spreadsheet A, we'd like the entire rows from spreadsheets B and C to be deleted (or just have the employee's name be replaced with something obvious like 'Deleted'). There's no need for bidirectional sync (if someone updates spreadsheet B or C, spreadsheet A can remain unchanged, and if they update the employee name on B or C, it does not need to be propogated back). I've looked around on google and stack overflow. There may be a set of custom google apps script that provide this functionality, but I haven't found it. This book and the blog it is based on talk about using mysql as a backend for google spreadsheets: https://leanpub.com/googlespreadsheetprogramming but I don't think that is quite what I'm looking for. Options I have considered: - roll them up into a single spreadsheet--this won't work for the real problem I'm solving - use importrange. This solves the update functionality but not the insert/delete problem - use importrange to import the names to a different sheet of spreadsheets B and C, and then use data validation to enforce correct names in the name columns. This might work for update/delete cases, but makes it super tedious to set up a new spreadsheet (D), and also makes it hard to separate first name and last name. - google fusion tables--they had some limitations that prevented us from moving over to them wholesale