How to Integrate Retool with Harvest

Log into your Harvest account. Navigate to your profile settings by clicking your name in the top-left corner, then selecting 'My Profile'. Scroll to the bottom of the profile page to find the 'Developers' section. Click 'Create New Personal Access Token'. Give the token a descriptive name like 'Retool Integration' and click 'Create Personal Access Token'. Harvest displays the token value once — copy it immediately. You also need your Harvest Account ID: this is displayed in the same Developers section as 'Account ID', typically a 6-7 digit number. Copy both values. Unlike some API integrations, Harvest personal access tokens do not expire — they remain valid until you revoke them in this same settings page. Personal access tokens have the same access level as your Harvest account, so use a dedicated account or be aware that all requests will be attributed to you.

In Retool, go to Resources → Create New → REST API. Set the Base URL to 'https://api.harvestapp.com/v2'. This is Harvest's API v2 base endpoint. Under the Headers section, add three required headers: first, 'Authorization' with value 'Bearer YOUR_TOKEN' (use {{ retoolContext.configVars.HARVEST_TOKEN }} once your config variable is set), second 'Harvest-Account-Id' with your account ID number ({{ retoolContext.configVars.HARVEST_ACCOUNT_ID }}), and third 'User-Agent' with a descriptive value like 'Retool Integration (yourteam@company.com)'. The User-Agent header is required by Harvest's API — requests without it may be rejected. Set up configuration variables in Retool Settings → Configuration Variables before saving the resource, marking the token as secret. Click 'Save Resource'. The test connection should return a 200 OK response.

1{
2  "Base URL": "https://api.harvestapp.com/v2",
3  "Headers": {
4    "Authorization": "Bearer {{ retoolContext.configVars.HARVEST_TOKEN }}",
5    "Harvest-Account-Id": "{{ retoolContext.configVars.HARVEST_ACCOUNT_ID }}",
6    "User-Agent": "Retool-Harvest-Integration (your-email@company.com)",
7    "Content-Type": "application/json"
8  }
9}

Create a new query using the Harvest resource. Set method to GET and path to '/time_entries'. Harvest's time entries endpoint accepts query parameters for filtering: 'from' and 'to' accept date strings in YYYY-MM-DD format for date range filtering, 'user_id' filters by a specific team member, 'project_id' filters by project, 'billable' accepts true or false to filter billable/non-billable entries, and 'per_page' sets page size up to 100. Add a DateRangePicker component to the Retool app and bind 'from' to its start value and 'to' to its end value. The response contains a 'time_entries' array and 'pagination' object with total_count and next_page for pagination. Each time entry includes hours, notes, the associated project (as a nested object), task, and user. Run the query to verify the data structure.

1// GET /time_entries
2// Query parameters:
3// from: {{ dateRange.start || new Date(new Date().getFullYear(), new Date().getMonth(), 1).toISOString().split('T')[0] }}
4// to: {{ dateRange.end || new Date().toISOString().split('T')[0] }}
5// user_id: {{ userFilter.value || '' }}
6// project_id: {{ projectFilter.value || '' }}
7// billable: {{ billableFilter.value || '' }}
8// per_page: 100
9// page: {{ pagination.page || 1 }}

Add a transformer to the time entries query to aggregate hours by user and calculate utilization metrics. Group entries by user ID and sum up billable and non-billable hours separately. Calculate the billable percentage for each user. This produces a summary array that can drive both a summary Table and a Chart component. Add the transformer to the query's Advanced tab. Create two views in your Retool app: a detail view showing the raw time entry table, and a summary view showing the per-user aggregation. Add a Bar Chart component bound to the summary transformer output, with the x-axis as user names and bars for billable vs non-billable hours. Add a target line at 80% billable rate using a custom reference line in the Chart settings.

1// Transformer: aggregate time entries by user
2const entries = data.time_entries || [];
3const userMap = {};
4
5entries.forEach(entry => {
6  const userId = entry.user.id;
7  const userName = entry.user.name;
8
9  if (!userMap[userId]) {
10    userMap[userId] = {
11      user_id: userId,
12      user_name: userName,
13      total_hours: 0,
14      billable_hours: 0,
15      non_billable_hours: 0
16    };
17  }
18
19  userMap[userId].total_hours += entry.hours;
20  if (entry.billable) {
21    userMap[userId].billable_hours += entry.hours;
22  } else {
23    userMap[userId].non_billable_hours += entry.hours;
24  }
25});
26
27return Object.values(userMap).map(user => ({
28  ...user,
29  total_hours: Math.round(user.total_hours * 100) / 100,
30  billable_hours: Math.round(user.billable_hours * 100) / 100,
31  non_billable_hours: Math.round(user.non_billable_hours * 100) / 100,
32  billable_pct: user.total_hours > 0
33    ? `${Math.round((user.billable_hours / user.total_hours) * 100)}%`
34    : '0%'
35})).sort((a, b) => b.billable_hours - a.billable_hours);

Create two additional queries for the finance and project management views. For invoices, use GET method with path '/invoices' and filter parameters: 'state' accepts 'open', 'partial', 'draft', 'paid', 'unpaid', 'overdue'. The invoice response includes client name, amount, due_on date, and amount_due fields. Add a transformer that calculates days_overdue for open invoices: Math.max(0, Math.floor((Date.now() - new Date(invoice.due_on)) / 86400000)). For the projects view, use GET '/projects' which returns project names, budgets, budget_by (hours vs money), and fee amounts. Combine project data with time entry data using a JavaScript query that joins on project ID to show hours logged vs budget remaining per project.

1// GET /invoices
2// Query parameters:
3// state: {{ invoiceStateFilter.value || 'open' }}
4// per_page: 100
5
6// Transformer: format invoice data
7const invoices = data.invoices || [];
8return invoices.map(inv => ({
9  id: inv.id,
10  invoice_number: inv.number,
11  client: inv.client.name,
12  amount: `$${parseFloat(inv.amount).toFixed(2)}`,
13  amount_due: `$${parseFloat(inv.due_amount).toFixed(2)}`,
14  issue_date: inv.issue_date,
15  due_date: inv.due_on,
16  status: inv.state,
17  days_overdue: inv.state === 'open' && inv.due_on < new Date().toISOString().split('T')[0]
18    ? Math.floor((Date.now() - new Date(inv.due_on)) / 86400000)
19    : 0
20})).sort((a, b) => b.days_overdue - a.days_overdue);