Helpful Examples

Example to get unique items in a dataset. This examples shows how to get a unique list of payers found in healthcare dataset:

curl -H "X-GIGASHEET-TOKEN: $GIGASHEET_API_KEY" --url https://api.gigasheet.com/file/3197bbe9_99c6_46e6_80b6_afa008305ce9/filter -X POST -H "Content-type: application/json" --data '
{"startRow":0,"endRow":100,"rowGroupCols":[{"id":"AR","aggFunc":"row count","displayName":"Payer","field":"AR","groupFunc":""}],"valueCols":[{"id":"AR","aggFunc":"row count","displayName":"Payer","field":"AR"},{"id":"D","aggFunc":"row count","displayName":"Billing Code Type","field":"D"}],"filterModel":{"_aggregate_cnf_":[],"_cnf_":[]},"sortModel":[{"colId":"D","sort":"desc"}],"columnHighlights":{}}'

Example client state body for filtering a healthcare dataset to 99214 Smart Rate and showing median rate per NPI, highest rates at the top:

{
  "startRow": 0,
  "endRow": 10,
  "valueCols": [
    {
      "aggFunc": "median",
      "field": "I",
      "displayName": "Negotiated Rate"
    }
  ],
  "rowGroupCols": [
    {
      "field": "N",
      "displayName": "NPI"
    }
  ],
  "sortModel": [
    {
      "colId": "I",
      "sort": "desc"
    }
  ],
  "filterModel": {
    "_cnf_": [
      [
        {
          "colId": "D",
          "filter": [
            "99214"
          ],
          "type": "equalsAny",
          "isCaseSensitive": true,
          "filterType": "text"
        }
      ],
      [
        {
          "colId": "L",
          "filter": [
            "True"
          ],
          "type": "equalsAny",
          "isCaseSensitive": true,
          "filterType": "text"
        }
      ]
    ]
  }
}

Extracting Filtered Data from a View

This guide explains how to extract filtered data from a saved View in Gigasheet using the REST API.

Overview

Views in Gigasheet store filter and sort configurations. To extract data with those filters applied, you need to:

1. Get the View's metadata (including client_state ID)
2. Retrieve the filterModel from the client state
3. Fetch data using the filter endpoint with the filterModel

Prerequisites

• Gigasheet API token
• Sheet handle (UUID format: 5f776238_022d_46c4_b0f1_8f6cfff75132)
• View handle (UUID format: 1374a9c9_f514_4204_b416_1ac9acacc40e)

API Sequence

Step 1: Get View Metadata

Retrieve the View's metadata to get the client_state ID.

Bash

curl -s --request GET \
  --url "https://api.gigasheet.com/dataset/{sheetHandle}/views/{viewHandle}" \
  --header "X-GIGASHEET-TOKEN: {apiToken}"

Response:

JSON

{
  "name": "MSOs",
  "id": "1374a9c9_f514_4204_b416_1ac9acacc40e",
  "client_state": "dcabe23f_2979_4b76_b273_ea17dd21ee40",
  "sheet": "5f776238_022d_46c4_b0f1_8f6cfff75132"
}

Step 2: Get Client State with FilterModel

Use the client_state ID to retrieve the full client state, which contains the filterModel.

Bash

curl -s --request GET \
  --url "https://api.gigasheet.com/dataset/{sheetHandle}/{clientStateId}" \
  --header "X-GIGASHEET-TOKEN: {apiToken}"

Response (truncated):

JSON

{
  "ClientState": {
    "filterModel": {
      "_cnf_": [
        [
          {
            "colId": "M",
            "filter": ["United States"],
            "filterType": "text",
            "isCaseSensitive": false,
            "type": "equalsAny"
          }
        ],
        [
          {
            "colId": "BB",
            "filter": ["Hospitals and Health Care", "Mental Health Care"],
            "filterType": "text",
            "isCaseSensitive": false,
            "type": "equalsAny"
          }
        ]
      ]
    },
    "sortModel": [
      { "colId": "A", "sort": "asc" }
    ]
  },
  "FileRows": 376651536
}

Step 3: Fetch Filtered Data

