Build on FetchTasksFromClickup to pull the time tracking data into a timesheet view.

FetchTimesheetEntriesFromClickUp.gs

/*
 * FetchTimesheetEntriesFromClickUp
 *
 * Fetch time tracking entries using the ClickUp API and return the data in calendar format as a two-dimensional array.
 * This can be used in a Google Apps Script to pull time tracking data into a Google Sheet, to suit entry into Harvest.
 * 
 * Author:  Heath Raftery
 * License: This work is licensed under the Creative Commons Attribution-ShareAlike 4.0 International License.
 *          To view a copy of this license, visit http://creativecommons.org/licenses/by-sa/4.0/ or send a
 *          letter to Creative Commons, PO Box 1866, Mountain View, CA 94042, USA.
 */

/**
 * Use the ClickUp API to fetch a matrix of timesheet entries from the `list` specified,
 * where each element is the total time spent for each day, starting from `start_day` up
 * until today, grouped by `row_names` according to the row number in the task's `row_field`.
 *
 * @param {"pk_12345678_FEEDFACEFEEDFACEFEEDFACEFEEDFACE"} token Your personal API token: https://clickup.com/api/developer-portal/authentication#generate-your-personal-api-token
 * @param {123456789} team The team identifier, which can be found by navigating to the list in ClickUp and looking for the 9 digit number in the URL, after "app.clickup.com/".
 * @param {123456789012} list The list identifier, which can now be found by navigating to the list in ClickUp, clicking the three dots next to the list name, clicking "Copy URL", and then looking in the clipboard for the number at the end of the URL.
 * @param {"Timesheet Row"} row_field The name of the field in ClickUp which contains the timesheet row number.
 * @param {"Documentation,Design,Validation"} row_names A comma-delimited array of names of the timesheet rows.
 * @param {1677628800000} start_day The first day to appear in the results. All time entries before this day are included in this day.
 * @return An array of arrays where the rows are weekly timesheet entries for each timesheet row, and the columns are the name of the timesheet row, the date of the Monday of that week, and a duration for each day of the week Monday through Sunday. Empty durations and days outside the request range are empty.
 * @customfunction
 */
function FetchTimesheetEntriesFromClickup(token, team, list, row_field, row_names, start_day)
{
  // **Step 1**
  // Fetching time entries only returns a small subset of the associated task's fields. We could just do
  // another query for each time entry to fetch the task itself, but we already have a "fetch all tasks"
  // function, so just use that upfront to get all tasks.
  const tasks = FetchTasksFromClickup(token, list, "id,"+row_field, null)

  // **Step 2**
  // Now go ahead and fetch the entries
  const serverUrl = "https://api.clickup.com/api/v2/"
  const taskEndpoint = "team/"+team+"/time_entries"
  const parameters = "start_date=0&include_task_tags=true&include_location_names=true&list_id="+list

  var options = {
    "headers" : {
      "Authorization" : token
    }
  };

  // There's no URL generator built-in, so do our own simple one.
  // More powerful one here: https://stackoverflow.com/a/63673352/3697870
  var resp = UrlFetchApp.fetch(serverUrl+taskEndpoint+"?"+parameters, options)
  var json = JSON.parse(resp.getContentText())
  
  const raw_entries = json.data
  
  // **Step 3**
  // Parse the raw entries data into a 2D array of rows and days, where each cell is the total time.
  const rows = row_names.split(",")
  var today = new Date() // actually returns "now" time and date.

  //GSheet treats dates as unix time, so start_day is UTC midnight on that day.
  //We could force GSheet to treat it as a string, but you know speadsheets, always
  //trying to turn things into dates. So rather than fighting, just correct it here:
  start_day = new Date(start_day) // First make it a Javascript date
  start_day.setMinutes(start_day.getTimezoneOffset()) // then shift it to midnight, local time

  const days = whole_days_between(start_day, today) + 1 //an entry for each day, including today
  //Oh javascript... this mess is just a 2D array, rows x days
  var entries = new Array(rows.length).fill(null).map(() => new Array(days).fill(0))
  
  for (const entry of raw_entries)
  {
    if (entry.task.status.status != "Closed")
      continue
    
    const start_time = new Date(parseInt(entry.start))
    // Find the task with matching id, and return its `row_field` value.
    // -1 because in ClickUp we specify the number of the row, but here we need the index.
    const row = tasks.find(e => e[0] == entry.task.id).at(1) - 1
    // put any time before start_day in the first day, otherwise at the integer day offset of the start time
    const day = Math.floor(Math.max(0, days_between(start_day, start_time)))
    // GSheets "duration" cell format is in units of "days", and ClickUp returns duration as
    // number of milliseconds. So it turns out we can convert duration to days like so:
    const duration = days_between(0, entry.duration)

    entries[row][day] += duration // accumulate time for this row and day
  }
  
  // **Step 4**
  // Only thing left to do is turn `entries` into spreadsheet rows
  var ret = []
  // Number of days after Monday of the first week before we have data
  const dead_days = (start_day.getDay()+6)%7 // getDay() retuns 0 for Sunday

  var week_beginning = new Date(start_day)
  week_beginning.setDate(start_day.getDate() - dead_days)
  
  for (var d = -dead_days; d < days; d += 7)
  {
    //Oh Javascript... all this to produce a "day month" string
    const week_beginning_fields = week_beginning.toDateString().split(" ")
    const week_beginning_string = week_beginning_fields[2] + " " + week_beginning_fields[1]

    for (var row_i in rows)
    {
      //Add row name and week beginning string to start of row
      var row = [rows[row_i], week_beginning_string]
      //Then add time for each day of the week
      for (var my_d = d; my_d < d+7 && my_d < days; my_d++)
      {
        if (my_d < 0)
          row.push(null)
        else
        {
          var v = entries[row_i][my_d]
          row.push(v == 0 ? null : v) //ignore zero entries to reduce visual noise
        }
      }
      
      ret.push(row)
    }

    week_beginning.setDate(week_beginning.getDate() + 7)
  }

  return ret;
}

