Datasheets
- OpenAPI 3.1.0 document: https://api.rollstack.com/public/v1/openapi.json
- The whole reference in Markdown: https://docs.rollstack.com/llms-full.txt
- An index of the reference: https://docs.rollstack.com/llms.txt
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 itsidfromlistReportsorcreateReport. Only reports whosetypeisSlideDeckwork here: another type, such as a Google Slides presentation, returns400. 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 asdatasheetIdtogetReportDatasheetDatato read its table (adatadatasheet only), indatasheetIdsonstartReportRunto update only some datasheets, or indatasheetIdsonsendReportMessagefor the slides agent to use it.kind(string, required): What the datasheet holds.data: a table, whichgetReportDatasheetDatareads.picture: a picture of a dashboard, tab or chart, such as a dashboard added withaddReportDatasheets, with no table to read. The slides agent can still use it when you pass it indatasheetIdsonsendReportMessage, andaddReportDashboardElementsadds the charts of a Tableau or Power BI one as data. One ofdata,picture.name(string, required): The datasheet's name, as shown in the report's editor, where editors can rename it. A datasheet added withaddReportDatasheetsstarts out named after its uploaded file, its dashboard or dashboard tab, or its range, such asA1: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 withcreateDatasetor 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,RollstackVariableorDbtsources, so expect none of them here.nullwhen 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 itnull.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 likeA1:F20, or an uploaded file. When it reads several sources, the first one's.nullin the same cases assourceType.datasetId(string or null, required): The uploaded file the datasheet reads, by the idreplaceDatasettakes, when itssourceTypeisCsvUploadorXlsxUpload. Replace the file, then start a run of the report, to bring new data into the slides drawn from it.nullfor 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.nulluntil 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.nullwhen 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'sid. 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.nullfor 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.nullwhen 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 itsidfromlistReportsorcreateReport. Only reports whosetypeisSlideDeckwork here: another type, such as a Google Slides presentation, returns400. 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,sheetIdand optionallyrange), a dashboard tab (connectionType,connectionId,dashboardIdand optionallytabId), a Salesforce report (connectionType,connectionIdandreportId), a Snowflake, Databricks or BigQuery table (connectionType,connectionId,database,schemaandtable), a Looker explore, Power BI semantic model or Tableau data source (connectionType,connectionIdandmodelId), or a range of a spreadsheet (spreadsheetId,sheetIdandrange). 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: theidreturned bycreateDataset. A CSV file comes in whole, as one table named after the file. An Excel workbook comes in one sheet at a time, so passsheetIdwith 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 thesheets[].idvalues fromgetDataset. 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 asA3: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 namedColumn 1,Column 2and 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 asA:Faren't accepted. Example:A3:H40.
- Option 2
connectionType(string, required): The type of the connection the dashboard is on:Looker,Metabase,Omni,Lightdash,Sigma,TableauorPowerBI. It must match the connection'stypeinlistConnections. One ofLooker,Metabase,Omni,Lightdash,Tableau,PowerBI,Sigma.connectionId(string, required): The connection the dashboard is on: theidof a connection fromlistConnections. You must have access to the connection in Rollstack. Example:c8e0a2b4-d6f8-4a0b-8c2d-4e6f8a0b2c4d.dashboardId(string, required): The dashboard to add: theidof a dashboard fromsearchDashboardson 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 thetabs[].idvalues fromgetDashboard, which for Tableau are a workbook's views, for Power BI a report's pages and for Sigma a workbook's pages, or thetabIdof a Sigma workbooksearchDashboardsfound 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
connectionTypeisSalesforceconnectionType(Salesforce, required):Salesforce. It must match the connection'stypeinlistConnections.connectionId(string, required): The Salesforce connection the report is on: theidof a connection fromlistConnections. You must have access to the connection in Rollstack. Example:c8e0a2b4-d6f8-4a0b-8c2d-4e6f8a0b2c4d.reportId(string, required): The report to add: theidof atabularorsummaryreport fromsearchDashboardson 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,DatabricksorBigQuery. It must match the connection'stypeinlistConnections. One ofSnowflake,Databricks,BigQuery.connectionId(string, required): The data warehouse connection the table is on: theidof a connection fromlistConnections. 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, asdatabaseinsearchTables.schema(string, required): The schema the table is in, asschemainsearchTables.table(string, required): The table or view to add, astableinsearchTables. The datasheet is nameddatabase.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,PowerBIorTableau. It must match the connection'stypeinlistConnections. One ofLooker,PowerBI,Tableau.connectionId(string, required): The connection the model is on: theidof a connection fromlistConnections. 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: theidof a Looker explore, Power BI semantic model or Tableau data source fromsearchModelson this connection. The datasheet is named after it and holds no rows: pass its id asdatasheetIdsonsendReportMessage, 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: theidof a file fromsearchSpreadsheets. 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 thesheets[].idvalues fromgetSpreadsheet. 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 asA1:F20, orB2for 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 namedColumn 1,Column 2and so on. Hidden rows and columns are left out, and whole columns such asA:Faren't accepted. Example:A1:F20.
- Option 1
Response 201
Success
application/json
datasheetIds(array of string, required): Unique identifiers of the new datasheets, one per source, in the same order assources. Pass them asdatasheetIdsonsendReportMessagefor 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 itsidfromlistReportsorcreateReport. Only reports whosetypeisSlideDeckwork here: another type, such as a Google Slides presentation, returns400. 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 itsidinlistReportDatasheets.
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):datafor a table,picturefor an image, which has no query. One ofdata,picture.elements(array of object or null, required): For the picture of a whole Tableau view or Power BI page, the charts on it, whichaddReportDashboardElementsadds as data or pictures.nullfor 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 asreftoaddReportDashboardElementsto 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 Tableauworksheetorlegend.reads(array of string, required): What it can be added as:datafor its table,picturefor 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): Whyelementsis empty or partial, such as charts not listed yet, ornull.query(object or null, required): Its data as a query, the Edit data editor's model, in the shapeset-querytakes: change what you need and send it whole.nullwhen its data is SQL or a join.sql(string or null, required): Its data as one SQL statement over its reads, ornull.ops(array of object or null, required): For a datasheet that joins its reads: the join and the steps after it, each by therefset-opskeeps it by.nullfor 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 itsidfromlistReportsorcreateReport. Only reports whosetypeisSlideDeckwork here: another type, such as a Google Slides presentation, returns400. 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 itsidinlistReportDatasheets.
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 itsidfromlistReportsorcreateReport. Only reports whosetypeisSlideDeckwork here: another type, such as a Google Slides presentation, returns400. 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: anidfromlistReportDatasheets, or one of thedatasheetIdsreturned byaddReportDatasheets. It must be one of this report's datasheets. A datasheet that reads a picture, such as a dashboard tab added withaddReportDatasheets, has no table, so the request fails: add the tab's charts as data withaddReportDashboardElements.
Query parameters
limit(integer): The maximum number of rows to return, starting from the first row of the table. If the table has more,truncatedistrue. Can range from1to1000. Defaults to200.
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 inrows.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 becomesColumn N, after the column's position, and a repeated one gets a_2,_3suffix.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 ofname, unless a table renames the column. Omitted when the column has none.type(string, required): What the column's cells contain:numberfor numbers,datefor dates and times,stringfor text, andbooleanfortrueorfalse. Any cell can also benullwhen it is empty. One ofnumber,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 mostlimitof them. Each row is an array of cells in the order ofcolumns. An empty cell isnull, and adatecell 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.nonedoesn't stack the series,stackedpiles them on top of one another, andpercentpiles 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 ofnone,stacked,percent.totalRow(true)subtotals(object): When the datasheet's query adds subtotals:rowFieldsare the columns its rows are grouped by, outermost first, andlevelshas one number per row inrows, 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, and0is 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 thanlimit, sorowsdoesn't contain all of them. Raiselimitto get more, up to the first1000rows.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 itsidfromlistReportsorcreateReport. Only reports whosetypeisSlideDeckwork here: another type, such as a Google Slides presentation, returns400. 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 itsidinlistReportDatasheets.
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'sreffrom theelementsgetReportDatasheetlists for this dashboard. For a table Tableau builds from several worksheets side by side, the refs of those worksheets joined by commas, like12,15,18, add it as one.as(string, required):datafor its table, to read withgetReportDatasheetDataand draw as a chart, table or figure on a slide;picturefor its image, to place on a slide as it looks;bothfor the two. Ask only for a read the element lists in itsreads. One ofdata,picture,both.
Response 200
Success
application/json
elements(array of object, required): Each chart you asked for, in the order ofelements.ref(string, required): Therefyou passed.name(string or null, required): The chart's name on the dashboard,nullwhen 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.nullwhen you asked only for its picture, or when it was refused.pictureDatasheetId(string or null, required): The datasheet of its picture,nullwhen 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,nullwhen 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 itsidfromlistReportsorcreateReport. Only reports whosetypeisSlideDeckwork here: another type, such as a Google Slides presentation, returns400. 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:createfor 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 atemporaryIdyou choose),set-queryorset-opsfor the data of one it has (getReportDatasheetgives what it is now),set-read-typeandset-unpivot. Each datasheet is its reads followed by the ops over them: callgetReportDataGuidefor the query model, SQL and joins. AtemporaryIdcan be used once: after a save, create the next datasheet under another.- When
typeiscreatetype(create, required)temporaryId(string, required): A stable id for the new datasheet, which later changes in this patch reference it byname(string, required): The datasheet's namereads(array of object, required): The elements it reads, each once: the one a query reads, or those a join readsalias(string, required): The name SQL reads this element by in FROM/JOIN, and ops name it by as a refdatasheetRef(string (uuid), required): An existing datasheet that reads one element: the new datasheet reads that element, with its filtersreadType(string or null): Omit to read it as that datasheet does One ofaggregated,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
typeisstringlabel(string, required)type(string, required)value(string or null, required)
- When
typeisintegerlabel(string, required)type(integer, required)value(integer or null, required)
- When
typeisnumberlabel(string, required)type(number, required)value(number or null, required)
- When
typeisbooleanlabel(string, required)type(boolean, required)value(boolean or null, required)
- When
typeisdatelabel(string, required)type(date, required)value(string (date) or null, required)
- When
typeistimestamplabel(string, required)type(timestamp, required)value(string (date-time) or null, required)
- When
query(object or null): The data as the Edit data editor builds it, over the one read; give query, or ops over several readscustomFields(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 averagedformula(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
as(string, required): The column's name
dimensions(array of object, required): The columns to group bycolumn(string, required): A column name, exact and case-sensitivebucket(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 ofday,week,month,quarter,year,month_of_year,quarter_of_year,day_of_week.as(string): Omit for the default namegroups(array of object): A text column only: values shown under one name, e.g. Email and Telephone as "Email & Tel"; the others keep theirsfill(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 ofsum,avg,min,max,median,count,count_distinct,first,last.column(string or null, required): null only with count, which counts rowsas(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 nonecolumn(string, required): A column name, exact and case-sensitivebucket(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 ofday,week,month,quarter,year,month_of_year,quarter_of_year,day_of_week.as(string): Omit for the default namegroups(array of object): A text column only: values shown under one name, e.g. Email and Telephone as "Email & Tel"; the others keep theirsfill(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
fnisrankfn(rank, required)column(string, required): A measure, by its name before renamesdirection(string, required): desc: the largest ranks 1 One ofasc,desc.as(string): The new column's name. Omit for the default name
- When
fnispercent_of_totalfn(percent_of_total, required)column(string, required): A measure, by its name before renamesas(string): The new column's name. Omit for the default name
- Option 3
fn(string, required): One ofdifference,percent_difference.column(string, required): A measure, by its name before renamesorderBy(string, required): The query's only dimension, such as a month, by its name before renames: the rows are ordered by it, oldest firstoffset(integer): How many rows back to compare with; omit for 1, the previous rowas(string): The new column's name. Omit for the default name
- Option 4
fn(string, required): One ofsum,avg,min,max.column(string, required): A measure, by its name before renamesas(string): The new column's name. Omit for the default name
- When
fnisformulafn(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 emptyas(string, required): The column's name
- When
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 calculationspivotLayout(object): Omit for the default: columns in the order their values first appearcolumns(object, required)- When
modeisfixedmode(fixed, required)values(array of object, required): One column per value, each under its own name: two values never share a column
- When
modeisdiscovermode(discover, required)order(string, required): One ofasc,desc,first_seen.limit(integer, required)
- When
nullLabel(string or null, required): Column name for NULL values; null drops themfill(number or null, required): Value for empty cells; null leaves them empty
filters(array of object, required): All must hold- When
kindiscomparekind(compare, required)column(string, required): A column name, exact and case-sensitiveop(string, required): One ofeq,neq,gt,gte,lt,lte.value(object, required)
- When
kindisinkind(in, required)column(string, required): A column name, exact and case-sensitivevalues(array of object, required)negate(boolean, required)
- When
kindisbetweenkind(between, required)column(string, required): A column name, exact and case-sensitivefrom(object, required)to(object, required)
- When
kindistextkind(text, required)column(string, required): A column name, exact and case-sensitiveop(string, required): One ofequals,contains,starts_with,ends_with.value(object, required)caseSensitive(boolean, required)negate(boolean, required)
- When
kindisis_blankkind(is_blank, required)column(string, required): A column name, exact and case-sensitivenegate(boolean, required)
- When
kindisrelative_datekind(relative_date, required)column(string, required): A column name, exact and case-sensitiverange(string, required): One ofcurrent,last,next.count(integer or null, required)unit(string, required): One ofday,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 todayincludeCurrent(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 outtoDate(boolean): current only: the unit so far, through today, e.g. this year to date. Omit for the whole unitearlier(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
- When
renames(array of object): Output columns under another namefrom(string, required): A column name, exact and case-sensitiveto(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 nonecolumn(string, required): A column, by its name before renames; a split's cell by its valuesdirection(string, required): One ofasc,desc.nulls(string): One offirst,last.
limit(integer or object or null, required): Rows to keep: a number, an integer parameter, or null for alltype(parameter, required)key(string, required)
limitFrom(string): One ofstart,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 nonerow(boolean, required): A last row with the total of each column; charts leave it outcolumn(boolean, required): With a pivot after the aggregate: a Total column with each row across the pivotsubtotals(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 columncolumn(string, required): An output column, by the name it is shown under, after any renameformat(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 valueskeepGroupsWhere(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
kindiscomparekind(compare, required)column(string, required): A column name, exact and case-sensitiveop(string, required): One ofeq,neq,gt,gte,lt,lte.value(object, required)
- When
kindisinkind(in, required)column(string, required): A column name, exact and case-sensitivevalues(array of object, required)negate(boolean, required)
- When
kindisbetweenkind(between, required)column(string, required): A column name, exact and case-sensitivefrom(object, required)to(object, required)
- When
kindistextkind(text, required)column(string, required): A column name, exact and case-sensitiveop(string, required): One ofequals,contains,starts_with,ends_with.value(object, required)caseSensitive(boolean, required)negate(boolean, required)
- When
kindisis_blankkind(is_blank, required)column(string, required): A column name, exact and case-sensitivenegate(boolean, required)
- When
kindisrelative_datekind(relative_date, required)column(string, required): A column name, exact and case-sensitiverange(string, required): One ofcurrent,last,next.count(integer or null, required)unit(string, required): One ofday,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 todayincludeCurrent(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 outtoDate(boolean): current only: the unit so far, through today, e.g. this year to date. Omit for the whole unitearlier(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
- When
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
kindiscomparekind(compare, required)column(string, required): A column name, exact and case-sensitiveop(string, required): One ofeq,neq,gt,gte,lt,lte.value(object, required)
- When
kindisinkind(in, required)column(string, required): A column name, exact and case-sensitivevalues(array of object, required)negate(boolean, required)
- When
kindisbetweenkind(between, required)column(string, required): A column name, exact and case-sensitivefrom(object, required)to(object, required)
- When
kindistextkind(text, required)column(string, required): A column name, exact and case-sensitiveop(string, required): One ofequals,contains,starts_with,ends_with.value(object, required)caseSensitive(boolean, required)negate(boolean, required)
- When
kindisis_blankkind(is_blank, required)column(string, required): A column name, exact and case-sensitivenegate(boolean, required)
- When
kindisrelative_datekind(relative_date, required)column(string, required): A column name, exact and case-sensitiverange(string, required): One ofcurrent,last,next.count(integer or null, required)unit(string, required): One ofday,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 todayincludeCurrent(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 outtoDate(boolean): current only: the unit so far, through today, e.g. this year to date. Omit for the whole unitearlier(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
- When
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
kindiscomparekind(compare, required)column(string, required): A column name, exact and case-sensitiveop(string, required): One ofeq,neq,gt,gte,lt,lte.value(object, required)
- When
kindisinkind(in, required)column(string, required): A column name, exact and case-sensitivevalues(array of object, required)negate(boolean, required)
- When
kindisbetweenkind(between, required)column(string, required): A column name, exact and case-sensitivefrom(object, required)to(object, required)
- When
kindistextkind(text, required)column(string, required): A column name, exact and case-sensitiveop(string, required): One ofequals,contains,starts_with,ends_with.value(object, required)caseSensitive(boolean, required)negate(boolean, required)
- When
kindisis_blankkind(is_blank, required)column(string, required): A column name, exact and case-sensitivenegate(boolean, required)
- When
kindisrelative_datekind(relative_date, required)column(string, required): A column name, exact and case-sensitiverange(string, required): One ofcurrent,last,next.count(integer or null, required)unit(string, required): One ofday,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 todayincludeCurrent(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 outtoDate(boolean): current only: the unit so far, through today, e.g. this year to date. Omit for the whole unitearlier(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
- When
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 stepsref(string, required): An existing op id to keep that op, or a new short nametitle(string or null): Omit to keep a kept op's title; a step op without one shows its step summaryinputs(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 asinput; a new first op reads the datasheet's one read, assourceor the read's aliasstep(object or null): A step op's call; on a kept op, send it only to change itfn(string, required): One ofselect,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 formatscolumn(string, required): An output column, by the name it is shown under, after any renameformat(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
typeisset-querytype(set-query, required)datasheetRef(string, required): An existing datasheet id, or the temporaryId of a datasheet created earlier in this patchparameters(array of object or null): Replaces the datasheet's complete parameter set; omit to keep itkey(string, required)parameter(object, required)- When
typeisstringlabel(string, required)type(string, required)value(string or null, required)
- When
typeisintegerlabel(string, required)type(integer, required)value(integer or null, required)
- When
typeisnumberlabel(string, required)type(number, required)value(number or null, required)
- When
typeisbooleanlabel(string, required)type(boolean, required)value(boolean or null, required)
- When
typeisdatelabel(string, required)type(date, required)value(string (date) or null, required)
- When
typeistimestamplabel(string, required)type(timestamp, required)value(string (date-time) or null, required)
- When
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 fromcustomFields(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 averagedformula(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
as(string, required): The column's name
dimensions(array of object, required): The columns to group bycolumn(string, required): A column name, exact and case-sensitivebucket(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 ofday,week,month,quarter,year,month_of_year,quarter_of_year,day_of_week.as(string): Omit for the default namegroups(array of object): A text column only: values shown under one name, e.g. Email and Telephone as "Email & Tel"; the others keep theirsname(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 ofsum,avg,min,max,median,count,count_distinct,first,last.column(string or null, required): null only with count, which counts rowsas(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 nonecolumn(string, required): A column name, exact and case-sensitivebucket(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 ofday,week,month,quarter,year,month_of_year,quarter_of_year,day_of_week.as(string): Omit for the default namegroups(array of object): A text column only: values shown under one name, e.g. Email and Telephone as "Email & Tel"; the others keep theirsname(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
fnisrankfn(rank, required)column(string, required): A measure, by its name before renamesdirection(string, required): desc: the largest ranks 1 One ofasc,desc.as(string): The new column's name. Omit for the default name
- When
fnispercent_of_totalfn(percent_of_total, required)column(string, required): A measure, by its name before renamesas(string): The new column's name. Omit for the default name
- Option 3
fn(string, required): One ofdifference,percent_difference.column(string, required): A measure, by its name before renamesorderBy(string, required): The query's only dimension, such as a month, by its name before renames: the rows are ordered by it, oldest firstoffset(integer): How many rows back to compare with; omit for 1, the previous rowas(string): The new column's name. Omit for the default name
- Option 4
fn(string, required): One ofsum,avg,min,max.column(string, required): A measure, by its name before renamesas(string): The new column's name. Omit for the default name
- When
fnisformulafn(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 emptyas(string, required): The column's name
- When
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 calculationspivotLayout(object): Omit for the default: columns in the order their values first appearcolumns(object, required)- When
modeisfixedmode(fixed, required)values(array of object, required): One column per value, each under its own name: two values never share a column
- When
modeisdiscovermode(discover, required)order(string, required): One ofasc,desc,first_seen.limit(integer, required)
- When
nullLabel(string or null, required): Column name for NULL values; null drops themfill(number or null, required): Value for empty cells; null leaves them empty
filters(array of object, required): All must hold- When
kindiscomparekind(compare, required)column(string, required): A column name, exact and case-sensitiveop(string, required): One ofeq,neq,gt,gte,lt,lte.value(object, required)
- When
kindisinkind(in, required)column(string, required): A column name, exact and case-sensitivevalues(array of object, required)negate(boolean, required)
- When
kindisbetweenkind(between, required)column(string, required): A column name, exact and case-sensitivefrom(object, required)to(object, required)
- When
kindistextkind(text, required)column(string, required): A column name, exact and case-sensitiveop(string, required): One ofequals,contains,starts_with,ends_with.value(object, required)caseSensitive(boolean, required)negate(boolean, required)
- When
kindisis_blankkind(is_blank, required)column(string, required): A column name, exact and case-sensitivenegate(boolean, required)
- When
kindisrelative_datekind(relative_date, required)column(string, required): A column name, exact and case-sensitiverange(string, required): One ofcurrent,last,next.count(integer or null, required)unit(string, required): One ofday,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 todayincludeCurrent(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 outtoDate(boolean): current only: the unit so far, through today, e.g. this year to date. Omit for the whole unitearlier(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 itselfcount(integer, required)unit(string, required): One ofday,week,month,quarter,year.
- When
renames(array of object): Output columns under another namefrom(string, required): A column name, exact and case-sensitiveto(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 nonecolumn(string, required): A column, by its name before renames; a split's cell by its valuesdirection(string, required): One ofasc,desc.nulls(string): One offirst,last.
limit(integer or object or null, required): Rows to keep: a number, an integer parameter, or null for alltype(parameter, required)key(string, required)
limitFrom(string): One ofstart,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 nonerow(boolean, required): A last row with the total of each column; charts leave it outcolumn(boolean, required): With a pivot after the aggregate: a Total column with each row across the pivotsubtotals(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 columncolumn(string, required): An output column, by the name it is shown under, after any renameformat(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 valueskeepGroupsWhere(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
kindiscomparekind(compare, required)column(string, required): A column name, exact and case-sensitiveop(string, required): One ofeq,neq,gt,gte,lt,lte.value(object, required)
- When
kindisinkind(in, required)column(string, required): A column name, exact and case-sensitivevalues(array of object, required)negate(boolean, required)
- When
kindisbetweenkind(between, required)column(string, required): A column name, exact and case-sensitivefrom(object, required)to(object, required)
- When
kindistextkind(text, required)column(string, required): A column name, exact and case-sensitiveop(string, required): One ofequals,contains,starts_with,ends_with.value(object, required)caseSensitive(boolean, required)negate(boolean, required)
- When
kindisis_blankkind(is_blank, required)column(string, required): A column name, exact and case-sensitivenegate(boolean, required)
- When
kindisrelative_datekind(relative_date, required)column(string, required): A column name, exact and case-sensitiverange(string, required): One ofcurrent,last,next.count(integer or null, required)unit(string, required): One ofday,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 todayincludeCurrent(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 outtoDate(boolean): current only: the unit so far, through today, e.g. this year to date. Omit for the whole unitearlier(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 itselfcount(integer, required)unit(string, required): One ofday,week,month,quarter,year.
- When
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
kindiscomparekind(compare, required)column(string, required): A column name, exact and case-sensitiveop(string, required): One ofeq,neq,gt,gte,lt,lte.value(object, required)
- When
kindisinkind(in, required)column(string, required): A column name, exact and case-sensitivevalues(array of object, required)negate(boolean, required)
- When
kindisbetweenkind(between, required)column(string, required): A column name, exact and case-sensitivefrom(object, required)to(object, required)
- When
kindistextkind(text, required)column(string, required): A column name, exact and case-sensitiveop(string, required): One ofequals,contains,starts_with,ends_with.value(object, required)caseSensitive(boolean, required)negate(boolean, required)
- When
kindisis_blankkind(is_blank, required)column(string, required): A column name, exact and case-sensitivenegate(boolean, required)
- When
kindisrelative_datekind(relative_date, required)column(string, required): A column name, exact and case-sensitiverange(string, required): One ofcurrent,last,next.count(integer or null, required)unit(string, required): One ofday,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 todayincludeCurrent(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 outtoDate(boolean): current only: the unit so far, through today, e.g. this year to date. Omit for the whole unitearlier(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 itselfcount(integer, required)unit(string, required): One ofday,week,month,quarter,year.
- When
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
kindiscomparekind(compare, required)column(string, required): A column name, exact and case-sensitiveop(string, required): One ofeq,neq,gt,gte,lt,lte.value(object, required)
- When
kindisinkind(in, required)column(string, required): A column name, exact and case-sensitivevalues(array of object, required)negate(boolean, required)
- When
kindisbetweenkind(between, required)column(string, required): A column name, exact and case-sensitivefrom(object, required)to(object, required)
- When
kindistextkind(text, required)column(string, required): A column name, exact and case-sensitiveop(string, required): One ofequals,contains,starts_with,ends_with.value(object, required)caseSensitive(boolean, required)negate(boolean, required)
- When
kindisis_blankkind(is_blank, required)column(string, required): A column name, exact and case-sensitivenegate(boolean, required)
- When
kindisrelative_datekind(relative_date, required)column(string, required): A column name, exact and case-sensitiverange(string, required): One ofcurrent,last,next.count(integer or null, required)unit(string, required): One ofday,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 todayincludeCurrent(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 outtoDate(boolean): current only: the unit so far, through today, e.g. this year to date. Omit for the whole unitearlier(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 itselfcount(integer, required)unit(string, required): One ofday,week,month,quarter,year.
- When
- When
typeisset-opstype(set-ops, required)datasheetRef(string, required): An existing datasheet id, or the temporaryId of a datasheet created earlier in this patchparameters(array of object or null): Replaces the datasheet's complete parameter set; omit to keep itkey(string, required)parameter(object, required)- When
typeisstringlabel(string, required)type(string, required)value(string or null, required)
- When
typeisintegerlabel(string, required)type(integer, required)value(integer or null, required)
- When
typeisnumberlabel(string, required)type(number, required)value(number or null, required)
- When
typeisbooleanlabel(string, required)type(boolean, required)value(boolean or null, required)
- When
typeisdatelabel(string, required)type(date, required)value(string (date) or null, required)
- When
typeistimestamplabel(string, required)type(timestamp, required)value(string (date-time) or null, required)
- When
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-queryref(string, required): An existing op id to keep that op, or a new short nametitle(string or null): Omit to keep a kept op's title; a step op without one shows its step summaryinputs(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 asinput; a new first op reads the datasheet's one read, assourceor the read's aliasalias(string or null): SQL ops: the name the statement reads this input byfrom(string, required): previous: the op above (above the first op, the datasheet's one read); op: the one ref names One ofprevious,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 itfn(string, required): One ofselect,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 themcolumn(string, required): An output column, by the name it is shown under, after any renameformat(string, required): numfmt pattern, e.g. #,##0.00, $#,##0, 0.0%, yyyy-mm-dd
- When
typeisset-read-typetype(set-read-type, required)datasheetRef(string, required): An existing datasheet id, or the temporaryId of a datasheet created earlier in this patchreadType(string, required): aggregated: its data, as the tool displays it; raw: its tidy read, to transform; image: its picture One ofaggregated,raw,image.
- When
typeisset-unpivottype(set-unpivot, required)datasheetRef(string, required): An existing datasheet id, or the temporaryId of a datasheet created earlier in this patchunpivot(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 onerows(array of object, required): One output row per input row and entry, in this order; the input columns no entry names repeat on eachlabel(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 onecolumn(string, required): The column holding the row labels, headings includedinto(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
kindiscomparekind(compare, required)column(string, required): A column name, exact and case-sensitiveop(string, required): One ofeq,neq,gt,gte,lt,lte.value(object, required)
- When
kindisinkind(in, required)column(string, required): A column name, exact and case-sensitivevalues(array of object, required)negate(boolean, required)
- When
kindisbetweenkind(between, required)column(string, required): A column name, exact and case-sensitivefrom(object, required)to(object, required)
- When
kindistextkind(text, required)column(string, required): A column name, exact and case-sensitiveop(string, required): One ofequals,contains,starts_with,ends_with.value(object, required)caseSensitive(boolean, required)negate(boolean, required)
- When
kindisis_blankkind(is_blank, required)column(string, required): A column name, exact and case-sensitivenegate(boolean, required)
- When
kindisrelative_datekind(relative_date, required)column(string, required): A column name, exact and case-sensitiverange(string, required): One ofcurrent,last,next.count(integer or null, required)unit(string, required): One ofday,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 todayincludeCurrent(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 outtoDate(boolean): current only: the unit so far, through today, e.g. this year to date. Omit for the whole unitearlier(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 itselfcount(integer, required)unit(string, required): One ofday,week,month,quarter,year.
- When
- When
Response 200
Success
application/json
- When
validistruevalid(true, required)resolvedDatasheetIds(object, required): The id of each datasheet acreatemakes, by itstemporaryId. Place a new datasheet on a slide by this id:renderReportSlidesdraws it with the same changes andsaveReportDatasheetChangessaves it under it.resolvedOperationIds(object, required): The id of each op a change adds, by itsref.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 tosaveReportDatasheetChanges.
- When
validisfalsevalid(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 itsidfromlistReportsorcreateReport. Only reports whosetypeisSlideDeckwork here: another type, such as a Google Slides presentation, returns400. 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 (SELECTorWITH). Its tables are the report's datasheets, named by theiridfromlistReportDatasheetsin double quotes, as inSELECT region, SUM(revenue) AS revenue FROM "<id>" GROUP BY region. A column is a number when every value is one, else text, dates included:CASTan ISO date (YYYY-MM-DD) toDATEbeforeyear(),date_trunc()or date arithmetic. Give every computed column a name withAS.
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 inrows.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 becomesColumn N, after the column's position, and a repeated one gets a_2,_3suffix.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 ofname, unless a table renames the column. Omitted when the column has none.type(string, required): What the column's cells contain:numberfor numbers,datefor dates and times,stringfor text, andbooleanfortrueorfalse. Any cell can also benullwhen it is empty. One ofnumber,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 mostlimitof them. Each row is an array of cells in the order ofcolumns. An empty cell isnull, and adatecell 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:rowFieldsare the columns its rows are grouped by, outermost first, andlevelshas one number per row inrows, 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, and0is 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 thanlimit, sorowsdoesn't contain all of them. Raiselimitto get more, up to the first1000rows.
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 itsidfromlistReportsorcreateReport. Only reports whosetypeisSlideDeckwork here: another type, such as a Google Slides presentation, returns400. 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:createfor 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 atemporaryIdyou choose),set-queryorset-opsfor the data of one it has (getReportDatasheetgives what it is now),set-read-typeandset-unpivot. Each datasheet is its reads followed by the ops over them: callgetReportDataGuidefor the query model, SQL and joins. AtemporaryIdcan be used once: after a save, create the next datasheet under another.- When
typeiscreatetype(create, required)temporaryId(string, required): A stable id for the new datasheet, which later changes in this patch reference it byname(string, required): The datasheet's namereads(array of object, required): The elements it reads, each once: the one a query reads, or those a join readsalias(string, required): The name SQL reads this element by in FROM/JOIN, and ops name it by as a refdatasheetRef(string (uuid), required): An existing datasheet that reads one element: the new datasheet reads that element, with its filtersreadType(string or null): Omit to read it as that datasheet does One ofaggregated,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
typeisstringlabel(string, required)type(string, required)value(string or null, required)
- When
typeisintegerlabel(string, required)type(integer, required)value(integer or null, required)
- When
typeisnumberlabel(string, required)type(number, required)value(number or null, required)
- When
typeisbooleanlabel(string, required)type(boolean, required)value(boolean or null, required)
- When
typeisdatelabel(string, required)type(date, required)value(string (date) or null, required)
- When
typeistimestamplabel(string, required)type(timestamp, required)value(string (date-time) or null, required)
- When
query(object or null): The data as the Edit data editor builds it, over the one read; give query, or ops over several readscustomFields(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 averagedformula(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
as(string, required): The column's name
dimensions(array of object, required): The columns to group bycolumn(string, required): A column name, exact and case-sensitivebucket(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 ofday,week,month,quarter,year,month_of_year,quarter_of_year,day_of_week.as(string): Omit for the default namegroups(array of object): A text column only: values shown under one name, e.g. Email and Telephone as "Email & Tel"; the others keep theirsfill(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 ofsum,avg,min,max,median,count,count_distinct,first,last.column(string or null, required): null only with count, which counts rowsas(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 nonecolumn(string, required): A column name, exact and case-sensitivebucket(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 ofday,week,month,quarter,year,month_of_year,quarter_of_year,day_of_week.as(string): Omit for the default namegroups(array of object): A text column only: values shown under one name, e.g. Email and Telephone as "Email & Tel"; the others keep theirsfill(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
fnisrankfn(rank, required)column(string, required): A measure, by its name before renamesdirection(string, required): desc: the largest ranks 1 One ofasc,desc.as(string): The new column's name. Omit for the default name
- When
fnispercent_of_totalfn(percent_of_total, required)column(string, required): A measure, by its name before renamesas(string): The new column's name. Omit for the default name
- Option 3
fn(string, required): One ofdifference,percent_difference.column(string, required): A measure, by its name before renamesorderBy(string, required): The query's only dimension, such as a month, by its name before renames: the rows are ordered by it, oldest firstoffset(integer): How many rows back to compare with; omit for 1, the previous rowas(string): The new column's name. Omit for the default name
- Option 4
fn(string, required): One ofsum,avg,min,max.column(string, required): A measure, by its name before renamesas(string): The new column's name. Omit for the default name
- When
fnisformulafn(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 emptyas(string, required): The column's name
- When
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 calculationspivotLayout(object): Omit for the default: columns in the order their values first appearcolumns(object, required)- When
modeisfixedmode(fixed, required)values(array of object, required): One column per value, each under its own name: two values never share a column
- When
modeisdiscovermode(discover, required)order(string, required): One ofasc,desc,first_seen.limit(integer, required)
- When
nullLabel(string or null, required): Column name for NULL values; null drops themfill(number or null, required): Value for empty cells; null leaves them empty
filters(array of object, required): All must hold- When
kindiscomparekind(compare, required)column(string, required): A column name, exact and case-sensitiveop(string, required): One ofeq,neq,gt,gte,lt,lte.value(object, required)
- When
kindisinkind(in, required)column(string, required): A column name, exact and case-sensitivevalues(array of object, required)negate(boolean, required)
- When
kindisbetweenkind(between, required)column(string, required): A column name, exact and case-sensitivefrom(object, required)to(object, required)
- When
kindistextkind(text, required)column(string, required): A column name, exact and case-sensitiveop(string, required): One ofequals,contains,starts_with,ends_with.value(object, required)caseSensitive(boolean, required)negate(boolean, required)
- When
kindisis_blankkind(is_blank, required)column(string, required): A column name, exact and case-sensitivenegate(boolean, required)
- When
kindisrelative_datekind(relative_date, required)column(string, required): A column name, exact and case-sensitiverange(string, required): One ofcurrent,last,next.count(integer or null, required)unit(string, required): One ofday,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 todayincludeCurrent(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 outtoDate(boolean): current only: the unit so far, through today, e.g. this year to date. Omit for the whole unitearlier(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
- When
renames(array of object): Output columns under another namefrom(string, required): A column name, exact and case-sensitiveto(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 nonecolumn(string, required): A column, by its name before renames; a split's cell by its valuesdirection(string, required): One ofasc,desc.nulls(string): One offirst,last.
limit(integer or object or null, required): Rows to keep: a number, an integer parameter, or null for alltype(parameter, required)key(string, required)
limitFrom(string): One ofstart,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 nonerow(boolean, required): A last row with the total of each column; charts leave it outcolumn(boolean, required): With a pivot after the aggregate: a Total column with each row across the pivotsubtotals(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 columncolumn(string, required): An output column, by the name it is shown under, after any renameformat(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 valueskeepGroupsWhere(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
kindiscomparekind(compare, required)column(string, required): A column name, exact and case-sensitiveop(string, required): One ofeq,neq,gt,gte,lt,lte.value(object, required)
- When
kindisinkind(in, required)column(string, required): A column name, exact and case-sensitivevalues(array of object, required)negate(boolean, required)
- When
kindisbetweenkind(between, required)column(string, required): A column name, exact and case-sensitivefrom(object, required)to(object, required)
- When
kindistextkind(text, required)column(string, required): A column name, exact and case-sensitiveop(string, required): One ofequals,contains,starts_with,ends_with.value(object, required)caseSensitive(boolean, required)negate(boolean, required)
- When
kindisis_blankkind(is_blank, required)column(string, required): A column name, exact and case-sensitivenegate(boolean, required)
- When
kindisrelative_datekind(relative_date, required)column(string, required): A column name, exact and case-sensitiverange(string, required): One ofcurrent,last,next.count(integer or null, required)unit(string, required): One ofday,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 todayincludeCurrent(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 outtoDate(boolean): current only: the unit so far, through today, e.g. this year to date. Omit for the whole unitearlier(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
- When
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
kindiscomparekind(compare, required)column(string, required): A column name, exact and case-sensitiveop(string, required): One ofeq,neq,gt,gte,lt,lte.value(object, required)
- When
kindisinkind(in, required)column(string, required): A column name, exact and case-sensitivevalues(array of object, required)negate(boolean, required)
- When
kindisbetweenkind(between, required)column(string, required): A column name, exact and case-sensitivefrom(object, required)to(object, required)
- When
kindistextkind(text, required)column(string, required): A column name, exact and case-sensitiveop(string, required): One ofequals,contains,starts_with,ends_with.value(object, required)caseSensitive(boolean, required)negate(boolean, required)
- When
kindisis_blankkind(is_blank, required)column(string, required): A column name, exact and case-sensitivenegate(boolean, required)
- When
kindisrelative_datekind(relative_date, required)column(string, required): A column name, exact and case-sensitiverange(string, required): One ofcurrent,last,next.count(integer or null, required)unit(string, required): One ofday,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 todayincludeCurrent(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 outtoDate(boolean): current only: the unit so far, through today, e.g. this year to date. Omit for the whole unitearlier(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
- When
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
kindiscomparekind(compare, required)column(string, required): A column name, exact and case-sensitiveop(string, required): One ofeq,neq,gt,gte,lt,lte.value(object, required)
- When
kindisinkind(in, required)column(string, required): A column name, exact and case-sensitivevalues(array of object, required)negate(boolean, required)
- When
kindisbetweenkind(between, required)column(string, required): A column name, exact and case-sensitivefrom(object, required)to(object, required)
- When
kindistextkind(text, required)column(string, required): A column name, exact and case-sensitiveop(string, required): One ofequals,contains,starts_with,ends_with.value(object, required)caseSensitive(boolean, required)negate(boolean, required)
- When
kindisis_blankkind(is_blank, required)column(string, required): A column name, exact and case-sensitivenegate(boolean, required)
- When
kindisrelative_datekind(relative_date, required)column(string, required): A column name, exact and case-sensitiverange(string, required): One ofcurrent,last,next.count(integer or null, required)unit(string, required): One ofday,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 todayincludeCurrent(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 outtoDate(boolean): current only: the unit so far, through today, e.g. this year to date. Omit for the whole unitearlier(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
- When
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 stepsref(string, required): An existing op id to keep that op, or a new short nametitle(string or null): Omit to keep a kept op's title; a step op without one shows its step summaryinputs(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 asinput; a new first op reads the datasheet's one read, assourceor the read's aliasstep(object or null): A step op's call; on a kept op, send it only to change itfn(string, required): One ofselect,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 formatscolumn(string, required): An output column, by the name it is shown under, after any renameformat(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
typeisset-querytype(set-query, required)datasheetRef(string, required): An existing datasheet id, or the temporaryId of a datasheet created earlier in this patchparameters(array of object or null): Replaces the datasheet's complete parameter set; omit to keep itkey(string, required)parameter(object, required)- When
typeisstringlabel(string, required)type(string, required)value(string or null, required)
- When
typeisintegerlabel(string, required)type(integer, required)value(integer or null, required)
- When
typeisnumberlabel(string, required)type(number, required)value(number or null, required)
- When
typeisbooleanlabel(string, required)type(boolean, required)value(boolean or null, required)
- When
typeisdatelabel(string, required)type(date, required)value(string (date) or null, required)
- When
typeistimestamplabel(string, required)type(timestamp, required)value(string (date-time) or null, required)
- When
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 fromcustomFields(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 averagedformula(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
as(string, required): The column's name
dimensions(array of object, required): The columns to group bycolumn(string, required): A column name, exact and case-sensitivebucket(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 ofday,week,month,quarter,year,month_of_year,quarter_of_year,day_of_week.as(string): Omit for the default namegroups(array of object): A text column only: values shown under one name, e.g. Email and Telephone as "Email & Tel"; the others keep theirsname(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 ofsum,avg,min,max,median,count,count_distinct,first,last.column(string or null, required): null only with count, which counts rowsas(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 nonecolumn(string, required): A column name, exact and case-sensitivebucket(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 ofday,week,month,quarter,year,month_of_year,quarter_of_year,day_of_week.as(string): Omit for the default namegroups(array of object): A text column only: values shown under one name, e.g. Email and Telephone as "Email & Tel"; the others keep theirsname(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
fnisrankfn(rank, required)column(string, required): A measure, by its name before renamesdirection(string, required): desc: the largest ranks 1 One ofasc,desc.as(string): The new column's name. Omit for the default name
- When
fnispercent_of_totalfn(percent_of_total, required)column(string, required): A measure, by its name before renamesas(string): The new column's name. Omit for the default name
- Option 3
fn(string, required): One ofdifference,percent_difference.column(string, required): A measure, by its name before renamesorderBy(string, required): The query's only dimension, such as a month, by its name before renames: the rows are ordered by it, oldest firstoffset(integer): How many rows back to compare with; omit for 1, the previous rowas(string): The new column's name. Omit for the default name
- Option 4
fn(string, required): One ofsum,avg,min,max.column(string, required): A measure, by its name before renamesas(string): The new column's name. Omit for the default name
- When
fnisformulafn(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 emptyas(string, required): The column's name
- When
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 calculationspivotLayout(object): Omit for the default: columns in the order their values first appearcolumns(object, required)- When
modeisfixedmode(fixed, required)values(array of object, required): One column per value, each under its own name: two values never share a column
- When
modeisdiscovermode(discover, required)order(string, required): One ofasc,desc,first_seen.limit(integer, required)
- When
nullLabel(string or null, required): Column name for NULL values; null drops themfill(number or null, required): Value for empty cells; null leaves them empty
filters(array of object, required): All must hold- When
kindiscomparekind(compare, required)column(string, required): A column name, exact and case-sensitiveop(string, required): One ofeq,neq,gt,gte,lt,lte.value(object, required)
- When
kindisinkind(in, required)column(string, required): A column name, exact and case-sensitivevalues(array of object, required)negate(boolean, required)
- When
kindisbetweenkind(between, required)column(string, required): A column name, exact and case-sensitivefrom(object, required)to(object, required)
- When
kindistextkind(text, required)column(string, required): A column name, exact and case-sensitiveop(string, required): One ofequals,contains,starts_with,ends_with.value(object, required)caseSensitive(boolean, required)negate(boolean, required)
- When
kindisis_blankkind(is_blank, required)column(string, required): A column name, exact and case-sensitivenegate(boolean, required)
- When
kindisrelative_datekind(relative_date, required)column(string, required): A column name, exact and case-sensitiverange(string, required): One ofcurrent,last,next.count(integer or null, required)unit(string, required): One ofday,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 todayincludeCurrent(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 outtoDate(boolean): current only: the unit so far, through today, e.g. this year to date. Omit for the whole unitearlier(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 itselfcount(integer, required)unit(string, required): One ofday,week,month,quarter,year.
- When
renames(array of object): Output columns under another namefrom(string, required): A column name, exact and case-sensitiveto(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 nonecolumn(string, required): A column, by its name before renames; a split's cell by its valuesdirection(string, required): One ofasc,desc.nulls(string): One offirst,last.
limit(integer or object or null, required): Rows to keep: a number, an integer parameter, or null for alltype(parameter, required)key(string, required)
limitFrom(string): One ofstart,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 nonerow(boolean, required): A last row with the total of each column; charts leave it outcolumn(boolean, required): With a pivot after the aggregate: a Total column with each row across the pivotsubtotals(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 columncolumn(string, required): An output column, by the name it is shown under, after any renameformat(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 valueskeepGroupsWhere(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
kindiscomparekind(compare, required)column(string, required): A column name, exact and case-sensitiveop(string, required): One ofeq,neq,gt,gte,lt,lte.value(object, required)
- When
kindisinkind(in, required)column(string, required): A column name, exact and case-sensitivevalues(array of object, required)negate(boolean, required)
- When
kindisbetweenkind(between, required)column(string, required): A column name, exact and case-sensitivefrom(object, required)to(object, required)
- When
kindistextkind(text, required)column(string, required): A column name, exact and case-sensitiveop(string, required): One ofequals,contains,starts_with,ends_with.value(object, required)caseSensitive(boolean, required)negate(boolean, required)
- When
kindisis_blankkind(is_blank, required)column(string, required): A column name, exact and case-sensitivenegate(boolean, required)
- When
kindisrelative_datekind(relative_date, required)column(string, required): A column name, exact and case-sensitiverange(string, required): One ofcurrent,last,next.count(integer or null, required)unit(string, required): One ofday,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 todayincludeCurrent(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 outtoDate(boolean): current only: the unit so far, through today, e.g. this year to date. Omit for the whole unitearlier(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 itselfcount(integer, required)unit(string, required): One ofday,week,month,quarter,year.
- When
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
kindiscomparekind(compare, required)column(string, required): A column name, exact and case-sensitiveop(string, required): One ofeq,neq,gt,gte,lt,lte.value(object, required)
- When
kindisinkind(in, required)column(string, required): A column name, exact and case-sensitivevalues(array of object, required)negate(boolean, required)
- When
kindisbetweenkind(between, required)column(string, required): A column name, exact and case-sensitivefrom(object, required)to(object, required)
- When
kindistextkind(text, required)column(string, required): A column name, exact and case-sensitiveop(string, required): One ofequals,contains,starts_with,ends_with.value(object, required)caseSensitive(boolean, required)negate(boolean, required)
- When
kindisis_blankkind(is_blank, required)column(string, required): A column name, exact and case-sensitivenegate(boolean, required)
- When
kindisrelative_datekind(relative_date, required)column(string, required): A column name, exact and case-sensitiverange(string, required): One ofcurrent,last,next.count(integer or null, required)unit(string, required): One ofday,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 todayincludeCurrent(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 outtoDate(boolean): current only: the unit so far, through today, e.g. this year to date. Omit for the whole unitearlier(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 itselfcount(integer, required)unit(string, required): One ofday,week,month,quarter,year.
- When
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
kindiscomparekind(compare, required)column(string, required): A column name, exact and case-sensitiveop(string, required): One ofeq,neq,gt,gte,lt,lte.value(object, required)
- When
kindisinkind(in, required)column(string, required): A column name, exact and case-sensitivevalues(array of object, required)negate(boolean, required)
- When
kindisbetweenkind(between, required)column(string, required): A column name, exact and case-sensitivefrom(object, required)to(object, required)
- When
kindistextkind(text, required)column(string, required): A column name, exact and case-sensitiveop(string, required): One ofequals,contains,starts_with,ends_with.value(object, required)caseSensitive(boolean, required)negate(boolean, required)
- When
kindisis_blankkind(is_blank, required)column(string, required): A column name, exact and case-sensitivenegate(boolean, required)
- When
kindisrelative_datekind(relative_date, required)column(string, required): A column name, exact and case-sensitiverange(string, required): One ofcurrent,last,next.count(integer or null, required)unit(string, required): One ofday,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 todayincludeCurrent(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 outtoDate(boolean): current only: the unit so far, through today, e.g. this year to date. Omit for the whole unitearlier(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 itselfcount(integer, required)unit(string, required): One ofday,week,month,quarter,year.
- When
- When
typeisset-opstype(set-ops, required)datasheetRef(string, required): An existing datasheet id, or the temporaryId of a datasheet created earlier in this patchparameters(array of object or null): Replaces the datasheet's complete parameter set; omit to keep itkey(string, required)parameter(object, required)- When
typeisstringlabel(string, required)type(string, required)value(string or null, required)
- When
typeisintegerlabel(string, required)type(integer, required)value(integer or null, required)
- When
typeisnumberlabel(string, required)type(number, required)value(number or null, required)
- When
typeisbooleanlabel(string, required)type(boolean, required)value(boolean or null, required)
- When
typeisdatelabel(string, required)type(date, required)value(string (date) or null, required)
- When
typeistimestamplabel(string, required)type(timestamp, required)value(string (date-time) or null, required)
- When
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-queryref(string, required): An existing op id to keep that op, or a new short nametitle(string or null): Omit to keep a kept op's title; a step op without one shows its step summaryinputs(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 asinput; a new first op reads the datasheet's one read, assourceor the read's aliasalias(string or null): SQL ops: the name the statement reads this input byfrom(string, required): previous: the op above (above the first op, the datasheet's one read); op: the one ref names One ofprevious,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 itfn(string, required): One ofselect,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 themcolumn(string, required): An output column, by the name it is shown under, after any renameformat(string, required): numfmt pattern, e.g. #,##0.00, $#,##0, 0.0%, yyyy-mm-dd
- When
typeisset-read-typetype(set-read-type, required)datasheetRef(string, required): An existing datasheet id, or the temporaryId of a datasheet created earlier in this patchreadType(string, required): aggregated: its data, as the tool displays it; raw: its tidy read, to transform; image: its picture One ofaggregated,raw,image.
- When
typeisset-unpivottype(set-unpivot, required)datasheetRef(string, required): An existing datasheet id, or the temporaryId of a datasheet created earlier in this patchunpivot(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 onerows(array of object, required): One output row per input row and entry, in this order; the input columns no entry names repeat on eachlabel(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 onecolumn(string, required): The column holding the row labels, headings includedinto(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
kindiscomparekind(compare, required)column(string, required): A column name, exact and case-sensitiveop(string, required): One ofeq,neq,gt,gte,lt,lte.value(object, required)
- When
kindisinkind(in, required)column(string, required): A column name, exact and case-sensitivevalues(array of object, required)negate(boolean, required)
- When
kindisbetweenkind(between, required)column(string, required): A column name, exact and case-sensitivefrom(object, required)to(object, required)
- When
kindistextkind(text, required)column(string, required): A column name, exact and case-sensitiveop(string, required): One ofequals,contains,starts_with,ends_with.value(object, required)caseSensitive(boolean, required)negate(boolean, required)
- When
kindisis_blankkind(is_blank, required)column(string, required): A column name, exact and case-sensitivenegate(boolean, required)
- When
kindisrelative_datekind(relative_date, required)column(string, required): A column name, exact and case-sensitiverange(string, required): One ofcurrent,last,next.count(integer or null, required)unit(string, required): One ofday,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 todayincludeCurrent(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 outtoDate(boolean): current only: the unit so far, through today, e.g. this year to date. Omit for the whole unitearlier(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 itselfcount(integer, required)unit(string, required): One ofday,week,month,quarter,year.
- When
- When
revisions(object, required): Therevisionsthe 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
savedistruesaved(true, required)versionBeforeId(string, required): The version of the report from before the save, whichrestoreReportVersionrestores with its datasheets as they were.resolvedDatasheetIds(object, required): The id of each datasheet acreatemade, by itstemporaryId. 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
savedisfalsesaved(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 newrevisions.