Import Product Hunt Data to Google Sheets

Product Hunt is a popular site for sharing new sites and products. In this guide, we’ll walk through how to pull data from the Product Hunt API directly into Google Sheets, using the API Connector add-on for Sheets. We’ll first get an API key from Product Hunt, and then set up a request to pull in the most popular products from Product Hunt to your spreadsheet. This article provides examples for the v1 REST API as well as the v2 GraphQL API.

STEP 1: GET YOUR PRODUCT HUNT API KEY

  1. Log in to Product Hunt and click your profile pic to open the Account drop-down menu. Click ‘API Dashboard’
  2. This page will prompt you to create a new application. Enter a name and a URL, and click Create Application (for our purposes, the exact values don’t matter)
    api-connector-producthunt-img1
  3. You’ll now see a page listing some API tokens. Scroll to the bottom and click Create token to create a token that never expires.
    api-connector-producthunt-img2
  4. Once you click ‘Create Token’, you’ll see a Token. Copy this as we’ll need it shortly. Congrats, you’re now ready to get started pulling data out of Product Hunt’s API.
    api-connector-producthunt-img3

STEP 2: CREATE YOUR API REQUEST URL

We’re going to follow the Product Hunt API documentation to view the most upvoted posts in a month.

  • Base URL:https://api.producthunt.com/v1/
  • Endpoint: /posts/all
  • Query strings: ?sort_by=votes_count&order=desc&search[featured_month]={month}&search[featured_year]={year}
    Query strings example: ?sort_by=votes_count&order=desc&search[featured_month]=5&search[featured_year]=2019

Putting it all together, we get the full API Request URL:

https://api.producthunt.com/v1/posts/all?sort_by=votes_count&order=desc&search[featured_month]=5&search[featured_year]=2019

STEP 3: PULL PRODUCT HUNT API DATA INTO SHEETS

We can now enter all our values into API Connector to start importing Product Hunt data into Google Sheets.

  1. Open up Google Sheets and click Add-ons > API Connector > Create New API Request.
  2. In the Create Request interface, enter the Request URL we just created
    api-connector-producthunt-img4
  3. Under Headers, enter a key-value pair like this, where Key = Authorization, and Value = Bearer {Token}:
    AuthorizationBearer {Token}

    {Token} is the value provided above in step 4.
    api-connector-producthunt-img5

  4. Create a new tab. You can call it whatever you like, but here we’ll call it ‘PH Upvoted’. While still in that tab, click ‘Set’ to use that tab as your data destination.
  5. Name your request. Again we’ll call it ‘PH Upvoted’
  6. Click Run and a moment later you’ll see most upvoted Product Hunt posts from May 2019 populate the PH Upvoted tab in your Google Sheet:
    api-connector-producthunt-img6

STEP 4: VARIATION – USE GRAPHQL

Product Hunt’s v2 API uses the GraphQL query language to pull API data. Unlike the v1 Rest API, this means you can pull all your API data in a single request. You can check the documentation for information on what fields are available, but the below steps provide a full example of a working query.

    1. To use GraphQL with the Product Hunt API, create your GraphQL query. It should look something like this:
      
      {
                  posts {
                    edges {
                      node {
                        id
                        name
                        description 
                        votesCount
                        website
                        thumbnail {
                          url
                        }
                        media {
                          videoUrl
                          type
                          url
                        }
                      }
                    }
                  }
                }
      
    2. Enter that entire query into a single cell, like this:
      api-connector-producthunt-img7
    3. Encode the entire cell by entering =encodeURL(A1) into cell A3.
    4. When you work with GraphQL APIs, you add your encoded GraphQL request to a “query” query string (documentation). So, assuming your sheet is called GraphQL, your entire request URL will look like this:
      https://api.producthunt.com/v2/api/graphql?query=+++GraphQL!A3+++

Choose POST method and enter this URL into API Connector:
api-connector-producthunt-img8

  1. Under Headers, enter a key-value pair like this, where Key = Authorization, and Value = Bearer {Token}:
    AuthorizationBearer {Token}

    api-connector-producthunt-img9

  2. We can’t have an empty POST body, so just enter {} into the POST body section.
  3. Create a new tab called PH GraphQL. While still in that tab, click ‘Set’ to use that tab as your data destination.
  4. Name your request. Again we’ll call it ‘PH GraphQL’
  5. Click Run and a moment later you’ll see the results of your GraphQL query populate your sheet.
    api-connector-producthunt-img10

Leave a Comment