Skip to content

Instantly share code, notes, and snippets.

@hraftery
Created November 7, 2022 02:51
Show Gist options
  • Save hraftery/cc8aea24e8643180fb1af0d3600a7b91 to your computer and use it in GitHub Desktop.
Save hraftery/cc8aea24e8643180fb1af0d3600a7b91 to your computer and use it in GitHub Desktop.
Fetch tasks from the ClickUp API, returning the fields specified as a two dimensional array.
/*
* 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;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment