Integrate Spreadsheets from Google Sheets, Dropbox, and Your Local Files in Python

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: Sample Data From the NJ File. The Headers will be the same in each file 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 Click on the Google Sheets Upload box to start connecting Select the Google Sheet you want to connect to Select the file 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 We will access the data from the r.json() object’s data key 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. Click on the Dropbox Upload Box to Start the Process 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

Still puzzling over something?