A New Way To Share Spreadsheet Data

DISCLAIMER: This is about our tool API Spreadsheets. We have been told it’s an easy and obvious way to share spreadsheet data amongst a team.

How do you currently share your spreadsheet data with other data peoples? Let me guess, you download your Google Sheet or Dropbox file. Or wait a minute, was the file in your email sent 3 days ago? Oh crap, it was on your work computer wasn’t it. Okay you found the file.

Now you email it to John to run some analysis. He runs his analysis. He sends it back to you. You re-run your old models…..and What the Hell?! The numbers don’t make sense any more and oh my god, your boss will be mad, you spent so much time doing this. AHHHHHH.

Image for post
Photo by Andrew Neel on Unsplash

Okay turns out John had added some new data that messed up your calculations. Once you take that into account, it’s all okay. Phew. Crisis Averted.

But the crisis shouldn’t have happened in the first place

Enter API Spreadsheets

What if you could work with the same source of truth for Spreadsheet data? And even if that data changes, your code would never have to change. And it will also read in the most up to date Spreadsheet data.

Even if the Spreadsheet is on Dropbox, Google Sheet or your Local Computer.

Our tool, API Spreadsheets, lets you do that.

Image for post

How?

We use the power of APIs to create a URL to your spreadsheet data, whether it is on Google Sheets, Dropbox or Locally. Then that URL stays current no matter what.

So you, John and Koya can all have the same URL in your Python code. And when the data changes, you won’t have to do anything :)

If you don’t know what an API is, feel free to check out our article describing APIs.

Okay, that sounds interesting. But how does it actually work?

Let’s walk through a scenario.

You are the team leader of a Data Analytics team. John and Koya are your direct reports and all three of you do experimental modeling in Python to figure out why your customers might be leaving.

You get a Customers.xlsx file every week from your sales team. And your team maintains a Regions file on Google Sheets. Both are essential to your modeling.

So instead of re-downloading and re-emailing the file to everyone every time the data changes, you can use API Spreadsheets to get a URL for each of your file.

You pick one person in charge of creating the URLs. It is important to have just one leader for Data Governance.

This person creates an account on API Spreadsheets and then uploads the Customers.xlsx file and connects to the Regions file on Google Sheets.

Image for post
You Click on Google Sheets and Upload or Drop Files to connect to both your files

After a file has been uploaded and connected, you will get an API URL. And a Python code snippet on how to read your data.

Image for post

You can play around with the data format and see how you would like to read your data. You can see how each Data Format looks in the Sample Response.

Image for post
Image for post

Now, all three of you can use the same Python code to read in the data!

Wait, so how will the Data update automatically?

For Google Sheets, the data will always just update automatically as you add, delete or update any rows.

For the Excel file, in this case Customers.xlsx, the Data Governer will need to replace the current version with the new version in their Dashboard on API Spreadsheets.

This is very simple AND it maintains your API URL. That means your code in Python will not need to change.

  1. Go To The Files Tab
Image for post

2. Go to your Customers.xlsx File, Click More Options, Click Replace File and Replace with your current version

Image for post
This will preserve the URL but update the data with the new file!

That’s it! Now your Customers.xlsx file is up to date and your URL will give you the most recent data.

Thats’ it!

We hope this helps you as much as it has helped some of our other users. Feel free to email us at info@apispreadsheets.com if you have any questions!

Products and resources that magically simplify hard spreadsheet tasks. Check us out at www.lovespreadsheets.com

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store