function days_between(start_date, end_date)
{
  return (end_date-start_date)/(24*60*60*1000)
}

// Even if start and end are the same clock time, if they cross a daylight savings
// transition, we wont get an even number of days. To be precise, we'd need to
// convert both to a common timezone. But rounding is good enough for us.
// See: https://stackoverflow.com/a/11252167/3697870
function whole_days_between(start_date, end_date)
{
  return Math.round(days_between(start_date, end_date))
}

/**
 * Use the ClickUp API to fetch an array of tasks from the `list` specified,
 * where each element is an array of fields specified by `fields`, optionally
 * sorted by the field in `sortBy`.
 *
 * @param {"pk_12345678_FEEDFACEFEEDFACEFEEDFACEFEEDFACE"} token Your personal API token: https://clickup.com/api/developer-portal/authentication#generate-your-personal-api-token
 * @param {123456789} list The list identifier, which can be found by navigating to the list in ClickUp and looking for the 9 digit number in the URL, after "app.clickup.com/".
 * @param {"Phase,name,status,time_spent"} fields A comma-delimited array of names of fields to include in the result.
 * @param {"Phase"} sortBy (Optional) One of the fields in `fields` to sort the records by.
 * @return An array of arrays where each row is a Task and each column is a field.
 * @customfunction
 */
function FetchTasksFromClickup(token, list, fields, sortBy)
{
  const serverUrl = "https://api.clickup.com/api/v2/"
  const taskEndpoint = "list/"+list+"/task"
  const parameters = "include_closed=true"

  var options = {
    "headers" : {
      "Authorization" : token
    }
  };

  // There's no URL generator built-in, so do our own simple one.
  // More powerful one here: https://stackoverflow.com/a/63673352/3697870
  var resp = UrlFetchApp.fetch(serverUrl+taskEndpoint+"?"+parameters, options);
  var json = JSON.parse(resp.getContentText());
  
  var ret=[];
  json = json.tasks;
  aRec = json[0];
  customFields = aRec.custom_fields;

  fields = fields.split(",");

  // Iterate by field, parsing all records once for each field, rather than by record
  // parsing each field, to keep the cyclomatic complexity low. Parsing the field is
  // the complex bit, while extracting them from records is then just a map. So we
  // loop on the relatively few fields, and then transpose at the end so we have
  // records in the rows and fields in the columns, ready for the spreadsheet.
  for (const field of fields)
  {
    if (field === "status")
      ret.push(json.map(a => a.status.status))
    else if (field === "time_spent")
      ret.push(json.map(a => a[field] == null ? null : a[field]/(24*60*60*1000)));
    else if (field in aRec)
      ret.push(json.map(a => a[field].toString()));
    else if((index = customFields.findIndex(e => { return e.name === field })) != -1)
    {
      var type = customFields[index].type;
      
      ret.push(json.map(a => {
        var value = a.custom_fields[index].value;
        if(value == null)
          return null;
        else if(type === "drop_down")
          return a.custom_fields[index].type_config.options[value].name;
        else if(type === "number")
          return Number(value);
        else
          return value;
      }));
    }
    else
      return "Field not found: " + field;
  }
  
  ret = ret[0].map((col, i) => ret.map(row => row[i])); //transpose
  if(sortBy != null)
  {
    sortByIdx = fields.indexOf(sortBy);
    ret = ret.sort((a, b) => {
      if(a[sortByIdx] < b[sortByIdx]) return -1;
      else if(a[sortByIdx] > b[sortByIdx]) return 1;
      else return 0;
    });
  }
  return ret;
}