· 951 words · 5 minute read

Yearly target as freelancer - Harvest&Sheets

I work as a freelance designer, usually on a per-hour base. This year I want to try something new and set a target for end-of-year earnings. I wrote a little script to create a visualization of the earning goals.

As a freelancer, I track my hours using a tool called Harvest. I’m happy with it, works well enough for me.

Every now and then, I take a look around if I can find better tools, and that’s how I ran into Cushion. I appreciate their communication and openness as a ‘company built by freelancers for freelancers’, and I tried their offer for a while. Sadly, it didn’t ‘click’.

One thing that really attracted me in Cushion was the ability to set yearly goals. A minimum goal, a target and a stretch-goal.

Harvest doesn’t have it, so I built it myself.

Stretch goals visualized in Google Sheets #

Harvest has a really simple-to-use API which works great. I implemented it with my existing Google Workspace tools, and now I have a straightforward dashboard that shows me if I’m on track for my yearly target.

Graph showing my actual accumulated income versus the targets I set for myself
Here you see the visualization in Google Sheets of my actual accumulated income versus my targets. I’ve cutoff the labels so no actual amounts are shown. As you can see, I’m currently behind on reaching even my minimum goal!

How does it work? #

I attached a small piece of javascript to the Google Sheet using Apps Script. It pulls in the latest data from Harvest, accumulates it, and then extrapolates. It then injects the data back into Google Sheets.

Can I use it also? #

You can find the script below. Let me know if you used it, and if you made any improvements!

Read through the code below before going through the steps. Never blindly copy-paste code from the web 😅

In Harvest #

  1. sign into Harvest
  2. go to developers section
  3. create a new ‘personal access token’

In Google Sheets #

  1. create a new Google Sheets document
  2. add a sheet and name it Data
  3. rename the existing sheet Visualization
  4. click ’extensions’ and ‘Apps Script’
  5. this opens a new page in ‘Apps Script’ with an empty editor

In Apps Script #

  1. copy and paste code below into Apps Script
  2. accept the scary terms from Google (“this can read/modify/delete every Sheet in your account”)
  3. run the code and check the output in your Google Sheets
  4. now you have the data, you can create any graphs you like

The code #

As always, it’s MIT-licensed. Have fun.

function fetchHarvestData() {
  const HARVEST_ACCOUNT_ID = 'XXX';
  const TOKEN = 'XXX';
  const START_DATE = '2025-01-01'; // Start of the year
  const END_DATE = new Date().toISOString().slice(0, 10); // Today
  
  // Goal amounts (in Euro)
  const MIN_GOAL = 10000;  // Minimum goal (example: €10,000)
  const TARGET_GOAL = 15000;  // Target goal (example: €15,000)
  const STRETCH_GOAL = 20000;  // Stretch goal (example: €20,000)

// Calculate the total workdays in the entire year (excluding weekends)
  const totalWorkdays = countWorkdays(START_DATE, '2025-12-31'); // Use the whole year range

  // Calculate the daily goal for each goal type
  const minGoalPerDay = MIN_GOAL / totalWorkdays;
  const targetGoalPerDay = TARGET_GOAL / totalWorkdays;
  const stretchGoalPerDay = STRETCH_GOAL / totalWorkdays;

  const url = `https://api.harvestapp.com/v2/time_entries?from=${START_DATE}&to=${END_DATE}`;
  const options = {
    method: 'get',
    headers: {
      'Authorization': `Bearer ${TOKEN}`,
      'Harvest-Account-Id': HARVEST_ACCOUNT_ID
    }
  };

  const response = UrlFetchApp.fetch(url, options);
  const data = JSON.parse(response.getContentText());
  
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
  sheet.clear();
  sheet.appendRow(['Date', 'Billable Amount', 'Accumulated Amount', 'Accumulated Min Goal', 'Accumulated Target Goal', 'Accumulated Stretch Goal', 'Projected Income']);

  const entries = data.time_entries;
  const dailyTotals = {};
  
  // Calculate daily totals for billable amounts
  entries.forEach(entry => {
    const date = entry.spent_date;
    const hours = entry.hours || 0;
    const rate = entry.billable ? (entry.billable_rate || 0) : 0;
    const amount = hours * rate;

    if (!dailyTotals[date]) {
      dailyTotals[date] = 0;
    }
    dailyTotals[date] += amount;
  });

  // Create an array of all dates in the range (for the whole year)
  const allDates = generateDateRange(START_DATE, '2025-12-31'); // Whole year range

  // Initialize accumulated totals
  let accumulatedAmount = 0;
  let accumulatedMinGoal = 0;
  let accumulatedTargetGoal = 0;
  let accumulatedStretchGoal = 0;
  
  let totalBillableAmount = 0;
  let totalWorkdaysCompleted = 0;

  let averageIncomePerWorkday = 0;
  let projectedIncomeAccumulation = 0;
  let rows = [];

  // Calculate the average income per workday for completed workdays
  allDates.forEach(date => {
    const billableAmount = dailyTotals[date] || 0;

    // Update accumulated amounts only for workdays (not weekends)
    const dayOfWeek = new Date(date).getDay();
    const isWorkday = dayOfWeek !== 0 && dayOfWeek !== 6; // 0 = Sunday, 6 = Saturday
    
    if (isWorkday) {
      accumulatedAmount += billableAmount;
      accumulatedMinGoal += minGoalPerDay;
      accumulatedTargetGoal += targetGoalPerDay;
      accumulatedStretchGoal += stretchGoalPerDay;

      totalBillableAmount += billableAmount;
      totalWorkdaysCompleted++;
    }

    // Calculate projected income
    const isFutureDate = new Date(date) > new Date(END_DATE);
    if (isFutureDate) {
      projectedIncomeAccumulation = accumulatedAmount + averageIncomePerWorkday * countWorkdays(END_DATE, date);
    } else {
      projectedIncomeAccumulation = accumulatedAmount;
      // Update the average income per workday
      averageIncomePerWorkday = totalWorkdaysCompleted > 0 ? totalBillableAmount / totalWorkdaysCompleted : 0;
    }

    // Add the row to the array
    rows.push([
      date,
      billableAmount,
      accumulatedAmount,
      accumulatedMinGoal,
      accumulatedTargetGoal,
      accumulatedStretchGoal,
      projectedIncomeAccumulation
    ]);
  });

  // Write all rows in bulk to the sheet
  sheet.getRange(2, 1, rows.length, rows[0].length).setValues(rows);
}

// Helper function to count workdays between two dates
function countWorkdays(startDate, endDate) {
  const start = new Date(startDate);
  const end = new Date(endDate);
  let count = 0;

  while (start <= end) {
    const dayOfWeek = start.getDay();
    if (dayOfWeek !== 0 && dayOfWeek !== 6) {  // Skip weekends (0 = Sunday, 6 = Saturday)
      count++;
    }
    start.setDate(start.getDate() + 1);
  }

  return count;
}

// Helper function to generate all dates between two dates
function generateDateRange(startDate, endDate) {
  const start = new Date(startDate);
  const end = new Date(endDate);
  const dates = [];

  while (start <= end) {
    const dateStr = start.toISOString().split('T')[0]; // YYYY-MM-DD format
    dates.push(dateStr);
    start.setDate(start.getDate() + 1);
  }

  return dates;
}