Build a Jira issue importer and exporter

Some of Northwind’s clients live in Jira and the studio team lives in Sheets. That split means status updates get retyped, copied, and quietly fall out of sync — a stand-up where half the board is a day stale. The fix is not to move everyone onto one tool; it is to mirror the issues both ways.

This script does both directions. pullJira reads issues from a Jira project and rebuilds a mirror sheet so the team can see the client’s board without a Jira login. pushJira reads a status column the team edits and writes those changes back to Jira through the REST API, so the studio’s view stays the source of truth for the work they own.

What you’ll need

• A Jira Cloud site and a project you have access to — the examples use the NW project on northwind.atlassian.net.
• A Jira API token, created at id.atlassian.com → Security → API tokens, saved as JIRA_TOKEN in Script Properties. Never paste it into the code — see Store API keys and secrets securely.
• A Google Sheet to act as the mirror. The first sheet receives the imported issues; the script writes its own headers.
• Knowing your Jira transition IDs if you plan to push status changes — Jira moves an issue by transition, not by status name (see “Watch out for”).

The script

// Your Jira Cloud site and the account the API token belongs to.
const JIRA_BASE = 'https://northwind.atlassian.net';
const JIRA_USER = '[email protected]';

// The spreadsheet that mirrors the Jira project.
const MIRROR_SHEET_ID = '1abcJiraMirrorId';

// Default JQL — which issues to pull when none is passed.
const DEFAULT_JQL = 'project = NW';

/**
 * Builds the Basic auth header Jira Cloud expects: base64 of
 * "email:apiToken". The token is read from Script Properties.
 */
function jiraAuthHeader() {
  const token = PropertiesService.getScriptProperties()
    .getProperty('JIRA_TOKEN');
  return 'Basic ' + Utilities.base64Encode(JIRA_USER + ':' + token);
}

/**
 * Imports Jira issues matching a JQL query and rebuilds the mirror
 * sheet so it always reflects the current Jira state.
 */
function pullJira(jql = DEFAULT_JQL) {
  // 1. Search Jira for the matching issues.
  const res = UrlFetchApp.fetch(
    JIRA_BASE + '/rest/api/3/search?jql=' + encodeURIComponent(jql),
    { headers: { Authorization: jiraAuthHeader() }, muteHttpExceptions: true }
  );
  const data = JSON.parse(res.getContentText());

  if (!data.issues || !data.issues.length) {
    Logger.log('No issues returned for JQL: ' + jql);
    return;
  }

  // 2. Flatten each issue into a row — key, summary, status, assignee.
  const rows = data.issues.map((i) => [
    i.key,
    i.fields.summary,
    i.fields.status.name,
    i.fields.assignee?.displayName || '',
  ]);

  // 3. Rebuild the mirror sheet from scratch so deleted issues drop off.
  const sheet = SpreadsheetApp.openById(MIRROR_SHEET_ID).getSheets()[0];
  sheet.clear();
  sheet.getRange(1, 1, 1, 4)
    .setValues([['key', 'summary', 'status', 'assignee']]);
  sheet.getRange(2, 1, rows.length, 4).setValues(rows);
  Logger.log('Mirrored ' + rows.length + ' issues from Jira.');
}

/**
 * Pushes a status transition back to Jira for a single issue.
 * Jira changes status via a transition ID, not a status name.
 */
function pushTransition(issueKey, transitionId) {
  UrlFetchApp.fetch(
    JIRA_BASE + '/rest/api/3/issue/' + issueKey + '/transitions',
    {
      method: 'post',
      contentType: 'application/json',
      headers: { Authorization: jiraAuthHeader() },
      payload: JSON.stringify({ transition: { id: transitionId } }),
      muteHttpExceptions: true,
    }
  );
}

/**
 * Reads a "transition" column the team fills in on the mirror sheet
 * and pushes each non-blank transition back to Jira.
 */
function pushJira() {
  const sheet = SpreadsheetApp.openById(MIRROR_SHEET_ID).getSheets()[0];
  const [header, ...rows] = sheet.getDataRange().getValues();

  const keyCol = header.indexOf('key');
  const transCol = header.indexOf('transition');
  if (transCol === -1) {
    Logger.log('Add a "transition" column to push changes.');
    return;
  }

  // Push every row that has a transition ID typed in.
  let pushed = 0;
  for (const r of rows) {
    if (r[transCol]) {
      pushTransition(r[keyCol], r[transCol]);
      pushed++;
    }
  }
  Logger.log('Pushed ' + pushed + ' transitions to Jira.');
}

How it works

1. jiraAuthHeader builds the Basic auth string Jira Cloud expects — base64 of email:token — pulling the token from Script Properties so it never lands in the code.
2. pullJira calls Jira’s /search endpoint with a JQL query. JQL is Jira’s filter language, so you can pull a whole project or just assignee = me.
3. If the search returns no issues it logs and stops rather than clearing the sheet for nothing.
4. Each issue is flattened to four fields. assignee uses optional chaining because an unassigned issue has a null assignee object.
5. The mirror sheet is cleared and rebuilt every run, so issues closed or removed in Jira disappear from the sheet too.
6. pushTransition posts to Jira’s /transitions endpoint. Jira does not let you set a status directly — you trigger a transition, identified by ID.
7. pushJira reads a transition column the team edits, and for every row with an ID typed in it fires the matching transition back to Jira.

Example run

After pullJira() the mirror sheet holds the current Jira board:

key	summary	status	assignee
NW-104	Fix export encoding	In Progress	Priya
NW-105	Client onboarding doc	To Do	(blank)
NW-106	Q3 invoice reconcile	Done	Awadesh

To move NW-105 forward, the team adds a transition column and types the transition ID (say 21 for “Start work”):

key	summary	status	assignee	transition
NW-105	Client onboarding doc	To Do	(blank)	21

Running pushJira() fires that transition and the next pullJira() shows NW-105 as In Progress.

Run it

These run on demand or on a schedule, depending on the direction:

1. Run pullJira by hand whenever you want a fresh import, or add a Time- driven trigger (a Day timer or hourly) to keep the mirror current.
2. Run pushJira after editing the transition column.
3. Approve the authorisation prompt the first time each runs.

To list an issue’s valid transition IDs, fetch GET /rest/api/3/issue/{key}/transitions once and read them from the response.

Watch out for

• Jira’s /search endpoint is paginated and returns 50 issues by default. For a large project, add &maxResults=100 and loop with startAt until you have them all, or the mirror will be truncated.
• Status is set by transition, not by name. A transition that exists for one issue may not exist for another — the IDs depend on the issue’s current status and the project’s workflow.
• The API token inherits your Jira permissions. If you cannot see an issue in Jira, the script cannot either.
• pullJira rebuilds the sheet, so any extra columns the team adds to the right (like transition or notes) get wiped on import. Keep editable columns on a separate tab, or rework the import to merge by key instead of clearing.
• UrlFetchApp is subject to a daily quota. A frequent trigger across many projects can use it up — pull no more often than the team actually needs.