Microsoft Excel Connector

Microsoft Excel Connector

Integration with Microsoft O365 Worksheet V1.0 Api

Introduction

Microsoft Excel Worksheets API allows the users to create, read and edit Microsoft excel sheets with the right access set to their Office365 accounts. The Goal of this connector is to integrate Tulip with Microsoft using Microsoft’s Graph API(v1.0) using HTTP Connectors & provide our customers with an out of box connector that can be imported into their instance.

Purpose

This document will highlight different connector functions that can be built by doing HTTP calls to Microsoft’s Graph API(v1.0) to interact with the M365 Excel worksheet to showcase various read and write actions on the worksheet that could be leveraged for various use cases.

API Doc

Microsoft Graph API Documentation

Pre-Requisites

1. Create an account on Microsoft O365 & configure the OAuth 2.0 credentials in Microsoft Entra ID.

2. Register API with the required scopes in the Microsoft portal. Azure - Entra ID

Video of setting up the connector:

Tulip Connection Details

The following information would need to be configured on Tulip’s

Connectors.

1. Running On: Cloud Connector Host
2. Host: graph.microsoft.com
3. Port:443
4. TLS: Yes

OAuth 2: To obtain the access token to communicate with Microsoft's Graph API

Connector Functions

1) Get one drive details

Purpose

This connector will fetch all the items from one drive.

Endpoint

HTTP POST call to the endpoint -https://graph.micrsoft.com:443/v1.0/me/drive/root/children

Required Input

No Inputs required

Required Output Extractor

1. SheetID: Unique identifier of the Workbook which will be used to carry out the other tasks.
2. CreatedAt: The Created date and time of the workbook.
3. LastModified: The last modified date and time of the workbook.
4. SheetName: The workbook name.
5. SpreadSheetURL: The URL of the workbook.

2) Get all Worksheets in a Workbook

Purpose

This connector will fetch all the worksheets of a workbook by passing the workbookID.

Endpoint

HTTP GET to the endpoint -https://graph.microsoft.com:443/v1.0/me/drive/items/{workbookID}/workbook/worksheets

Required Input

The following are the URL parameters that are to be passed

1. Workbook Id

Required Output Extractor

1. sheetId: Unique identifier of the SpreadSheet which will be used to carry out the other tasks.
2. sheetName:The display name of the worksheet.
3. sheetPosition:The zero-based position of the worksheet within the workbook.
4. sheetVisibility: The Visibility of the worksheet. The possible values are Visible, Hidden, VeryHidden.

3) Get Worksheet Metadata

Purpose

This connector will fetch the data from the WorkbookID & Worksheet Name.

Endpoint

HTTP GET call to the endpoint https://graph.microsoft.com:443/v1.0/me/drive/items/{workbookID}/workbook/worksheets/{worksheetName}/

Required Input

The following are the URL parameters that are to be passed

1. Workbook ID
2. Worksheet Name

Required Output Extractor

1. sheetId: Unique identifier of the SpreadSheet which will be used to carry out the other tasks.
2. sheetName:The display name of the worksheet.
3. sheetPosition:The zero-based position of the worksheet within the workbook.
4. sheetVisibility: The Visibility of the worksheet. The possible values are: Visible, Hidden, VeryHidden.

4) Creating a new Worksheet

Purpose

1. Users can pass a Workbook ID and Worksheet name as an input & Tulip will create a new Worksheet with sheet name provided. The response of the API will be the metadata and various properties of the worksheet. This can be further used to fetch and Update data from the worksheets.
2. Information is pulled from Microsoft Graph V1.0 API can then be further used either within Tulip apps, Tulip tables, or Tulip variables which would be piped into other connector functions.

Endpoint

HTTP POST call to the endpoint https://graph.microsoft.com:443/v1.0/me/drive/items/{workbookID}/workbook/worksheets/add

Required Input

The following are the parameters that are sent as the Request Body

1. Workbook ID - The Workbook ID
2. Worksheet Name - Worksheet Title

Required Output Extractor

1. WorksheetId: Unique identifier of the SpreadSheet which will be used to carry out the other tasks .
2. spreadsheetURL: The URL of the newly created Spreadsheet.
3. Additional parameters that are returned with the response can be included as part of the output parameters.

5) Update worksheet properties

Purpose

Users can pass a Workbook ID, Old Worksheet Name, Worksheet position and New Worksheet Name as an input & Tulip will update the Worksheet with sheet name provided. The response of the API will be the metadata and various properties of the worksheet. This can be further used to fetch and Update data from the spreadsheets.

Endpoint

HTTP PATCH call to the endpoint -https://graph.microsoft.com:443/v1.0/me/drive/items/{workbookID}/workbook/worksheets/{worksheetName}

Required Input

The following are the parameters that are sent as the Request Body

