API Connector Documentation

Print

Import Harvest Data to Google Sheets

Last Updated OnMarch 19, 2024
byAna
< Knowledge Base

In this guide, we’ll walk through how to pull data from the Harvest API directly into Google Sheets, using the API Connector add-on for Sheets.

There are 2 ways to connect to the Harvest API:

  • Preset "Connect" button (OAuth)
  • Personal access token. Please check the appendix for detailed instructions to retrieve your token.

Contents

Before You Begin

Click here to install the API Connector add-on from the Google Marketplace.

Part 1: Connect to the Harvest API

The easiest way to get started with the Harvest API is through API Connector’s built-in integration.

  1. In Sheets, open API Connector and create a new request (Extensions > API Connector > Open > Create request)
  2. Select Harvest from the drop-down list of applications
    harvest-application
  3. Under Authorization, click Connect to Harvest
    harvest-authorization
  4. You will see a modal asking you to approve the connection. Click Authorize App.
    harvest-img2
  5. You’ll then be returned to your Google Sheet, and can verify that your Harvest connection is active.

Part 2: Get Your Harvest Account ID

Harvest requires that you pass your account ID into a header when making requests to the API, so we need to take a quick detour here to find out our account ID. In API Connector, create a custom request and enter the following URL:

https://id.getharvest.com/api/v2/accounts

Under Authentication, choose Harvest. Choose an output sheet and click Run and you should see your account ID(s) returned into a sheet, under the field name accounts.id. Mark this ID down as we'll need it for all our other requests.

Part 3: Pull Data from Harvest to Sheets

Now that we have our ID, let’s pull some real data into Sheets.

  1. Toggle back to the Harvest connection, and choose the /time_entries endpoint.
    harvest-endpoints
  2. Enter your Harvest account ID, and select any optional parameters by which you'd like to filter the response.
  3. Set a destination sheet, name your request, and hit Run.
    harvest-response

Part 4: Fetch and Append New Data

Rather than retrieving the entire data set each time you run your request, you can set your request to fetch new data only. There are a few approaches you could take; here’s one:

  1. Run an initial request to the endpoint you want, e.g. /time_entries, setting the from and to parameters to fetch the initial set of historical data. Let's call this sheet TimeEntries.
  2. Create a new sheet called MaxDate and find the maximum (i.e. latest) date entry from your initial pull with a function like =max(TimeEntries!$B:$B).
  3. If the result is a number like 44928 instead of a date, first select the cell and click Format > Number > Date.
  4. Switch your request to Append mode and reference that new date cell like this:  +++MaxDate!A2+++
    harvest-maxdate
  5. This will ensure that each request only retrieves data from after the last date in your sheet, and append mode will add each new pull to the end of your existing dataset.
  6. Set your request to run on a schedule. You won’t need to update your request again.

Part 5: Create a Custom Request

Alternatively, you can run a custom request instead of using API Connector’s built-in integration, using any of the endpoints and parameters shown in the API documentation. To create a custom request, enter the full API URL, select Harvest from the OAuth menu (or connect with a token), and enter a Header of Key = Harvest-Account-Id, Value = your_account_id.

Here's a sample request setup:

  • Application: Custom
  • Method: GET
  • Request URL: https://api.harvestapp.com/v2/time_entries?page=1
  • OAuth: Harvest
  • Headers:
    • Harvest-Account-Id: your_account_id


harvest-results1

Part 6: Handle Pagination

Harvest limits the number of records returned in each request. By default, 2000 records will be returned unless you use the 'page' and 'per_page' parameters as described in their documentation.
harvest-pagination

With API Connector you can either run these request URLs manually or loop through them automatically with pagination handling. Harvest provides a "next page URL" so we'll enter it like this:

  • Pagination type: next page URL
  • Next page path: links.next
  • Run until: choose when to stop fetching data
    pagination-nextpageurl

Part 7: API Documentation

Official API documentation: https://help.getharvest.com/api-v2/

Appendix: Connect with a Personal Access Token

