Import data from your own API into Google Drive, with 30 lines of code


#1

Datadeck provides a broad range of data connectors which are really handy. But what if you want to use data from a web app that’s not supported yet, or from your own proprietary API?

In this post, I’m going to demonstrate how to use Google Drive as an intermediary to bridge some data API and Datadeck. As you can see, with as few as 30 lines of Python code, you can save Seattle’s weather forecast data from Yahoo’s Weather API into a CSV file in Google Drive, and then, of course, build a beautiful chart in Datadeck.

Preparation

  1. Install Python 3.x and latest pip. I’m using Python 3.6.1.
  2. Clone the sample code from my GitHub repository.
  3. In a command shell, get into “weather” folder in the sample code, and install required Python packages by running:

pip3 install -r requirements.txt

  1. Follow this nice post to get your Client Id and Client Secret for accessing Google Drive API, and update them into settings.yaml file in the source code:
client_config:
  client_id: <<Your Google Client Id>>
  client_secret: <<Your Google Client Secret>>

All the prep is done, now show me the code!

You can find full source code in the GitHub repository. Here I’ll just give a quick walkthrough. First import all modules used. We use PyDrive to access Google Drive which greatly simplifies authorization and file upload.

from pydrive.auth import GoogleAuth
from pydrive.drive import GoogleDrive
import requests

Yahoo’s Weather API allows you query weather forecast with YQL (similar to SQL) and get back data in JSON.

# Fetch weather forecast data
weather_query = '''
    select * from weather.forecast where woeid in 
        (select woeid from geo.places(1) 
            where text="Seattle, WA")'''
data = requests.get('https://query.yahooapis.com/v1/public/yql?format=json&q=' \
    + weather_query)
data_json = data.json()

Finally we format the data into CSV and upload it onto Google Drive. Note that before creating a new file we first see if a previous upload already exists, if so, overwrite it.

# Open or create google drive file
gauth = GoogleAuth()
gauth.LocalWebserverAuth()
drive = GoogleDrive(gauth)
file_name = 'seattleweather.csv'
files = drive.ListFile({'q': "title='{}' and trashed=false".format(file_name)}).GetList()
if files:
    gd_file = files[0]
else:
    gd_file = drive.CreateFile({'title': file_name, 'mimeType': 'text/csv'})

# Set content and upload
content = 'date,high,low\n'
for item in data_json['query']['results']['channel']['item']['forecast']:
    content += '{},{},{}\n'.format(item['date'],item['high'], item['low'])
gd_file.SetContentString(content)
gd_file.Upload()
print('File uploaded, title: {}, id: {}'.format(gd_file['title'], gd_file['id']))

When you run the code for the first time, you’ll see the standard Google authorization page. After you authorize it, a credentials.json file will be created with tokens saved there, so in the future you don’t need to do it again.

Last but not least, create your beautiful chart in Datadeck. What a reward!
image

If you want to get it fully automated and refresh data everyday, you can either find a server and set a daily scheduled cron there, or use cloud-based automations like AWS Lambda service.


DataDeck Release Notes (09-19-2018)
#2

#3

Wow, I didn’t suppose the code could be such simple to import data into google drive and show the widget in Datadeck. It’s incredibly useful that now I could actually create the dashboard for almost any type of my data source, Thanks @ymc! :+1:

BTW, I followed the instruction step by step using Python 2.7, it also works!

Below is the chart I just created:


#4

#5

Yiming, this is just awesome!


#6

I guess we need to start looking at a developers portal :wink:


#7

Love this. Thanks @ymc :slight_smile: