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.
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.
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.
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.
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.
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.
- Go To The Files Tab
2. Go to your Customers.xlsx File, Click More Options, Click Replace File and Replace with your current version
That’s it! Now your Customers.xlsx file is up to date and your URL will give you the most recent data.
We hope this helps you as much as it has helped some of our other users. Feel free to email us at firstname.lastname@example.org if you have any questions!