Back to Blog

How to Automate Harvest Data Export to Google Sheets Without Any Script

The regular way to export a Clients/Projects/Tasks/Team report from Harvest is the following:

  • Select the report you need
  • Click on the Export button
  • Pick either Excel or CSV in the dropdown menu

The report will be downloaded to your PC or laptop in the chosen format. You’ll have to go through this flow every time you need to update your data. But in this blog post, you’ll learn how to automate export reports from Harvest and pull them right to Google Sheets. Is this what you’ve been looking for? Here we go then!

Integrate Harvest with Google Sheets 

The integration we offer doesn’t require any Google Sheets app script – it will exist on the Harvest API. All you’ll need to do is install Coupler.io, a Google Sheets add-on, and configure a JSON Client importer depending on the report you want. Let’s go step by step.

Install Coupler.io

Coupler.io is the Google Sheets add-on. It pulls data from different data sources, such as QuickBooks, Pipedrive, Xero, Clockify, and many more. Install Coupler.io from the G Suite Marketplace

You can find more information about the add-on and data sources it works with on the Coupler.io home page.

Set up a JSON Client importer

You’ll need a JSON Client importer to set up the Harvest Google Sheets integration. Run Coupler.io in the Add-ons menu of a spreadsheet, click +Add Importer and select JSON Client. Of the available fields, you’ll mostly need to fill in the following: 

  • Title – add the name of your Harvest to Google Sheets integration (it’s up to you). 
  • Sheet Name – type in the name of your sheet (it’s up to you).  
  • JSON URL – insert the JSON URL to import data from. The JSON URL will change depending on the report you want to import.
Harvest reportJSON URL
Clients reporthttps://api.harvestapp.com/v2/reports/time/clients
Projects reporthttps://api.harvestapp.com/v2/reports/time/projects
Tasks reporthttps://api.harvestapp.com/v2/reports/time/tasks
Team reporthttps://api.harvestapp.com/v2/reports/time/team
  • Automatic data refresh – choose the frequency to automate data import, or toggle off to disable this feature. 

Open the Additional section as well

  • HTTP Method – pick GET to pull data to a spreadsheet.
  • HTTP headers – you’ll need to insert the following headers:
Authorization: Bearer {insert-your-access-token} 
Harvest-Account-Id: {insert-your-account-id}
User-Agent: MyApp ({insert-your-email})

How to get an access token and account ID

  1. Step 1

    Follow this link https://id.getharvest.com/developers and press Create New Personal Access Token.

  2. Step 2

    Name your Personal Access Token and click Create Personal Access Token.

  3. Step 3

    In the open page, you’ll get both an access token and account ID.

Enter the access token, account ID and your email (the one you use to log in to Harvest) in the HTTP headers field. Here is how it should look: 

Authorization: Bearer 2323686.pt.K51jaKNGiV-Pk7yBzLnQ3_ZqHpJmot6idBqvSbrfTL-10d4o2K4GVEgeY6wSgbZ_G0iRbSSyRDJ73YDQbSui 
Harvest-Account-Id: 1299752
User-Agent: MyApp (email@example.com)
  • URL query string – specify the start and end dates of your requested report using this following string:
from: {start-date}
to: {end-date}
page: 1
per_page: 1000

The format for {start-date} and {end-date} is YYYYMMDD. For example, here is how it looks for the period from January 1, 2020 to December 31, 2020:

from: 20200101
to: 20201231

Harvest stores up to 1000 rows (per_page: 1000) on one page (page: 1). If your report exceeds 1000 rows, read this section to learn how to handle this. 

  • Path – use the following string to select a nested report part from the JSON response:
results

The Settings section is optional – check out the Coupler.io knowledge base to learn more about it. 

Click Add Importer, and run it to export the Harvest report to the spreadsheet.

How many rows does my Harvest time report contain? 

One Harvest to Google Sheets integration can pull one page that contains up to 1000 rows. To learn how many pages your imported report has, update the JSON Client importer:

  • Clear the Path field
  • Insert the following string in Fields:
results,per_page,total_pages

The entire configuration should look as follows:

JSON URLPick the JSON URL depending on the report you need
HTTP MethodGET
HTTP headersAuthorization: Bearer {access-token}
Harvest-Account-Id: {account-ID}
User-Agent: MyApp ({email-address})
URL query stringfrom: {start-date}
to: {end-date}
page: 1
per_page: 1000
Fieldsresults,per_page,total_pages 

Run the importer. The imported data will contain a column total_pages. It shows the number of pages Harvest placed your data in. If it’s 1, then it’s OK. 

If it’s 2 or more, then your report contains more than 1000 rows and you should split it into multiple pages.

My report is more than 1000 rows: can I pull it to a spreadsheet?

Sure you can! But since Harvest splits all your data into chunks of 1000 rows per 1 page, you’ll have to set up a separate JSON Client importer for each 1000 rows. In the URL query string, you’ll also need to specify the chunk number (page:) to import. 

For example, if you are importing a Clients report with 1345 rows, you’ll need to set up two JSON Client importers with the following configuration:

Importer #1

Title Clients report
Sheet NameClients report
JSON URLhttps://api.harvestapp.com/v2/reports/time/clients
HTTP MethodGET
HTTP headersAuthorization: Bearer {access-token}
Harvest-Account-Id: {account-ID}
User-Agent: MyApp ({email-address})
URL query stringfrom: 20200101
to: 20201231
page: 1
per_page: 1000
Pathresults

Importer #2

Title Clients report page#2
Sheet NameClients report page#2
JSON URLhttps://api.harvestapp.com/v2/reports/time/clients
HTTP MethodGET
HTTP headersAuthorization: Bearer {access-token}
Harvest-Account-Id: {account-ID}
User-Agent: MyApp ({email-address})
URL query stringfrom: 20200101
to: 20201231
page: 2
per_page: 1000
Pathresults

For 2537 rows, you’ll need three JSON Client importers, and so on. When ready, run them all, and the report will be imported into different sheets by pages.

Once you have them in the spreadsheet, create a separate sheet and merge data from both pages using this formula:

={
  filter('Clients report'!A1:F, len('Clients report'!A1:A) > 0);
  filter('Clients report page#2'!A2:F, len('Clients report page#2'!A2:A) > 0)
}

Check it out in this spreadsheet.

The formula merges A to F columns from both sheets. We used FILTER + LEN functions to exclude empty rows. The first row (columns headers) from the second page is also excluded. Read our blog post to learn more about merging columns and cells in Google Sheets.

To wrap up

At Coupler.io, you can find several ready-to-use importers, such as QuickBooks, Xero, and others. To export time data from Harvest, you need to use the JSON Client importer at this time. However, the Coupler.io team is working on new importers and we encourage you to share your opinion. Fill out this form and share which data source you need to connect to Google Sheets. Your use case can help us decide which dedicated importer will be created next. Good luck with your data!

Back to Blog

Access your data
in a simple format for free!

Start Free