capabilities.work

Sort, Filter, and Total a Table for Reporting

A 7-step recipe with Microsoft Excel. We can help you get access.

Recipe · 7 steps

Sort, Filter, and Total a Table for Reporting

with Microsoft Excel

Order, narrow, and total an Excel table in one pass.

  1. Open a persisting session so the sort, filter, and totals row are all committed.
  2. Resolve the table on the worksheet that is being prepared for reporting.
  3. Read the table's columns so the sort key index and the filter column id are chosen against the table's real schema rather than assumed.
  4. Sort the table rows on the chosen column. The sort is applied to the table in place and persists with the workbook.
  5. Narrow the table to the rows matching the criterion. filterOn Values with a single criterion keeps only rows whose column equals that value.
  6. Patch the table to display its header and totals rows so the prepared view carries its own summary line.
  7. Read the rows back so the caller receives the table in its sorted, filtered, totalled state.
What you'll need to have ready
  • itemId: The drive item id of the workbook file.
  • worksheetIdOrName: The worksheet holding the target table.
  • sortKey: The zero-based column index to sort the table on.
  • ascending: Whether the sort runs ascending.
  • filterColumnId: The id of the column to apply the filter to.
  • filterCriterion: The value the filter keeps (e.g. "Open").
For your developer

Arazzo workflow microsoft-excel:prepare-table-report, 7 operations: createWorkbookSession → listTables → listTableColumns → sortTable → applyTableFilter → updateTable → listTableRows. Specs on APIs.io.

More recipes with Microsoft Excel

Append a Row to a TableBuild a Chart from a Data RangeFlatten a Table into a Plain RangeCreate a Worksheet and Seed It with DataDiscover Workbook StructureWrite Through a Named RangeDelete a Table Row by IndexRead, Write, and Verify a Cell RangeAdd and Retire a Table ColumnCalculate with a Workbook Function and Write the Result