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 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 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 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 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 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 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 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 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 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]])