Automate reports exports with a Google Sheets template

This guide is for teams who want data from multiple reports in one place, refreshed with a single action, without deploying separate infrastructure. By the end of this guide you’ll have your own Google Sheets template pulling live Reports API data. You’ll also understand the Apps Script code well enough to add new reports of your own.

What the template includes

The template is a Google Sheet with an Apps Script project behind it.

Make template copy

Opens in Google Sheets

Config — where you connect your account and set the reports configuration.
Total chats, Ratings, Tag usage — one tab per report, each a clean data table.
Dashboard — KPI cards and charts, rebuilt automatically from the data tabs every time you refresh.

Everything after the Config tab is generated automatically — you don’t build charts or formulas by hand.

Prerequisites

A Text account with permission to create personal access tokens.
A Google account to hold your copy of the sheet.

You don’t need coding experience to use this template as-is. Apps Script familiarity is only useful if you want to customize it — see the customize the code section.

How the template works

The Apps Script project inside the spreadsheet handles the report configuration and data pulls. When you click Refresh reports, the script:

Reads your settings from the Config tab.
Calls the Reports API over HTTPS, once per report, using your token for authentication.
Writes each response into its data tab.
Rebuilds the Dashboard tab from the fresh data.

There’s no infrastructure to run or maintain. Google executes the script, your browser only needs to be open to click the button.

Set up your template

Make your own copy

Open the template and choose File → Make a copy. Each copy of the file gets its own private Apps Script project.

Create a personal access token

Open Text and go to Settings → API access → Personal access tokens. Create a new token with the reports_read scope. Copy the Base64-encoded token shown on that screen — you’ll paste it in the next step.

Connect the sheet

On the Config tab, click Set API token.

The first time you do this, Google shows its standard permission screen for any script that calls an external service — this isn’t specific to the template.

To approve the permissions, click Advanced and approve the requested connection. Once done, paste your token into the dialog and save.When the connection is successful, you’ll see a Connected status in the Config tab.

Choose your date range

Still on the Config tab, set:

From / To — leave blank for the last 30 days, or pick specific dates.
Timezone — used to bucket time-based reports correctly.
Distribution — the time bucket for reports that support it, like Total chats and Ratings. Possible values: hour, day, month, year.

Refresh reports

Click Refresh reports, or use Reports API → Refresh all reports from the menu. Either one asks you to confirm first, since it replaces the data on every report tab. Each data tab fills in, and the Dashboard rebuilds with current KPI cards and charts. The Config tab shows the connection status and the time of the last refresh.

You can use Load sample data to preview the layout with fake numbers. No API calls will be made with that action.

Your personal access token is stored in Apps Script’s Script Properties for the project associated with your copy of the sheet. The Set API token dialog won’t show the value back to you once it’s saved — that’s a property of the dialog, not of the token’s visibility overall. The report tabs don’t update on their own — they show whatever data was last pulled during a refresh.

Treat edit access to your copy like you’d treat the token itself — anyone with edit access can see it by opening Extensions → Apps Script → Project Settings and use it to refresh the report data. Give everyone else Viewer access instead; that role doesn’t include the Apps Script menu.

Anyone who makes their own copy of the main template starts with no token set and connects their own account independently.

Customize the code

Open Extensions → Apps Script to see the project, organized into single-purpose files:

File	Responsibility
`Config.gs`	Constants shared by every file — sheet names, which Config-tab cell holds each setting, the timezone list, and the color theme.
`ReportsApi.gs`	The HTTP client. Builds the auth header from the stored token, posts to a report endpoint, and turns 401/403/429 and other failures into specific error messages.
`Reports.gs`	The report registry and the refresh logic. Each entry defines a report’s sheet name, API path, request body, headers, row parser, and chart — add a new report by appending one entry here. Refreshing loops over the registry, fetches each report, and rebuilds the dashboard.
`Sheets.gs`	Every read or write to the spreadsheet. Reads the Config tab’s settings, clears and redraws a report tab’s title, header, and data rows on refresh, and builds the guided Connect → Data range → Refresh layout the first time the Config tab is created.
`Dashboard.gs`	Rebuilds the Dashboard tab on every refresh. Reads the three data tabs, draws KPI cards, and places one chart per report in a fixed grid.
`Menu.gs`	Builds the “Reports API” menu on open and runs the token-setup flow: opening the dialog, saving the pasted token, and checking or clearing it afterward. Add new menu items here.
`SampleData.gs`	Fake sample data for the template. The numbers come from a deterministic formula, so the same cell always produces the same value and screenshots stay stable between runs — useful for previewing the template before anyone connects a real token.
`SetTokenDialog.html`	The modal for pasting a token.

To add a new report, edit Reports.gs. To change authentication or error handling, edit ReportsApi.gs. UI changes — new buttons, new menu entries — go in Menu.gs.

Add a new report

New reports are added declaratively, as one entry in reportRegistry_() inside Reports.gs. Each entry describes an endpoint and how to turn its response into rows:

{
  key: 'agents_performance',
  sheetName: 'Agent performance',
  path: '/agents/performance',                 // appended to REPORTS_API.baseUrl
  body: function (cfg) {
    return timeRangeBody_(cfg, /* withDistribution */ false);
  },
  headers: ['Agent', 'Chats handled', 'Avg first response time'],
  rows: function (json) {
    const records = (json && json.records) || {};
    return Object.keys(records).map(function (agent) {
      const r = records[agent] || {};
      return [agent, num_(r.chats_count), num_(r.first_response_time)];
    });
  },
  chart: { type: 'COLUMN', title: 'Chats handled per agent' }, // optional
}

The registry entry is picked up automatically by refreshAllReports(), gets its own tab (created if it doesn’t exist), and appears in the “Reports API” menu. For a one-click shortcut, add a matching line in Menu.gs.

More chart options exist than shown here. See colors, cols, and maxRows in the ratings and tags entries in Reports.gs.

Keep exploring

Personal access tokens
Reports API reference

​What the template includes

Make template copy

​Prerequisites

​How the template works

​Set up your template

​Token storage and sharing

​Customize the code

​Add a new report

What the template includes

Prerequisites

How the template works

Set up your template

Token storage and sharing

Customize the code

Add a new report