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; SORT Negotiated Rate DESC; LIMIT 25; DISPLAY ``` Run it with `POST /dataset/{handle}/query`, sending `{"query": ""}`. 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 ```bash 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; SORT Negotiated Rate DESC; LIMIT 3; DISPLAY"}' ``` ```json { "headers": ["#", "NPI", "Billing Code", "Negotiated Rate"], "types": ["UInt64", "String", "String", "Float64"], "rows": [ [1, "1234123456", "99214", 340.60], [2, "1298765432", "99214", 121.05], [3, "1555512345", "99214", 34.60] ], "counts": {"sheet_rows": 2343434, "matching_rows": 1039, "matching_groups": 0}, "permalink": "https://app.gigasheet.com/spreadsheet/rates.csv//" } ``` `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]`. ## Statements | Statement | Purpose | |---|---| | `DISPLAY` | Produce the result. Exactly one, and it must be last. | | `COLUMN ` | Show this column. Repeat to select several; omit to show all. | | `FILTER ` | Keep matching rows. Repeat to require all of them. | | `SORT ASC\|DESC` | Order the result. | | `ROWGROUPBY ` | Group rows by a column. | | `AGGREGATE ()` | Summarise a column within each group. | | `LIMIT ` | Return at most `n` rows, 1 to 10000. Defaults to 10. | | `HELP` | Return a one-paragraph summary of the language. | `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 `FILTER`s 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; SORT Revenue DESC; DISPLAY` therefore returns `["Region", "groupCount", "sum(Revenue)"]`. Sorting by the group count is the default, so `SORT count() DESC` is accepted and ignored. `SORT count() ASC` is an error — to sort ascending by a count, aggregate a different column with `count(col)` and sort on that. ## 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 | Parameter | Default | Effect | |---|---|---| | `count` | `false` | Add `counts` with `sheet_rows`, `matching_rows` and `matching_groups`. `matching_groups` is `0` when the query does not group. | | `show_state` | `false` | Add `client_state`, the compiled query. Useful when a program runs but does not do what you meant. | | `omit_rows` | `false` | Return only `headers`, `types` and `permalink`, running no query. Cheap way to inspect a sheet's schema. Cannot be combined with `count`. | ## Errors A problem with your program is a `400` whose `Message` is the interpreter's own text. A `502` means the query could not be compiled at all and is not something your program can fix. ## 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.

200OK

400Bad Request

500Internal Server Error

503Service Unavailable