ClickUp API to Google Sheets: A Working Apps Script Guide

The ClickUp API is a REST API at https://api.clickup.com/api/v2, authenticated with a token, returning JSON. ClickUp's own reference documents every endpoint. What it does not document is the shape of a working integration: which parameters are required for the result to match what a person sees in the app, and which ceilings you hit in what order.

This is that, worked through end to end with Google Apps Script as the host, because Apps Script runs inside the spreadsheet, can be scheduled, and costs nothing. The API parts transfer to any language; only the last 2 sections are Apps Script specific.

ClickUp API cost

Yes, on every plan. ClickUp sells no separate API tier and gates no endpoint behind a subscription. What the plan buys is throughput: the rate limit page gives Free Forever, Unlimited, and Business the same 100 requests per minute per token, Business Plus 1,000, and Enterprise 10,000. A Free workspace can run everything below; it just runs it slowly against a large List.

2 ClickUp limits get mistaken for API limits. Custom Fields are capped at 60 uses on Free Forever, so a field you expect in the response may not exist on the task. And view exports are capped at five on the lower plans, which is usually the reason someone is reading an API guide at all. The free plan limits has the rest of the table.

Getting a ClickUp API key

For a script only you run, a personal API token is the shortest path. In ClickUp, open your avatar, then Settings, Apps, and generate a token. It begins pk_ and carries every permission your own account has, which is what makes where you put it matter.

Store it in Script Properties rather than in the code, so it does not travel with a copy of the spreadsheet:

// Run once, from the editor, then delete the literal.
function saveToken() {
  PropertiesService.getScriptProperties().setProperty("CLICKUP_TOKEN", "pk_...");
}

function token() {
  return PropertiesService.getScriptProperties().getProperty("CLICKUP_TOKEN");
}

The token is sent as a bare Authorization header with no Bearer prefix, which trips up anyone used to OAuth:

function clickupFetch(path) {
  const response = UrlFetchApp.fetch("https://api.clickup.com/api/v2" + path, {
    headers: { Authorization: token() },
    muteHttpExceptions: true,
  });
  if (response.getResponseCode() === 429) throw new Error("rate limited");
  return JSON.parse(response.getContentText());
}

If other people will run this, a personal token is the wrong answer, because it acts as you and carries your permissions. That case needs an OAuth app, and OAuth in Apps Script is enough work that it is worth asking whether an off-the-shelf add-on is cheaper than your time.

Finding the List id

Everything hangs off a List id, and the hierarchy is Workspace, Space, Folder, List, with Lists allowed to sit directly in a Space.

The quickest route is the URL. Open the List in ClickUp and the address ends in the id: app.clickup.com/9012345678/v/l/6-901234567890-1. The middle number of that final segment is the List id.

The programmatic route walks down:

const teams = clickupFetch("/team").teams;
const spaces = clickupFetch("/team/" + teams[0].id + "/space?archived=false").spaces;
const folders = clickupFetch("/space/" + spaces[0].id + "/folder?archived=false").folders;
const lists = clickupFetch("/folder/" + folders[0].id + "/list?archived=false").lists;
// Folderless Lists need a separate call:
const loose = clickupFetch("/space/" + spaces[0].id + "/list?archived=false").lists;

That last line is easy to forget and produces a script that silently cannot see a third of the workspace.

The pagination loop

GET /list/{list_id}/task returns 100 tasks per page, and the response carries no total count. You page until a page comes back short:

function fetchAllTasks(listId) {
  const tasks = [];
  let page = 0;
  while (true) {
    const query = "?page=" + page + "&subtasks=true&include_closed=true";
    const batch = clickupFetch("/list/" + listId + "/task" + query).tasks;
    tasks.push(...batch);
    if (batch.length < 100) break;
    page += 1;
  }
  return tasks;
}

2 query parameters decide whether the result matches what a person sees in ClickUp.

subtasks=true is required or you get parents only. This is the single most common cause of a script that returns a third of the expected rows, and what each route does with subtasks covers the rest of the trap, including the deletes that never reach you.

include_closed=true is required or Done tasks are missing. Leave it off and your completion-rate report is 100% forever, which looks like good news until someone checks.

Whatever combination you choose, use the same one everywhere in the script. A refresh that reads a narrower set than the initial load will delete rows it simply could not see.

function fieldValue(field) {
  if (field.type === "drop_down") {
    const option = (field.type_config.options || []).find(
      (candidate) => candidate.id === field.value,
    );
    if (!option) return "";
    return option.name;
  }
  if (field.type === "labels") {
    const chosen = field.value || [];
    return (field.type_config.options || [])
      .filter((option) => chosen.includes(option.id))
      .map((option) => option.label)
      .join(", ");
  }
  if (field.type === "date" && field.value) {
    return new Date(Number(field.value));
  }
  return field.value;
}

function withRetry(work, attempts) {
  attempts = attempts || 5;
  for (let attempt = 0; attempt < attempts; attempt += 1) {
    try {
      return work();
    } catch (error) {
      if (attempt === attempts - 1) throw error;
      Utilities.sleep(Math.pow(2, attempt) * 1000);
    }
  }
}

const started = Date.now();
// inside the paging loop
if (Date.now() - started > 4.5 * 60 * 1000) {
  PropertiesService.getScriptProperties().setProperty("resumePage", String(page));
  return; // next trigger picks it up
}

const rows = tasks.map(toRow);
const sheet = SpreadsheetApp.getActive().getSheetByName("Tasks");
sheet.getRange(2, 1, sheet.getMaxRows() - 1, headers.length).clearContent();
sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);