This section is provided as an alternative to the method described above. Instead of clicking Harvest in the Connections manager, you will retrieve your personal access token yourself. Once you have it, enter it into the Headers section. Since you're manually including an API token, leave OAuth2 authentication set to None.
  1. Sign into Harvest and navigate to their Developer Tools page (https://id.getharvest.com/developers)
  2. Click "Create New Personal Access Token"
    harvest-img10
  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-img11
  4. You should now see a page containing your API token and account ID. Congrats! You're now ready to access the Harvest API. Keep these details handy as we'll need them in a moment.
    harvest-img12
  5. Now set up your request, substituting in your own account ID where it says your_account_id, your own token where it says your_token, and your own token name and email where it says your_token_name (email). You can also substitute in any request URL from the documentation:
    • Application: Custom
    • Method: GET
    • Request URL: https://api.harvestapp.com/v2/time_entries?page=1
    • Headers:
      • Harvest-Account-Id: your_account_id
      • Authorization: Bearer your_token
      • User-Agent: your_token_name (email)

harvest-img13

26 thoughts on “Import Harvest Data to Google Sheets”

  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

    Reply

    • 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

      Reply

  2. 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?

    Reply

    • Hi Shawn, the string "Bearer" is required by Harvest. 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.

      Reply

  3. 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 ?

    Reply

    • 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”)

      Reply

  4. Hi.
    I can't use a token at harvest because my client prefer the authentication by using email&password.

    client = harvest.Harvest(f"https://{account}.harvestapp.com", email, password)
    client.who_am_i

    My client wants to make an arbitrary query to the harvest API to get a report on how the user spent their time.
    But in that case, I don't find any solution in harvest API v2 documentation.
    Please help me with this problem.

    Kind regards.
    Mark.

    Reply

    • Sorry, I don't think I can help you here, since as you found in the documentation, Harvest V2 API only provides 2 methods of authentication, and neither involves email & password. However you can check the V1 version of their API. It uses Basic Authentication, which basically means encoding your email address and password to base64 and adding it to a header. Maybe that works for you? (V1 documentation).

      Reply

  5. Hello,

    I created a new tab/sheet and when the sheet is selected I click 'Set' and I get

    ScriptError: Authorization is required to perform that action.

    Do you know what I need to fix here? I have written out the three headers according to your format. I don't see any issues there or with the API URL.

    Any ideas? I appreciate your help.

    Reply

      • Hello Ana, Thank you for the prompt reply. What I'm trying to do is get the Time Entries with the Tasks name "Admin". Did try to add the parameters after the URL name=Admin and still it doesn't work. Can youp please help?

      • If you're looking to filter the time_entries endpoint, you need to use the task_id parameter, like this: https://api.harvestapp.com/v2/time_entries?task_id=YOUR_TASK_ID, where you substitute in your own task ID. If you don't know your task ID, you can find it by querying the endpoint https://api.harvestapp.com/v2/tasks/.

  7. Any idea how to get a custom column to stay when the API runs? For example, Harvest has a date column but in order to keep my pivots flowing I need a month column. I made one off to the side but it disappears whenever the API runs. Thoughts?

    Reply

    • Hi John, I suggest using a Sheets function like QUERY() to pull all your data into a second sheet, and then creating any custom columns and calculations in that second sheet. Alternately you can use JMESPath filtering, which allows you to fix your columns in place from the start.
      Update: you can now use the visual field editor to fix fields in place. You could also choose a starting column for your column and put any calculations on the left side where they won't get overwritten.

      Reply

  9. Hi Ana,

    I've duplicated last year's report to a new sheet for 2022, and I've including the same setup I had for the API connector with Harvest.

    When I run the report, I can only see times logged in the past two days rather than the whole month.
    https://api.harvestapp.com/v2/time_entries?from=2022-01-01&to=2022-01-31

    Any ideas on how to resolve this?

    Reply

  10. Hi Ana, I am trying to get active projects returned for certain clients using multiple filters but it is not working:
    https://api.harvestapp.com/v2/projects?is_active=true&client_id=8208457&client_id=11692539&client_id=10828140&client_id=4089177

    What am I doing wrong?
    Thanks,
    LC

    Reply

    • My reading of their documentation is that you can only include a single client ID (it says client_id: Only return projects belonging to the client with the given ID). If you'd like to get multiple client IDs, I'd set up a multi-query request. Just list out your URLs one after the other, like this, and API Connector will run through them from top to bottom:
      https://api.harvestapp.com/v2/projects?is_active=true&client_id=8208457
      https://api.harvestapp.com/v2/projects?is_active=true&client_id=11692539
      https://api.harvestapp.com/v2/projects?is_active=true&client_id=10828140
      https://api.harvestapp.com/v2/projects?is_active=true&client_id=4089177

      Reply

Leave a Comment

Jump To