How can I maintain referential integrity between two google spreadsheets?

4
votes

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
1
google-apps-scriptgoogle-sheets
Interesting question. What would be wrong with merging the spreadsheets to be 1 sheet each in a large master spreadsheet? – rickcnagy
@br1ckb0t different parts of the organization are responsible for different spreadsheets, and perhaps have sensitive data. Even if I could merge them into one spreadsheet with different sheets, how would that solve the insert or delete scenarios? – mooreds
Since you can articulate your problem, you might have a go at writing a script? Not really helpful, I know, but it might be the simplest answer... – Rom1
@Rom1 I'm trying to avoid writing custom code, but that may be required. – mooreds
Fusion SQL is limited compared to JET/T-SQL, but it depends on how complex your relationship structure is. Off the cuff, I'd say you can't nest deeper than 1 level via merge (2 if your joins can be emulated with a WHERE clause). but Fusion tables do support calculated columns, and if necessary, you can manipulate more complex structures in sheets, then upload to a temporary Fusion table for reference. the Fusion API does execute requests much quicker than the Visualization HTTP method now. – Tim

1 Answers

3
votes

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.