Skip to main content

Datasheets

The data behind a report's charts and tables. A datasheet reads one source, an uploaded file, a dashboard tab of a connection or a range of a spreadsheet, and keeps a copy of what it read; the report's charts and tables are drawn from those copies. A datasheet can also connect a Snowflake, Databricks or BigQuery table, or a Looker explore, Power BI semantic model or Tableau data source, which the slides agent queries in the warehouse or BI tool for each chart and table it builds.

Add sources with addReportDatasheets, see when each was last read and whether it failed with listReportDatasheets, and read the table behind a chart with getReportDatasheetData.

Get the data guide

GET https://api.rollstack.com/public/v1/data-guide

Read this once before you add or shape the data of a report: how to send a CSV or an Excel workbook and pick the range of a sheet, how to check a figure with queryReportData, how to shape data with previewReportDatasheetChanges, renderReportSlides and saveReportDatasheetChanges, and the query model those changes write. It needs no report. With stepFunctions, it also answers the exact params of those step functions, for the steps after a join.

Requires the slide_decks:read scope.

Operation ID: getReportDataGuide.

Query parameters

  • stepFunctions (string or array of string): Step functions whose exact params to answer with the guide, for the steps after a join: repeat the parameter for each, as in ?stepFunctions=filter&stepFunctions=sort.

Response 200

Success

application/json

  • guide (string, required): The guide, in Markdown: how to send a CSV or a workbook, pick a range of a sheet, check a figure with a query, shape data with datasheet changes and save it.
  • stepFunctions (object): Each step function asked for, with its label and the JSON Schema of its params.

List report datasheets

GET https://api.rollstack.com/public/v1/reports/{id}/datasheets

The datasheets a report's charts and tables are built from, with their source and when each was last read. An entry whose copyOf is set repeats that datasheet for one more element of a slide: it shows the same data and is read together with it, so count each piece of data once by skipping the entries with a copyOf. Only a datasheet a slide shows has a lastBuiltAt.

Requires the slide_decks:read scope.

Operation ID: listReportDatasheets.

Path parameters

  • id (string, required): The report to act on, by its id from listReports or createReport. Only reports whose type is SlideDeck work here: another type, such as a Google Slides presentation, returns 400. You can also copy it from the report's link in Rollstack: it's the part right after /slides/.

Response 200

Success

application/json: array of object

  • id (string, required): Unique identifier for the datasheet. Pass it as datasheetId to getReportDatasheetData to read its table (a data datasheet only), in datasheetIds on startReportRun to update only some datasheets, or in datasheetIds on sendReportMessage for the slides agent to use it.
  • kind (string, required): What the datasheet holds. data: a table, which getReportDatasheetData reads. picture: a picture of a dashboard, tab or chart, such as a dashboard added with addReportDatasheets, with no table to read. The slides agent can still use it when you pass it in datasheetIds on sendReportMessage, and addReportDashboardElements adds the charts of a Tableau or Power BI one as data. One of data, picture.
  • name (string, required): The datasheet's name, as shown in the report's editor, where editors can rename it. A datasheet added with addReportDatasheets starts out named after its uploaded file, its dashboard or dashboard tab, or its range, such as A1:F20.
  • sourceType (string or null, required): The kind of source the datasheet reads; when it reads several, the kind of the first.

    • Looker, Tableau, Metabase, PowerBI, Lightdash, Omni, Sigma: data from that BI tool, such as a dashboard, a tab or one of its charts.
    • Salesforce: a Salesforce report.
    • Snowflake, Databricks, BigQuery: a query run on that warehouse connection, or a table connected for the slides agent.
    • GoogleSheet, MicrosoftExcel: a sheet or range of a Google Sheets or Excel file.
    • CsvUpload: a CSV file uploaded with createDataset or in the report's editor.
    • XlsxUpload: a sheet or range of an Excel workbook uploaded to Rollstack.
    • ManualTable: values typed into the report's editor, or the data of a chart imported from PowerPoint.

    Datasheets don't read GoogleExcel, RollstackVariable or Dbt sources, so expect none of them here. null when the datasheet reads no source, or Rollstack no longer has a record of its first source; deleting an uploaded file or a connection doesn't make it null.

    One of GoogleSheet, GoogleExcel, MicrosoftExcel, Tableau, Salesforce, Metabase, PowerBI, Looker, RollstackVariable, Lightdash, Omni, Sigma, Snowflake, Databricks, BigQuery, CsvUpload, XlsxUpload, ManualTable, Dbt.

  • sourceName (string or null, required): The name of what the datasheet reads, such as a dashboard tab, a range like A1:F20, or an uploaded file. When it reads several sources, the first one's. null in the same cases as sourceType.
  • datasetId (string or null, required): The uploaded file the datasheet reads, by the id replaceDataset takes, when its sourceType is CsvUpload or XlsxUpload. Replace the file, then start a run of the report, to bring new data into the slides drawn from it. null for every other source.
  • lastReadAt (string (date-time) or null, required): When Rollstack last read the datasheet's source successfully and stored a fresh copy of its data; for a datasheet that reads several sources, the latest such read of any of them. null until a read succeeds.
  • lastBuiltAt (string (date-time) or null, required): When the datasheet's data was last applied to the report's slides, updating the charts, tables and pictures drawn from it. null when its current data hasn't been applied to the slides yet, for example while nothing in the report uses the datasheet.
  • copyOf (string or null, required): When this datasheet is a copy of an earlier one of the report, with the same sources, steps and filter values, that one's id. An element has a datasheet of its own, so Rollstack makes such a copy for each further element that shows a datasheet. Copies are read from the source together, once, and show the same data: read and change the original. null for a datasheet that is no copy.
  • error (string or null, required): Why the datasheet's latest update failed, while reading its source or applying its data to the slides, as a human-readable message. The slides keep showing the datasheet's last good data until an update succeeds. null when the latest update worked.

Add sources to report

POST https://api.rollstack.com/public/v1/reports/{id}/datasheets

Adds sources to a report as datasheets, and answers their ids in the order of the sources. A source is a CSV file uploaded with createDataset, or a dashboard of a Looker, Metabase, Omni, Lightdash, Sigma, Tableau or Power BI connection: find it with searchDashboards, confirm it with the user, optionally check its tabs and charts with getDashboard, and add it by its connection, id and tabId from getDashboard's tabs, or from searchDashboards for a Sigma workbook found by a link (else the tab it opens on, a Power BI report's or Sigma workbook's first page). A dashboard comes in as one picture of the tab, read with the user's access to the connection, from which the slides agent can add its charts. To draw a Tableau or Power BI dashboard's figures yourself, list its charts with getReportDatasheet on that picture and add the ones you need as data with addReportDashboardElements. A source can also be a tabular or summary report of a Salesforce connection, found with searchDashboards and added by its connection and reportId: it comes in as a table of its rows, read with the user's access to the connection; a matrix or joined report is rejected. A source can also be a table or view of a Snowflake, Databricks or BigQuery connection, found with searchTables and added by its connection, database, schema and table: it stays in the warehouse, where the slides agent queries it with SQL with the user's access to the connection, as its default user when one is set, and the queries the agent saves run again on every update. A source can also be a range of a Google Sheets or Excel file the user added in the app: find the file with searchSpreadsheets, take the sheet's id from getSpreadsheet, and pass the range inside the sheet in A1 notation, like A1:F20. A range comes in as a table, read with the user's own Google or Microsoft account on every run. A source can also be a sheet or a range of an uploaded Excel workbook, whose sheets getDataset lists. Pass the ids as datasheetIds on sendReportMessage for the slides agent to build slides from them. Only the report's editors may.

Requires the slide_decks:write scope.

Operation ID: addReportDatasheets.

Path parameters

  • id (string, required): The report to act on, by its id from listReports or createReport. Only reports whose type is SlideDeck work here: another type, such as a Google Slides presentation, returns 400. You can also copy it from the report's link in Rollstack: it's the part right after /slides/.

Headers

  • Idempotency-Key (string): A unique value, such as a UUID, that makes retrying this call safe. A retry with the same key within 24 hours answers the first call's result instead of running it again. Use a new key for each new call. From 1 to 255 printable ASCII characters.

Request body, required

application/json

  • sources (array of object, required): The data to add to the report, each source becoming one of its datasheets: an uploaded CSV file (datasetId), a sheet or range of an uploaded workbook (datasetId, sheetId and optionally range), a dashboard tab (connectionType, connectionId, dashboardId and optionally tabId), a Salesforce report (connectionType, connectionId and reportId), a Snowflake, Databricks or BigQuery table (connectionType, connectionId, database, schema and table), a Looker explore, Power BI semantic model or Tableau data source (connectionType, connectionId and modelId), or a range of a spreadsheet (spreadsheetId, sheetId and range). An uploaded file, a Salesforce report or a range comes in as a table, a warehouse table stays in the warehouse and a model in its BI tool for the slides agent to query, and a dashboard tab comes in as a picture of the tab. If any source can't be added, none is. From 1 to 100 sources.
    • Option 1
      • datasetId (string, required): The uploaded file to add: the id returned by createDataset. A CSV file comes in whole, as one table named after the file. An Excel workbook comes in one sheet at a time, so pass sheetId with it. It can be any uploaded file of the report's organization, including one another member uploaded. Example: b2c4d6e8-f0a1-4b3c-9d5e-7f9a1b3c5d7e.
      • sheetId (string): For an uploaded workbook, the sheet to read: one of the sheets[].id values from getDataset. Required for a workbook, and rejected for a CSV file.
      • range (string): For an uploaded workbook, the cells to read from the sheet, in A1 notation without the sheet's name, such as A3:H40. The datasheet contains them as a table and is named after the range. Start it at the header row so the columns take its names; without a header, they are named Column 1, Column 2 and so on. Omit it to read everything the sheet uses, in a datasheet named after the sheet: do that only when the sheet's first row is the header. Whole columns such as A:F aren't accepted. Example: A3:H40.
    • Option 2
      • connectionType (string, required): The type of the connection the dashboard is on: Looker, Metabase, Omni, Lightdash, Sigma, Tableau or PowerBI. It must match the connection's type in listConnections. One of Looker, Metabase, Omni, Lightdash, Tableau, PowerBI, Sigma.
      • connectionId (string, required): The connection the dashboard is on: the id of a connection from listConnections. You must have access to the connection in Rollstack. Example: c8e0a2b4-d6f8-4a0b-8c2d-4e6f8a0b2c4d.
      • dashboardId (string, required): The dashboard to add: the id of a dashboard from searchDashboards on this connection. For Tableau and Sigma, it is a workbook, and for Power BI, a report. Example: 128.
      • tabId (string): The tab to add: one of the tabs[].id values from getDashboard, which for Tableau are a workbook's views, for Power BI a report's pages and for Sigma a workbook's pages, or the tabId of a Sigma workbook searchDashboards found by a link. The datasheet is a picture of that tab, named after it. If omitted, Rollstack adds the tab the dashboard opens on (the first view or page for Tableau, Power BI and Sigma), or, for a dashboard without tabs such as an Omni dashboard, the whole dashboard, named after it. A tab the dashboard doesn't have is rejected. Example: 12-overview.
    • When connectionType is Salesforce
      • connectionType (Salesforce, required): Salesforce. It must match the connection's type in listConnections.
      • connectionId (string, required): The Salesforce connection the report is on: the id of a connection from listConnections. You must have access to the connection in Rollstack. Example: c8e0a2b4-d6f8-4a0b-8c2d-4e6f8a0b2c4d.
      • reportId (string, required): The report to add: the id of a tabular or summary report from searchDashboards on this connection. The datasheet contains the report's rows as a table, a summary report's with its groupings as their first columns, and is named after the report. A matrix or joined report is rejected. Example: 00O5e000008abcdEAA.
    • Option 4
      • connectionType (string, required): The type of the connection the table is on: Snowflake, Databricks or BigQuery. It must match the connection's type in listConnections. One of Snowflake, Databricks, BigQuery.
      • connectionId (string, required): The data warehouse connection the table is on: the id of a connection from listConnections. You must have your own access to the connection in Rollstack. Example: c8e0a2b4-d6f8-4a0b-8c2d-4e6f8a0b2c4d.
      • database (string, required): The database the table is in, as database in searchTables.
      • schema (string, required): The schema the table is in, as schema in searchTables.
      • table (string, required): The table or view to add, as table in searchTables. The datasheet is named database.schema.table, and the slides agent queries it in the warehouse with SQL over all its rows. Example: ORDERS.
    • Option 5
      • connectionType (string, required): The type of the connection the model is on: Looker, PowerBI or Tableau. It must match the connection's type in listConnections. One of Looker, PowerBI, Tableau.
      • connectionId (string, required): The connection the model is on: the id of a connection from listConnections. You must have access to the connection in Rollstack, and for Tableau your own Tableau user on it. Example: c8e0a2b4-d6f8-4a0b-8c2d-4e6f8a0b2c4d.
      • modelId (string, required): The model to add: the id of a Looker explore, Power BI semantic model or Tableau data source from searchModels on this connection. The datasheet is named after it and holds no rows: pass its id as datasheetIds on sendReportMessage, and the slides agent queries the model in its BI tool for the data its slides need. A model the connection doesn't have is rejected. Example: ecommerce.order_items.
    • Option 6
      • spreadsheetId (string, required): The Google Sheets or Excel file to read from: the id of a file from searchSpreadsheets. It must be a file you added in the app yourself. Example: e6f8a0b2-c4d6-4e8f-9a0b-2c4d6e8f0a1b.
      • sheetId (string, required): The sheet the range is on: one of the sheets[].id values from getSpreadsheet. A sheet the file doesn't have is rejected. Example: 0.
      • range (string, required): The cells to read from the sheet, in A1 notation without the sheet's name, such as A1:F20, or B2 for a single cell. The datasheet contains them as a table and is named after the range. Start the range at the header row so the columns take its names; without a header, they are named Column 1, Column 2 and so on. Hidden rows and columns are left out, and whole columns such as A:F aren't accepted. Example: A1:F20.

Response 201

Success

