google spreadsheet update same row from multiple files

Question

So I'm currently involved in a local political campaign.

Master File

I have one master file with about 30,000 voters in a large geographical area. I have some basic information about each voter (like ID, city or phone number) and some gained information about each of them (like when did we last talk to them, which volunteer knows them or what is their political affiliation).

Shared files

Although I can't share this file with a lot of people I do need to share portions of that data with different people.

A few examples:

1. I want the volunteer team in each city to have the list of the registered voters in that city - so that they can call them.
2. I also want each volunteer to have a list of people whom he knows and or been in contact with. Obviously, this two lists have interlapping data and I need to be able to update them from both of those files.
3. Also, I want to be able to update those rows from my master file - so that I can easily enter some data I get in bulk.

I don't know of any way to implement this. Is this even possible in google sheets? Is there another online free web tool that I could use?

thanks

Answer 1

Sure, it's possible, but you have to make a lot of script work.

Here are some thoughts.

1. Why script

You can't achieve this with help of importrange function for 3 reasons:

1. Security. Each volonteer will have a potential access to all your data.
2. Speed. Importrange will dramatically slow down your calculations.
3. View only. The formula result is view-only, no edits can be done.

---

2. Data design

To help yourself with a script work, you may consider creating:

1. IDs for each row in each file.
2. IDs for fields (columns) in each file.

The first row in each sheet may contain column ID:

 1       2               3           4
ID    city    phone number         ...

---

3. Writing a script

When you deal with a script, you have some limits:

1. A script can't run online, you'll have to use triggers, and run each hour.
2. You can't run a trigger each minute because you'll deal with Google quotas.

As you have 3 conflicting types of files, you have to decide, which changes are main.

Main File > Team File > Member File.

One possible way to solve conflicts is to use a timestamp for changes in respective fields.

---

Conclusion

google-sheets is probably, could be replaced with a good CRM in your case.

Please see the Importer, my project for doing import with a script.