Skip to content
Zoan Docs

Excel Online — Write range

Excel Online — Write range

Section titled “Excel Online — Write range”

Type: excelonline-write-range  ·  Package: Excel Online v1.0.4

Writes a set of values to a specific range. The range must match the data dimensions (same rows and columns). It gives you full control over where the data lands, for example to fill an area of a template.

How it works

Section titled “How it works”

It works on the session created with Excel Online — Connect. Pass it in the session parameter, or place this activity inside the body of Excel Online — Connect to inherit it automatically.

In values you pass a DataTable (or a DataRow for a single row); only the row values are written, without the column-name row. The change is saved automatically.

Details to keep in mind:

  • The range must have exactly as many rows and columns as the table. A table with 10 rows and 3 columns goes in A2:C11. If you would rather not compute it, use Write sheet (from A1) or Append rows (at the end).
  • To write the headers too, write them first in the top row with another Write range of a DataRow, or with Write cell.
  • Null cells (DBNull) in the table are left untouched: they keep whatever was there. To leave them empty, clear the range first with Clear range.
  • Numbers and booleans are sent as such; any other value (dates included) is sent as text. Cell formatting is kept.
  • The table must have at least one row; with no rows it matches no range.

Required parameters

Section titled “Required parameters”
ParameterTypeDefaultDescription
sheetstring—Sheet name.
rangestring—Target range in A1 notation. Must match the data dimensions. E.g. A2:C11.
valuesDataTable | DataRow—The DataTable to write (or a DataRow for a single row); values only.

Optional parameters

Section titled “Optional parameters”
ParameterTypeDefaultDescription
sessionZoanExcelOnlineSession—Excel Online — Connect session. Inherited inside Connect’s body.

Output

Section titled “Output”

This activity does not return a value. The change is saved automatically.

Common errors

Section titled “Common errors”
MessageCause and fix
No Excel Online session available. Provide 'session' from an excelonline-connect activity, or place this activity inside the Connect activity's body so the session is inherited automatically.The activity is outside Connect’s body and did not receive session. Place it inside the body or pass session = workbook.
'sheet' is required. / 'range' is required. / 'values' is required.The parameter is empty or its expression returned null.
'values' must be a DataTable (or a single DataRow).You passed another type (a list, an array, a text). Build the table with Build DataTable.
InvalidArgument (Microsoft Graph, 400)The range size does not match the data, or the address is not valid. Compute the range from table.Rows.Count and table.Columns.Count.
itemNotFound (Microsoft Graph, 404)There is no sheet with that name.

Example

Section titled “Example”
Build DataTable
  columns = arr("Month", "Total")
  rows    = arr(arr("June", 1500), arr("July", 1720))
  → output: summary
Excel Online — Connect   credential = credential("m365-finance")   path = "Reports/Report.xlsx"
 └─ body:
      Excel Online — Write range   sheet = "Data"   range = "A2:B" + (summary.Rows.Count + 1)   values = summary
Section titled “Related activities”