application/json

  • datasheetIds (array of string, required): Unique identifiers of the new datasheets, one per source, in the same order as sources. Pass them as datasheetIds on sendReportMessage for the slides agent to build slides from them.

Get a datasheet

GET https://api.rollstack.com/public/v1/reports/{id}/datasheets/{datasheetId}

What a datasheet's data is written as: its query, in the shape set-query takes, its one SQL statement, or its join and the steps after it, and its formats. Read it before you change a datasheet with previewReportDatasheetChanges: set-query replaces the whole query, so start from this one. For the picture of a Tableau view or Power BI page added with addReportDatasheets, it also lists the charts on it, which addReportDashboardElements adds as data. Only the report's editors may.

Requires the slide_decks:read scope.

Operation ID: getReportDatasheet.

Path parameters

  • id (string, required): The report to act on, by its id from listReports or createReport. Only reports whose type is SlideDeck work here: another type, such as a Google Slides presentation, returns 400. You can also copy it from the report's link in Rollstack: it's the part right after /slides/.
  • datasheetId (string, required): One of this report's datasheets, by its id in listReportDatasheets.

Response 200

Success

application/json

  • id (string, required): The datasheet's id.
  • name (string, required): The datasheet's name, as the report's editor shows it.
  • kind (string, required): data for a table, picture for an image, which has no query. One of data, picture.
  • elements (array of object or null, required): For the picture of a whole Tableau view or Power BI page, the charts on it, which addReportDashboardElements adds as data or pictures. null for any other datasheet. Listing them takes the dashboard's picture when it never had one, which can take a minute: past the wait the call answers that the source is still being read, so call it again.
    • ref (string, required): What to pass as ref to addReportDashboardElements to add this chart.
    • name (string, required): The chart's name on the dashboard.
    • type (string, required): Its kind in the dashboard's tool, such as a Tableau worksheet or legend.
    • reads (array of string, required): What it can be added as: data for its table, picture for its image.
    • datasheetIds (array of string, required): The report's datasheets that already read it with the dashboard's filters, to use instead of adding it again.
  • elementsNote (string or null, required): Why elements is empty or partial, such as charts not listed yet, or null.
  • query (object or null, required): Its data as a query, the Edit data editor's model, in the shape set-query takes: change what you need and send it whole. null when its data is SQL or a join.
  • sql (string or null, required): Its data as one SQL statement over its reads, or null.
  • ops (array of object or null, required): For a datasheet that joins its reads: the join and the steps after it, each by the ref set-ops keeps it by. null for any other.
  • formats (array of object, required): How its columns' numbers are shown: a numfmt pattern for each output column.
    • column (string, required)
    • format (string, required)

Remove datasheet from report

DELETE https://api.rollstack.com/public/v1/reports/{id}/datasheets/{datasheetId}

Removes a datasheet the report's slides no longer show, such as a source that was added and not used, or one whose read keeps failing. A datasheet a chart, table, picture or sentence still shows is refused: change or remove that slide first. The source itself, such as the dashboard or the uploaded file, is not touched. Only the report's editors may.

Requires the slide_decks:write scope.

Operation ID: removeReportDatasheet.

Path parameters

  • id (string, required): The report to act on, by its id from listReports or createReport. Only reports whose type is SlideDeck work here: another type, such as a Google Slides presentation, returns 400. You can also copy it from the report's link in Rollstack: it's the part right after /slides/.
  • datasheetId (string, required): One of this report's datasheets, by its id in listReportDatasheets.

Headers

  • Idempotency-Key (string): A unique value, such as a UUID, that makes retrying this call safe. A retry with the same key within 24 hours answers the first call's result instead of running it again. Use a new key for each new call. From 1 to 255 printable ASCII characters.

Response 200

Success

application/json

  • removed (array of string, required): The datasheet removed.

Get datasheet data

GET https://api.rollstack.com/public/v1/reports/{id}/datasheets/{datasheetId}/data

The table a report's chart is drawn from: the datasheet's steps run over the copies stored at its sources' last reads; the source is never queried. Only the report's editors may: the table was read with an editor's credentials, can hold columns and rows the slides never show, and skips the source's row-level security. Fails for a picture datasheet (its kind in listReportDatasheets), which has no table. Also fails until every source it reads has been read, after a source's latest read failed, and once a stored copy expires, 30 days after its read. At most 30 reads a minute per user.

Requires the slide_decks:read scope.

Operation ID: getReportDatasheetData.

Path parameters

  • id (string, required): The report to act on, by its id from listReports or createReport. Only reports whose type is SlideDeck work here: another type, such as a Google Slides presentation, returns 400. You can also copy it from the report's link in Rollstack: it's the part right after /slides/.
  • datasheetId (string, required): The datasheet whose table to read: an id from listReportDatasheets, or one of the datasheetIds returned by addReportDatasheets. It must be one of this report's datasheets. A datasheet that reads a picture, such as a dashboard tab added with addReportDatasheets, has no table, so the request fails: add the tab's charts as data with addReportDashboardElements.

Query parameters

  • limit (integer): The maximum number of rows to return, starting from the first row of the table. If the table has more, truncated is true. Can range from 1 to 1000. Defaults to 200.

Response 200

Success

application/json

  • columns (array of object, required): The columns of the table, in the same order as the cells of each row in rows.
    • name (string, required): The column's name in the datasheet's table, which the report's charts and tables use to refer to it. Unless the datasheet renames it, a column read from an uploaded file or a spreadsheet range is named after its header; a blank header becomes Column N, after the column's position, and a repeated one gets a _2, _3 suffix.
    • label (string): The column's display name, when its source gives one, such as a field's label in Looker or Metabase. Charts and tables drawn from the datasheet show it instead of name, unless a table renames the column. Omitted when the column has none.
    • type (string, required): What the column's cells contain: number for numbers, date for dates and times, string for text, and boolean for true or false. Any cell can also be null when it is empty. One of number, date, string, boolean.
  • rows (array of array of string or number or boolean or null, required): The rows of the table, starting from the first, at most limit of them. Each row is an array of cells in the order of columns. An empty cell is null, and a date cell is an ISO 8601 string in UTC, at midnight when the source gives a day without a time.
  • stacking (string): How the chart in the datasheet's source stacks its series, such as a Looker, Metabase or Power BI chart, or a chart imported from PowerPoint. Area, bar and column charts drawn from the datasheet stack the same way, unless their own Stacking setting overrides it. none doesn't stack the series, stacked piles them on top of one another, and percent piles them up to 100% so each shows its share of the total. Omitted when the source sets no stacking, as with an uploaded file or a spreadsheet range; charts then don't stack unless set to. One of none, stacked, percent.
  • totalRow (true)
  • subtotals (object): When the datasheet's query adds subtotals: rowFields are the columns its rows are grouped by, outermost first, and levels has one number per row in rows, how many of those columns the row is grouped by. A row grouped by fewer than all of them is a subtotal above its group's rows, and 0 is the total row. Charts leave those rows out. Omitted without subtotals.
    • rowFields (array of string, required)
    • levels (array of integer, required)
  • truncated (boolean, required): Whether the table has more rows than limit, so rows doesn't contain all of them. Raise limit to get more, up to the first 1000 rows.
  • readAt (string (date-time), required): When Rollstack read the source data the table is built from. For a datasheet that reads several sources, the oldest of those reads.

Add dashboard charts to report

POST https://api.rollstack.com/public/v1/reports/{id}/datasheets/{datasheetId}/elements

Adds charts of a Tableau view or Power BI page the report holds as a picture, each as a datasheet of its own: its data, to read with getReportDatasheetData or queryReportData and draw on a slide, its picture, or both. Pass the picture's datasheet id, from addReportDatasheets or listReportDatasheets, and the refs of the charts getReportDatasheet lists for it. Each chart is read with the dashboard's filters and the user's access to the connection, and a chart the report already reads that way is reused. Reading a dashboard can take a minute: past the wait the call answers that the source is still being read, so send the same call again, which reuses what the first one added. It runs one at a time with your queryReportData, previewReportDatasheetChanges and saveReportDatasheetChanges calls: send it once they answer. Only the report's editors may.

Requires the slide_decks:write scope.

Operation ID: addReportDashboardElements.

Path parameters

  • id (string, required): The report to act on, by its id from listReports or createReport. Only reports whose type is SlideDeck work here: another type, such as a Google Slides presentation, returns 400. You can also copy it from the report's link in Rollstack: it's the part right after /slides/.
  • datasheetId (string, required): One of this report's datasheets, by its id in listReportDatasheets.

Headers

  • Idempotency-Key (string): A unique value, such as a UUID, that makes retrying this call safe. A retry with the same key within 24 hours answers the first call's result instead of running it again. Use a new key for each new call. From 1 to 255 printable ASCII characters.

Request body, required

application/json

  • elements (array of object, required): The charts to add, only the ones the slides need, at most 12 a call.
    • ref (string, required): A chart's ref from the elements getReportDatasheet lists for this dashboard. For a table Tableau builds from several worksheets side by side, the refs of those worksheets joined by commas, like 12,15,18, add it as one.
    • as (string, required): data for its table, to read with getReportDatasheetData and draw as a chart, table or figure on a slide; picture for its image, to place on a slide as it looks; both for the two. Ask only for a read the element lists in its reads. One of data, picture, both.

Response 200

Success

application/json

  • elements (array of object, required): Each chart you asked for, in the order of elements.
    • ref (string, required): The ref you passed.
    • name (string or null, required): The chart's name on the dashboard, null when it has none.
    • dataDatasheetId (string or null, required): The datasheet of its table, a new one or the one the report already had for this chart with the dashboard's filters. null when you asked only for its picture, or when it was refused.
    • pictureDatasheetId (string or null, required): The datasheet of its picture, null when you asked only for its data, or when it was refused.
    • error (string or null, required): Why it was refused, or why its data or picture could not be read, null when nothing went wrong.

Preview datasheet changes

POST https://api.rollstack.com/public/v1/reports/{id}/datasheets/changes/preview

Tries changes to a report's datasheets, to shape data before it goes on a slide: a new datasheet, a query, steps after a join, a read type, an unpivot. It runs every datasheet the changes touch over the data stored at the sources' last reads and saves nothing, not even a new datasheet. Answers each datasheet's columns and five sample rows, the ids a new datasheet gets, and the revisions to send to saveReportDatasheetChanges. A change that fails answers valid false with its reason, naming the datasheet and step: fix it and preview again. Draw a slide with the same changes by sending them to renderReportSlides. Only the report's editors may. Your previews run one at a time, together with your queryReportData and saveReportDatasheetChanges calls and the renderReportSlides calls that carry datasheetChanges: send the next only once this one answers, never several at once, and try several datasheets' changes in one preview. At most 30 previews and queryReportData calls a minute per user, counted together.

Requires the slide_decks:write scope.

Operation ID: previewReportDatasheetChanges.

Path parameters

  • id (string, required): The report to act on, by its id from listReports or createReport. Only reports whose type is SlideDeck work here: another type, such as a Google Slides presentation, returns 400. You can also copy it from the report's link in Rollstack: it's the part right after /slides/.

Headers

  • Idempotency-Key (string): A unique value, such as a UUID, that makes retrying this call safe. A retry with the same key within 24 hours answers the first call's result instead of running it again. Use a new key for each new call. From 1 to 255 printable ASCII characters.

Request body, required

