How to sync between my mongodb and Google spreadsheets?

Question

I would like to use Google Spreasheets for allowing people to visualize and edit the data of tables stored in mongodb collections.

The data is created by web services, around 50 mongodb collections are created daily. (The data is tabular in essence: all the documents of a collections are flat key value objects with the same keys.)

Now, I want to allow people inside my company to visualize and edit the data of the tables using Google Spreadsheet.

I am looking for the best way to keep mongobd and Google Spreasheets in Sync.

I looked at Google Sheets API and Google Drive API. It seems to be feasible. My design is:

1. Each time a web service writes data to a mongo collection, it updates the corresponding spreadsheet via Google Sheets API (java)
2. Once a minute, using Google Drive Revision API, I check what spreadsheets were recently updated.

I could differentiate between changes made by people through the Spreadsheets app and changes made by the web service, because the web service uses a special user.

But as far as I know, the Google Drive revision API doesn't allow to get only the changes that are made by a set of users.

Is my design appropriate?

Answer 1

If you do want to go with your change tracking method have your data persistence layer push to both MongoDB and to some sort of queue (Redis? RabbitMQ?). Then have another service read off the queue to push the data into Google Sheets. That way you have a ordered record of what's suppose to happen without slowing down the persisting of your data (at least not by much). This can be made pretty robust so that if your service is down, or the Google Sheets API is down, you wont lose track and have to do a full resync.

I'm not sure how much data we're talking about here, but if possible it might be easier to dump the entire DB to a spreadsheet every few minutes, or just as often as you really need, rather than change tracking everything.

You may also want to check out Business Intelligence tools if you have the budget. They're specifically designed to provide information to business people based on your data. They may also have a connector for it to suck in all the data from MongoDB, without you having to right an integration layer. Then you can focus on building things more important to your business.