Collects metadata, languages, contributors, traffic, referrers/paths, branches, PR/issue activity, and trend charts for every GitHub repository you can access (own, org member, collaborator), storing results in a Google Sheet for long-term history, since GitHub's traffic API only exposes the trailing 14 days.
- Quickstart
- Files
- What you get, once running
- Detailed token setup
- Running it: the menu, in detail
- The
Configurationsheet - How the charts work
- Diagnostic tool: raw API snapshot export
- Maintenance tools: fixing duplicate/malformed date rows
- Performance: why this doesn't get slower as your history grows
- Troubleshooting
- Note on testing
- Create a GitHub token: GitHub > Settings > Developer settings > Personal access tokens > Fine-grained tokens > Generate new token. Give it read-only access to: Metadata, Contents, Administration, Pull requests, Issues. Copy the token now; GitHub only shows it once. (Need org repos, or repos owned by another individual? See Detailed token setup below first.)
- Open script.google.com and create a new
project (or
Extensions > Apps Scriptfrom an existing Google Sheet). - Paste the contents of
Code.gsinto the editor. - Project Settings > Script Properties > Add script property:
name it
GITHUB_PAT, paste your token as the value, and save. - Reload the spreadsheet tab in your browser. A "GitHub Analytics" menu appears in the menu bar.
- Click through the menu in order:
- "1. Initialize spreadsheet"
- "2. Run collection now (main)" (check the
Errorssheet afterward) - "3. Install daily/6-hourly trigger"
That's it. main() now runs on its own schedule and refreshes charts
automatically at the end of every run.
Apps Script has no module/import system: every .gs file in a project is
loaded into one shared global scope at runtime, effectively concatenated
together. So this whole project is a single file, Code.gs, containing
everything: config/credentials, the GitHub API client (REST + GraphQL),
spreadsheet helpers, all collectors, the chart builder, maintenance and
diagnostic tools, main(), and the custom menu. It's organized into 15
numbered, commented sections. Search the file for 1. CONFIGURATION,
2. GITHUB API CLIENT, etc. to jump around.
- 13 data sheets:
Repositories,RepositorySnapshots,Languages,Contributors,TrafficViews,TrafficClones,PopularReferrers,PopularPaths,Branches,Configuration, plusExecutionLogandErrorsfor observability, andRawApiDebugfor on-demand raw-response debugging (see "Diagnostic tool" below). - Two full sets of chart tabs:
Charts(every repo, public and private) andPublic Charts(public repos only). Both are pinned as the leftmost tabs in the spreadsheet.Public Chartsis safe to share or publish on its own (Google Sheets supports publishing individual tabs) without exposing anything about your private repositories. - A "GitHub Analytics" menu so you (or a non-technical teammate) can run everything by clicking, without opening the script editor.
Skip this if the one-liner in Quickstart worked for you. Read this if you need org repos, collaborate on repos owned by other individuals, or want to understand the permission model more precisely.
This determines which repos the token can even see:
- Selecting your personal account lets the token see repos you own and repos you collaborate on that are also owned by you or an org, but fine-grained tokens cannot be scoped to a repository owned by a different individual who added you as a collaborator. This is a known GitHub limitation, not a bug in this script.
- If you need org repos too, you generally need a token created/approved per organization (see "Organization approval" below).
- If you collaborate on repos owned by other individual people (not
orgs), use a classic PAT instead (Developer settings > Tokens
(classic) > Generate new token > scope:
repo). Classic tokens follow your actual collaborator status across any repo regardless of owner. Trade-off: broader, less granular permissions than fine-grained tokens.
Choose All repositories (matches this project's "every accessible repository" goal) or Only select repositories if you want to scope it down manually (remember to revisit this list as you create new repos).
| Permission | Level | Why |
|---|---|---|
| Metadata | Read-only | Baseline requirement for nearly every read call; auto-selected. |
| Contents | Read-only | Branches, languages, and other repo-level reads. |
| Administration | Read-only | Easy to miss. Required for the traffic endpoints (views, clones, referrers, paths). Without it, those calls return 403, recorded as a non-fatal row in the Errors sheet rather than failing silently. |
| Pull requests | Read-only | PR state counts (open/closed/merged/draft). |
| Issues | Read-only | Issue state counts. |
If any target repos belong to an organization, that org may require owner approval before the token can see its repos. Check Organization settings > Personal access tokens > Pending requests. Until approved, the token will silently show 0 repos from that org (not an error, just absent from results).
The menu is split into two groups: a numbered 1-2-3 setup flow for first-time use, and a set of on-demand tools underneath for things you'll reach for occasionally rather than every time.
- "1. Initialize spreadsheet (first-time setup)": creates the
spreadsheet (if not already bound) and every sheet with headers,
including a pre-filled
Configurationsheet. Safe to re-run any time; it won't wipe existing data. - "2. Run collection now (main)": does a full collection pass across
every accessible repository, and automatically refreshes both chart tabs
at the end. Check the
ErrorsandExecutionLogsheets afterward. This is also the function the recurring trigger will call once installed. - "3. Install daily/6-hourly trigger" (
setupTriggers): installs the recurring schedule so you don't have to click "Run collection now" manually going forward. ReadsRUN_FREQUENCYfrom theConfigurationsheet (dailyorevery6hours). Re-run this after changingRUN_FREQUENCYin Configuration; it clears old triggers first so you won't get duplicates.
After initial setup, your regular workflow is simply letting the trigger run
main() automatically. Charts refresh themselves as part of every run, so
there's normally nothing further to click.
- "Refresh charts now": manually rebuilds both
ChartsandPublic Charts. Only needed if you want updated graphs without waiting for or triggering a full collection run (main()already does this automatically at the end of every run). - "Export raw API snapshot (debug)": see "Diagnostic tool" below.
- "Repair legacy date cells + dedupe Traffic sheets": see "Maintenance tools" below. Mainly relevant if you've manually pasted historical data into these sheets from another source.
Edit the Value column. No redeploy is needed for most of these; they're
read fresh at the start of every main() run.
| Key | Default | What it does |
|---|---|---|
RUN_FREQUENCY |
daily |
daily or every6hours. Requires re-running "Install trigger" after changing. |
INCLUDE_ARCHIVED |
yes |
Include archived repositories. |
INCLUDE_FORKS |
yes |
Include forked repositories. |
COLLECT_CONTRIBUTORS |
yes |
Toggle contributor collection entirely. |
COLLECT_RELEASES |
yes |
Reserved; release metadata is actually pulled cheaply via GraphQL alongside repo discovery regardless. This flag exists for parity with the other COLLECT_* toggles. |
COLLECT_BRANCHES |
yes |
Toggle branch collection entirely. |
COLLECT_TRAFFIC |
yes |
Toggle views/clones/referrers/paths collection entirely. |
COLLECT_REFERRERS_PATHS |
yes |
Toggle just the referrers/paths portion of traffic collection. |
MAX_RUNTIME_MS |
300000 |
Safety cutoff so main() exits before Apps Script's hard 6-minute execution ceiling. |
API_PAUSE_THRESHOLD |
50 |
Remaining GitHub API quota at which the script starts proactively throttling. |
ACTIVITY_QUERY_PAUSE_MS |
150 |
Small delay before each per-repo GraphQL call that includes a search(...) field (draft PR count), as extra caution against secondary rate limits on search-heavy traffic. |
CONTRIBUTORS_FREQUENCY_DAYS |
1 |
Collect contributors every N days instead of every run. 1 means every run. |
BRANCHES_FREQUENCY_DAYS |
1 |
Collect branches every N days instead of every run. 1 means every run. |
REFERRERS_PATHS_FREQUENCY_DAYS |
7 |
Collect referrers/paths every N days. Defaults to weekly rather than daily, since GitHub's own data here is a rolling 14-day snapshot and collecting it daily mostly re-records the same overlapping window. |
PRUNE_ROLLING_SNAPSHOTS_DAYS |
60 |
Delete PopularReferrers/PopularPaths rows older than this many days. Runs automatically once a week (Sundays), not every run, to keep the cost of the cleanup itself low. |
BATCH_FLUSH_EVERY_N_REPOS |
25 |
Sheet writes are staged in memory and flushed in batches rather than per-repo (see "Performance" below). This controls how many repos are processed between flushes. Lower it if you're worried about losing progress to Apps Script's execution ceiling mid-run; a final flush always happens at the end regardless. |
CHART_TOP_N_REPOS |
15 |
Per-repository breakdown charts (Stars/Forks/Open Issues/Traffic over time) collapse to "Top N + Other" once you have more repos than this, to keep the legend legible. |
Both Charts (all repos) and Public Charts (public repos only) are built
from hidden backing sheets (ChartData / PublicChartData respectively),
filled by reading the raw history sheets directly in Apps Script (not
live spreadsheet formulas) each time initializeCharts() runs. This was a
deliberate design choice after earlier attempts using Google Sheets'
QUERY(...pivot...) formulas produced charts with blank legend text, a
real bug in how that formula's auto-generated headers interact with Sheets'
chart-building API. Computing the pivot tables ourselves guarantees the
legend always shows your actual repository names.
The trade-off: these charts are a snapshot as of the last time
initializeCharts() ran. They do not update live as main() collects new
data in the background. Since main() calls initializeCharts()
automatically at the end of every collection run, in normal use you should
rarely need to think about this, but if you manually edit a data sheet
(e.g. the maintenance tools below) without also running a full main()
pass, remember to click "Refresh charts now" afterward to see the change
reflected.
- Per-repository breakdown: Stars, Forks, and Open Issues over time (one
line per repo), and Traffic Views/Clones per day (stacked bar, one segment
per repo). Collapses to "Top N + Other" past
CHART_TOP_N_REPOSrepos. - Cumulative traffic: running-total Views and Clones over time (stacked area), showing each repo's contribution to the overall total.
- Org-wide totals: Stars/Forks/Open Issues/Open PRs summed across all matching repos, one line per metric. A top-level trend view that doesn't get cluttered as your repo count grows.
- Top paths / top referrers: most recent 14-day snapshot, ranked bar chart across all matching repos (see the note on referrers/paths data shape in Troubleshooting; this is a snapshot ranking, not a time series).
- Top languages by bytes: most recent snapshot, summed across all matching repos.
- PR merge rate: current merged-vs-closed-unmerged ratio per repo, as a ranked bar chart.
Charts includes every repo (public and private); Public Charts includes
only repos where Visibility = public in the Repositories sheet. Both are
pinned as the leftmost tabs automatically, regardless of how many data
sheets accumulate over time.
"Export raw API snapshot (debug)" (or exportRawApiSnapshot() directly)
re-hits every REST/GraphQL endpoint the collector uses: traffic views,
clones, popular referrers/paths, contributors, branches, and the per-repo
GraphQL activity query, for every discovered repository. It writes the
untouched response body (no normalization, no filtering) into a
RawApiDebug sheet: Timestamp | Repository | Endpoint | HTTP Status | URL | Response Length | Raw Response (JSON).
This is meant for exactly one situation: something in the collected data
looks off (missing rows, unexpected values) and you want to see precisely
what GitHub actually sent back, independent of anything this script did
with it afterward. Each run clears previous debug rows first, since it's a
point-in-time snapshot rather than an accumulating history. Very large
responses (rare, only likely for contributors/branches on unusually
large repos) are truncated at ~45,000 characters with a note, since a
single Sheets cell caps out at 50,000.
If you ever manually paste historical data into these sheets from another source, you can end up with rows that look like duplicates (same repo, same displayed date) but don't get merged by normal collection, for two distinct reasons:
- Type mismatch: a pasted cell that lost its real
Datetype and became plain text. This part is already handled automatically:normalizeKeyValue_()recognizes and re-normalizes date-shaped text so it matches a properly-typedDatecell for the same day. - Timezone drift: a genuinely different underlying value that only
displays the same. This happens when a date-like string gets
auto-converted by Sheets using your spreadsheet's local timezone instead
of UTC. For example, in a
Europe/Stockholmspreadsheet, a stray"2026-06-25"string can get stored as2026-06-24T22:00:00Z(midnight Stockholm time, converted to UTC). That cell then displays as "2026-06-25" (matching a clean, correctly-stored row for that day), while its true UTC calendar day is actually the 24th. So the two rows are genuinely different by GitHub's own UTC-anchored definition of a "day," even though they look identical in the sheet, and ordinary dedup logic correctly won't merge them.
"Repair legacy date cells + dedupe Traffic sheets"
(repairAndDedupeTrafficSheets()) fixes both, in the right order. It first
scans every Date-suffixed column in TrafficViews/TrafficClones for
cells that aren't exactly UTC midnight, and corrects them to clean UTC
midnight for the calendar day they were clearly meant to represent (read
via your spreadsheet's own timezone, since that's how the bad value was
originally produced). Only then does it run the dedupe pass; repair has to
happen first, since dedupe can only merge rows whose keys already match.
Run this any time you suspect stray duplicate-looking rows, especially after pasting in data manually. It's safe to re-run: it's a no-op if there's nothing left to fix.
An earlier version of this script wrote to sheets once per repository per
sub-collector (traffic, contributors, branches, etc.), and each of those
writes read the entire existing history of that sheet just to figure out
which rows to update vs. append. That meant total work scaled with
repositories x existing history size, which is exactly why it got
steadily slower, day over day, as more history accumulated, eventually
failing to finish within Apps Script's execution ceiling.
The current version separates fetching from GitHub (still per-repo, as
it has to be) from writing to sheets. Each repo's results are staged in
memory, and sheets are only written in batches (BATCH_FLUSH_EVERY_N_REPOS,
plus a final flush at the end), so each sheet's history is read and indexed
once per batch rather than once per repo. Total work now scales with
repositories + existing history size, not their product, so duration
should stay roughly flat as your history grows, rather than climbing
indefinitely.
curl -H "Authorization: Bearer YOUR_TOKEN" \
-H "Accept: application/vnd.github+json" \
https://api.github.com/userShould return your profile JSON. Then confirm Administration access on one of your own repos:
curl -H "Authorization: Bearer YOUR_TOKEN" \
-H "Accept: application/vnd.github+json" \
https://api.github.com/repos/OWNER/REPO/traffic/viewsA 403 here means the Administration:Read permission isn't actually active
(or an org hasn't approved it yet). Fix that before running main().
setupTriggers() schedules the daily run for a fixed hour, but that hour is
interpreted in whatever timezone your spreadsheet is set to
(File > Settings > General > Time zone), not necessarily UTC. If the
schedule seems off by a few hours, that setting, not the script, is why.
Adjust it there if you want the run to land at a specific UTC hour.
- Traffic data only covers the last 14 days per API call. That's a
GitHub limitation, not this script's. The whole point of the historical
sheets (
TrafficViews/TrafficClones) is to stitch daily snapshots into permanent history over time; the longer this runs, the more useful it gets. - A 403 on any endpoint is logged, not silent. It's still treated as
non-fatal (the script records "unavailable" and moves on to the next
repo), but it also writes a row to the
Errorssheet explaining the likely cause (missing push access, missingAdministration:Readon a fine-grained PAT, or an unauthorized org for the token), so you have visibility instead of a silent, permanent gap in the data. - "Views" and "Clones" measure narrower things than you might expect.
Views means visits to your repository's page on github.com, not traffic
to a GitHub Pages site, raw.githubusercontent.com content, or a CDN
serving your files. Clones means actual
git clone/checkout operations (including CI/Actions checkouts, IDE git integrations, dependency tools), not someone loading a web page you happen to host in the repo. Also worth knowing: your own page views don't count while you're logged in, so if you're the primary visitor of your own small or hobby repos, near-zero view counts are expected, not a sign anything is broken. - A repo's traffic "rolling window" can stall short of today, even with a
perfectly valid 200 response. In practice we've observed this
correlating with public, low-external-traffic repositories specifically.
GitHub returns a complete, well-formed
views/clonesarray, just one that stops advancing past some earlier date instead of rolling forward to "yesterday" like your more actively-viewed repos do. This lines up with GitHub's own public account (via a GitHub Community Discussions thread) of a 2025 traffic-pipeline rebuild that explicitly deprioritized low-recent-viewership repos under load, and a second, independent report describing public repos specifically losing traffic updates during the same period. There's no official doc confirming the exact ongoing mechanism, so treat this as an observed pattern rather than settled fact, but it's a real GitHub-side behavior, not a bug in this script, and nothing in this codebase can force GitHub's data to advance faster. - Referrers/paths are a rolling top-10-of-14-days snapshot, not a daily
time series. Collecting them every single day mostly just re-records
overlapping data.
REFERRERS_PATHS_FREQUENCY_DAYS(default 7) and the automatic weekly pruning (PRUNE_ROLLING_SNAPSHOTS_DAYS) both exist to keep this from bloating your spreadsheet with mostly-redundant rows. - Contributor counts can lag real activity. This is a documented GitHub API caching behavior, not something this script can work around. The endpoint is also sorted by commits on the default branch only, and caps around 500 contributors, so contributors who only appear via squash-merged PRs under a different default-branch author, or on non-default branches, may not show up.
- Apps Script's 6-minute execution ceiling:
MAX_RUNTIME_MS(default 5 minutes) makesmain()stop gracefully before hitting that limit if you have a lot of repositories. Combined with batched flushing (BATCH_FLUSH_EVERY_N_REPOS), a run that gets cut short only loses progress since its last flush, not the whole run, and any repos not reached this run are simply reprocessed from the top of the list next time. - Rate limits are handled automatically. The script checks GitHub's rate-limit headers on every call and pauses if it gets close to the limit, retrying transient errors with backoff. You generally don't need to think about this unless you're running against a huge number of repositories.
This code was written and reviewed against the documented GitHub REST/GraphQL
and Apps Script APIs, and has since been run against a live account with
real fixes applied based on actual execution logs, raw API response dumps,
and hands-on debugging of specific data-quality issues (duplicate rows,
timezone drift, stalled traffic windows). If anything new comes up, check
the Errors/ExecutionLog sheets first, and use "Export raw API snapshot
(debug)" to see exactly what GitHub is returning before assuming it's a bug
in this script.