Automate the syncing of Discourse queries to Google Sheets

Documentation Integrations
, ,
1

Sync Discourse Data Explorer queries to Google Sheets

:bookmark: This how-to guide explains how to automate importing Discourse Data Explorer query results into Google Sheets using Google Apps Script.

:person_raising_hand: Required user level: Administrator

Overview

By connecting Google Sheets to your Discourse site’s Data Explorer plugin, you can automatically pull query results on a schedule. This is useful for creating dashboards, tracking metrics, or sharing reports with team members who don’t have Discourse admin access.

Prerequisites

Before you begin, ensure you have:

  • The Data Explorer plugin enabled on your Discourse site
  • A saved Data Explorer query you want to sync
  • Admin access to your Discourse site
  • A Google account with access to Google Sheets

Step 1: Prepare Discourse

Get your query ID

  1. Navigate to your Discourse site’s Admin panel
  2. Go to Plugins → Data Explorer
  3. Open the query you want to sync
  4. Look at the URL in your browser’s address bar—it will look like .../queries/123. The number at the end is your query ID

Generate an API key

  1. Go to Admin → Advanced → API Keys

  2. Click New API Key

  3. Configure the key:

    • Description: Enter something descriptive like “Google Sheets Sync”
    • User Level: Select “Single User” and choose an admin user, or select “All Users”
    • Scope: Select “Granular”, then check run queries under the Data Explorer section

    :information_source: Using the granular “run queries” scope limits this API key to only running Data Explorer queries, which is more secure than using a global key.

  4. Click Save and copy the API key immediately—you won’t be able to see it again

For more details on API keys, see: Create and configure an API key

Step 2: Set up Google Apps Script

Google Apps Script includes UrlFetchApp as a built-in service—you don’t need to install anything. Simply type it into the code editor and the script engine recognizes it automatically.

  1. Open your Google Sheet
  2. Go to Extensions → Apps Script
  3. Delete any existing code in Code.gs and paste the following:
function syncDiscourseData() {
  // ============ CONFIGURATION ============
  const DISCOURSE_URL = "https://your-forum.com"; // Your Discourse URL (no trailing slash)
  const QUERY_ID = "123";                         // Your Data Explorer query ID
  const API_KEY = "your_api_key_here";            // Your API key
  const API_USERNAME = "system";                  // Username for API requests
  // ========================================
  
  const url = `${DISCOURSE_URL}/admin/plugins/explorer/queries/${QUERY_ID}/run.csv`;
  
  const options = {
    "method": "post",
    "headers": {
      "Api-Key": API_KEY,
      "Api-Username": API_USERNAME
    }
  };

  try {
    const response = UrlFetchApp.fetch(url, options);
    const csvData = response.getContentText();
    const data = Utilities.parseCsv(csvData);
    
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    
    // Clear existing data and write new data
    sheet.clear(); 
    sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
    
    // Add a "Last Updated" timestamp two columns after the data
    const timestampCell = sheet.getRange(1, data[0].length + 2);
    const now = new Date();
    timestampCell.setValue("Last Updated: " + Utilities.formatDate(now, Session.getScriptTimeZone(), "yyyy-MM-dd HH:mm:ss"));
    timestampCell.setFontWeight("bold");
    
    Logger.log("Successfully synced " + (data.length - 1) + " rows");
    
  } catch (e) {
    Logger.log("Error: " + e.toString());
  }
}
  1. Update the configuration values at the top of the script:
    • Replace https://your-forum.com with your Discourse URL
    • Replace 123 with your query ID
    • Replace your_api_key_here with your API key

Step 3: Run and authorize the script

  1. Click the Save icon (:floppy_disk:) and name your project (e.g., “Discourse Sync”)

  2. Click the Run button (:play_button:)

  3. A popup will appear asking for authorization:

    • Click Review Permissions
    • Select your Google account
    • If you see “Google hasn’t verified this app”, click Advanced → Go to [Project Name] (unsafe)
    • Click Allow

  4. Check your Google Sheet—the data should now appear

:bulb: If you encounter errors, click View → Logs in the Apps Script editor to see detailed error messages.

Step 4: Set up automated sync (optional)

To run the sync automatically on a schedule:

  1. In the Apps Script editor, click the Triggers icon (:one_o_clock:) in the left sidebar

  2. Click + Add Trigger (bottom right)

  3. Configure the trigger:

    • Function to run: syncDiscourseData
    • Event source: Time-driven
    • Type of time based trigger: Choose your preferred frequency (e.g., Day timer, Hour timer)
    • Time of day/interval: Select when you want the sync to run

  4. Click Save

Handling queries with parameters

If your Data Explorer query uses parameters, add them to the request payload:

const options = {
  "method": "post",
  "headers": {
    "Api-Key": API_KEY,
    "Api-Username": API_USERNAME
  },
  "payload": {
    "params": JSON.stringify({
      "start_date": "2024-01-01",
      "category_id": "5"
    })
  }
};

:warning: All parameter values must be strings, even for numeric parameters.

For more details on running parameterized queries, see: Run Data Explorer queries with the Discourse API

Handling large datasets

CSV exports default to a maximum of 10,000 rows. For larger datasets, implement pagination in your query using LIMIT and OFFSET parameters:

--[params]
-- integer :limit = 1000
-- integer :page = 0

SELECT *
FROM your_table
OFFSET :page * :limit
LIMIT :limit

Then modify your script to loop through pages until no more results are returned.

Troubleshooting

Issue Solution
403 Forbidden error Verify your API key has the “run queries” scope and the username has admin access
404 Not Found error Check that the query ID is correct and the query exists
Empty results Verify the query returns data when run directly in Data Explorer
Rate limiting errors Discourse limits Data Explorer API requests to 2 per 10 seconds by default. Add delays between requests if needed

Additional resources

Last edited by @MarkDoerr 2026-01-27T18:37:19Z

Last checked by @MarkDoerr 2026-01-27T18:31:42Z

Check documentPerform check on document:
2 Likes