Instead of downloading data in Excel from the ParseHub extension just one time, we will import the data into Google Sheets. In this example we will get the latest run of data from our events project.
Every time the project runs and scrapes the website, this Google Sheet will be uploaded with new data.
Step 1: Find your API key & Project Token
API key
The API key is located under your account settings.
1. In the ParseHub desktop app, click on "My Account" from the sidebar
2. Select "Account" from the dropdown
3. Copy and paste the API key that you find on this screen
Project Token
1. Open the project that you want to get data from using the API
2. Go under the "Settings" tab of the project
3. Copy and paste the project token from this tab
Step 2: Open Google Sheets and create IMPORTDATA function
1. Open a new Google Sheet.
2. Click on the A1 cell and type in =IMPORTDATA()
3. In the =IMPORTDATA() function create your url like the following for a regular CSV file:
=IMPORTDATA("https://www.parsehub.com/api/v2/projects/PROJECT_TOKEN/last_ready_run/data?api_key=API_KEY&format=csv")
To import a CSV Wide file, you can use the formula below:
=IMPORTDATA("https://www.parsehub.com/api/v2/projects/PROJECT_TOKEN/last_ready_run/data?api_key=API_KEY&format=csv2")
4. Replace the PROJECT_TOKEN with the actual project token from the "Settings" tab of your project.
5. Replace the API_KEY with the API key from your account.
We created this url based on the ParseHub API reference. Take a look at it for more information.
Step 3: Schedule your Project to Get Data Consistently
You can make the data in your Google Sheet automatically update. All you have to do is set a schedule in your project. ParseHub will run on that schedule and update your Google Sheet whenever it scrapes new data.
1. Go into your project
2. Click "Get Data"
3. Click "Schedule"
4. Next to the "Schedule" heading select how often you want ParseHub to run "minute, hour, day, week, month, year". Customize the time and date according to your selection on the dropdown or text box that appears.
5. Click "Save and Schedule"
That's it! ParseHub will run on a schedule for your specific project.
You also need to have your spreadsheet refresh on a schedule. This will take a script written in the Google Script editor.
In your spreadsheet, click on:
Tools > Script editor...
and enter this as a line in the function for a regular CSV:
SpreadsheetApp.getActiveSheet().getRange('A1').setValue('=importdata("https://www.parsehub.com/api/v2/projects/PROJECT TOKEN/last_ready_run/data?api_key=API KEY&format=csv")')
or this line for a CSV Wide
SpreadsheetApp.getActiveSheet().getRange('A1').setValue('=importdata("https://www.parsehub.com/api/v2/projects/PROJECT TOKEN/last_ready_run/data?api_key=API KEY&format=csv2")')
and then replacing with your project token and your API key just as before.
Save the project. I named it CalendarRefresh. Then click on:
Resources > Current project's triggers
To add a new trigger and authorize it. Set it to however frequently you would like. It could be a good idea to schedule the spreadsheet to refresh more often than the ParseHub project in case they there is an issue with coordination. Also, as mentioned in the Geckoboard tutorial, the script does fail every once and a while.
Save the trigger and go back to your spreadsheet, and it will update before your eyes when you have scheduled it to.