Workbook resource type

Workbook is the top level object which contains related workbook objects such as worksheets, tables, ranges, etc.

Properties

None

Methods

Method Return Type Description
Create Session workbookSessionInfo Create a workbook session to start a persistent or non-persistent session.
Close Session None Close an existing session.
Refresh Session None Refresh an existing session.

Relationships

Relationship Type Description
names NamedItem collection Represents a collection of workbook scoped named items (named ranges and constants). Read-only.
tables Table collection Represents a collection of tables associated with the workbook. Read-only.
worksheets Worksheet collection Represents a collection of worksheets associated with the workbook. Read-only.

Functions

Excel functions: Invoke a workbook function using the syntax POST /workbook/functions/{function-name} and providing the function argument(s) in the body using a JSON object. The function's resulting value and any error strings are returned in the function result object. The error value of null indicates successful execution of the function.

The complete list of supported functions are listed here. Refer to the function signature for specific parameter names and data types.

Important notes: * The range input parameter is supplied using a range object instead of the range address string.
* The index parameter is 1-indexed unlike the 0-index used in most of the APIs.

Example:

In the below example, vlookup function is called by passing lookup value, input range and the value to be returned.

Request:

POST https://graph.microsoft.com/beta/me/drive/root:/book1.xlsx:/workbook/functions/vlookup
content-type: Application/Json 
authorization: Bearer {access-token} 
workbook-session-id: {session-id}

{
    "lookupValue": "Temperature",
    "tableArray": { "Address": "Sheet1!E1:G5" },
    "colIndexNum": 2,
    "rangeLookup": false
}

Response:

HTTP code: 200, OK
content-type: application/json;odata.metadata 

{
    "@odata.context": "https://graph.microsoft.com/beta/$metadata#workbookFunctionResult",
    "@odata.type": "#microsoft.graph.workbookFunctionResult",
    "@odata.id": "/users('f6d92604-4b76-4b70-9a4c-93dfbcc054d5')/drive/root/workbook/functions/vlookup()",
    "error": null,
    "value": "28.3"
}

Example:

In the below example, median function is called by passing the input range(s) in an array.

Request:

POST https://graph.microsoft.com/beta/me/drive/root:/book1.xlsx:/workbook/functions/median
content-type: Application/Json 
authorization: Bearer {access-token} 
workbook-session-id: {session-id}

{
"values" :  [
        { "address": "Sheet2!A1:A5" },
        { "address": "Sheet2!B1:B5" },
      ] 
}

Response:

HTTP code: 200, OK
content-type: application/json;odata.metadata 

{
  "@odata.context": "https://graph.microsoft.com/beta/$metadata#workbookFunctionResult",
  "@odata.type": "#microsoft.graph.workbookFunctionResult",
  "@odata.id": "/users('2abcad6a-2fca-4b6e-9577-e358a757d77d')/drive/root/workbook/functions/median()",
  "error": null,
  "value": 30
}