Importing Insights Data into Google Sheets

This guide shows how to import insights data from the ClearFeed API into Google Sheets using Google Apps Script.

Prerequisites

• A ClearFeed API token (see Personal Access Token)

• A Google Sheet to import the data into

Overview

You will:

1. Open a Google Sheet and add a bound script

2. Use Apps Script to call the ClearFeed API

3. Populate the sheet with data from the API

Step-by-Step Instructions

1. Open the Script Editor

In your Google Sheet:

• Go to Extensions > Apps Script

2. Add the Script

Replace the default code with the following. The script below populates a simple report based on the example in the Insights API Documentation Documentation. It can be tailored to your requirements subsequently:

function fetchClearfeedInsights() {
  const token = 'your-api-token-here';  // Replace with your actual token
  const url = 'https://api.clearfeed.app/v1/rest/insights/query';

  const payload = {
    query: {
      measures: [
        "Requests.count",
        "Requests.first_response_time_avg"
      ],
      time_dimensions: [
        {
          dimension: "Requests.created_at",
          date_range: "Last week",
          granularity: "day"
        }
      ],
      dimensions: [
        "Requests.priority"
      ],
      filters: [
        {
          member: "Requests.state",
          operator: "equals",
          values: ["in_progress"]
        }
      ]
    }
  };

  const options = {
    method: "post",
    contentType: "application/json",
    headers: {
      Authorization: "Bearer " + token
    },
    payload: JSON.stringify(payload)
  };

  const response = UrlFetchApp.fetch(url, options);
  const result = JSON.parse(response.getContentText());

  const data = result.insights || [];

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.clear(); // Clear previous content

  if (data.length === 0) {
    sheet.getRange(1, 1).setValue("No data found");
    return;
  }

  // Extract headers
  const headers = Object.keys(data[0]);
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);

  // Write data rows
  const rows = data.map(rowObj => headers.map(h => rowObj[h]));
  sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);
}

3. Replace the Token

Replace the line:

const token = 'your-api-token-here';

With your actual ClearFeed API token.

4. Run the Script

• Save the script.

• Click the ▶️ Run button

• Authorize the script when prompted

Result

After running, your sheet will be populated with insights data like:

Requests.priority	Requests.created_at.day	Requests.count	Requests.first_response_time_avg
normal	2025-05-13T00:00:00.000	1	87.0000000000000000
high	2025-05-18T00:00:00.000	1	240.0000000000000000

Notes

• The script clears the sheet each time before writing new data

• If no data is returned, the message "No data found" is placed in cell A1

• The script writes all keys from the API response as column headers

Optional: Automate with a Trigger

To run this automatically (e.g. daily):

1. Open the script editor

2. Click the clock icon ("Triggers")

3. Add a trigger for fetchClearfeedInsights

4. Choose "Time-driven" and select your schedule

API Token Security Reminder

For convenience, this example uses a hardcoded API token. In production or multi-user environments, consider using secure methods such as PropertiesService or Google Secrets Manager.

Support

For help with API queries or customizations, reach out to ClearFeed Support ([email protected]).