Fetch tasks from the ClickUp API, returning the fields specified as a two dimensional array.

FetchTasksFromClickup.gs

/*
 * FetchTasksFromClickup
 *
 * A simple function to fetch tasks using the ClickUp API and return the fields specified as a two-dimensional array.
 * This can be used in a Google Apps Script to pull task data into a Google Sheet.
 * 
 * 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 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"

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

  var resp = UrlFetchApp.fetch(serverUrl+taskEndpoint, options);
  var json = JSON.parse(resp.getContentText());
  
  var ret=[];
  json = json.tasks;
  aRec = json[0];
  customFields = aRec.custom_fields;

  fields = fields.split(",");
  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;
}