Sync tasks with Trello or Asana

Every Northwind client seems to run a different task tool. One lives in Trello, another in Asana, and the studio itself reports on everything in a single spreadsheet. Keeping that spreadsheet honest means someone copying card titles and due dates across by hand — work that is tedious, error-prone, and always a little behind.

This script pulls a board’s cards straight into a sheet on a schedule. Point it at a Trello board and it mirrors every card — id, title, list, due date and link — so the studio’s reporting tab reflects the client’s board without anyone retyping a thing. The same pattern works for Asana with a different endpoint, shown at the end.

What you’ll need

• A Google Sheet to hold the mirrored cards. The script writes to its first tab and creates the header row itself.
• A Trello account with access to the board you want to mirror, and its board ID (the short code in the board URL, e.g. trello.com/b/<id>/...).
• A Trello API key and token, saved as TRELLO_KEY and TRELLO_TOKEN in Script Properties — see Store API keys and secrets securely.
• The board ID and sheet ID set in the config block below.

The script (Trello)

// The Trello board to mirror — the short code from the board URL.
const TRELLO_BOARD = 'BOARD_ID';

// The spreadsheet that mirrors the board's cards.
const TASKS_SHEET_ID = '1abcTrelloId';

// The columns written to the sheet, in order.
const HEADERS = ['id', 'name', 'list', 'due', 'url'];

/**
 * Pulls every card from a Trello board and rewrites the tasks sheet
 * so it mirrors the board.
 */
function pullTrelloCards() {
  // 1. Read the API credentials from Script Properties — never inline them.
  const props = PropertiesService.getScriptProperties();
  const key = props.getProperty('TRELLO_KEY');
  const token = props.getProperty('TRELLO_TOKEN');
  if (!key || !token) {
    Logger.log('Missing TRELLO_KEY or TRELLO_TOKEN in Script Properties.');
    return;
  }

  // 2. Ask Trello for every card on the board. Auth travels as query
  //    parameters, which is how Trello's REST API expects it.
  const url = 'https://api.trello.com/1/boards/' + TRELLO_BOARD +
    '/cards?key=' + key + '&token=' + token;
  const response = UrlFetchApp.fetch(url, { muteHttpExceptions: true });

  // 3. Bail out clearly if the request failed (bad board, bad token).
  if (response.getResponseCode() !== 200) {
    Logger.log('Trello request failed: ' + response.getContentText());
    return;
  }

  // 4. Flatten each card into a spreadsheet row. `due` can be null,
  //    so fall back to an empty string for a clean cell.
  const cards = JSON.parse(response.getContentText());
  const rows = cards.map((c) => [
    c.id,
    c.name,
    c.idList,
    c.due || '',
    c.url,
  ]);

  // 5. Rewrite the sheet from scratch so it always reflects the board.
  const sheet = SpreadsheetApp.openById(TASKS_SHEET_ID).getSheets()[0];
  sheet.clear();
  sheet.getRange(1, 1, 1, HEADERS.length).setValues([HEADERS]);
  if (rows.length) {
    sheet.getRange(2, 1, rows.length, HEADERS.length).setValues(rows);
  }
  Logger.log('Mirrored ' + rows.length + ' Trello cards.');
}

How it works

1. pullTrelloCards reads TRELLO_KEY and TRELLO_TOKEN from Script Properties. If either is missing the script logs a message and stops, so a half-configured project fails loudly.
2. It builds the board’s cards endpoint, passing the key and token as query parameters — Trello’s REST API authenticates that way rather than with a header.
3. muteHttpExceptions lets the script check the response itself. A non-200 code (a wrong board ID or a revoked token) is logged and the run stops.
4. Each card is flattened to five columns. c.due is null for cards with no due date, so c.due || '' keeps those cells clean instead of writing the word “null”.
5. The first tab is cleared and rewritten: header row, then one row per card. It is a full mirror — cards deleted on the board disappear from the sheet on the next run.

Example run

The Trello board has these cards:

Card	List	Due
Draft homepage copy	In progress	2026-05-30
Client review call	To do	(none)
Ship favicon fix	Done	2026-05-20

After a run, the first tab of the tasks sheet holds:

id	name	list	due	url
66f1a…	Draft homepage copy	5e2b…	2026-05-30T00:00:00Z	https://trello.com/c/aB1
66f1b…	Client review call	5e2a…		https://trello.com/c/cD2
66f1c…	Ship favicon fix	5e2c…	2026-05-20T00:00:00Z	https://trello.com/c/eF3

The list column holds Trello’s internal list IDs. Map those to readable names with a small lookup tab, or fetch /boards/<id>/lists and join on the ID.

Trigger it

Run this on a schedule so the reporting tab stays current on its own:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Add a trigger: choose pullTrelloCards, event source Time-driven, and an Hour timer every few hours during the working day.
3. Save and approve the authorisation prompt the first time.

For Asana, the same shape applies — only the request changes. Asana uses a bearer token in a header and returns tasks under a data key:

// The Asana project to mirror, and its personal access token key.
const ASANA_PROJECT = 'PROJECT_GID';

/**
 * Pulls tasks from an Asana project. A drop-in alternative to the
 * Trello version above — write the rows to the same sheet.
 */
function pullAsanaTasks() {
  const token = PropertiesService.getScriptProperties()
    .getProperty('ASANA_TOKEN');
  if (!token) {
    Logger.log('Missing ASANA_TOKEN in Script Properties.');
    return;
  }

  // Asana wants a bearer token and returns tasks under `data`.
  const url = 'https://app.asana.com/api/1.0/projects/' + ASANA_PROJECT +
    '/tasks?opt_fields=name,due_on,completed,permalink_url';
  const response = UrlFetchApp.fetch(url, {
    headers: { Authorization: 'Bearer ' + token },
    muteHttpExceptions: true,
  });
  if (response.getResponseCode() !== 200) {
    Logger.log('Asana request failed: ' + response.getContentText());
    return;
  }

  const tasks = JSON.parse(response.getContentText()).data;
  Logger.log('Asana returned ' + tasks.length + ' tasks.');
  // Map and write `tasks` to the sheet exactly as pullTrelloCards does.
}

Watch out for

• Trello returns list IDs, not list names. If the report needs human-readable columns, fetch the board’s lists once and build an ID-to-name lookup.
• The full clear() then rewrite wipes any manual notes on the sheet each run. Keep annotations on a separate tab so they survive.
• Trello tokens belong to a person, not a workspace. If the person who issued the token loses board access, the call starts returning 401 — the response guard will catch it.
• A board can hold hundreds of cards. The cards endpoint returns up to 1000 by default; very large boards need pagination with the before parameter.
• Trello and Asana both rate-limit (roughly 100 and 150 requests per minute). One scheduled call is fine, but do not loop the function in a tight retry.