Make G-Sheets a UI for your MongoDB
I recently wrote a blog post on different ways to sync Google Sheets with MongoDB, and thought it would be useful to lay out the key points here for anybody who is facing this problem!
The problem
G sheets is an incredibly popular way for business teams to interact with data. You can use Sheets for internal tools, BI, ad hoc analysis, you name it. However, it's extremely tricky to sync data between G sheets and NoSQL databases like MongoDB. This limits the usefulness of either tool. So, what options are there? There are a few. Method #1: Write your own script
You could build your own in-house solution using Google's API and MongoDB change streams. Mongo's change streams make it pretty easy to track events in MongoDB, but the Google API is trickier to work with. For two-way syncs, you also need to figure out a way to map records to each other, since Google Sheets does not offer unique record IDs Pros: totally tailored to your use case, max flexibility Cons: diverts engineering resources, can be very high-maintenance, not super scalable Method #2: Use Zapier
Most of you are probably familiar with Zapier, which is a great automations tool However, when it comes to simple data syncing, Zapier is pretty limiting: you have to set up zaps for every possible data change (e.g. updates, added records, etc), you can quickly hit your monthly zap limit if you do batch updates, the fastest sync is 15 minutes unless you pay for the Professional plan, and it's impossible to do two-way syncing without hitting an infinite loop. Pros: No-code, familiar to previous Zapier users Cons: Surprisingly high-maintenance, can quickly hit monthly limits, slow syncing, no two-way syncs Method #3: ETL / Reverse ETL
If you've dealt with data syncing woes in the past, you may have come across one of the many ETL/reverse ETL solutions out there. ETL = "Extract-Transform-Load." Basically, ETL is a way to get data from your SaaS tools (e.g. Salesforce, Zoho, ADP) into your data warehouse. Fivetran, Stitch, and Airbyte are some big players. Reverse ETL: the opposite! Data flows from your warehouse to the SaaS tools. Hightouch, Census, and Grouparoo are big players. These tools are robust, but they can also get a bit complex and pricey for smaller companies and do not support two-way syncing. They are also slow -- 10 minute sync intervals is usually the fastest you can do. For two-way syncs, you also need a way to handle merge conflicts (i.e., what happens if somebody is editing the same record in G Sheets and MongoDB at the same time?) Pros: Many players in the space to choose from, a lot of integrations Cons: Slow syncing speeds (usually 10 minutes at the fastest), too complicated for many use cases, no two-way syncs Method #4: Use Bracket for two-way syncs
Sometimes, you just need data in two different places to match in real time. Maybe you don't want to deal with the hassle of setting up ETL and reverse ETL. Maybe you want simple bidirectional syncing that's engineer-friendly, yet simple enough for nontechnical users to set up. Bracket is designed for use cases like this. You can connect G sheets with MongoDB and set up one-way or two-way syncs with a few clicks. Pros: built for bidirectional syncing but can also handle one-way syncs, simple onboarding Cons: Still building out integrations for new frontend tools, but our G sheets connector is live! I included more links at the actual blog post that might be helpful. Happy syncing everybody!