Import data into Google Sheets

Instead of down­load­ing 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.

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 PRO­JEC­T_­TO­KEN 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 in­for­ma­tion.

Step 3: Schedule your Project to Get Data by the Hour

You can make the data in your Google sheet automatically update. All you have to do is set a schedule for 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 ParseHub to run on a schedule. 

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 Apps Script Editor.

In your spreadsheet within the top menu bar, click on:

Extensions > Apps Script 

This will open up a new tab or window on your browser and bring you to the Apps Script Editor dashboard.

You will need to connect the script to your current Google sheet. To do this, you can click on the Services element located on the menu on the left-hand side under Libraries. A pop-up will appear and within the scrollable options choose Google Sheets API. Click on Add.

You should be prompted to allow permissions for Apps Script to access your Google sheet to provide authorization.  

You should see that the service is added in the left menu pane: 

Once this is complete, you can now add your script to the editor.

Enter this as a line in the function for a regular CSV:

or this line for a CSV Wide

and then replacing with your project token and your API key just as before.

Save the project and rename it. Then click on the trigger icon in the left menu pane:

To add a new trigger and authorize it, click the blue "Add Trigger" button in the bottom right-hand corner. 

Update the settings as shown in the screenshot above. You may change the frequency in the timer drop-down menu.  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 run and update before your eyes when you have scheduled it to.

Please contact support at hello@parsehub.com if you have any questions about importing your data using Google Sheets API.