I am going to show you how to easily bring data together from all these sources using API Spreadsheets and Python. And keep it updated and in sync. So you can keep your cool and be happy!

We will use 1 Google Sheet, 1 Local File and 1 Dropbox File. Each file will contain Covid data from 1 unique US State: TX, NJ or CA. The data will be broken up on each row by county and look like the following:

Let’s get started!

---

Step 0: Create an Empty Python File

Create an empty Python file named spreadsheet_integration.py and save it somewhere.

The final code for the file will be as follows. We will walk through how to get there in this post.

Step 1: Create an API Spreadsheets Free Account

Create a free account by clicking on Sign Up

Step 2: Connect to Your Google Sheet

Click on the Google Sheet Upload Box

Select the Google Sheet you want to connect to

Copy the Python Code from the Read Tab and paste it in your Python file

Your Python File will now have the following code

We are now going to change two things in the code to make the data easier to deal with

1. We will access the data from the r.json() object’s data key
2. We will import pandas and convert the data to a Pandas Dataframe

Our code will now look like the following

All API links in this post are public so feel free to use this to test!

Step 3: Connect to Your Dropbox File

Click on the Dropbox Upload box. The first click will either ask you to authenticate OR if your account is already open in the tab, it will just refresh the page. This is expected behavior from Dropbox authentication.

Now click on the Dropbox Upload box again and you will see a File Chooser. Select your file.

Follow the same steps as above for copying the Python code in your file.

However, we are now going to change the name of the requests and the data to be more identifying.

Step 4: Upload File From Your Desktop

Now we are going to upload the file from your desktop.

We are not going to bore you with this process. Simply click on the Upload or Drop Files box and upload your file.

Follow the same steps as above to get the python code and insert it into your Python file. Then change the variable names so it all makes sense.

This will lead us to our final code

Step 5: Do Something with your Data

That’s it! That’s how easy it is to integrate spreadsheet data from various sources.

The best part is that if the underlying Google Sheet or Dropbox File data changes then your data is automatically updated.

And if you want to share your data with someone you simply need to send them the API link.

Good luck with your integration journey and feel free to drop us a note at info@lovespreadsheets.com if you run into any trouble or have any questions.

Checkout our other tutorials!

Go Up