Skip to content

Instantly share code, notes, and snippets.

@hraftery

hraftery/FetchTasksFromClickup.gs

Created November 7, 2022 02:51
Download ZIP

Revisions

  1. hraftery created this gist Nov 7, 2022.
    84 changes: 84 additions & 0 deletions FetchTasksFromClickup.gs
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,84 @@
    /*
    * 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;
    }