Sync Google Sheets with MongoDB: 4 ways to do it

‍Intro

Companies of all sizes have always relied on Google Sheets. No matter how much you try to move away from it, there will always be new use cases where business teams will revert back to the infinitely flexible spreadsheet. But often data gets siloed.

The problem

Google Sheets enables nontechnical teams  to easily work with data. The problem is that often a company’s data primarily sits (or should sit) in a backend database like MongoDB  — so, how to bridge the gap between the two?

This problem comes up in many ways. Here are some examples:

1. Logistics & e-commerce companies have shipping and order data that needs manual adjustments from the Ops team – fulfillment statuses need to be updated, damaged shipments need to be logged, and any changes to order details need to be communicated.  . The Ops team needs to both access and edit this data quickly.
2. B2B SaaS companies want an easy way to toggle certain features on/off for customers, especially during trial periods. The Customer Success team needs a way to manage these feature tier flags without constantly bothering engineering. 
3. Fintech companies handle underwriting in Google Sheets. Review teams need an easy way to input underwriting criteria for potential customers and have that sync  with MongoDB so an approval / denial can be issued to the customer-facing app.

Incomplete or mismatching data, constant requests to engineers… sound familiar? Below, we talk through 4 different ways to sync between Google Sheets and MongoDB.

‍

Method #1: Write an internal script

For some teams, the knee-jerk method is to build an internal script. After all, why pay for something you could do yourself?

If you’re just doing one-way syncs from MongoDB to Sheets, it may be enough to work with MongoDB change streams and the Sheets API. MongoDB offers change streams, making it simple to access real-time data changes.  However, the Sheets API comes with a few drawbacks, such as strict rate limiting. It can also be challenging to maintain a proper record mapping, since Sheets does not mandate a unique primary key.

If you need to sync both ways, things will get more complex. You need to develop a way to map records from Sheets to documents from MongoDB, control the schema so that extraneous data doesn’t pollute your database, and figure out a method for handling merge conflicts.

 Even looking past these limitations, internal scripts invariably require development and maintenance time, and as any developer knows, can break at the worst of times :) 

Pros: Maximum flexibility & control, tailored to your use case

Cons: Requires valuable engineering resources to build and maintain, potentially not scalable, no customer service when things go wrong or your data schema changes‍

‍

Method #2: Use Zapier?

Want to use Zapier to do this? You can’t. Not because it doesn’t track deletes, updates to existing records, and only does one way syncs. But because MongoDB isn’t supported.

Pros: None

Cons: It doesn’t work

‍

Method #3: ETL/reverse ETL

If you don’t want to worry about the hassle of writing internal scripts and don’t think Zapier will fit your use case, your next best bet is to use a more technically savvy solution: ETL (or ELT) and reverse ETL. ETL and reverse ETL can help ensure a single source of truth for your company.

In case you’ve never used ETL before: ETL stands for “Extract-Transform-Load.” Imagine you have a number of SaaS apps–like Salesforce, Zoho, and ADP–with important data in them that you’d like to standardize in a data warehouse, like Redshift or BigQuery. With an ETL provider, you can extract from those apps, transform the data so that it’s standardized & cleaned, and load it into the data warehouse. Some common tools are Fivetran, Stitch, and Airbyte.

Reverse ETL: Exactly what it sounds like! Reverse ETL is very similar to ETL, except you’re pulling from your data warehouse into your SaaS apps. Reverse ETL is handy if data in your warehouse is updating frequently, and you want your nontechnical teams to see the most up-to-date data in their SaaS tools. Some common tools are Hightouch, Census, and Grouparoo (now a part of Airbyte).

However, ETL and reverse ETL have three drawbacks: 

1. It’s pricey! If you need data to simply sync both ways, ETL and reverse ETL can get complicated: you’ll need two separate providers, meaning two separate sales and billing processes and double payments. You will also need to figure out how to navigate the infinite loop problem.
2. Not optimized for speed. If you need data to be updated faster than 5 minutes, these tools will not fit your use case. This is especially problematic when you need your business teams to have access to data as soon as possible.
3. You still need a method for handling merge conflicts. 

Pros: Robust tools, many options available, a ton of integrations

Cons: Slow syncing speeds, too complicated for many companies and use cases, not able to provide two-way syncs under one roof, and the infinite loop problem.

‍

Method #4: Use Bracket for two-way syncs

Sometimes, you just want the same data to show up in two different places in real-time. Getting your G-sheets and MongoDB to mirror each other can significantly reduce data sharing frictions on your team and cut down reliance on engineers. But as shown by the options above, this is surprisingly hard to do!

Our frustrations led us to build Bracket, which does fast bidirectional syncing between tools like Google Sheets and MongoDB (and any other database for that matter). 

Bracket is built for data teams and engineers, but requires zero code to maintain.   To set up a sync, all you need to do is:

1. Create an empty G sheet with field names but no data.
2. Go to app.usebracket.com
3. Enter the connection details for your MongoDB collection and Google Sheet. Optional: add an aggregation statement to transform the data between MongoDB and Sheets. 
4. Select syncing direction and double check that the field mapping is accurate. Click sync, and voila! 

The full step-by-step guide is in our docs. And now you’ll never have to worry about complicated zaps or internal scripts again.

Pros: built for bidirectionality, extremely tailored to your use case. Only pay for 1 tool to do syncs in either direction

Cons: Still building out integrations for new tools, but our MongoDB and Google Sheet connectors are live!

‍Want to get your data syncing? Get started with Bracket here.