Sync GitHub issues to a project sheet

Northwind’s engineers track their work in GitHub issues, which is exactly where that work belongs. The trouble is everyone else — the project manager, the client lead, the founder — does not live in GitHub and should not have to. They just want to know what is open, who owns it, and what is labelled urgent, without asking an engineer for a status update.

This script gives them that view. It pulls every issue from a repository through the GitHub API and writes it to a Google Sheet anyone can open: number, title, state, author, labels, created date, and a direct link. Run it on a schedule and the sheet is a live, no-login window onto the project’s backlog.

What you’ll need

• A GitHub repository whose issues you want to surface.
• A GitHub personal access token with repo scope (or read access to issues for a fine-grained token). A private repo needs this; a public one technically works without a token, but you will hit rate limits fast.
• That token saved as GITHUB_TOKEN in Script Properties — see Store API keys and secrets securely.
• A Google Sheet for the output. The script writes to the first tab and clears it on every run, so use a dedicated sheet.

The script

// The repository to sync, as "owner/name".
const REPO = 'northwind/site';

// The spreadsheet that holds the issue list. The first tab is rebuilt each run.
const ISSUES_SHEET_ID = '1abcIssuesId';

/**
 * Pulls every issue from the GitHub repo and writes it to the
 * project sheet, rebuilding the first tab from scratch.
 */
function syncIssues() {
  // 1. The token lives in Script Properties, never in the code.
  const token = PropertiesService.getScriptProperties()
    .getProperty('GITHUB_TOKEN');

  // 2. Ask GitHub for all issues — open and closed — up to 100 in one page.
  const url = 'https://api.github.com/repos/' + REPO +
    '/issues?state=all&per_page=100';
  const res = UrlFetchApp.fetch(url, {
    headers: {
      Authorization: 'Bearer ' + token,
      Accept: 'application/vnd.github+json',
    },
    muteHttpExceptions: true,
  });
  const issues = JSON.parse(res.getContentText());

  // 3. Bail out early if the repo has no issues (or the call failed).
  if (!Array.isArray(issues) || !issues.length) {
    Logger.log('No issues returned from GitHub — nothing to do.');
    return;
  }

  // 4. Flatten each issue into a spreadsheet row.
  //    Labels are an array of objects, so join their names into one cell.
  const rows = issues.map((i) => [
    i.number,
    i.title,
    i.state,
    i.user.login,
    (i.labels || []).map((l) => l.name).join(','),
    new Date(i.created_at),
    i.html_url,
  ]);

  // 5. Rebuild the first tab so closed-and-deleted issues never linger.
  const sheet = SpreadsheetApp.openById(ISSUES_SHEET_ID).getSheets()[0];
  sheet.clear();
  sheet.getRange(1, 1, 1, 7).setValues([
    ['#', 'title', 'state', 'author', 'labels', 'created', 'url'],
  ]);
  sheet.getRange(2, 1, rows.length, 7).setValues(rows);

  Logger.log('Wrote ' + rows.length + ' issue(s) to the project sheet.');
}

How it works

1. syncIssues reads the GitHub token from Script Properties so the credential never appears in the code.
2. It calls the repo’s issues endpoint with state=all, so both open and closed issues come back, and per_page=100 to pull a full page at once. The Accept header pins the response to the current GitHub API version.
3. If the response is not an array — or is empty — it logs a message and stops. This also catches the case where GitHub returned an error object instead of a list.
4. It maps each issue to a row. The one fiddly field is labels, which GitHub returns as an array of objects; the script pulls each name and joins them into a single comma-separated cell.
5. It clears the first tab, writes a fresh header, and writes one row per issue — so issues that were closed and removed do not hang around from a prior run.

Example run

For a repo with these issues:

number	title	state	labels
142	Checkout button misaligned on mobile	open	bug, urgent
139	Add dark mode to settings	open	enhancement
131	Update privacy policy copy	closed	content

After a run, the project sheet holds:

#	title	state	author	labels	created	url
142	Checkout button misaligned on mobile	open	priya-dev	bug,urgent	2025-07-21	github.com/…/142
139	Add dark mode to settings	open	sam-eng	enhancement	2025-07-18	github.com/…/139
131	Update privacy policy copy	closed	dana-pm	content	2025-07-10	github.com/…/131

The project manager filters the labels column for urgent or the state column for open and has the status update without messaging anyone.

Trigger it

The sheet should keep pace with the backlog, so run this on a schedule:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose syncIssues, a Time-driven source, and an Hour timer every few hours — or a Day timer if the backlog moves slowly.

Watch out for

• The issues endpoint returns pull requests too. In GitHub’s data model a PR is a kind of issue, so PRs show up as rows. To list only true issues, skip any item that has a pull_request field in the map step.
• It reads one page of 100. A busy repo has more, and GitHub paginates via the Link response header. For a larger repo, loop with a page parameter until a page comes back with fewer than 100 items.
• Rate limits are real. An authenticated token gets 5,000 requests an hour; unauthenticated calls get only 60. Always send the token, even for a public repo, and do not trigger this every minute.
• The sheet is cleared and rebuilt every run. Do not keep manual notes or extra columns on the first tab — they will be wiped. Use a second tab for working notes.
• created_at is a UTC timestamp. new Date() parses it, but the cell displays in the spreadsheet’s timezone — check it reads the date you expect.