MENU
    Microsoft Excel Connector
    • 21 Feb 2024
    • 7 Minutes to read
    • Contributors

    Microsoft Excel Connector


    Article summary

    To download the app, visit: Library

    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


    Was this article helpful?