Runs a GigaScript program against a sheet and returns the matching rows as JSON, along with a link that opens the sheet showing the same results. GigaScript is a small query language; its syntax and behaviour are documented below. Errors are returned verbatim from the interpreter so that they can be acted on directly.

GigaScript reference

GigaScript is a small query language for Gigasheet sheets. A program is a list of statements
separated by ;, ending in DISPLAY:

FILTER Billing Code = 99214; LIMIT 25; DISPLAY

Run it with POST /dataset/{handle}/query, sending {"query": "<program>"}. Every request
needs an API token in X-GIGASHEET-TOKEN, even for a public sheet; without one the response is
a 403.

Each request is independent. The sheet is reset before every program runs, so nothing carries
over from an earlier request, and a program never modifies the sheet.

The language is deliberately forgiving of the ways a language model tends to write queries, and
its error messages are written to be read and acted on. When a program is rejected, the response
body carries the interpreter's message unchanged — it will usually tell you exactly what to
write instead.

A worked example

curl -X POST "https://api.gigasheet.com/dataset/$HANDLE/query?count=true" \
  -H "X-GIGASHEET-TOKEN: $TOKEN" \
  -H 'Content-Type: application/json' \
  -d '{"query": "FILTER Billing Code = 99214; ROWGROUPBY NPI; AGGREGATE median(Negotiated Rate); SORT Negotiated Rate DESC; LIMIT 3; DISPLAY"}'

{
  "headers": ["NPI", "groupCount", "median(Negotiated Rate)"],
  "types":   ["String", "UInt64", "Float64"],
  "rows": [
    ["1234123456", 12, 340.60],
    ["1298765432", 4, 121.05],
    ["1555512345", 31, 34.60]
  ],
  "counts": {"sheet_rows": 2343434, "matching_rows": 1039, "matching_groups": 212},
  "permalink": "https://app.gigasheet.com/spreadsheet/rates.csv/<handle>#<version>"
}

headers, types and every entry in rows are the same length and in the same order, so
rows[n][i] is always the value of headers[i].

LIMIT applies to groups, so only 3 of the 212 matching groups are returned.

Statements

DISPLAY on its own returns the first rows of every column, which is the quickest way to learn
a sheet you have not seen before.

Column names are matched exactly, including case, and may contain spaces —
FILTER Linkedin Company Industry contains health is a single column named
Linkedin Company Industry. Sheets with two columns of the same name are rejected; call
POST /dataset/{handle}/rename-columns-to-unique first.

Filtering

Supported operators: =, ==, !=, >, >=, <, <=, contains, not contains,
starts with, ends with, like, in, not in, is null, is not null, is empty,
is not empty.

Case sensitivity depends on the operator. = and in match case exactly, on the assumption
that you know the value you are looking for. contains, not contains and like ignore case,
on the assumption that you are searching. So FILTER Region = west will not match West, but
FILTER Region contains west will.

Repeating FILTER means AND. There is no AND keyword — writing one is an error that tells
you to split the clause:

FILTER Status = Open; FILTER Revenue > 100000; DISPLAY

For OR, use one FILTER and join with OR:

FILTER Region = West OR Region = East; DISPLAY

Two separate FILTERs on the same column with = are rejected rather than silently returning
nothing, since AND-ing them can never match.

like treats % as a wildcard and matches as a regular expression.

Grouping and aggregation

Grouping returns one row per distinct value, and always includes a groupCount column:

ROWGROUPBY Region; DISPLAY

→ headers: ["Region", "groupCount"], ordered by count, descending.

That default ordering is why ROWGROUPBY without an AGGREGATE rejects any SORT — the
order is already decided. Either drop the SORT, or give it something else to sort by:

ROWGROUPBY Region; AGGREGATE sum(Revenue); SORT Revenue DESC; DISPLAY

→ headers: ["Region", "groupCount", "sum(Revenue)"]

Rules worth knowing:

• AGGREGATE requires ROWGROUPBY.
• Any AGGREGATE other than the default count() requires a SORT.
• The function call must be written func(col) or func() — AGGREGATE sum Revenue is an error.
• Available functions: sum, count, min, max, unique, avg, first, median, mode.
• sum, avg and median require a numeric column, and are rejected on text columns.
• Only one ROWGROUPBY is supported. To look inside a group, FILTER on its value instead.
• A column mentioned only in SORT gets aggregated too, with sum if it is numeric and
  mode otherwise, and appears in the results.
  ROWGROUPBY Region; AGGREGATE max(Close Date); SORT Revenue DESC; DISPLAY therefore returns
  a sum(Revenue) column as well as max(Close Date). Read the aggregation columns by name from
  headers, since they do not follow the order of the program.

Sorting by the group count is the default, so SORT count() DESC is accepted and ignored.
Because it is ignored, it does not satisfy the SORT that another AGGREGATE requires:
ROWGROUPBY Region; AGGREGATE median(Revenue); SORT count() DESC; DISPLAY is rejected. Sort on
the aggregated column instead, as in SORT Revenue DESC.
SORT count() ASC is an error — to sort ascending by a count, aggregate a different column with
count(col) and sort on that.

A grouped result holds only the first LIMIT groups (10 by default), in sort order. The
group you are looking for may not be among them. To see a specific group, FILTER on its value
rather than raising LIMIT. Add count=true to learn how many groups matched.

Results

• Ungrouped results always begin with the row number column, named #, of type UInt64.
• Grouped results are the group column, then groupCount, then one column per aggregation.
• types are storage types with the nullability wrapper removed. Any value may be null
  regardless of its type.
• Integers too large for JSON to represent exactly (beyond 2^53) are returned as strings, to
  avoid silently losing precision. A UInt64 column can therefore contain string values.

Parameters

Errors

A problem with your program is a 400 whose Message is the interpreter's own text. A 403
means the request had no API token, or the token cannot read the sheet. A 502 means the query
could not be compiled at all and is not something your program can fix.

Performance

• Do not SORT an ungrouped result on a large sheet. It sorts every matching row and can be
  very slow. FILTER down to the rows you need first, or group and sort the groups.
• Grouping is usually fine, including sorting the groups by an aggregate.
• A large LIMIT returns a large response slowly. Ask for only the rows you need.
• Run a few heavy queries at a time. Many at once (about eight or more) can back up the engine
  and cause timeouts. On a timeout, wait 20 to 60 seconds, then retry with fewer concurrent
  requests. DISPLAY alone is a cheap check that the engine is responding.

Limitations

• Grouped queries return a permalink, but opening it does not currently restore the grouping
  (GIGA-7647). The rows and counts in the response are correct either way.
• A statement beginning with # is treated as a comment and skipped. Because # is also the
  name of the row number column, a filter on it must not be the first thing in a statement.
• ; separates statements and so cannot appear inside a filter value.
• Pivots are not supported.