Excel workbooks in OneDrive and SharePoint over the Microsoft Graph workbook API: worksheets, cell ranges, tables, rows as records, sessions and recalculation. Credentials, clouds, retries and errors come from microsoft-graph; drives and items are addressed as in microsoft-onedrive.

Namespaces

  • ::microsoft::excel::workbooks - sessions (with-session, create-session, close-session, refresh-session), calculate
  • ::microsoft::excel::worksheets - list, get, add, rename, update, delete
  • ::microsoft::excel::ranges - read-values, write-values, read-records, write-records, used range, clear, insert, delete
  • ::microsoft::excel::tables - list, create, update, delete, rows, read-table-records, append-records
  • ::microsoft::excel::records - pure conversion between value grids and records, A1 notation helpers

Credentials. Microsoft documents the workbook API for delegated work or school tokens. Application (app-only) tokens are not supported, and most session calls are not available on personal accounts.

Addressing a workbook. An item is an id, a path from the drive root ("/Reports/Q3.xlsx"), or a driveItem Map. opts.drive picks the drive, as in microsoft-onedrive. Only .xlsx files work.

Quick start

::ranges ::microsoft::excel::ranges
::tables ::microsoft::excel::tables

book "/Reports/Q3.xlsx"
leads ::ranges/read-records(book, "Leads")
::tables/append-records(book, "LeadsTable", [{name: "Ada", stage: "demo"}])

Values

LOCAL_KEYS

LOCAL_KEYS: Vec [
  "drive",
  "session",
  "sheet",
  "address",
  "persist",
  "values-only",
  "apply-to",
  "has-headers",
  "index"
]

Functions

append-records alias of ::microsoft::excel::tables/append-records

fn (item: Any, table: Str, records: Vec): Map
fn (item: Any, table: Str, records: Vec, columns: Vec?): Map
fn (item: Any, table: Str, records: Vec, columns: Vec?, opts: Map): Map

Add records as table rows. columns sets the cell order; by default the table's own columns are fetched. Missing values are empty cells. An empty Vec makes no call and returns {}.

::microsoft::excel::tables/append-records("/Reports/Q3.xlsx", "Leads", [{name: "Ada", stage: "demo"}])

call

fn (method: Str, path: Str, body: Any, opts: Map): Any

A Graph call with this package's conventions: the session header, credential opts and a JSON (or empty) body.

Example

// A workbook endpoint this package does not wrap
book ::microsoft::excel/workbook-path("/Reports/Q3.xlsx", {})
::microsoft::excel/call("POST", `${book}/names/add`, {name: "Rate", reference: "=Data!$B$1"}, {session: "s1"})

collection

fn (result: Result): Vec

The items of a collection response, or the Err it holds.

Example

::microsoft::excel/collection({value: [{name: "Data"}]})  // [{name: "Data"}]
::microsoft::excel/collection({})  // []

compact

fn (body: Map): Map

Drop null values from a request body.

Example

::microsoft::excel/compact({name: "Leads", style: null})  // {name: "Leads"}

fetch

fn (path: Str, defaults: Map, opts: Map): Any

GET path with OData parameters from defaults and opts.

Example

// A workbook endpoint this package does not wrap
book ::microsoft::excel/workbook-path("/Reports/Q3.xlsx", {})
result ::microsoft::excel/fetch(`${book}/names`, {}, {top: 10})
map(::microsoft::excel/collection(result), %.name)

list-tables alias of ::microsoft::excel::tables/list-tables

fn (item: Any): Vec
fn (item: Any, opts: Map): Vec

GET /workbook/tables, or a worksheet's tables with opts.sheet. Returns a Vec of tables.

Example

tables ::microsoft::excel::tables/list-tables("/Reports/Q3.xlsx")
map(tables, %.name)

// One worksheet's tables
::microsoft::excel::tables/list-tables("/Reports/Q3.xlsx", {sheet: "Pipeline"})

list-worksheets alias of ::microsoft::excel::worksheets/list-worksheets

fn (item: Any): Vec
fn (item: Any, opts: Map): Vec

GET /workbook/worksheets. Returns a Vec of {id, name, position, visibility}.

Example

sheets ::microsoft::excel::worksheets/list-worksheets("/Reports/Q3.xlsx")
map(sheets, %.name)

// A workbook in another drive
::microsoft::excel::worksheets/list-worksheets("01ABC", {drive: "b1"})

odata-string

fn (value: Str): Str

A value as an encoded OData string literal: quotes doubled, then percent-encoded, wrapped in single quotes.

Example

::microsoft::excel/odata-string("A1:B2")  // "'A1%3AB2'"
::microsoft::excel/odata-string("it's")  // "'it%27%27s'"

query-opts

fn (defaults: Map, opts: Map): Map

The OData parameters of opts (pure): defaults, overridden by opts minus credential keys and this package's local keys, nulls dropped.

Example

::microsoft::excel/query-opts({select: "values"}, {top: 5, drive: "b1", session: "s1"})  // {select: "values", top: 5}

range-path

fn (item: Any, sheet: Str, address: Str, opts: Map): Str

The Graph path of a cell range on a worksheet.

::microsoft::excel/range-path("01ABC", "Data", "A1:B2", {drive: "b1"})
// "/drives/b1/items/01ABC/workbook/worksheets/Data/range(address='A1%3AB2')"

read-records alias of ::microsoft::excel::ranges/read-records