application/json

  • changes (array of object, required): The changes to the report's datasheets, applied in order: create for a datasheet the report doesn't have yet (a query, one SQL statement, or a join and steps over elements that datasheets of the report read already, under a temporaryId you choose), set-query or set-ops for the data of one it has (getReportDatasheet gives what it is now), set-read-type and set-unpivot. Each datasheet is its reads followed by the ops over them: call getReportDataGuide for the query model, SQL and joins. A temporaryId can be used once: after a save, create the next datasheet under another.
    • When type is create
      • type (create, required)
      • temporaryId (string, required): A stable id for the new datasheet, which later changes in this patch reference it by
      • name (string, required): The datasheet's name
      • reads (array of object, required): The elements it reads, each once: the one a query reads, or those a join reads
        • alias (string, required): The name SQL reads this element by in FROM/JOIN, and ops name it by as a ref
        • datasheetRef (string (uuid), required): An existing datasheet that reads one element: the new datasheet reads that element, with its filters
        • readType (string or null): Omit to read it as that datasheet does One of aggregated, raw.
      • parameters (array of object, required): User-controlled periods, limits, thresholds and selections: SQL reads them as columns of the params input, a step as {"type":"parameter","key":"<key>"}
        • key (string, required)
        • parameter (object, required)
          • When type is string
            • label (string, required)
            • type (string, required)
            • value (string or null, required)
          • When type is integer
            • label (string, required)
            • type (integer, required)
            • value (integer or null, required)
          • When type is number
            • label (string, required)
            • type (number, required)
            • value (number or null, required)
          • When type is boolean
            • label (string, required)
            • type (boolean, required)
            • value (boolean or null, required)
          • When type is date
            • label (string, required)
            • type (date, required)
            • value (string (date) or null, required)
          • When type is timestamp
            • label (string, required)
            • type (timestamp, required)
            • value (string (date-time) or null, required)
      • query (object or null): The data as the Edit data editor builds it, over the one read; give query, or ops over several reads
        • customFields (array of object): Columns worked out on each source row before the filters and the grouping, which filters, dimensions, splits and measures can then name like a source column. A formula reads one row only and may name an earlier custom field. Examples: price times quantity, summed by region; deal-size bands with if(); diffdays between two dates, then averaged
          • formula (string, required): A formula over one source row: ${Column} names a source column or an earlier custom field. No sum(), mean(), min(), max(), median(), count(), offset(), index() or running
          total(), which read other rows
          • as (string, required): The column's name
        • dimensions (array of object, required): The columns to group by
          • column (string, required): A column name, exact and case-sensitive
          • bucket (string or null, required): A date unit to group a date or timestamp column by (month: each month of each year), a part of the date over every year (monthofyear: Jan to Dec, quarterofyear: 1 to 4, dayofweek: Mon to Sun), else null. This year beside last year by month is monthofyear split by the year One of day, week, month, quarter, year, month_of_year, quarter_of_year, day_of_week.
          • as (string): Omit for the default name
          • groups (array of object): A text column only: values shown under one name, e.g. Email and Telephone as "Email & Tel"; the others keep theirs
          • fill (boolean): A date grouped by a unit or a part only, one key at most: every period between the first and the last (every month, quarter or weekday of a part), for each combination of the other keys, a count or sum of no rows as 0. Omit to show only the periods the rows have
        • measures (array of object, required)
          • fn (string, required): One of sum, avg, min, max, median, count, count_distinct, first, last.
          • column (string or null, required): null only with count, which counts rows
          • as (string): Omit for the default name, such as "Sum of Sales"
          • filters (array of object): Only the rows these all match, beside measures of every row: e.g. a count of the mortgages that are Buy to Let. Omit for every row
        • pivots (array of object, required): Dimensions whose values become columns, outer first: each level splits the one above; empty for none
          • column (string, required): A column name, exact and case-sensitive
          • bucket (string or null, required): A date unit to group a date or timestamp column by (month: each month of each year), a part of the date over every year (monthofyear: Jan to Dec, quarterofyear: 1 to 4, dayofweek: Mon to Sun), else null. This year beside last year by month is monthofyear split by the year One of day, week, month, quarter, year, month_of_year, quarter_of_year, day_of_week.
          • as (string): Omit for the default name
          • groups (array of object): A text column only: values shown under one name, e.g. Email and Telephone as "Email & Tel"; the others keep theirs
          • fill (boolean): A date grouped by a unit or a part only, one key at most: every period between the first and the last (every month, quarter or weekday of a part), for each combination of the other keys, a count or sum of no rows as 0. Omit to show only the periods the rows have
        • calculations (array of object): Columns beside each row over every row the aggregate gives: a rank, a percent of total, a difference or percent difference from the previous row ordered by the only dimension, a total/average/min/max of a measure, or a formula over the row's dimensions and measures, such as a rate. A formula with sum(), mean(), min() or max() reads only the rows the limit keeps, such as each top 10 row's share of the 10. Need a dimension or measure and no split
          • When fn is rank
            • fn (rank, required)
            • column (string, required): A measure, by its name before renames
            • direction (string, required): desc: the largest ranks 1 One of asc, desc.
            • as (string): The new column's name. Omit for the default name
          • When fn is percent_of_total
            • fn (percent_of_total, required)
            • column (string, required): A measure, by its name before renames
            • as (string): The new column's name. Omit for the default name
          • Option 3
            • fn (string, required): One of difference, percent_difference.
            • column (string, required): A measure, by its name before renames
            • orderBy (string, required): The query's only dimension, such as a month, by its name before renames: the rows are ordered by it, oldest first
            • offset (integer): How many rows back to compare with; omit for 1, the previous row
            • as (string): The new column's name. Omit for the default name
          • Option 4
            • fn (string, required): One of sum, avg, min, max.
            • column (string, required): A measure, by its name before renames
            • as (string): The new column's name. Omit for the default name
          • When fn is formula
            • fn (formula, required)
            • formula (string, required): A formula: ${Column} names a dimension or measure by its name before renames; numbers, "text", yes, no, null; + - * / (dividing by 0 gives empty); = != < <= > >=; AND, OR, NOT; if(condition, then, else), coalesce(a, b), round(x, digits), abs(x), concat(a, b), isnull(x), tostring(x); sum(x), mean(x), min(x) and max(x) of a column over the rows shown, after the row limit, such as ${Sales} / sum(${Sales}) for each row's share of the rows shown; offset(x, n), the value n rows below in the rows shown (-1 the row above, empty past the first or last), and index(x, n), the value in row n of the rows shown (counting from 1), such as ${Sales} - offset(${Sales}, 1) for the lead over the next row; median(x), and count(x) of its values that aren't empty, over the rows shown; runningtotal(x), the sum of x from the first row shown down to this one, in the rows' order; power(x, y), x to the power y (empty for a negative x with a fractional y); diffdays(start, end), the whole days from the start date to the end date, negative when the end is earlier, empty when either is empty
            • as (string, required): The column's name
        • columnOrder (array of string): Every column once, by its name before renames, in the order to show them, removed columns optional; omit for dimensions, measures, then calculations
        • pivotLayout (object): Omit for the default: columns in the order their values first appear
          • columns (object, required)
            • When mode is fixed
              • mode (fixed, required)
              • values (array of object, required): One column per value, each under its own name: two values never share a column
            • When mode is discover
              • mode (discover, required)
              • order (string, required): One of asc, desc, first_seen.
              • limit (integer, required)
          • nullLabel (string or null, required): Column name for NULL values; null drops them
          • fill (number or null, required): Value for empty cells; null leaves them empty
        • filters (array of object, required): All must hold
          • When kind is compare
            • kind (compare, required)
            • column (string, required): A column name, exact and case-sensitive
            • op (string, required): One of eq, neq, gt, gte, lt, lte.
            • value (object, required)
          • When kind is in
            • kind (in, required)
            • column (string, required): A column name, exact and case-sensitive
            • values (array of object, required)
            • negate (boolean, required)
          • When kind is between
            • kind (between, required)
            • column (string, required): A column name, exact and case-sensitive
            • from (object, required)
            • to (object, required)
          • When kind is text
            • kind (text, required)
            • column (string, required): A column name, exact and case-sensitive
            • op (string, required): One of equals, contains, starts_with, ends_with.
            • value (object, required)
            • caseSensitive (boolean, required)
            • negate (boolean, required)
          • When kind is is_blank
            • kind (is_blank, required)
            • column (string, required): A column name, exact and case-sensitive
            • negate (boolean, required)
          • When kind is relative_date
            • kind (relative_date, required)
            • column (string, required): A column name, exact and case-sensitive
            • range (string, required): One of current, last, next.
            • count (integer or null, required)
            • unit (string, required): One of day, week, month, quarter, year.
            • complete (boolean): last/next only: whole units, leaving out the current one, e.g. the last 3 complete months on Oct 21 are Jul, Aug and Sep. Omit for the window through today
            • includeCurrent (boolean): With complete only: the current unit too, e.g. the last 1 complete year with the current one is last year and this year. Omit to leave it out
            • toDate (boolean): current only: the unit so far, through today, e.g. this year to date. Omit for the whole unit
            • earlier (object): current/last only: the same window, this many units earlier: this year to date 1 year earlier is the same dates last year, and the last 3 complete months 3 months earlier are the 3 months before them. Omit for the window itself
        • renames (array of object): Output columns under another name
          • from (string, required): A column name, exact and case-sensitive
          • to (string, required): The column's new name
        • sorts (array of object, required): Columns to sort by, the first first, each next one breaking ties; empty for none
          • column (string, required): A column, by its name before renames; a split's cell by its values
          • direction (string, required): One of asc, desc.
          • nulls (string): One of first, last.
        • limit (integer or object or null, required): Rows to keep: a number, an integer parameter, or null for all
          • type (parameter, required)
          • key (string, required)
        • limitFrom (string): One of start, end.
        • totals (object): A table's total row, its subtotal rows and, with a split, its Total column, each measure over the rows shown (under a limit, only those). Omit for none
          • row (boolean, required): A last row with the total of each column; charts leave it out
          • column (boolean, required): With a pivot after the aggregate: a Total column with each row across the pivot
          • subtotals (boolean): Grouped by two or more columns: a subtotal row above each group of every group-by column but the last, as a pivot table's subtotals, with each measure over the group's rows; charts leave them out
        • formats (array of object): numfmt patterns by output column
          • column (string, required): An output column, by the name it is shown under, after any rename
          • format (string, required): numfmt pattern, e.g. #,##0.00, $#,##0, 0.0%, yyyy-mm-dd
        • removedColumns (array of string): Columns, by their name before renames, to leave out of the rows while keeping the grouping, e.g. group by region and month but show no region column; a split's column is named by its values
        • keepGroupsWhere (array of object): Conditions on the grouped rows' dimensions and measures (by their name before renames), all of which must hold, right after the grouping and before the calculations, the sort and the limit, as SQL's HAVING does, so the calculations and the limit only see the rows kept: each region's share among the regions with more than 20 deals. To keep rows by a measure or a calculation while the calculations still cover every group, as for the top 10 of the customers over 1,000,000, use keepRowsBeforeLimit. Needs a dimension or measure and no split
          • When kind is compare
            • kind (compare, required)
            • column (string, required): A column name, exact and case-sensitive
            • op (string, required): One of eq, neq, gt, gte, lt, lte.
            • value (object, required)
          • When kind is in
            • kind (in, required)
            • column (string, required): A column name, exact and case-sensitive
            • values (array of object, required)
            • negate (boolean, required)
          • When kind is between
            • kind (between, required)
            • column (string, required): A column name, exact and case-sensitive
            • from (object, required)
            • to (object, required)
          • When kind is text
            • kind (text, required)
            • column (string, required): A column name, exact and case-sensitive
            • op (string, required): One of equals, contains, starts_with, ends_with.
            • value (object, required)
            • caseSensitive (boolean, required)
            • negate (boolean, required)
          • When kind is is_blank
            • kind (is_blank, required)
            • column (string, required): A column name, exact and case-sensitive
            • negate (boolean, required)
          • When kind is relative_date
            • kind (relative_date, required)
            • column (string, required): A column name, exact and case-sensitive
            • range (string, required): One of current, last, next.
            • count (integer or null, required)
            • unit (string, required): One of day, week, month, quarter, year.
            • complete (boolean): last/next only: whole units, leaving out the current one, e.g. the last 3 complete months on Oct 21 are Jul, Aug and Sep. Omit for the window through today
            • includeCurrent (boolean): With complete only: the current unit too, e.g. the last 1 complete year with the current one is last year and this year. Omit to leave it out
            • toDate (boolean): current only: the unit so far, through today, e.g. this year to date. Omit for the whole unit
            • earlier (object): current/last only: the same window, this many units earlier: this year to date 1 year earlier is the same dates last year, and the last 3 complete months 3 months earlier are the 3 months before them. Omit for the window itself
        • keepRowsBeforeLimit (array of object): Conditions on the grouped rows' dimensions, measures and before-limit calculations (by their name before renames), all of which must hold, after the calculations and before the column order, the sort and the limit, so the limit counts only the rows kept while the calculations still cover every group: the top 10 customers of those over 1,000,000, the regions with over 5% of the total. A condition on a calculation that reads the rows the limit keeps (sum(), offset()) goes in keepRowsWhere. Needs a dimension or measure and no split
          • When kind is compare
            • kind (compare, required)
            • column (string, required): A column name, exact and case-sensitive
            • op (string, required): One of eq, neq, gt, gte, lt, lte.
            • value (object, required)
          • When kind is in
            • kind (in, required)
            • column (string, required): A column name, exact and case-sensitive
            • values (array of object, required)
            • negate (boolean, required)
          • When kind is between
            • kind (between, required)
            • column (string, required): A column name, exact and case-sensitive
            • from (object, required)
            • to (object, required)
          • When kind is text
            • kind (text, required)
            • column (string, required): A column name, exact and case-sensitive
            • op (string, required): One of equals, contains, starts_with, ends_with.
            • value (object, required)
            • caseSensitive (boolean, required)
            • negate (boolean, required)
          • When kind is is_blank
            • kind (is_blank, required)
            • column (string, required): A column name, exact and case-sensitive
            • negate (boolean, required)
          • When kind is relative_date
            • kind (relative_date, required)
            • column (string, required): A column name, exact and case-sensitive
            • range (string, required): One of current, last, next.
            • count (integer or null, required)
            • unit (string, required): One of day, week, month, quarter, year.
            • complete (boolean): last/next only: whole units, leaving out the current one, e.g. the last 3 complete months on Oct 21 are Jul, Aug and Sep. Omit for the window through today
            • includeCurrent (boolean): With complete only: the current unit too, e.g. the last 1 complete year with the current one is last year and this year. Omit to leave it out
            • toDate (boolean): current only: the unit so far, through today, e.g. this year to date. Omit for the whole unit
            • earlier (object): current/last only: the same window, this many units earlier: this year to date 1 year earlier is the same dates last year, and the last 3 complete months 3 months earlier are the 3 months before them. Omit for the window itself
        • keepRowsWhere (array of object): Conditions on the rows' own columns (dimensions, measures and calculations by their name before renames), all of which must hold, after the sort, the limit and every calculation: keep the rank 1 row of the top 10, with its share of the 10. To keep rows before the limit, use keepRowsBeforeLimit. Needs a dimension or measure and no split
          • When kind is compare
            • kind (compare, required)
            • column (string, required): A column name, exact and case-sensitive
            • op (string, required): One of eq, neq, gt, gte, lt, lte.
            • value (object, required)
          • When kind is in
            • kind (in, required)
            • column (string, required): A column name, exact and case-sensitive
            • values (array of object, required)
            • negate (boolean, required)
          • When kind is between
            • kind (between, required)
            • column (string, required): A column name, exact and case-sensitive
            • from (object, required)
            • to (object, required)
          • When kind is text
            • kind (text, required)
            • column (string, required): A column name, exact and case-sensitive
            • op (string, required): One of equals, contains, starts_with, ends_with.
            • value (object, required)
            • caseSensitive (boolean, required)
            • negate (boolean, required)
          • When kind is is_blank
            • kind (is_blank, required)
            • column (string, required): A column name, exact and case-sensitive
            • negate (boolean, required)
          • When kind is relative_date
            • kind (relative_date, required)
            • column (string, required): A column name, exact and case-sensitive
            • range (string, required): One of current, last, next.
            • count (integer or null, required)
            • unit (string, required): One of day, week, month, quarter, year.
            • complete (boolean): last/next only: whole units, leaving out the current one, e.g. the last 3 complete months on Oct 21 are Jul, Aug and Sep. Omit for the window through today
            • includeCurrent (boolean): With complete only: the current unit too, e.g. the last 1 complete year with the current one is last year and this year. Omit to leave it out
            • toDate (boolean): current only: the unit so far, through today, e.g. this year to date. Omit for the whole unit
            • earlier (object): current/last only: the same window, this many units earlier: this year to date 1 year earlier is the same dates last year, and the last 3 complete months 3 months earlier are the 3 months before them. Omit for the window itself
      • ops (array of object or null): Only over several reads: a join step over every read first, naming each read in its inputs (from op, ref the read alias), then steps
        • ref (string, required): An existing op id to keep that op, or a new short name
        • title (string or null): Omit to keep a kept op's title; a step op without one shows its step summary
        • inputs (array of object or null): Omit to keep a kept op wired as it is (one that read the op above reads its new op above). A new op reads the op above, which SQL reads as input; a new first op reads the datasheet's one read, as source or the read's alias
        • step (object or null): A step op's call; on a kept op, send it only to change it
          • fn (string, required): One of select, rename, filter, sort, limit, aggregate, pivot, window, join.
          • params (object, required): The params of its fn, whose exact JSON Schema getReportDataGuide answers when you name the fn in stepFunctions
      • formats (array of object or null): With ops; a query has its own formats
        • column (string, required): An output column, by the name it is shown under, after any rename
        • format (string, required): numfmt pattern, e.g. #,##0.00, $#,##0, 0.0%, yyyy-mm-dd
      • sql (string or null): In place of a query or ops: one DuckDB SELECT over the reads, each a table named by its alias, with its formats in formats. Only for what a query can't do, such as a date stored as text (CAST("Month" AS DATE) AS "Month") or a figure from two datasheets that read one row each (SELECT won."Revenue" / goal."Goal" AS "Share of goal" FROM won, goal)
    • When type is set-query
      • type (set-query, required)
      • datasheetRef (string, required): An existing datasheet id, or the temporaryId of a datasheet created earlier in this patch
      • parameters (array of object or null): Replaces the datasheet's complete parameter set; omit to keep it
        • key (string, required)
        • parameter (object, required)
          • When type is string
            • label (string, required)
            • type (string, required)
            • value (string or null, required)
          • When type is integer
            • label (string, required)
            • type (integer, required)
            • value (integer or null, required)
          • When type is number
            • label (string, required)
            • type (number, required)
            • value (number or null, required)
          • When type is boolean
            • label (string, required)
            • type (boolean, required)
            • value (boolean or null, required)
          • When type is date
            • label (string, required)
            • type (date, required)
            • value (string (date) or null, required)
          • When type is timestamp
            • label (string, required)
            • type (timestamp, required)
            • value (string (date-time) or null, required)
      • query (object, required): The data as the Edit data editor builds it, replacing all the datasheet's ops but its read: getReportDatasheet gives its current query to start from
        • customFields (array of object): Columns worked out on each source row before the filters and the grouping, which filters, dimensions, splits and measures can then name like a source column. A formula reads one row only and may name an earlier custom field. Examples: price times quantity, summed by region; deal-size bands with if(); diffdays between two dates, then averaged
          • formula (string, required): A formula over one source row: ${Column} names a source column or an earlier custom field. No sum(), mean(), min(), max(), median(), count(), offset(), index() or running
          total(), which read other rows
          • as (string, required): The column's name
        • dimensions (array of object, required): The columns to group by
          • column (string, required): A column name, exact and case-sensitive
          • bucket (string or null, required): A date unit to group a date or timestamp column by (month: each month of each year), a part of the date over every year (monthofyear: Jan to Dec, quarterofyear: 1 to 4, dayofweek: Mon to Sun), else null. This year beside last year by month is monthofyear split by the year One of day, week, month, quarter, year, month_of_year, quarter_of_year, day_of_week.
          • as (string): Omit for the default name
          • groups (array of object): A text column only: values shown under one name, e.g. Email and Telephone as "Email & Tel"; the others keep theirs
            • name (string, required)
            • values (array of string, required)
          • fill (boolean): A date grouped by a unit or a part only, one key at most: every period between the first and the last (every month, quarter or weekday of a part), for each combination of the other keys, a count or sum of no rows as 0. Omit to show only the periods the rows have
        • measures (array of object, required)
          • fn (string, required): One of sum, avg, min, max, median, count, count_distinct, first, last.
          • column (string or null, required): null only with count, which counts rows
          • as (string): Omit for the default name, such as "Sum of Sales"
          • filters (array of object): Only the rows these all match, beside measures of every row: e.g. a count of the mortgages that are Buy to Let. Omit for every row
        • pivots (array of object, required): Dimensions whose values become columns, outer first: each level splits the one above; empty for none
          • column (string, required): A column name, exact and case-sensitive
          • bucket (string or null, required): A date unit to group a date or timestamp column by (month: each month of each year), a part of the date over every year (monthofyear: Jan to Dec, quarterofyear: 1 to 4, dayofweek: Mon to Sun), else null. This year beside last year by month is monthofyear split by the year One of day, week, month, quarter, year, month_of_year, quarter_of_year, day_of_week.
          • as (string): Omit for the default name
          • groups (array of object): A text column only: values shown under one name, e.g. Email and Telephone as "Email & Tel"; the others keep theirs
            • name (string, required)
            • values (array of string, required)
          • fill (boolean): A date grouped by a unit or a part only, one key at most: every period between the first and the last (every month, quarter or weekday of a part), for each combination of the other keys, a count or sum of no rows as 0. Omit to show only the periods the rows have
        • calculations (array of object): Columns beside each row over every row the aggregate gives: a rank, a percent of total, a difference or percent difference from the previous row ordered by the only dimension, a total/average/min/max of a measure, or a formula over the row's dimensions and measures, such as a rate. A formula with sum(), mean(), min() or max() reads only the rows the limit keeps, such as each top 10 row's share of the 10. Need a dimension or measure and no split
          • When fn is rank
            • fn (rank, required)
            • column (string, required): A measure, by its name before renames
            • direction (string, required): desc: the largest ranks 1 One of asc, desc.
            • as (string): The new column's name. Omit for the default name
          • When fn is percent_of_total
            • fn (percent_of_total, required)
            • column (string, required): A measure, by its name before renames
            • as (string): The new column's name. Omit for the default name
          • Option 3
            • fn (string, required): One of difference, percent_difference.
            • column (string, required): A measure, by its name before renames
            • orderBy (string, required): The query's only dimension, such as a month, by its name before renames: the rows are ordered by it, oldest first
            • offset (integer): How many rows back to compare with; omit for 1, the previous row
            • as (string): The new column's name. Omit for the default name
          • Option 4
            • fn (string, required): One of sum, avg, min, max.
            • column (string, required): A measure, by its name before renames
            • as (string): The new column's name. Omit for the default name
          • When fn is formula
            • fn (formula, required)
            • formula (string, required): A formula: ${Column} names a dimension or measure by its name before renames; numbers, "text", yes, no, null; + - * / (dividing by 0 gives empty); = != < <= > >=; AND, OR, NOT; if(condition, then, else), coalesce(a, b), round(x, digits), abs(x), concat(a, b), isnull(x), tostring(x); sum(x), mean(x), min(x) and max(x) of a column over the rows shown, after the row limit, such as ${Sales} / sum(${Sales}) for each row's share of the rows shown; offset(x, n), the value n rows below in the rows shown (-1 the row above, empty past the first or last), and index(x, n), the value in row n of the rows shown (counting from 1), such as ${Sales} - offset(${Sales}, 1) for the lead over the next row; median(x), and count(x) of its values that aren't empty, over the rows shown; runningtotal(x), the sum of x from the first row shown down to this one, in the rows' order; power(x, y), x to the power y (empty for a negative x with a fractional y); diffdays(start, end), the whole days from the start date to the end date, negative when the end is earlier, empty when either is empty
            • as (string, required): The column's name
        • columnOrder (array of string): Every column once, by its name before renames, in the order to show them, removed columns optional; omit for dimensions, measures, then calculations
        • pivotLayout (object): Omit for the default: columns in the order their values first appear
          • columns (object, required)
            • When mode is fixed
              • mode (fixed, required)
              • values (array of object, required): One column per value, each under its own name: two values never share a column
            • When mode is discover
              • mode (discover, required)
              • order (string, required): One of asc, desc, first_seen.
              • limit (integer, required)
          • nullLabel (string or null, required): Column name for NULL values; null drops them
          • fill (number or null, required): Value for empty cells; null leaves them empty
        • filters (array of object, required): All must hold
          • When kind is compare
            • kind (compare, required)
            • column (string, required): A column name, exact and case-sensitive
            • op (string, required): One of eq, neq, gt, gte, lt, lte.
            • value (object, required)
          • When kind is in
            • kind (in, required)
            • column (string, required): A column name, exact and case-sensitive
            • values (array of object, required)
            • negate (boolean, required)
          • When kind is between
            • kind (between, required)
            • column (string, required): A column name, exact and case-sensitive
            • from (object, required)
            • to (object, required)
          • When kind is text
            • kind (text, required)
            • column (string, required): A column name, exact and case-sensitive
            • op (string, required): One of equals, contains, starts_with, ends_with.
            • value (object, required)
            • caseSensitive (boolean, required)
            • negate (boolean, required)
          • When kind is is_blank
            • kind (is_blank, required)
            • column (string, required): A column name, exact and case-sensitive
            • negate (boolean, required)
          • When kind is relative_date
            • kind (relative_date, required)
            • column (string, required): A column name, exact and case-sensitive
            • range (string, required): One of current, last, next.
            • count (integer or null, required)
            • unit (string, required): One of day, week, month, quarter, year.
            • complete (boolean): last/next only: whole units, leaving out the current one, e.g. the last 3 complete months on Oct 21 are Jul, Aug and Sep. Omit for the window through today
            • includeCurrent (boolean): With complete only: the current unit too, e.g. the last 1 complete year with the current one is last year and this year. Omit to leave it out
            • toDate (boolean): current only: the unit so far, through today, e.g. this year to date. Omit for the whole unit
            • earlier (object): current/last only: the same window, this many units earlier: this year to date 1 year earlier is the same dates last year, and the last 3 complete months 3 months earlier are the 3 months before them. Omit for the window itself
              • count (integer, required)
              • unit (string, required): One of day, week, month, quarter, year.
        • renames (array of object): Output columns under another name
          • from (string, required): A column name, exact and case-sensitive
          • to (string, required): The column's new name
        • sorts (array of object, required): Columns to sort by, the first first, each next one breaking ties; empty for none
          • column (string, required): A column, by its name before renames; a split's cell by its values
          • direction (string, required): One of asc, desc.
          • nulls (string): One of first, last.
        • limit (integer or object or null, required): Rows to keep: a number, an integer parameter, or null for all
          • type (parameter, required)
          • key (string, required)
        • limitFrom (string): One of start, end.
        • totals (object): A table's total row, its subtotal rows and, with a split, its Total column, each measure over the rows shown (under a limit, only those). Omit for none
          • row (boolean, required): A last row with the total of each column; charts leave it out
          • column (boolean, required): With a pivot after the aggregate: a Total column with each row across the pivot
          • subtotals (boolean): Grouped by two or more columns: a subtotal row above each group of every group-by column but the last, as a pivot table's subtotals, with each measure over the group's rows; charts leave them out
        • formats (array of object): numfmt patterns by output column
          • column (string, required): An output column, by the name it is shown under, after any rename
          • format (string, required): numfmt pattern, e.g. #,##0.00, $#,##0, 0.0%, yyyy-mm-dd
        • removedColumns (array of string): Columns, by their name before renames, to leave out of the rows while keeping the grouping, e.g. group by region and month but show no region column; a split's column is named by its values
        • keepGroupsWhere (array of object): Conditions on the grouped rows' dimensions and measures (by their name before renames), all of which must hold, right after the grouping and before the calculations, the sort and the limit, as SQL's HAVING does, so the calculations and the limit only see the rows kept: each region's share among the regions with more than 20 deals. To keep rows by a measure or a calculation while the calculations still cover every group, as for the top 10 of the customers over 1,000,000, use keepRowsBeforeLimit. Needs a dimension or measure and no split
          • When kind is compare
            • kind (compare, required)
            • column (string, required): A column name, exact and case-sensitive
            • op (string, required): One of eq, neq, gt, gte, lt, lte.
            • value (object, required)
          • When kind is in
            • kind (in, required)
            • column (string, required): A column name, exact and case-sensitive
            • values (array of object, required)
            • negate (boolean, required)
          • When kind is between
            • kind (between, required)
            • column (string, required): A column name, exact and case-sensitive
            • from (object, required)
            • to (object, required)
          • When kind is text
            • kind (text, required)
            • column (string, required): A column name, exact and case-sensitive
            • op (string, required): One of equals, contains, starts_with, ends_with.
            • value (object, required)
            • caseSensitive (boolean, required)
            • negate (boolean, required)
          • When kind is is_blank
            • kind (is_blank, required)
            • column (string, required): A column name, exact and case-sensitive
            • negate (boolean, required)
          • When kind is relative_date
            • kind (relative_date, required)
            • column (string, required): A column name, exact and case-sensitive
            • range (string, required): One of current, last, next.
            • count (integer or null, required)
            • unit (string, required): One of day, week, month, quarter, year.
            • complete (boolean): last/next only: whole units, leaving out the current one, e.g. the last 3 complete months on Oct 21 are Jul, Aug and Sep. Omit for the window through today
            • includeCurrent (boolean): With complete only: the current unit too, e.g. the last 1 complete year with the current one is last year and this year. Omit to leave it out
            • toDate (boolean): current only: the unit so far, through today, e.g. this year to date. Omit for the whole unit
            • earlier (object): current/last only: the same window, this many units earlier: this year to date 1 year earlier is the same dates last year, and the last 3 complete months 3 months earlier are the 3 months before them. Omit for the window itself
              • count (integer, required)
              • unit (string, required): One of day, week, month, quarter, year.
        • keepRowsBeforeLimit (array of object): Conditions on the grouped rows' dimensions, measures and before-limit calculations (by their name before renames), all of which must hold, after the calculations and before the column order, the sort and the limit, so the limit counts only the rows kept while the calculations still cover every group: the top 10 customers of those over 1,000,000, the regions with over 5% of the total. A condition on a calculation that reads the rows the limit keeps (sum(), offset()) goes in keepRowsWhere. Needs a dimension or measure and no split
          • When kind is compare
            • kind (compare, required)
            • column (string, required): A column name, exact and case-sensitive
            • op (string, required): One of eq, neq, gt, gte, lt, lte.
            • value (object, required)
          • When kind is in
            • kind (in, required)
            • column (string, required): A column name, exact and case-sensitive
            • values (array of object, required)
            • negate (boolean, required)
          • When kind is between
            • kind (between, required)
            • column (string, required): A column name, exact and case-sensitive
            • from (object, required)
            • to (object, required)
          • When kind is text
            • kind (text, required)
            • column (string, required): A column name, exact and case-sensitive
            • op (string, required): One of equals, contains, starts_with, ends_with.
            • value (object, required)
            • caseSensitive (boolean, required)
            • negate (boolean, required)
          • When kind is is_blank
            • kind (is_blank, required)
            • column (string, required): A column name, exact and case-sensitive
            • negate (boolean, required)
          • When kind is relative_date
            • kind (relative_date, required)
            • column (string, required): A column name, exact and case-sensitive
            • range (string, required): One of current, last, next.
            • count (integer or null, required)
            • unit (string, required): One of day, week, month, quarter, year.
            • complete (boolean): last/next only: whole units, leaving out the current one, e.g. the last 3 complete months on Oct 21 are Jul, Aug and Sep. Omit for the window through today
            • includeCurrent (boolean): With complete only: the current unit too, e.g. the last 1 complete year with the current one is last year and this year. Omit to leave it out
            • toDate (boolean): current only: the unit so far, through today, e.g. this year to date. Omit for the whole unit
            • earlier (object): current/last only: the same window, this many units earlier: this year to date 1 year earlier is the same dates last year, and the last 3 complete months 3 months earlier are the 3 months before them. Omit for the window itself
              • count (integer, required)
              • unit (string, required): One of day, week, month, quarter, year.
        • keepRowsWhere (array of object): Conditions on the rows' own columns (dimensions, measures and calculations by their name before renames), all of which must hold, after the sort, the limit and every calculation: keep the rank 1 row of the top 10, with its share of the 10. To keep rows before the limit, use keepRowsBeforeLimit. Needs a dimension or measure and no split
          • When kind is compare
            • kind (compare, required)
            • column (string, required): A column name, exact and case-sensitive
            • op (string, required): One of eq, neq, gt, gte, lt, lte.
            • value (object, required)
          • When kind is in
            • kind (in, required)
            • column (string, required): A column name, exact and case-sensitive
            • values (array of object, required)
            • negate (boolean, required)
          • When kind is between
            • kind (between, required)
            • column (string, required): A column name, exact and case-sensitive
            • from (object, required)
            • to (object, required)
          • When kind is text
            • kind (text, required)
            • column (string, required): A column name, exact and case-sensitive
            • op (string, required): One of equals, contains, starts_with, ends_with.
            • value (object, required)
            • caseSensitive (boolean, required)
            • negate (boolean, required)
          • When kind is is_blank
            • kind (is_blank, required)
            • column (string, required): A column name, exact and case-sensitive
            • negate (boolean, required)
          • When kind is relative_date
            • kind (relative_date, required)
            • column (string, required): A column name, exact and case-sensitive
            • range (string, required): One of current, last, next.
            • count (integer or null, required)
            • unit (string, required): One of day, week, month, quarter, year.
            • complete (boolean): last/next only: whole units, leaving out the current one, e.g. the last 3 complete months on Oct 21 are Jul, Aug and Sep. Omit for the window through today
            • includeCurrent (boolean): With complete only: the current unit too, e.g. the last 1 complete year with the current one is last year and this year. Omit to leave it out
            • toDate (boolean): current only: the unit so far, through today, e.g. this year to date. Omit for the whole unit
            • earlier (object): current/last only: the same window, this many units earlier: this year to date 1 year earlier is the same dates last year, and the last 3 complete months 3 months earlier are the 3 months before them. Omit for the window itself
              • count (integer, required)
              • unit (string, required): One of day, week, month, quarter, year.
    • When type is set-ops
      • type (set-ops, required)
      • datasheetRef (string, required): An existing datasheet id, or the temporaryId of a datasheet created earlier in this patch
      • parameters (array of object or null): Replaces the datasheet's complete parameter set; omit to keep it
        • key (string, required)
        • parameter (object, required)
          • When type is string
            • label (string, required)
            • type (string, required)
            • value (string or null, required)
          • When type is integer
            • label (string, required)
            • type (integer, required)
            • value (integer or null, required)
          • When type is number
            • label (string, required)
            • type (number, required)
            • value (number or null, required)
          • When type is boolean
            • label (string, required)
            • type (boolean, required)
            • value (boolean or null, required)
          • When type is date
            • label (string, required)
            • type (date, required)
            • value (string (date) or null, required)
          • When type is timestamp
            • label (string, required)
            • type (timestamp, required)
            • value (string (date-time) or null, required)
      • ops (array of object, required): The complete op list after the reads, in run order: an op whose ref is an existing op id keeps that op, and existing ops left out are removed. Only on a datasheet with a join step: keep the join first with steps after it; on any other, use set-query
        • ref (string, required): An existing op id to keep that op, or a new short name
        • title (string or null): Omit to keep a kept op's title; a step op without one shows its step summary
        • inputs (array of object or null): Omit to keep a kept op wired as it is (one that read the op above reads its new op above). A new op reads the op above, which SQL reads as input; a new first op reads the datasheet's one read, as source or the read's alias
          • alias (string or null): SQL ops: the name the statement reads this input by
          • from (string, required): previous: the op above (above the first op, the datasheet's one read); op: the one ref names One of previous, op.
          • ref (string or null): op: the ref of an earlier op in this list, or a read's operation id or alias
        • step (object or null): A step op's call; on a kept op, send it only to change it
          • fn (string, required): One of select, rename, filter, sort, limit, aggregate, pivot, window, join.
          • params (object, required): The params of its fn, whose exact JSON Schema getReportDataGuide answers when you name the fn in stepFunctions
      • formats (array of object or null): Replaces the datasheet's formats; omit to keep them
        • column (string, required): An output column, by the name it is shown under, after any rename
        • format (string, required): numfmt pattern, e.g. #,##0.00, $#,##0, 0.0%, yyyy-mm-dd
    • When type is set-read-type
      • type (set-read-type, required)
      • datasheetRef (string, required): An existing datasheet id, or the temporaryId of a datasheet created earlier in this patch
      • readType (string, required): aggregated: its data, as the tool displays it; raw: its tidy read, to transform; image: its picture One of aggregated, raw, image.
    • When type is set-unpivot
      • type (set-unpivot, required)
      • datasheetRef (string, required): An existing datasheet id, or the temporaryId of a datasheet created earlier in this patch
      • unpivot (object or null): The columns of the range this datasheet reads to turn into rows, which every datasheet reading the range raw gets, and this one then reads it raw. Null takes it out; omit to keep the range's current one
        • rows (array of object, required): One output row per input row and entry, in this order; the input columns no entry names repeat on each
          • label (string, required): What the name column says on this row, such as the column's name or "2022"
          • columns (array of string or null, required): The input column for each of values, in order; null leaves that value empty on this row
        • name (string, required): The column that holds each row's label, such as "Year"
        • values (array of string, required): The columns the values go in: one, such as "Value", or one per measure
      • fillDown (object or null): Run first, as Power Query fills down: each heading row, a label with every other cell empty, becomes the value of a new column on the rows under it, and the heading row is left out. Null takes it out; omit to keep the range's current one
        • column (string, required): The column holding the row labels, headings included
        • into (string, required): The new column the headings fill, such as "Section"
      • removeRows (array of object or null): Run after fillDown: rows matching any of these are left out, such as the sheet's own totals that add up the rows above them. Null takes them out; omit to keep the range's current ones
        • When kind is compare
          • kind (compare, required)
          • column (string, required): A column name, exact and case-sensitive
          • op (string, required): One of eq, neq, gt, gte, lt, lte.
          • value (object, required)
        • When kind is in
          • kind (in, required)
          • column (string, required): A column name, exact and case-sensitive
          • values (array of object, required)
          • negate (boolean, required)
        • When kind is between
          • kind (between, required)
          • column (string, required): A column name, exact and case-sensitive
          • from (object, required)
          • to (object, required)
        • When kind is text
          • kind (text, required)
          • column (string, required): A column name, exact and case-sensitive
          • op (string, required): One of equals, contains, starts_with, ends_with.
          • value (object, required)
          • caseSensitive (boolean, required)
          • negate (boolean, required)
        • When kind is is_blank
          • kind (is_blank, required)
          • column (string, required): A column name, exact and case-sensitive
          • negate (boolean, required)
        • When kind is relative_date
          • kind (relative_date, required)
          • column (string, required): A column name, exact and case-sensitive
          • range (string, required): One of current, last, next.
          • count (integer or null, required)
          • unit (string, required): One of day, week, month, quarter, year.
          • complete (boolean): last/next only: whole units, leaving out the current one, e.g. the last 3 complete months on Oct 21 are Jul, Aug and Sep. Omit for the window through today
          • includeCurrent (boolean): With complete only: the current unit too, e.g. the last 1 complete year with the current one is last year and this year. Omit to leave it out
          • toDate (boolean): current only: the unit so far, through today, e.g. this year to date. Omit for the whole unit
          • earlier (object): current/last only: the same window, this many units earlier: this year to date 1 year earlier is the same dates last year, and the last 3 complete months 3 months earlier are the 3 months before them. Omit for the window itself
            • count (integer, required)
            • unit (string, required): One of day, week, month, quarter, year.

Response 200

Success

application/json

  • When valid is true
    • valid (true, required)
    • resolvedDatasheetIds (object, required): The id of each datasheet a create makes, by its temporaryId. Place a new datasheet on a slide by this id: renderReportSlides draws it with the same changes and saveReportDatasheetChanges saves it under it.
    • resolvedOperationIds (object, required): The id of each op a change adds, by its ref.
    • removedOperations (array of object, required): The existing ops a change drops, so you can check what it replaced.
      • datasheetId (string, required)
      • operationId (string, required)
      • summary (string, required)
    • outputs (object, required): Each changed datasheet by id, with its columns and five sample rows.
    • revisions (object, required): The revision of each existing datasheet the changes edit, to send back to saveReportDatasheetChanges.
  • When valid is false
    • valid (false, required)
    • error (string, required): Why the changes failed, naming the datasheet, op and field when it can. Fix them and preview again.

Query report data

POST https://api.rollstack.com/public/v1/reports/{id}/data/query

Runs one read-only SQL query over the tables of a report's datasheets, to check a figure before it goes on a slide or to explore data before shaping it. It reads the copies stored at the sources' last reads, never a source, and saves nothing: a datasheet's table is the one getReportDatasheetData answers. Only the report's editors may. At most 200 rows and 30 seconds a query. Your queries run one at a time, together with your previewReportDatasheetChanges and saveReportDatasheetChanges calls and the renderReportSlides calls that carry datasheetChanges: send the next only once this one answers, never several at once, and check several figures in one query, as columns of one row or rows of a UNION ALL. At most 30 queries and previewReportDatasheetChanges calls a minute per user, counted together.

Requires the slide_decks:write scope.

Operation ID: queryReportData.

Path parameters

  • id (string, required): The report to act on, by its id from listReports or createReport. Only reports whose type is SlideDeck work here: another type, such as a Google Slides presentation, returns 400. You can also copy it from the report's link in Rollstack: it's the part right after /slides/.

Headers

  • Idempotency-Key (string): A unique value, such as a UUID, that makes retrying this call safe. A retry with the same key within 24 hours answers the first call's result instead of running it again. Use a new key for each new call. From 1 to 255 printable ASCII characters.

Request body, required

application/json

  • sql (string, required): One read-only DuckDB query (SELECT or WITH). Its tables are the report's datasheets, named by their id from listReportDatasheets in double quotes, as in SELECT region, SUM(revenue) AS revenue FROM "<id>" GROUP BY region. A column is a number when every value is one, else text, dates included: CAST an ISO date (YYYY-MM-DD) to DATE before year(), date_trunc() or date arithmetic. Give every computed column a name with AS.

Response 200

Success

application/json

  • columns (array of object, required): The columns of the table, in the same order as the cells of each row in rows.
    • name (string, required): The column's name in the datasheet's table, which the report's charts and tables use to refer to it. Unless the datasheet renames it, a column read from an uploaded file or a spreadsheet range is named after its header; a blank header becomes Column N, after the column's position, and a repeated one gets a _2, _3 suffix.
    • label (string): The column's display name, when its source gives one, such as a field's label in Looker or Metabase. Charts and tables drawn from the datasheet show it instead of name, unless a table renames the column. Omitted when the column has none.
    • type (string, required): What the column's cells contain: number for numbers, date for dates and times, string for text, and boolean for true or false. Any cell can also be null when it is empty. One of number, date, string, boolean.
  • rows (array of array of string or number or boolean or null, required): The rows of the table, starting from the first, at most limit of them. Each row is an array of cells in the order of columns. An empty cell is null, and a date cell is an ISO 8601 string in UTC, at midnight when the source gives a day without a time.
  • totalRow (true)
  • subtotals (object): When the datasheet's query adds subtotals: rowFields are the columns its rows are grouped by, outermost first, and levels has one number per row in rows, how many of those columns the row is grouped by. A row grouped by fewer than all of them is a subtotal above its group's rows, and 0 is the total row. Charts leave those rows out. Omitted without subtotals.
    • rowFields (array of string, required)
    • levels (array of integer, required)
  • truncated (boolean, required): Whether the table has more rows than limit, so rows doesn't contain all of them. Raise limit to get more, up to the first 1000 rows.

Save datasheet changes

POST https://api.rollstack.com/public/v1/reports/{id}/datasheets/changes

Saves changes to a report's datasheets that you tried with previewReportDatasheetChanges: send the same changes with the revisions it answered. Nothing is saved when a datasheet they edit changed since, or when they fail: the answer says why, and you preview again. The charts and tables on slides that show a changed datasheet are read again, and a new datasheet is placed by the id it answers and the slides saved with saveReportSlides. Take the save back by restoring versionBeforeId with restoreReportVersion. It runs one at a time with your queryReportData and previewReportDatasheetChanges calls and the renderReportSlides calls that carry datasheetChanges: send it only once those have answered. Only the report's editors may. Counts against the 20 renders and saves a minute per user.

Requires the slide_decks:write scope.

Operation ID: saveReportDatasheetChanges.

Path parameters

  • id (string, required): The report to act on, by its id from listReports or createReport. Only reports whose type is SlideDeck work here: another type, such as a Google Slides presentation, returns 400. You can also copy it from the report's link in Rollstack: it's the part right after /slides/.

Headers

  • Idempotency-Key (string): A unique value, such as a UUID, that makes retrying this call safe. A retry with the same key within 24 hours answers the first call's result instead of running it again. Use a new key for each new call. From 1 to 255 printable ASCII characters.

Request body, required

application/json

  • changes (array of object, required): The changes to the report's datasheets, applied in order: create for a datasheet the report doesn't have yet (a query, one SQL statement, or a join and steps over elements that datasheets of the report read already, under a temporaryId you choose), set-query or set-ops for the data of one it has (getReportDatasheet gives what it is now), set-read-type and set-unpivot. Each datasheet is its reads followed by the ops over them: call getReportDataGuide for the query model, SQL and joins. A temporaryId can be used once: after a save, create the next datasheet under another.
    • When type is create
      • type (create, required)
      • temporaryId (string, required): A stable id for the new datasheet, which later changes in this patch reference it by
      • name (string, required): The datasheet's name
      • reads (array of object, required): The elements it reads, each once: the one a query reads, or those a join reads
        • alias (string, required): The name SQL reads this element by in FROM/JOIN, and ops name it by as a ref
        • datasheetRef (string (uuid), required): An existing datasheet that reads one element: the new datasheet reads that element, with its filters
        • readType (string or null): Omit to read it as that datasheet does One of aggregated, raw.
      • parameters (array of object, required): User-controlled periods, limits, thresholds and selections: SQL reads them as columns of the params input, a step as {"type":"parameter","key":"<key>"}
        • key (string, required)
        • parameter (object, required)
          • When type is string
            • label (string, required)
            • type (string, required)
            • value (string or null, required)
          • When type is integer
            • label (string, required)
            • type (integer, required)
            • value (integer or null, required)
          • When type is number
            • label (string, required)
            • type (number, required)
            • value (number or null, required)
          • When type is boolean
            • label (string, required)
            • type (boolean, required)
            • value (boolean or null, required)
          • When type is date
            • label (string, required)
            • type (date, required)
            • value (string (date) or null, required)
          • When type is timestamp
            • label (string, required)
            • type (timestamp, required)
            • value (string (date-time) or null, required)
      • query (object or null): The data as the Edit data editor builds it, over the one read; give query, or ops over several reads
        • customFields (array of object): Columns worked out on each source row before the filters and the grouping, which filters, dimensions, splits and measures can then name like a source column. A formula reads one row only and may name an earlier custom field. Examples: price times quantity, summed by region; deal-size bands with if(); diffdays between two dates, then averaged
          • formula (string, required): A formula over one source row: ${Column} names a source column or an earlier custom field. No sum(), mean(), min(), max(), median(), count(), offset(), index() or running
          total(), which read other rows
          • as (string, required): The column's name
        • dimensions (array of object, required): The columns to group by
          • column (string, required): A column name, exact and case-sensitive
          • bucket (string or null, required): A date unit to group a date or timestamp column by (month: each month of each year), a part of the date over every year (monthofyear: Jan to Dec, quarterofyear: 1 to 4, dayofweek: Mon to Sun), else null. This year beside last year by month is monthofyear split by the year One of day, week, month, quarter, year, month_of_year, quarter_of_year, day_of_week.
          • as (string): Omit for the default name
          • groups (array of object): A text column only: values shown under one name, e.g. Email and Telephone as "Email & Tel"; the others keep theirs
          • fill (boolean): A date grouped by a unit or a part only, one key at most: every period between the first and the last (every month, quarter or weekday of a part), for each combination of the other keys, a count or sum of no rows as 0. Omit to show only the periods the rows have
        • measures (array of object, required)
          • fn (string, required): One of sum, avg, min, max, median, count, count_distinct, first, last.
          • column (string or null, required): null only with count, which counts rows
          • as (string): Omit for the default name, such as "Sum of Sales"
          • filters (array of object): Only the rows these all match, beside measures of every row: e.g. a count of the mortgages that are Buy to Let. Omit for every row
        • pivots (array of object, required): Dimensions whose values become columns, outer first: each level splits the one above; empty for none
          • column (string, required): A column name, exact and case-sensitive
          • bucket (string or null, required): A date unit to group a date or timestamp column by (month: each month of each year), a part of the date over every year (monthofyear: Jan to Dec, quarterofyear: 1 to 4, dayofweek: Mon to Sun), else null. This year beside last year by month is monthofyear split by the year One of day, week, month, quarter, year, month_of_year, quarter_of_year, day_of_week.
          • as (string): Omit for the default name
          • groups (array of object): A text column only: values shown under one name, e.g. Email and Telephone as "Email & Tel"; the others keep theirs
          • fill (boolean): A date grouped by a unit or a part only, one key at most: every period between the first and the last (every month, quarter or weekday of a part), for each combination of the other keys, a count or sum of no rows as 0. Omit to show only the periods the rows have
        • calculations (array of object): Columns beside each row over every row the aggregate gives: a rank, a percent of total, a difference or percent difference from the previous row ordered by the only dimension, a total/average/min/max of a measure, or a formula over the row's dimensions and measures, such as a rate. A formula with sum(), mean(), min() or max() reads only the rows the limit keeps, such as each top 10 row's share of the 10. Need a dimension or measure and no split
          • When fn is rank
            • fn (rank, required)
            • column (string, required): A measure, by its name before renames
            • direction (string, required): desc: the largest ranks 1 One of asc, desc.
            • as (string): The new column's name. Omit for the default name
          • When fn is percent_of_total
            • fn (percent_of_total, required)
            • column (string, required): A measure, by its name before renames
            • as (string): The new column's name. Omit for the default name
          • Option 3
            • fn (string, required): One of difference, percent_difference.
            • column (string, required): A measure, by its name before renames
            • orderBy (string, required): The query's only dimension, such as a month, by its name before renames: the rows are ordered by it, oldest first
            • offset (integer): How many rows back to compare with; omit for 1, the previous row
            • as (string): The new column's name. Omit for the default name
          • Option 4
            • fn (string, required): One of sum, avg, min, max.
            • column (string, required): A measure, by its name before renames
            • as (string): The new column's name. Omit for the default name
          • When fn is formula
            • fn (formula, required)
            • formula (string, required): A formula: ${Column} names a dimension or measure by its name before renames; numbers, "text", yes, no, null; + - * / (dividing by 0 gives empty); = != < <= > >=; AND, OR, NOT; if(condition, then, else), coalesce(a, b), round(x, digits), abs(x), concat(a, b), isnull(x), tostring(x); sum(x), mean(x), min(x) and max(x) of a column over the rows shown, after the row limit, such as ${Sales} / sum(${Sales}) for each row's share of the rows shown; offset(x, n), the value n rows below in the rows shown (-1 the row above, empty past the first or last), and index(x, n), the value in row n of the rows shown (counting from 1), such as ${Sales} - offset(${Sales}, 1) for the lead over the next row; median(x), and count(x) of its values that aren't empty, over the rows shown; runningtotal(x), the sum of x from the first row shown down to this one, in the rows' order; power(x, y), x to the power y (empty for a negative x with a fractional y); diffdays(start, end), the whole days from the start date to the end date, negative when the end is earlier, empty when either is empty
            • as (string, required): The column's name
        • columnOrder (array of string): Every column once, by its name before renames, in the order to show them, removed columns optional; omit for dimensions, measures, then calculations
        • pivotLayout (object): Omit for the default: columns in the order their values first appear
          • columns (object, required)
            • When mode is fixed
              • mode (fixed, required)
              • values (array of object, required): One column per value, each under its own name: two values never share a column
            • When mode is discover
              • mode (discover, required)
              • order (string, required): One of asc, desc, first_seen.
              • limit (integer, required)
          • nullLabel (string or null, required): Column name for NULL values; null drops them
          • fill (number or null, required): Value for empty cells; null leaves them empty
        • filters (array of object, required): All must hold
          • When kind is compare
            • kind (compare, required)
            • column (string, required): A column name, exact and case-sensitive
            • op (string, required): One of eq, neq, gt, gte, lt, lte.
            • value (object, required)
          • When kind is in
            • kind (in, required)
            • column (string, required): A column name, exact and case-sensitive
            • values (array of object, required)
            • negate (boolean, required)
          • When kind is between
            • kind (between, required)
            • column (string, required): A column name, exact and case-sensitive
            • from (object, required)
            • to (object, required)
          • When kind is text
            • kind (text, required)
            • column (string, required): A column name, exact and case-sensitive
            • op (string, required): One of equals, contains, starts_with, ends_with.
            • value (object, required)
            • caseSensitive (boolean, required)
            • negate (boolean, required)
          • When kind is is_blank
            • kind (is_blank, required)
            • column (string, required): A column name, exact and case-sensitive
            • negate (boolean, required)
          • When kind is relative_date
            • kind (relative_date, required)
            • column (string, required): A column name, exact and case-sensitive
            • range (string, required): One of current, last, next.
            • count (integer or null, required)
            • unit (string, required): One of day, week, month, quarter, year.
            • complete (boolean): last/next only: whole units, leaving out the current one, e.g. the last 3 complete months on Oct 21 are Jul, Aug and Sep. Omit for the window through today
            • includeCurrent (boolean): With complete only: the current unit too, e.g. the last 1 complete year with the current one is last year and this year. Omit to leave it out
            • toDate (boolean): current only: the unit so far, through today, e.g. this year to date. Omit for the whole unit
            • earlier (object): current/last only: the same window, this many units earlier: this year to date 1 year earlier is the same dates last year, and the last 3 complete months 3 months earlier are the 3 months before them. Omit for the window itself
        • renames (array of object): Output columns under another name
          • from (string, required): A column name, exact and case-sensitive
          • to (string, required): The column's new name
        • sorts (array of object, required): Columns to sort by, the first first, each next one breaking ties; empty for none
          • column (string, required): A column, by its name before renames; a split's cell by its values
          • direction (string, required): One of asc, desc.
          • nulls (string): One of first, last.
        • limit (integer or object or null, required): Rows to keep: a number, an integer parameter, or null for all
          • type (parameter, required)
          • key (string, required)
        • limitFrom (string): One of start, end.
        • totals (object): A table's total row, its subtotal rows and, with a split, its Total column, each measure over the rows shown (under a limit, only those). Omit for none
          • row (boolean, required): A last row with the total of each column; charts leave it out
          • column (boolean, required): With a pivot after the aggregate: a Total column with each row across the pivot
          • subtotals (boolean): Grouped by two or more columns: a subtotal row above each group of every group-by column but the last, as a pivot table's subtotals, with each measure over the group's rows; charts leave them out
        • formats (array of object): numfmt patterns by output column
          • column (string, required): An output column, by the name it is shown under, after any rename
          • format (string, required): numfmt pattern, e.g. #,##0.00, $#,##0, 0.0%, yyyy-mm-dd
        • removedColumns (array of string): Columns, by their name before renames, to leave out of the rows while keeping the grouping, e.g. group by region and month but show no region column; a split's column is named by its values
        • keepGroupsWhere (array of object): Conditions on the grouped rows' dimensions and measures (by their name before renames), all of which must hold, right after the grouping and before the calculations, the sort and the limit, as SQL's HAVING does, so the calculations and the limit only see the rows kept: each region's share among the regions with more than 20 deals. To keep rows by a measure or a calculation while the calculations still cover every group, as for the top 10 of the customers over 1,000,000, use keepRowsBeforeLimit. Needs a dimension or measure and no split
          • When kind is compare
            • kind (compare, required)
            • column (string, required): A column name, exact and case-sensitive
            • op (string, required): One of eq, neq, gt, gte, lt, lte.
            • value (object, required)
          • When kind is in
            • kind (in, required)
            • column (string, required): A column name, exact and case-sensitive
            • values (array of object, required)
            • negate (boolean, required)
          • When kind is between
            • kind (between, required)
            • column (string, required): A column name, exact and case-sensitive
            • from (object, required)
            • to (object, required)
          • When kind is text
            • kind (text, required)
            • column (string, required): A column name, exact and case-sensitive
            • op (string, required): One of equals, contains, starts_with, ends_with.
            • value (object, required)
            • caseSensitive (boolean, required)
            • negate (boolean, required)
          • When kind is is_blank
            • kind (is_blank, required)
            • column (string, required): A column name, exact and case-sensitive
            • negate (boolean, required)
          • When kind is relative_date
            • kind (relative_date, required)
            • column (string, required): A column name, exact and case-sensitive
            • range (string, required): One of current, last, next.
            • count (integer or null, required)
            • unit (string, required): One of day, week, month, quarter, year.
            • complete (boolean): last/next only: whole units, leaving out the current one, e.g. the last 3 complete months on Oct 21 are Jul, Aug and Sep. Omit for the window through today
            • includeCurrent (boolean): With complete only: the current unit too, e.g. the last 1 complete year with the current one is last year and this year. Omit to leave it out
            • toDate (boolean): current only: the unit so far, through today, e.g. this year to date. Omit for the whole unit
            • earlier (object): current/last only: the same window, this many units earlier: this year to date 1 year earlier is the same dates last year, and the last 3 complete months 3 months earlier are the 3 months before them. Omit for the window itself
        • keepRowsBeforeLimit (array of object): Conditions on the grouped rows' dimensions, measures and before-limit calculations (by their name before renames), all of which must hold, after the calculations and before the column order, the sort and the limit, so the limit counts only the rows kept while the calculations still cover every group: the top 10 customers of those over 1,000,000, the regions with over 5% of the total. A condition on a calculation that reads the rows the limit keeps (sum(), offset()) goes in keepRowsWhere. Needs a dimension or measure and no split
          • When kind is compare
            • kind (compare, required)
            • column (string, required): A column name, exact and case-sensitive
            • op (string, required): One of eq, neq, gt, gte, lt, lte.
            • value (object, required)
          • When kind is in
            • kind (in, required)
            • column (string, required): A column name, exact and case-sensitive
            • values (array of object, required)
            • negate (boolean, required)
          • When kind is between
            • kind (between, required)
            • column (string, required): A column name, exact and case-sensitive
            • from (object, required)
            • to (object, required)
          • When kind is text
            • kind (text, required)
            • column (string, required): A column name, exact and case-sensitive
            • op (string, required): One of equals, contains, starts_with, ends_with.
            • value (object, required)
            • caseSensitive (boolean, required)
            • negate (boolean, required)
          • When kind is is_blank
            • kind (is_blank, required)
            • column (string, required): A column name, exact and case-sensitive
            • negate (boolean, required)
          • When kind is relative_date
            • kind (relative_date, required)
            • column (string, required): A column name, exact and case-sensitive
            • range (string, required): One of current, last, next.
            • count (integer or null, required)
            • unit (string, required): One of day, week, month, quarter, year.
            • complete (boolean): last/next only: whole units, leaving out the current one, e.g. the last 3 complete months on Oct 21 are Jul, Aug and Sep. Omit for the window through today
            • includeCurrent (boolean): With complete only: the current unit too, e.g. the last 1 complete year with the current one is last year and this year. Omit to leave it out
            • toDate (boolean): current only: the unit so far, through today, e.g. this year to date. Omit for the whole unit
            • earlier (object): current/last only: the same window, this many units earlier: this year to date 1 year earlier is the same dates last year, and the last 3 complete months 3 months earlier are the 3 months before them. Omit for the window itself
        • keepRowsWhere (array of object): Conditions on the rows' own columns (dimensions, measures and calculations by their name before renames), all of which must hold, after the sort, the limit and every calculation: keep the rank 1 row of the top 10, with its share of the 10. To keep rows before the limit, use keepRowsBeforeLimit. Needs a dimension or measure and no split
          • When kind is compare
            • kind (compare, required)
            • column (string, required): A column name, exact and case-sensitive
            • op (string, required): One of eq, neq, gt, gte, lt, lte.
            • value (object, required)
          • When kind is in
            • kind (in, required)
            • column (string, required): A column name, exact and case-sensitive
            • values (array of object, required)
            • negate (boolean, required)
          • When kind is between
            • kind (between, required)
            • column (string, required): A column name, exact and case-sensitive
            • from (object, required)
            • to (object, required)
          • When kind is text
            • kind (text, required)
            • column (string, required): A column name, exact and case-sensitive
            • op (string, required): One of equals, contains, starts_with, ends_with.
            • value (object, required)
            • caseSensitive (boolean, required)
            • negate (boolean, required)
          • When kind is is_blank
            • kind (is_blank, required)
            • column (string, required): A column name, exact and case-sensitive
            • negate (boolean, required)
          • When kind is relative_date
            • kind (relative_date, required)
            • column (string, required): A column name, exact and case-sensitive
            • range (string, required): One of current, last, next.
            • count (integer or null, required)
            • unit (string, required): One of day, week, month, quarter, year.
            • complete (boolean): last/next only: whole units, leaving out the current one, e.g. the last 3 complete months on Oct 21 are Jul, Aug and Sep. Omit for the window through today
            • includeCurrent (boolean): With complete only: the current unit too, e.g. the last 1 complete year with the current one is last year and this year. Omit to leave it out
            • toDate (boolean): current only: the unit so far, through today, e.g. this year to date. Omit for the whole unit
            • earlier (object): current/last only: the same window, this many units earlier: this year to date 1 year earlier is the same dates last year, and the last 3 complete months 3 months earlier are the 3 months before them. Omit for the window itself
      • ops (array of object or null): Only over several reads: a join step over every read first, naming each read in its inputs (from op, ref the read alias), then steps
        • ref (string, required): An existing op id to keep that op, or a new short name
        • title (string or null): Omit to keep a kept op's title; a step op without one shows its step summary
        • inputs (array of object or null): Omit to keep a kept op wired as it is (one that read the op above reads its new op above). A new op reads the op above, which SQL reads as input; a new first op reads the datasheet's one read, as source or the read's alias
        • step (object or null): A step op's call; on a kept op, send it only to change it
          • fn (string, required): One of select, rename, filter, sort, limit, aggregate, pivot, window, join.
          • params (object, required): The params of its fn, whose exact JSON Schema getReportDataGuide answers when you name the fn in stepFunctions
      • formats (array of object or null): With ops; a query has its own formats
        • column (string, required): An output column, by the name it is shown under, after any rename
        • format (string, required): numfmt pattern, e.g. #,##0.00, $#,##0, 0.0%, yyyy-mm-dd
      • sql (string or null): In place of a query or ops: one DuckDB SELECT over the reads, each a table named by its alias, with its formats in formats. Only for what a query can't do, such as a date stored as text (CAST("Month" AS DATE) AS "Month") or a figure from two datasheets that read one row each (SELECT won."Revenue" / goal."Goal" AS "Share of goal" FROM won, goal)
    • When type is set-query
      • type (set-query, required)
      • datasheetRef (string, required): An existing datasheet id, or the temporaryId of a datasheet created earlier in this patch
      • parameters (array of object or null): Replaces the datasheet's complete parameter set; omit to keep it
        • key (string, required)
        • parameter (object, required)
          • When type is string
            • label (string, required)
            • type (string, required)
            • value (string or null, required)
          • When type is integer
            • label (string, required)
            • type (integer, required)
            • value (integer or null, required)
          • When type is number
            • label (string, required)
            • type (number, required)
            • value (number or null, required)
          • When type is boolean
            • label (string, required)
            • type (boolean, required)
            • value (boolean or null, required)
          • When type is date
            • label (string, required)
            • type (date, required)
            • value (string (date) or null, required)
          • When type is timestamp
            • label (string, required)
            • type (timestamp, required)
            • value (string (date-time) or null, required)
      • query (object, required): The data as the Edit data editor builds it, replacing all the datasheet's ops but its read: getReportDatasheet gives its current query to start from
        • customFields (array of object): Columns worked out on each source row before the filters and the grouping, which filters, dimensions, splits and measures can then name like a source column. A formula reads one row only and may name an earlier custom field. Examples: price times quantity, summed by region; deal-size bands with if(); diffdays between two dates, then averaged
          • formula (string, required): A formula over one source row: ${Column} names a source column or an earlier custom field. No sum(), mean(), min(), max(), median(), count(), offset(), index() or running
          total(), which read other rows
          • as (string, required): The column's name
        • dimensions (array of object, required): The columns to group by
          • column (string, required): A column name, exact and case-sensitive
          • bucket (string or null, required): A date unit to group a date or timestamp column by (month: each month of each year), a part of the date over every year (monthofyear: Jan to Dec, quarterofyear: 1 to 4, dayofweek: Mon to Sun), else null. This year beside last year by month is monthofyear split by the year One of day, week, month, quarter, year, month_of_year, quarter_of_year, day_of_week.
          • as (string): Omit for the default name
          • groups (array of object): A text column only: values shown under one name, e.g. Email and Telephone as "Email & Tel"; the others keep theirs
            • name (string, required)
            • values (array of string, required)
          • fill (boolean): A date grouped by a unit or a part only, one key at most: every period between the first and the last (every month, quarter or weekday of a part), for each combination of the other keys, a count or sum of no rows as 0. Omit to show only the periods the rows have
        • measures (array of object, required)
          • fn (string, required): One of sum, avg, min, max, median, count, count_distinct, first, last.
          • column (string or null, required): null only with count, which counts rows
          • as (string): Omit for the default name, such as "Sum of Sales"
          • filters (array of object): Only the rows these all match, beside measures of every row: e.g. a count of the mortgages that are Buy to Let. Omit for every row
        • pivots (array of object, required): Dimensions whose values become columns, outer first: each level splits the one above; empty for none
          • column (string, required): A column name, exact and case-sensitive
          • bucket (string or null, required): A date unit to group a date or timestamp column by (month: each month of each year), a part of the date over every year (monthofyear: Jan to Dec, quarterofyear: 1 to 4, dayofweek: Mon to Sun), else null. This year beside last year by month is monthofyear split by the year One of day, week, month, quarter, year, month_of_year, quarter_of_year, day_of_week.
          • as (string): Omit for the default name
          • groups (array of object): A text column only: values shown under one name, e.g. Email and Telephone as "Email & Tel"; the others keep theirs
            • name (string, required)
            • values (array of string, required)
          • fill (boolean): A date grouped by a unit or a part only, one key at most: every period between the first and the last (every month, quarter or weekday of a part), for each combination of the other keys, a count or sum of no rows as 0. Omit to show only the periods the rows have
        • calculations (array of object): Columns beside each row over every row the aggregate gives: a rank, a percent of total, a difference or percent difference from the previous row ordered by the only dimension, a total/average/min/max of a measure, or a formula over the row's dimensions and measures, such as a rate. A formula with sum(), mean(), min() or max() reads only the rows the limit keeps, such as each top 10 row's share of the 10. Need a dimension or measure and no split
          • When fn is rank
            • fn (rank, required)
            • column (string, required): A measure, by its name before renames
            • direction (string, required): desc: the largest ranks 1 One of asc, desc.
            • as (string): The new column's name. Omit for the default name
          • When fn is percent_of_total
            • fn (percent_of_total, required)
            • column (string, required): A measure, by its name before renames
            • as (string): The new column's name. Omit for the default name
          • Option 3
            • fn (string, required): One of difference, percent_difference.
            • column (string, required): A measure, by its name before renames
            • orderBy (string, required): The query's only dimension, such as a month, by its name before renames: the rows are ordered by it, oldest first
            • offset (integer): How many rows back to compare with; omit for 1, the previous row
            • as (string): The new column's name. Omit for the default name
          • Option 4
            • fn (string, required): One of sum, avg, min, max.
            • column (string, required): A measure, by its name before renames
            • as (string): The new column's name. Omit for the default name
          • When fn is formula
            • fn (formula, required)
            • formula (string, required): A formula: ${Column} names a dimension or measure by its name before renames; numbers, "text", yes, no, null; + - * / (dividing by 0 gives empty); = != < <= > >=; AND, OR, NOT; if(condition, then, else), coalesce(a, b), round(x, digits), abs(x), concat(a, b), isnull(x), tostring(x); sum(x), mean(x), min(x) and max(x) of a column over the rows shown, after the row limit, such as ${Sales} / sum(${Sales}) for each row's share of the rows shown; offset(x, n), the value n rows below in the rows shown (-1 the row above, empty past the first or last), and index(x, n), the value in row n of the rows shown (counting from 1), such as ${Sales} - offset(${Sales}, 1) for the lead over the next row; median(x), and count(x) of its values that aren't empty, over the rows shown; runningtotal(x), the sum of x from the first row shown down to this one, in the rows' order; power(x, y), x to the power y (empty for a negative x with a fractional y); diffdays(start, end), the whole days from the start date to the end date, negative when the end is earlier, empty when either is empty
            • as (string, required): The column's name
        • columnOrder (array of string): Every column once, by its name before renames, in the order to show them, removed columns optional; omit for dimensions, measures, then calculations
        • pivotLayout (object): Omit for the default: columns in the order their values first appear
          • columns (object, required)
            • When mode is fixed
              • mode (fixed, required)
              • values (array of object, required): One column per value, each under its own name: two values never share a column
            • When mode is discover
              • mode (discover, required)
              • order (string, required): One of asc, desc, first_seen.
              • limit (integer, required)
          • nullLabel (string or null, required): Column name for NULL values; null drops them
          • fill (number or null, required): Value for empty cells; null leaves them empty
        • filters (array of object, required): All must hold
          • When kind is compare
            • kind (compare, required)
            • column (string, required): A column name, exact and case-sensitive
            • op (string, required): One of eq, neq, gt, gte, lt, lte.
            • value (object, required)
          • When kind is in
            • kind (in, required)
            • column (string, required): A column name, exact and case-sensitive
            • values (array of object, required)
            • negate (boolean, required)
          • When kind is between
            • kind (between, required)
            • column (string, required): A column name, exact and case-sensitive
            • from (object, required)
            • to (object, required)
          • When kind is text
            • kind (text, required)
            • column (string, required): A column name, exact and case-sensitive
            • op (string, required): One of equals, contains, starts_with, ends_with.
            • value (object, required)
            • caseSensitive (boolean, required)
            • negate (boolean, required)
          • When kind is is_blank
            • kind (is_blank, required)
            • column (string, required): A column name, exact and case-sensitive
            • negate (boolean, required)
          • When kind is relative_date
            • kind (relative_date, required)
            • column (string, required): A column name, exact and case-sensitive
            • range (string, required): One of current, last, next.
            • count (integer or null, required)
            • unit (string, required): One of day, week, month, quarter, year.
            • complete (boolean): last/next only: whole units, leaving out the current one, e.g. the last 3 complete months on Oct 21 are Jul, Aug and Sep. Omit for the window through today
            • includeCurrent (boolean): With complete only: the current unit too, e.g. the last 1 complete year with the current one is last year and this year. Omit to leave it out
            • toDate (boolean): current only: the unit so far, through today, e.g. this year to date. Omit for the whole unit
            • earlier (object): current/last only: the same window, this many units earlier: this year to date 1 year earlier is the same dates last year, and the last 3 complete months 3 months earlier are the 3 months before them. Omit for the window itself
              • count (integer, required)
              • unit (string, required): One of day, week, month, quarter, year.
        • renames (array of object): Output columns under another name
          • from (string, required): A column name, exact and case-sensitive
          • to (string, required): The column's new name
        • sorts (array of object, required): Columns to sort by, the first first, each next one breaking ties; empty for none
          • column (string, required): A column, by its name before renames; a split's cell by its values
          • direction (string, required): One of asc, desc.
          • nulls (string): One of first, last.
        • limit (integer or object or null, required): Rows to keep: a number, an integer parameter, or null for all
          • type (parameter, required)
          • key (string, required)
        • limitFrom (string): One of start, end.
        • totals (object): A table's total row, its subtotal rows and, with a split, its Total column, each measure over the rows shown (under a limit, only those). Omit for none
          • row (boolean, required): A last row with the total of each column; charts leave it out
          • column (boolean, required): With a pivot after the aggregate: a Total column with each row across the pivot
          • subtotals (boolean): Grouped by two or more columns: a subtotal row above each group of every group-by column but the last, as a pivot table's subtotals, with each measure over the group's rows; charts leave them out
        • formats (array of object): numfmt patterns by output column
          • column (string, required): An output column, by the name it is shown under, after any rename
          • format (string, required): numfmt pattern, e.g. #,##0.00, $#,##0, 0.0%, yyyy-mm-dd
        • removedColumns (array of string): Columns, by their name before renames, to leave out of the rows while keeping the grouping, e.g. group by region and month but show no region column; a split's column is named by its values
        • keepGroupsWhere (array of object): Conditions on the grouped rows' dimensions and measures (by their name before renames), all of which must hold, right after the grouping and before the calculations, the sort and the limit, as SQL's HAVING does, so the calculations and the limit only see the rows kept: each region's share among the regions with more than 20 deals. To keep rows by a measure or a calculation while the calculations still cover every group, as for the top 10 of the customers over 1,000,000, use keepRowsBeforeLimit. Needs a dimension or measure and no split
          • When kind is compare
            • kind (compare, required)
            • column (string, required): A column name, exact and case-sensitive
            • op (string, required): One of eq, neq, gt, gte, lt, lte.
            • value (object, required)
          • When kind is in
            • kind (in, required)
            • column (string, required): A column name, exact and case-sensitive
            • values (array of object, required)
            • negate (boolean, required)
          • When kind is between
            • kind (between, required)
            • column (string, required): A column name, exact and case-sensitive
            • from (object, required)
            • to (object, required)
          • When kind is text
            • kind (text, required)
            • column (string, required): A column name, exact and case-sensitive
            • op (string, required): One of equals, contains, starts_with, ends_with.
            • value (object, required)
            • caseSensitive (boolean, required)
            • negate (boolean, required)
          • When kind is is_blank
            • kind (is_blank, required)
            • column (string, required): A column name, exact and case-sensitive
            • negate (boolean, required)
          • When kind is relative_date
            • kind (relative_date, required)
            • column (string, required): A column name, exact and case-sensitive
            • range (string, required): One of current, last, next.
            • count (integer or null, required)
            • unit (string, required): One of day, week, month, quarter, year.
            • complete (boolean): last/next only: whole units, leaving out the current one, e.g. the last 3 complete months on Oct 21 are Jul, Aug and Sep. Omit for the window through today
            • includeCurrent (boolean): With complete only: the current unit too, e.g. the last 1 complete year with the current one is last year and this year. Omit to leave it out
            • toDate (boolean): current only: the unit so far, through today, e.g. this year to date. Omit for the whole unit
            • earlier (object): current/last only: the same window, this many units earlier: this year to date 1 year earlier is the same dates last year, and the last 3 complete months 3 months earlier are the 3 months before them. Omit for the window itself
              • count (integer, required)
              • unit (string, required): One of day, week, month, quarter, year.
        • keepRowsBeforeLimit (array of object): Conditions on the grouped rows' dimensions, measures and before-limit calculations (by their name before renames), all of which must hold, after the calculations and before the column order, the sort and the limit, so the limit counts only the rows kept while the calculations still cover every group: the top 10 customers of those over 1,000,000, the regions with over 5% of the total. A condition on a calculation that reads the rows the limit keeps (sum(), offset()) goes in keepRowsWhere. Needs a dimension or measure and no split
          • When kind is compare
            • kind (compare, required)
            • column (string, required): A column name, exact and case-sensitive
            • op (string, required): One of eq, neq, gt, gte, lt, lte.
            • value (object, required)
          • When kind is in
            • kind (in, required)
            • column (string, required): A column name, exact and case-sensitive
            • values (array of object, required)
            • negate (boolean, required)
          • When kind is between
            • kind (between, required)
            • column (string, required): A column name, exact and case-sensitive
            • from (object, required)
            • to (object, required)
          • When kind is text
            • kind (text, required)
            • column (string, required): A column name, exact and case-sensitive
            • op (string, required): One of equals, contains, starts_with, ends_with.
            • value (object, required)
            • caseSensitive (boolean, required)
            • negate (boolean, required)
          • When kind is is_blank
            • kind (is_blank, required)
            • column (string, required): A column name, exact and case-sensitive
            • negate (boolean, required)
          • When kind is relative_date
            • kind (relative_date, required)
            • column (string, required): A column name, exact and case-sensitive
            • range (string, required): One of current, last, next.
            • count (integer or null, required)
            • unit (string, required): One of day, week, month, quarter, year.
            • complete (boolean): last/next only: whole units, leaving out the current one, e.g. the last 3 complete months on Oct 21 are Jul, Aug and Sep. Omit for the window through today
            • includeCurrent (boolean): With complete only: the current unit too, e.g. the last 1 complete year with the current one is last year and this year. Omit to leave it out
            • toDate (boolean): current only: the unit so far, through today, e.g. this year to date. Omit for the whole unit
            • earlier (object): current/last only: the same window, this many units earlier: this year to date 1 year earlier is the same dates last year, and the last 3 complete months 3 months earlier are the 3 months before them. Omit for the window itself
              • count (integer, required)
              • unit (string, required): One of day, week, month, quarter, year.
        • keepRowsWhere (array of object): Conditions on the rows' own columns (dimensions, measures and calculations by their name before renames), all of which must hold, after the sort, the limit and every calculation: keep the rank 1 row of the top 10, with its share of the 10. To keep rows before the limit, use keepRowsBeforeLimit. Needs a dimension or measure and no split
          • When kind is compare
            • kind (compare, required)
            • column (string, required): A column name, exact and case-sensitive
            • op (string, required): One of eq, neq, gt, gte, lt, lte.
            • value (object, required)
          • When kind is in
            • kind (in, required)
            • column (string, required): A column name, exact and case-sensitive
            • values (array of object, required)
            • negate (boolean, required)
          • When kind is between
            • kind (between, required)
            • column (string, required): A column name, exact and case-sensitive
            • from (object, required)
            • to (object, required)
          • When kind is text
            • kind (text, required)
            • column (string, required): A column name, exact and case-sensitive
            • op (string, required): One of equals, contains, starts_with, ends_with.
            • value (object, required)
            • caseSensitive (boolean, required)
            • negate (boolean, required)
          • When kind is is_blank
            • kind (is_blank, required)
            • column (string, required): A column name, exact and case-sensitive
            • negate (boolean, required)
          • When kind is relative_date
            • kind (relative_date, required)
            • column (string, required): A column name, exact and case-sensitive
            • range (string, required): One of current, last, next.
            • count (integer or null, required)
            • unit (string, required): One of day, week, month, quarter, year.
            • complete (boolean): last/next only: whole units, leaving out the current one, e.g. the last 3 complete months on Oct 21 are Jul, Aug and Sep. Omit for the window through today
            • includeCurrent (boolean): With complete only: the current unit too, e.g. the last 1 complete year with the current one is last year and this year. Omit to leave it out
            • toDate (boolean): current only: the unit so far, through today, e.g. this year to date. Omit for the whole unit
            • earlier (object): current/last only: the same window, this many units earlier: this year to date 1 year earlier is the same dates last year, and the last 3 complete months 3 months earlier are the 3 months before them. Omit for the window itself
              • count (integer, required)
              • unit (string, required): One of day, week, month, quarter, year.
    • When type is set-ops
      • type (set-ops, required)
      • datasheetRef (string, required): An existing datasheet id, or the temporaryId of a datasheet created earlier in this patch
      • parameters (array of object or null): Replaces the datasheet's complete parameter set; omit to keep it
        • key (string, required)
        • parameter (object, required)
          • When type is string
            • label (string, required)
            • type (string, required)
            • value (string or null, required)
          • When type is integer
            • label (string, required)
            • type (integer, required)
            • value (integer or null, required)
          • When type is number
            • label (string, required)
            • type (number, required)
            • value (number or null, required)
          • When type is boolean
            • label (string, required)
            • type (boolean, required)
            • value (boolean or null, required)
          • When type is date
            • label (string, required)
            • type (date, required)
            • value (string (date) or null, required)
          • When type is timestamp
            • label (string, required)
            • type (timestamp, required)
            • value (string (date-time) or null, required)
      • ops (array of object, required): The complete op list after the reads, in run order: an op whose ref is an existing op id keeps that op, and existing ops left out are removed. Only on a datasheet with a join step: keep the join first with steps after it; on any other, use set-query
        • ref (string, required): An existing op id to keep that op, or a new short name
        • title (string or null): Omit to keep a kept op's title; a step op without one shows its step summary
        • inputs (array of object or null): Omit to keep a kept op wired as it is (one that read the op above reads its new op above). A new op reads the op above, which SQL reads as input; a new first op reads the datasheet's one read, as source or the read's alias
          • alias (string or null): SQL ops: the name the statement reads this input by
          • from (string, required): previous: the op above (above the first op, the datasheet's one read); op: the one ref names One of previous, op.
          • ref (string or null): op: the ref of an earlier op in this list, or a read's operation id or alias
        • step (object or null): A step op's call; on a kept op, send it only to change it
          • fn (string, required): One of select, rename, filter, sort, limit, aggregate, pivot, window, join.
          • params (object, required): The params of its fn, whose exact JSON Schema getReportDataGuide answers when you name the fn in stepFunctions
      • formats (array of object or null): Replaces the datasheet's formats; omit to keep them
        • column (string, required): An output column, by the name it is shown under, after any rename
        • format (string, required): numfmt pattern, e.g. #,##0.00, $#,##0, 0.0%, yyyy-mm-dd
    • When type is set-read-type
      • type (set-read-type, required)
      • datasheetRef (string, required): An existing datasheet id, or the temporaryId of a datasheet created earlier in this patch
      • readType (string, required): aggregated: its data, as the tool displays it; raw: its tidy read, to transform; image: its picture One of aggregated, raw, image.
    • When type is set-unpivot
      • type (set-unpivot, required)
      • datasheetRef (string, required): An existing datasheet id, or the temporaryId of a datasheet created earlier in this patch
      • unpivot (object or null): The columns of the range this datasheet reads to turn into rows, which every datasheet reading the range raw gets, and this one then reads it raw. Null takes it out; omit to keep the range's current one
        • rows (array of object, required): One output row per input row and entry, in this order; the input columns no entry names repeat on each
          • label (string, required): What the name column says on this row, such as the column's name or "2022"
          • columns (array of string or null, required): The input column for each of values, in order; null leaves that value empty on this row
        • name (string, required): The column that holds each row's label, such as "Year"
        • values (array of string, required): The columns the values go in: one, such as "Value", or one per measure
      • fillDown (object or null): Run first, as Power Query fills down: each heading row, a label with every other cell empty, becomes the value of a new column on the rows under it, and the heading row is left out. Null takes it out; omit to keep the range's current one
        • column (string, required): The column holding the row labels, headings included
        • into (string, required): The new column the headings fill, such as "Section"
      • removeRows (array of object or null): Run after fillDown: rows matching any of these are left out, such as the sheet's own totals that add up the rows above them. Null takes them out; omit to keep the range's current ones
        • When kind is compare
          • kind (compare, required)
          • column (string, required): A column name, exact and case-sensitive
          • op (string, required): One of eq, neq, gt, gte, lt, lte.
          • value (object, required)
        • When kind is in
          • kind (in, required)
          • column (string, required): A column name, exact and case-sensitive
          • values (array of object, required)
          • negate (boolean, required)
        • When kind is between
          • kind (between, required)
          • column (string, required): A column name, exact and case-sensitive
          • from (object, required)
          • to (object, required)
        • When kind is text
          • kind (text, required)
          • column (string, required): A column name, exact and case-sensitive
          • op (string, required): One of equals, contains, starts_with, ends_with.
          • value (object, required)
          • caseSensitive (boolean, required)
          • negate (boolean, required)
        • When kind is is_blank
          • kind (is_blank, required)
          • column (string, required): A column name, exact and case-sensitive
          • negate (boolean, required)
        • When kind is relative_date
          • kind (relative_date, required)
          • column (string, required): A column name, exact and case-sensitive
          • range (string, required): One of current, last, next.
          • count (integer or null, required)
          • unit (string, required): One of day, week, month, quarter, year.
          • complete (boolean): last/next only: whole units, leaving out the current one, e.g. the last 3 complete months on Oct 21 are Jul, Aug and Sep. Omit for the window through today
          • includeCurrent (boolean): With complete only: the current unit too, e.g. the last 1 complete year with the current one is last year and this year. Omit to leave it out
          • toDate (boolean): current only: the unit so far, through today, e.g. this year to date. Omit for the whole unit
          • earlier (object): current/last only: the same window, this many units earlier: this year to date 1 year earlier is the same dates last year, and the last 3 complete months 3 months earlier are the 3 months before them. Omit for the window itself
            • count (integer, required)
            • unit (string, required): One of day, week, month, quarter, year.
  • revisions (object, required): The revisions the preview of the same changes answered. The save is refused when a datasheet they name changed since, so it never writes over someone else's edit: preview again and save the new revisions.

Response 200

Success

application/json

  • When saved is true
    • saved (true, required)
    • versionBeforeId (string, required): The version of the report from before the save, which restoreReportVersion restores with its datasheets as they were.
    • resolvedDatasheetIds (object, required): The id of each datasheet a create made, by its temporaryId. Place it on a slide by this id.
    • datasheets (array of object, required): The datasheets on slides whose data was read again, each with its error when the read failed.
      • id (string, required)
      • error (string or null, required)
  • When saved is false
    • saved (false, required)
    • error (string, required): Why nothing was saved: the changes failed, or a datasheet they edit changed since the preview. Preview again and save with the new revisions.