> ## 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.

# Automating the Reports API

> Step through a practical example of using the Reports API to retrieve analytics data and customize metrics.

The [Reports API](https://platform.text.com/docs/data-reporting) lets you extract analytics and historical data from Text, such as chat statistics, agent performance metrics, and trends. This is useful when creating custom reports, building dashboards, or integrating data with external tools. The API gives you raw access to the data to analyze it your way.

## When to use the Reports API

Use the Reports API in scenarios where you need to programmatically retrieve Text reports data for analysis or integration. For example:

* **Export data for ad-hoc analysis**: You can pull chat metrics (like total chats, first response time, chat ratings) on demand and load them into a spreadsheet or application for deeper analysis.
* **Automate regular reporting**: You can script automatic data pulls if you need to generate recurring reports (daily, weekly, monthly) or send scheduled updates.
* **Integrate with BI tools and databases**: The API lets you merge Text data with data from other sources. For instance, you can feed Text stats into Google Sheets, Tableau, or Looker Studio to build unified dashboards.

In short, use the Reports API whenever you need direct, flexible access to Text's reporting data outside of the standard UI for custom analysis or integration.

## What this guide covers

* Authenticating with the Reports API using a personal access token.
* Ad-hoc and automated data retrieval into Google Sheets through a Google Apps Script.
* Connect the API to Business Intelligence tools like Tableau Cloud and Looker Studio.

## Prerequisites

Before you begin, make sure you have the following:

* [Text account](https://www.text.com/) with reporting access (Team plan or higher).
* A personal access token — create one in Text under [Settings → API access → Personal access tokens](https://www.text.com/app/settings/integrations/api-access/personal-access-tokens).
* Basic familiarity with JSON and CSV.
* An account in the service you want to store the pulled data in (Google, Tableau, or Looker).

## Personal access token authentication

Before pulling any data, you need to authenticate with the API. The simple approach we will use in this guide is a personal access token (PAT).

To generate your PAT, 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 scopes needed for the Reports API endpoints you want to pull. For instance, the [Total Chats](/docs/api/reports/v3.6/chats/total-chats) endpoint requires the `reports_read` scope.

After creating your token, copy the token value, your Account ID, and the Base64 encoded token — you won't be able to view any of these again after closing the page.

Treat your PAT like a password. Do not expose it in client-side code or share it publicly. Use secure storage or environment variables where possible.

### Base64 token encoding

Scripts and external tools often require you to parse the token into a Base64 format, where the Account ID is combined with your personal access token: `<account_id>:<pat>`. The result is used in the HTTP Authorization header as **Basic** Authentication. Text already returns this encoded value after creating your token.

## Ad-hoc data retrieval to Google Sheets

One of the quickest ways to analyze Text data is to pull it into [Google Sheets](https://docs.google.com/spreadsheets/). Before you start, create an empty Google Sheet to hold your data.

### Apps Script

Google Apps Script allows you to use JavaScript to manipulate your spreadsheet and call external APIs.

To open the script editor, go to **Extensions > Apps Script** in your created sheet. Before you paste the script, add your personal access token as a script property: navigate to **Project Settings > Script Properties** and add a property named `base64token` with your encoded token as the value.

Below is a ready-to-use script that allows you to customize the data range, the Reports API endpoints you call, and column header names:

```js theme={null}
function pullTextReportData() {
  // Config
  const SHEET_NAME = "Text report";
  const DAYS_BACK = 30; // how many days back (including today)
  const METRIC_MAP = {
    Date: "__DATE__",
    "Average response time (s)": "chats/response_time.response_time",
    "Chatting duration": "chats/duration.agents_chatting_duration",
    "Chat count": "chats/total_chats.total",
    "Agent hours": "agents/availability.hours",
    "Bad rates": "chats/ratings.bad",
  };

  // Authorization
  const props = PropertiesService.getScriptProperties();
  const BASIC_AUTH = props.getProperty("base64token"); // base64 of "accountId:PAT"
  if (!BASIC_AUTH) throw new Error("Add token to Script properties.");
  const headers = {
    Authorization: "Basic " + BASIC_AUTH,
    "Content-Type": "application/json",
  };

  // Dates
  const today = new Date();
  const from = new Date(today);
  from.setDate(from.getDate() - (DAYS_BACK - 1));
  const fromISO = isoDay(from, "00:00:00Z");
  const toISO = isoDay(today, "23:59:59Z");
  const allDates = listDates(from, today);

  // Build metric requests
  const base = "https://api.livechatinc.com/v3.6/reports/";
  const metrics = Object.entries(METRIC_MAP)
    .filter(([_, path]) => path !== "__DATE__")
    .map(([col, path]) => {
      const [endpoint, ...pathParts] = path.split(".");
      return { col, endpoint, pathParts };
    });

  // Fetch each endpoint
  const body = JSON.stringify({
    distribution: "day",
    filters: { from: fromISO, to: toISO },
  });
  const byMetric = {};
  metrics.forEach((m) => {
    try {
      const resp = UrlFetchApp.fetch(base + m.endpoint, {
        method: "post",
        headers,
        payload: body,
        muteHttpExceptions: true,
      });
      if (resp.getResponseCode() !== 200) return;
      const json = JSON.parse(resp.getContentText());
      const recs = json && json.records ? json.records : {};
      byMetric[m.col] = {};
      Object.keys(recs).forEach((date) => {
        let v = recs[date];
        for (const k of m.pathParts) {
          if (v && typeof v === "object") v = v[k];
          else {
            v = undefined;
            break;
          }
        }
        if (v !== undefined) byMetric[m.col][date] = v;
      });
    } catch (e) {
      // skip on error; keep going
    }
  });

  // Build table
  const headersRow = Object.keys(METRIC_MAP);
  const rows = allDates.map((d) =>
    headersRow.map((h) => {
      if (METRIC_MAP[h] === "__DATE__") return d;
      const m = byMetric[h];
      return m && m[d] !== undefined ? m[d] : "";
    }),
  );

  // Write sheet
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName(SHEET_NAME) || ss.insertSheet(SHEET_NAME);
  sh.clearContents();
  sh.getRange(1, 1, 1, headersRow.length).setValues([headersRow]);
  if (rows.length)
    sh.getRange(2, 1, rows.length, headersRow.length).setValues(rows);
  sh.setFrozenRows(1);
  sh.autoResizeColumns(1, headersRow.length);

  function isoDay(d, tail) {
    const yyyy_mm_dd = new Date(d.getTime() - d.getTimezoneOffset() * 60000)
      .toISOString()
      .slice(0, 10);
    return `${yyyy_mm_dd}T${tail}`;
  }
  function listDates(start, end) {
    const out = [];
    const cur = new Date(
      Date.UTC(start.getUTCFullYear(), start.getUTCMonth(), start.getUTCDate()),
    );
    const stop = new Date(
      Date.UTC(end.getUTCFullYear(), end.getUTCMonth(), end.getUTCDate()),
    );
    while (cur <= stop) {
      const y = cur.getUTCFullYear(),
        m = String(cur.getUTCMonth() + 1).padStart(2, "0"),
        d = String(cur.getUTCDate()).padStart(2, "0");
      out.push(`${y}-${m}-${d}`);
      cur.setUTCDate(cur.getUTCDate() + 1);
    }
    return out;
  }
}
```

After adding this script, save it and run the `pullTextReportData` function in the Apps Script editor. Check your Google Sheet — you should see your headers and 30 rows of data.

### Script breakdown

* The script defines configuration values: the sheet name, number of days to go back, and a `METRIC_MAP` linking column names to API endpoints and JSON paths.
* It retrieves the Base64-encoded token from script properties for Basic Auth.
* It calculates the date range for the past 30 days, formatted as ISO timestamp strings.
* For each metric in `METRIC_MAP`, it calls the corresponding Reports API endpoint and stores the daily values in `byMetric`.
* It writes the resulting table to the Google Sheet, with a frozen header row and auto-resized columns.

#### Changing endpoints

The `METRIC_MAP` maps column headers to API endpoint paths:

```json theme={null}
'Chat count': 'chats/total_chats.total'
```

* `chats/total_chats` — the API endpoint (`/reports/chats/total_chats`)
* `.total` — the field in each daily record to extract

To add another property, for example continuous chats from the same endpoint:

```json theme={null}
'Continuous chat count': 'chats/total_chats.continuous'
```

#### Changing the data range

To change the data range, edit the `DAYS_BACK` value:

```javascript theme={null}
const DAYS_BACK = 30; // how many days back (including today)
```

### Setting a trigger

You can set an automatic trigger to update the data on a schedule. Go to **Triggers** in the Apps Script editor and create a new trigger for the `pullTextReportData` function. You can schedule it to run nightly, every X hours, or on sheet open.

### Automated no-code options

If you're not comfortable writing script code, add-ons like API Connector or Apipheny let you configure API calls via a simple interface in Google Sheets. You can also use services like Zapier to connect Text data to Google Sheets.

## Connecting the Sheet to Looker

1. Open **Looker Studio > Reports** and create a new report.
2. From the available Google Connectors, choose Google Sheets.
3. Find and select your sheet, choose the appropriate worksheet, and click **Add**.

The **Data** menu on the right will show all fields from your sheet to use in your Looker report.

## Connecting to Tableau

1. Open Tableau Cloud and navigate to **New > Virtual Connection**.
2. Select Google Drive from the available connectors and authorize the connection.
3. Find and select the sheet that holds your data, then click **Connect**.
4. Publish the connection and assign it to a project.

You can extend the setup to fit your team's needs — add more endpoints to `METRIC_MAP`, adjust the reporting window, or schedule updates more frequently.