> ## Documentation Index
> Fetch the complete documentation index at: https://www.text.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Automate reports exports with a Google Sheets template

> A Google Sheets template that automates Reports API exports via built-in Apps Script.

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.

<Card title="Make template copy" icon="table" href="https://docs.google.com/spreadsheets/d/1mIINa7p4rukEVKjpAK1TzarZ_6GEt9uJGz7nVJVGJU8/edit?usp=sharing" horizontal>
  Opens in Google Sheets
</Card>

* **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](https://accounts.livechat.com/signup?landing_page=https%3A%2F%2Fwww.text.com%2F\&client_id=fc7bf1555adb6d3d2a441f10fd5b6eb5\&redirect_uri=https%3A%2F%2Fwww.text.com%2Fapp\&response_type=token\&source_type=website\&source_id=header-signup\&source_url=https%3A%2F%2Fwww.text.com%2F) 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](#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:

1. Reads your settings from the Config tab.
2. Calls the Reports API over HTTPS, once per report, using your token for authentication.
3. Writes each response into its data tab.
4. 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

<Steps>
  <Step title="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.
  </Step>

  <Step title="Create a personal access token">
    Open Text and go to **Settings → API access → [Personal access
    tokens](https://www.text.com/app/settings/integrations/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.
  </Step>

  <Step title="Connect the sheet">
    On the Config tab, click **Set API token**.

    <Note>
      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.
    </Note>

    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.
  </Step>

  <Step title="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`.
  </Step>

  <Step title="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.

    <Tip>
      You can use **Load sample data** to preview the layout with fake numbers. No API calls will be made with that action.
    </Tip>
  </Step>
</Steps>

## Token storage and sharing

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.

<Warning>
  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.
</Warning>

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:

```javascript theme={null}
{
  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`.

<Info>
  **More chart options exist than shown here.** See `colors`, `cols`, and
  `maxRows` in the `ratings` and `tags` entries in `Reports.gs`.
</Info>

***

**Keep exploring**

<p>
  <Icon icon="file" /> [Personal access
  tokens](/docs/authentication/personal-access-tokens)

  <br />

  <Icon icon="file" /> [Reports API reference](/docs/api/reports/v3.6)
</p>