How To

How to sync spreadsheets and databases - without losing all your data

Ian Yanusko
May 27, 2022
min read

Maybe this sounds familiar: most of your company is using spreadsheet tools (like G Sheets, Airtable, or Notion), but it’s a royal pain to sync data between them, leaving people stuck copy-and-pasting across sheets. Or, maybe your company has a rift between the business users, who love their spreadsheets, and your engineers, who are sick of data getting siloed outside the database or warehouse.

These situations are far too common, and they’re exactly why we built Bracket. With Bracket, you can sync data between spreadsheets and databases — both ways, and in real time.

Here are a few common questions we hear from customers:

  1. How do I know the spreadsheet users at my company won’t delete my whole database?
  2. How do I know this third-party tool won’t overwrite my production data? Can I revert changes?
  3. What happens if teams are editing the same data simultaneously?
  4. What happens if the connection fails?

No matter what data movement solution you choose, you should be asking these questions! Below, we talk through what Bracket does to keep your data safe.

Q: How do I know spreadsheet users won’t delete my whole database?

A: We turn row deletes off by default.

For engineers, one of the scariest things about opening up write access to production data is the thought of somebody accidentally deleting data. Your production data is mission-critical, so we totally get it!

To put your engineers’ minds at ease, by default Bracket disables deletes from the spreadsheet. This means that if you have Airtable linked with MongoDB, any row deletes in Airtable will not delete the linked document in MongoDB. However, any single cell deletes will get synced with the backend database, so clearing a single cell will clear but not delete that linked object in the database.

If you want your spreadsheet users to be able to delete entire rows or documents, we can enable that, but by default this is “off.”

Q: How do I know Bracket won’t overwrite my production data? Can I revert changes?

A: Bracket is a reactive tool - we only listen for changes made in your linked tools - and yes, there are a few ways to revert changes.

Bracket never makes changes on your behalf. All changes enabled by Bracket occur in one of the linked databases or spreadsheets. We simply listen for changes and push them to the other side.

There may be times when you want to revert your data to a previous state because of mistaken data entry. There are a few ways to do this:

  1. If the mistake was small (e.g. a typo in an Airtable cell), manually fix the mistake and it will sync the corrected version.
  2. If the mistake was too large to manually fix and you have a record of the previous state (e.g., snapshots from your own database host), you can revert changes in the database and force push that reverted state to the linked spreadsheet using the Bracket web app. Note that we’ll need to turn off syncing during this time.

Q: What happens if teams are editing the same data simultaneously?

A: You decide rules on how merge conflicts are resolved.

The whole reason we built Bracket is to enable technical and nontechnical teams to work together on the same data, but that brings up an obvious problem: what happens if teams edit the same data simultaneously?

First, a quick note on merge conflicts: in most cases, Bracket syncs your data on a scheduled basis (every minute, every 5 minutes, etc), which is how merge conflicts are even possible in the first place. The higher the frequency of updates, the lower the chance of merge conflicts, but they’re always technically possible!

Right now, we have a simple fix for this: you choose a source to act as master. For example, if you’ve connected Notion and Postgres, and you want your Postgres to win merge conflicts, then Postgres will always override.

We’re actively working on enabling our customers to decide the merge conflict winner by field and even by user!

Q: What happens if the connection fails?

A: You can keep editing your data like normal, and once the connection is restored we'll sync one side to the other.

There are three ways the Bracket connection could fail: 1) one of the paired data tools experiences an outage, 2) Bracket experiences an outage, or 3) the linked databases or sheets are deleted or modified in a way such that Bracket cannot connect to them.

Either way, the Bracket team will be alerted immediately, and we will let you know via Slack that the sync is down.

During the outage, you can continue to update your spreadsheet tool and database, but be aware that to restart syncing, we will need to force push one side’s data to the other in order to reestablish the link. Because of this, we recommend that you only make updates to one of the paired tools during the outage.

In short, these are the ways we keep your data safe. We’re proud to say we save our customers dozens of hours a month that they used to spend on manual scripts and copy & paste.

Want to see if Bracket can help? Get started here.