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


🚀 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]).

Last updated

Was this helpful?