fn (item: Any, sheet: Str): Vec
fn (item: Any, sheet: Str, opts: Map): Vec

A worksheet as records, one Map per row keyed by the first row. Reads the used range unless opts.address names a range. Blank cells are null and fully blank rows are skipped.

leads ::microsoft::excel::ranges/read-records("/Reports/Q3.xlsx", "Leads")

read-table-records alias of ::microsoft::excel::tables/read-table-records

fn (item: Any, table: Str): Vec
fn (item: Any, table: Str, opts: Map): Vec

A table's data as records keyed by its column names. Blank cells are null and fully blank rows are skipped, so an empty table gives []. It always reads the whole table; use list-rows to page.

Example

leads ::microsoft::excel::tables/read-table-records("/Reports/Q3.xlsx", "Leads")
map(leads, %.name)

// A workbook in another drive
::microsoft::excel::tables/read-table-records("01ABC", "Leads", {drive: "b1"})

read-values alias of ::microsoft::excel::ranges/read-values

fn (item: Any, sheet: Str, address: Str): Vec
fn (item: Any, sheet: Str, address: Str, opts: Map): Vec

A range's values as a grid (a Vec of rows).

Example

grid ::microsoft::excel::ranges/read-values("/Reports/Q3.xlsx", "Data", "A1:C10")
first(grid)

// A workbook in another drive
::microsoft::excel::ranges/read-values("01ABC", "Data", "A1:C10", {drive: "b1"})

session-headers

fn (opts: Map): Map

The workbook-session-id header for opts.session, or {} (pure).

Example

::microsoft::excel/session-headers({session: "s1"})  // {"workbook-session-id": "s1"}
::microsoft::excel/session-headers({})  // {}

session-id

fn (opts: Map): Str?

The workbook session in opts.session, given as an id or a session Map from create-session; null when unset (pure).

Example

::microsoft::excel/session-id({session: "s1"})  // "s1"
::microsoft::excel/session-id({session: {id: "s2", persistChanges: true}})  // "s2"
::microsoft::excel/session-id({})  // null

table-path

fn (item: Any, table: Str, opts: Map): Str

The Graph path of a table, by name or id, at the workbook level.

Example

::microsoft::excel/table-path("01ABC", "Leads", {drive: "b1"})  // "/drives/b1/items/01ABC/workbook/tables/Leads"

with-session alias of ::microsoft::excel::workbooks/with-session

fn (item: Any, f: Fn): Any
fn (item: Any, opts: Map, f: Fn): Any

Open a session, call f with opts carrying it, close the session, and return what f returned, an Err included. A failure to close is ignored, since Excel expires the session on its own.

::excel ::microsoft::excel

::excel/with-session("/Reports/Q3.xlsx", (s) {
  ::excel/write-values("/Reports/Q3.xlsx", "Data", "A1", [["a", 1]], s)
  ::excel/read-values("/Reports/Q3.xlsx", "Data", "A1:B1", s)
})

without-query

fn (opts: Map): Map

opts without its query parameters, keeping credentials and this package's own keys. For reads whose query the package has to control: a caller's select or top would otherwise replace it.

Example

::microsoft::excel/without-query({drive: "b1", select: "address", top: 5})  // {drive: "b1"}

workbook-path

fn (item: Any, opts: Map): Str

The Graph path of an item's workbook.

::microsoft::excel/workbook-path("/Reports/Q3.xlsx", {})
// "/me/drive/root:/Reports/Q3.xlsx:/workbook"

worksheet-path

fn (item: Any, sheet: Str, opts: Map): Str

The Graph path of a worksheet, by name or id.

Example

::microsoft::excel/worksheet-path("/Reports/Q3.xlsx", "Q3 Plan", {})  // "/me/drive/root:/Reports/Q3.xlsx:/workbook/worksheets/Q3%20Plan"

write-records alias of ::microsoft::excel::ranges/write-records

fn (item: Any, sheet: Str, address: Str, records: Vec): Map
fn (item: Any, sheet: Str, address: Str, records: Vec, columns: Vec?): Map
fn (item: Any, sheet: Str, address: Str, records: Vec, columns: Vec?, opts: Map): Map

Write records as a header row and data rows from a top-left cell. columns sets the order (default, or null: every key in order of first appearance). Missing values empty their cells. No records and no columns make no call.

Example

leads [{name: "Ada", stage: "demo"}, {name: "Grace", stage: "trial"}]
::microsoft::excel::ranges/write-records("/Reports/Q3.xlsx", "Pipeline", "A1", leads)

// Choose the columns and their order
::microsoft::excel::ranges/write-records("/Reports/Q3.xlsx", "Pipeline", "A1", leads, ["stage", "name"])

// With a session, keeping the default columns
::microsoft::excel::ranges/write-records("/Reports/Q3.xlsx", "Pipeline", "A1", leads, null, {session: "s1"})

write-values alias of ::microsoft::excel::ranges/write-values

fn (item: Any, sheet: Str, address: Str, grid: Vec): Map
fn (item: Any, sheet: Str, address: Str, grid: Vec, opts: Map): Map

Write a grid to a worksheet. address is a full range, or a single top-left cell that is expanded to the grid's size. Short rows are padded with null, which leaves those cells unchanged. An empty grid makes no call and returns {}.

::microsoft::excel::ranges/write-values("/Reports/Q3.xlsx", "Data", "B2", [["a", 1], ["b", 2]])