Use the /file/{handle}/filter endpoint with the filterModel to fetch filtered rows.

Bash

curl -s --request POST \
  --url "https://api.gigasheet.com/file/{sheetHandle}/filter" \
  --header "Content-Type: application/json" \
  --header "X-GIGASHEET-TOKEN: {apiToken}" \
  --data '{
    "startRow": 0,
    "endRow": 500,
    "filterModel": {
      "_cnf_": [
        [{"colId": "M", "filter": ["United States"], "filterType": "text", "isCaseSensitive": false, "type": "equalsAny"}]
      ]
    },
    "AllowUnstableOrder": true
  }'

Response:

JSON

{
  "rows": [
    {
      "A": 12345,
      "B": "John Doe",
      "M": "United States",
      ...
    },
    ...
  ],
  "lastRow": 500
}

Pagination

To fetch more rows, adjust startRow and endRow:

Batch	startRow	endRow
1	0	500
2	500	1000
3	1000	1500

Note: The API supports fetching up to 10,000 rows per request.

FilterModel Structure

The filterModel uses a CNF (Conjunctive Normal Form) structure:

JSON

{
  "_cnf_": [
    // Each array is an AND group
    [
      // Conditions within an array are OR'd together
      { "colId": "M", "filter": ["United States"], "type": "equalsAny" }
    ],
    [
      // This group is AND'd with the above
      { "colId": "BB", "filter": ["Healthcare"], "type": "containsAny" }
    ]
  ]
}

Filter Types

Type	Description
equalsAny	Value equals any item in the filter array
containsAny	Value contains any item in the filter array
notEqual	Value does not equal the filter
greaterThan	Value is greater than the filter
lessThan	Value is less than the filter

Complete Example (Node.js)

JavaScript

const SHEET_HANDLE = '5f776238_022d_46c4_b0f1_8f6cfff75132';
const VIEW_HANDLE = '1374a9c9_f514_4204_b416_1ac9acacc40e';
const API_TOKEN = 'your-api-token';

async function extractViewData(rowCount = 500) {
  // Step 1: Get view metadata
  const viewRes = await fetch(
    `https://api.gigasheet.com/dataset/${SHEET_HANDLE}/views/${VIEW_HANDLE}`,
    { headers: { 'X-GIGASHEET-TOKEN': API_TOKEN } }
  );
  const viewData = await viewRes.json();
  const clientStateId = viewData.client_state;

  // Step 2: Get filterModel from client state
  const stateRes = await fetch(
    `https://api.gigasheet.com/dataset/${SHEET_HANDLE}/${clientStateId}`,
    { headers: { 'X-GIGASHEET-TOKEN': API_TOKEN } }
  );
  const stateData = await stateRes.json();
  const filterModel = stateData.ClientState?.filterModel || {};
  const sortModel = stateData.ClientState?.sortModel || [];

  // Step 3: Fetch filtered data with pagination
  const allRows = [];
  const batchSize = 500;
  
  for (let start = 0; start < rowCount; start += batchSize) {
    const end = Math.min(start + batchSize, rowCount);
    
    const dataRes = await fetch(
      `https://api.gigasheet.com/file/${SHEET_HANDLE}/filter`,
      {
        method: 'POST',
        headers: {
          'Content-Type': 'application/json',
          'X-GIGASHEET-TOKEN': API_TOKEN
        },
        body: JSON.stringify({
          startRow: start,
          endRow: end,
          filterModel,
          sortModel,
          AllowUnstableOrder: true
        })
      }
    );
    
    const data = await dataRes.json();
    allRows.push(...data.rows);
    
    // Stop if we've reached the end of available data
    if (data.rows.length < batchSize) break;
  }

  return allRows;
}

// Usage
extractViewData(1000).then(rows => {
  console.log(`Extracted ${rows.length} filtered rows`);
});

Error Handling

Status	Description
401	Invalid or missing API token
404	Sheet or View not found
429	Rate limit exceeded
500	Server error

Rate Limits

• Respect Gigasheet's rate limits
• Add delays between batch requests if needed
• Consider using exponential backoff for retries