Skip to main content

Preview datasheet changes

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

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

Requires the slide_decks:write scope.

Operation ID: previewReportDatasheetChanges.

Path parameters

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

Headers

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

Request body, required

application/json

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

Response 200

Success

application/json

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