1. Workbook ID - The Workbook ID
2. Worksheet Name - Worksheet Title
3. Worksheet Position-The zero-based position of the worksheet within the workbook
4. New Worksheet name- New worksheet title

Required Output Extractor

1. sheetID: Unique identifier of the Workbook which will be used to carry out the other tasks
2. sheetName: The display name of the worksheet.
3. sheetPosition:The zero-based position of the worksheet within the workbook.

6) Get a Specific Cell data

Purpose

This connector will fetch the data from worksheets of a workbook based on provided WorkbookID, Worksheet Name, Row index, and Column index as an input. The response of the API will be the data of the provided cell value.

Endpoint

HTTP GET call to the endpoint -https://graph.microsoft.com:443/v1.0/me/drive/items/{workbookID}/workbook/worksheets/{worksheetName}/cell(row={row index},column={column index})

Required Input

The following are the URL parameters that are to be passed

1. Workbook Id- Workbook ID
2. Worksheet Name-Worksheet Title
3. Row Index- Row number of the cell to be retrieved
4. Column Index- Column number of the cell to be retrieved

Required Output Extractor

This connector returns the cell value of the provided row & column index.

7) Get Worksheet Range data

Purpose

This connector will fetch the data from worksheets of a workbook based on provided WorkbookID, Worksheet Name, Start Range and End Range as an input . The response of the API will be the data of the provided range.

Endpoint

HTTP GET call to the endpoint -https://graph.microsoft.com:443/v1.0/me/drive/items/{workbookID}/workbook/worksheets/{worksheetName}/range(address=’{start range}:{end range}’)

Required Input

The following are the URL parameters that are to be passed

1. Workbook Id- Workbook ID
2. Worksheet name-Worksheet Title
3. Start range- Start range
4. End range- End range

Required Output Extractor

This connector returns all the cell values within the provided range.

8) Update values by Range

Purpose

Users can pass a Workbook ID, Worksheet Name, Start Range, End Range, and Values to update as input & Tulip will update range values with provided range. The response of the API will be the updated range values.

Endpoint

HTTP PATCH call to the endpoint -https://graph.microsoft.com:443/v1.0/me/drive/items/{workbookID}/workbook/worksheets/{worksheetName}/range(address=’{start range}:{end range}’)

Required Input

The following are the URL parameters that are to be passed

1. Workbook Id- Workbook ID
2. Worksheet name-Worksheet Title
3. Start range- Start range
4. End range- End range
5. Values of each cell within the provided range

Required Output Extractor

We don’t require any output extractor.

9) Create a table in the worksheet

Purpose

1. Users can pass a Workbook ID , Worksheet Name, Worksheet Address and Has Headers as an input & Tulip will create a new Table with table name. The response of the API will be the metadata and various properties of the table.
2. This can be further used to fetch and Update data from the spreadsheets.
3. Information is pulled from Microsoft Graph V1.0 API can then be further used either within Tulip apps, Tulip tables, or Tulip variables which would be piped into other connector functions.

Endpoint

HTTP POST call to the endpoint https://graph.microsoft.com:443/v1.0/me/drive/items/{itemID}/workbook/worksheets/add

Required Input

The following are the parameters that are sent as the Request Body

1. Workbook ID - The Workbook ID
2. Worksheet Name - Worksheet Title
3. Address-Address or name of the range object representing the data source. If the address does not contain a sheet name, the currently-active sheet is used.
4. hasHeaders-Boolean value that indicates whether the data being imported has column labels. If the source does not contain headers (i.e, when this property set to false), Excel will automatically generate a header shifting the data down by one row

Required Output Extractor

This returns the metadata values of the newly created table.

10) Add a new row in a table

Purpose

Users can pass a Workbook ID , Table Name and Row values as an input & Tulip will create a new row with provided row values. The response of the API will be the created row values in the table.

Endpoint

HTTP POST call to the endpoint https://graph.microsoft.com:443/v1.0/me/drive/items/{workbookID}/workbook/tables/{tablename}/rows/add

Required Input

The following are the parameters that are sent as the Request Body

1. Workbook ID - The Workbook ID
2. Worksheet Name - Worksheet Title
3. Row Values

Required Output Extractor

This returns the row values of the newly created row.

11) Clear the worksheet data

Purpose

Users can pass a Workbook ID , Worksheet Name, start range and end range as an input & Tulip will clear the cell values based on the given range.

Endpoint

HTTP POST call to the endpoint https://graph.microsoft.com:443/v1.0/me/drive/items/{workbookID}/workbook/worksheet/{worksheetName}/range(address=’{start range}:{end range}’)/clear

Required Input

The following are the parameters that are sent as the Request Body

1. Workbook ID - The Workbook ID
2. Worksheet Name - Worksheet Title
3. Start Range
4. End Range

Further Reading

Check out more information regarding Microsoft Connectors

• Microsoft Calendar Connector
• Microsoft Outlook Connector
• Microsoft Teams Connector