Import Harvest Data to Google Sheets

Import Harvest Data to Google Sheets

Harvest is a popular time tracking and invoicing tool that provides numerous integrations, but no official integration for Harvest and Google Sheets. In this guide, we’ll walk through how to pull data from the Harvest API directly into Google Sheets, using the free API Connector add-on. We’ll first get a personal access token from Harvest, and then set up a request to pull in data from Harvest to your spreadsheet.

PART 1: GET YOUR HARVEST PERSONAL ACCESS TOKEN

  1. Sign into Harvest and navigate to their Developer Tools page (https://id.getharvest.com/developers)
  2. Click “Create New Personal Access Token”
    harvest-img1
  3. Give it a name and click “Create Personal Access Token”. You can name it anything, but we’ll call it ‘Google Sheets’ here.
    harvest-img2
  4. You should now see a page containing all your API access details. Congrats! You’re now ready to access the Harvest API. Keep these details handy as we’ll need them in a moment.
    harvest-img3



PART 2: CREATE YOUR API REQUEST URL


We’ll follow the Harvest API documentation to get time entries.

  • API root: https://api.harvestapp.com/v2
  • Endpoint: /time_entries
  • Parameters: ?page={page number}
    Parameters Example: ?page=1

The ‘page’ parameter is optional but we’ll include it to show how parameters work. Putting it all together, we get the full API Request URL:
https://api.harvestapp.com/v2/time_entries?page=1

PART 3: ENTER VALUES INTO API CONNECTOR


We can now enter all our values into API Connector and start importing Harvest 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:
    harvest-img4
  3. Under Headers, enter key-value pairs like this:
    Harvest-Account-Id {Account ID}, e.g. 812345
    Authorization Bearer {Your Token}, e.g. Bearer 1533312.pt.41xldm_pVgn_d1zo_HvXVUeN0V_LqycGwmiSh8Fkxoautf6SRuPCRNIQP2c-ReFN8xduysKmDt1TltJw_mKwDg
    User-Agent {Token Name} ({Email Address}), e.g. Google Sheets (example@mixedanalytics.com)

    Of course you’ll need to substitute in your own values using the values from your API access details page and your own email address for {Email Address}. (The token in this example has already been deleted). The whole thing should look something like this:

    harvest-img5

  4. Create a new tab. You can call it whatever you like, but here we’ll call it ‘Time Entries p1’. While still in that tab, click ‘Set’ to use that tab as your data destination.
  5. Name your request. Again we’ll call it ‘Time Entries p1’
  6. Click Run and a moment later you’ll see Harvest data populate your Google Sheet:
    harvest-img6

PART 4: NOTES AND EXPANSIONS

  1. You can check the documentation for the full list of available API requests,  but if you just want to jump in, you can play around with the URLs you enter in the API URL path field. Try the following (one at a time):
    https://api.harvestapp.com/v2/time_entries
    https://api.harvestapp.com/v2/clients
    https://api.harvestapp.com/v2/invoices
    https://api.harvestapp.com/v2/tasks
    https://api.harvestapp.com/v2/expenses
    https://api.harvestapp.com/v2/projects
  2. Note that Harvest limits the number of records returned in each request. By default, only 100 records will be returned unless you use the ‘page’ parameter as described in their documentation.

    harvest-sheets-img5

    Each page contains 100 records. To access more than 100 records, edit the “page” parameter to your URL path, like this:
    page 1: https://api.harvestapp.com/v2/time_entries?page=1
    page 2: https://api.harvestapp.com/v2/time_entries?page=2

    With API Connector, you can either set these up as separate requests or run them simultaneously (paid feature) for added efficiency.

  3. You may also want to limit your pulls for certain client IDs. You can combine parameters like this:
    https://api.harvestapp.com/v2/time_entries?client_id=1234567&page=1
    (you can find your client ID in the “clients” table).

Following the above steps, you will now be able to import data from the Harvest API directly into Google Sheets. By setting Google Sheets as a data source, you can also use this method to create Harvest data visualizations and dashboards in Google Data Studio.

IMPORTANT SECURITY NOTE

Anyone with Owner or Edit access to your Google Sheet can view all the information you've saved within API Connector, including API keys and other credentials. Treat these keys as passwords and limit access to your sheet accordingly.

Comments:10

  1. Great help, pulls data in! Is it possible to set a data range of the data pulled in however, i’m unclear how much data this is pulling

  2. Most of Harvest’s API responses sort by recency (e.g. the ‘spent_date’ or ‘created_at’ fields) and are limited to 100 records. You can see the specific default sort field in the documentation, but generally this means that if you run a Harvest API query without any parameters, it will return most recent 100 records. If you wanted to get data for, say, February, you’d add parameters like https://api.harvestapp.com/v2/time_entries?from=2019-02-01&to=2019-02-28

  3. I’m getting a “Failed to run request” error when coping the above (but with my token info).
    Can you explain what “Bearer” in the Authorization key is for?

    1. Hi Shawn, the string “Bearer” is required by Harvest, you can see this in the screenshot under step #4 (under “Testing your Token”). More generally, “Bearer” authentication is an HTTP authentication scheme.
      If you’re still having trouble feel free to email over a screenshot and I’ll take a look.

    1. Hey Zee, I followed up with Shawn and he hadn’t entered the headers exactly as written. It worked once he edited it to follow the screenshot and instructions. Can you please send over a screenshot of your create request screen so I can check it?

    1. The user agent actually doesn’t really matter, Harvest just wants a way to contact you if necessary. So you could use either email and it wouldn’t make a difference.

      Your headers look correct, though there’s some weird artifact visible next to Harvest-Account-Id. So please verify that a) there are no spaces or other extra characters around the URL or headers, and b) that your Harvest account ID & authorization value are correct.

      You can also troubleshoot by trying your request in a different online API tool like https://inspector.swagger.io/builder. Then, if it still doesn’t work, you’ll know the issue is that your header values are incorrect, and if it does work, you can just copy and paste your setup from there into API Connector.

  4. Hello my data got pulled in, but it didn’t pull in Header information to the Gsheet, and some fields that are text , are showing as numbers. Can you help me ?

    1. Can you please click the ‘Output Options’ button and check what mode you’re in? Overwrite mode will print out headers, while Append mode won’t (info).
      As for fields showing up as numbers instead of text, formats are automatically assigned by Google based on its best guess of what the data is. If it has guessed incorrectly, please try changing the format using Sheets built-in format functions. You can also create a new sheet where you assign the correct data formats to each column, and then populate those columns by querying your API data sheet (e.g. =QUERY(Harvest!A:Z, “select A, B,C”)

Leave a Reply

Your email address will not be published.