Search API Connector Documentation

Print

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 API Connector add-on for Sheets.

CONTENTS

The Harvest API requires authentication for access, through either your own API token or an OAuth2 integration. This article primarily discusses connecting through API Connector’s paid OAuth2 Connection Manager, which handles authentication for you. If you’d prefer to manage the process manually for free, please skip to the Appendix for step by step instructions on connecting with an API token.

BEFORE YOU BEGIN

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

PART 1: CONNECT TO THE HARVEST API

If you haven’t connected to the Harvest API before, you’ll first need to initiate the connection.

  1. Open up Google Sheets and click Add-ons > API Connector > Manage Connections.
  2. In the list of available connections, find Harvest and click Connect.
    harvest-img1
  3. You will see a modal asking you to approve the connection. Click Authorize App.
    harvest-img2
  4. You’ll then be returned to your Google Sheet, and can verify that your GitHub connection is active in the Connections screen.
    harvest-img3

PART 2: GET YOUR HARVEST ACCOUNT ID

Harvest requires that you pass your account ID into a header when making requests to the API. This is actually pretty useful if you’re working with multiple Harvest accounts, since it enables you to differentiate between them.

Therefore, as a precursor to getting data, let’s first find out our account ID. In API Connector, open up the Create tab 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 shortly.

PART 3: CREATE YOUR HARVEST API REQUEST URL

OK, now let’s get some real data. We’ll start with 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 4: PULL HARVEST API DATA INTO SHEETS

Let’s get that Harvest time entry data into Google Sheets.

  1. Open up Google Sheets and click Add-ons > API Connector > Open.
  2. In the Create screen, enter the Request URL we just created:
    harvest-img4
  3. Under Headers, enter your account ID from above as a Header, like this:
    Harvest-Account-IdYOUR_ACCOUNT_ID
    harvest-img5
  4. Choose “Harvest” from the drop-down authentication menu.
    harvest-img6
  5. Create a new tab and click ‘Set current’ to use that tab as your data destination.
  6. Name your request and click Run. A moment later you’ll see Harvest data populate your Google Sheet:
    harvest-img7

PART 5: MORE EXAMPLE API URLS

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

PART 6: HANDLE FILTERING

You may want to limit your pulls for certain client IDs or time ranges. 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).

PART 7: HANDLE PAGINATION

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.

Each page contains 100 records. To access more than 100 records, edit the “page” parameter to your URL path, like this:

https://api.harvestapp.com/v2/time_entries?page=1
https://api.harvestapp.com/v2/time_entries?page=2

With API Connector you can either run these request URLs manually or loop through them automatically with pagination handling (paid feature). Harvest provides a “next page URL” so we’ll use that type.

  • API URL: enter your request URL as usual
  • Pagination type: next page URL
  • Field name: links.next
  • Number of pages: enter the number of pages you’d like to fetch
    harvest-img9

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 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-img12
  5. Enter these values into the Headers section, like this. You’ll also need to include your email address for the User-Agent key. The whole thing should look something like this:
    harvest-img13
Harvest-Account-IdYOUR_ACCOUNT_ID
AuthorizationBearer YOUR_TOKEN
User-AgentYOUR_TOKEN_NAME (YOUR_EMAIL)

18 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
      • Hi Ana,
        is it planned to expand the record limit in the near future? with only 100 records we’re very limited in all requests we do. 🙁

      • I’m not sure if I totally understand your question. The 100 record per page limit comes from Harvest, not API Connector, and the article explains two different methods of getting more than 100 records by requesting additional pages.

  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/.

  6. 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, you can’t include custom columns in the destination sheet since all data will be overwritten each time the request runs. 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.

      Reply

Leave a Reply to Shawn Mckenzie Cancel reply