Grist is a powerful alternative to spreadsheets with an API that’s friendly to integration. If you’re using Power BI and want to pull data directly from your Grist tables, here’s a reusable Power Query function that gets the job done.

The Function

This M function takes four parameters:

  • API_Key: your Grist API token
  • Team_URL: your team’s Grist base URL
  • Doc_ID: the document ID, get this from the document settings
  • Table_ID: the table name within that document

It builds the full API URL, calls the endpoint, and expands the returned records into a Power BI table.

// This function fetches and returns a table of records from a specific Grist document table.
// Parameters:
//   API_Key   - Your Grist API token as a text string (e.g., "abc123...")
//   Team_URL  - The base URL of your Grist team site (e.g., "https://docs.getgrist.com")
//   Doc_ID    - The ID of the document you want to access
//   Table_ID  - The ID of the table within that document
(
  API_Key as text,
  Team_URL as text,
  Doc_ID as text,
  Table_ID as text
) =>
let
  // Ensure no trailing slash in the team URL
  CleanTeamURL = Text.TrimEnd(Team_URL, "/"),

  // Build the full API endpoint URL
  Full_URL = CleanTeamURL & "/api/docs/" & Doc_ID & "/tables/" & Table_ID & "/records",

  // Define the request headers with authorization and expected content type
  RequestHeaders = [
    #"Authorization" = "Bearer " & API_Key,
    #"Accept" = "application/json"
  ],

  // Make the web request and parse the JSON response
  RecordsList = Json.Document(Web.Contents(Full_URL, [Headers = RequestHeaders])),

  // Extract the list of records from the JSON
  ExpandRecord = RecordsList[records],

  // Convert the list of records to a table
  ListToTable = Table.FromList(
    ExpandRecord,
    Splitter.SplitByNothing(),
    null,
    null,
    ExtraValues.Error
  ),

  // Expand the outer record structure (e.g., id, fields)
  ExpandCols = Table.ExpandRecordColumn(
    ListToTable,
    "Column1",
    Record.FieldNames(ListToTable{0}[Column1])
  ),

  // Expand the 'fields' record into actual table columns
  ExpandFields = Table.ExpandRecordColumn(
    ExpandCols,
    "fields",
    Record.FieldNames(ExpandCols{0}[fields])
  )

in
  ExpandFields

Function Notes

  • Text.TrimEnd ensures the base URL is consistent regardless of user input.
  • It dynamically extracts field names, making it robust against changes in your Grist schema.
  • All requests use standard bearer token authorization.
  • Ideal for use in Power BI reports that need to pull live data from Grist.

Questions

Can I use this function to access data from a Grist Desktop app?

When you open the Grist desktop app on Windows, it serves the api at localhost:47478. So yes, just make sure to do the following:

  1. The Grist desktop app is running serving the database you’re trying to access.
  2. Invoke the function with:
GetGristData("your_api_key", "http://localhost:47478/", "your_doc_id", "your_table_name")