Integrate with Excel

Before you begin

Prepare to connect to Microsoft

As part of the integration, you need to whitelist the Next Matter domain and create a service account. See if you've completed all the required steps.

It might also happen that a placeholder can be replaced by a data reference of the data provided in one of the previous steps. That's why it's a good idea to learn more about Data references.

I want to get table range or add or update table row without any code

Use a Next Matter no-code step. You can select it in Integrations. For details, see Excel no-code

I want to use an advanced integration: Vlookup, add worksheet

Prerequisites

Get your file drive ID. You'll need it in the step configuration. To get the ID
1. Sign in to https://developer.microsoft.com/graph/graph-explorer.
2. Select GET as the method, and provide the following URL:
  GET https://graph.microsoft.com/v1.0/users/{user-id}/drives
  This call will list all the drive data for a specific user.
3. View the JSON response for the value of the id parameter.
Get the drive item ID (workbook ID). To get it, call this endpoint: https://graph.microsoft.com/v1.0/me/drive/root/search(q='.xlsx')?select=name,id,webUrl
Get the table name you'll be editing. To get a table name, click on the table.
Go to Table Tools > Design > Properties > Table Name. On a Mac, go to the Table tab > Table Name
Make sure that the range is formatted as a table in Excel.

🚧
Good to know: Check out our ready-made templates available when you add a step and select Templates > 3rd party templates. You can use the templates to create a set of pre-configured steps.
Need a specific template? Click the Contact us button in the top right of the page and let us know.

Add worksheet to workbook

In your Next Matter workflow, click +Add step and select Integration > Custom integration.
Click Settings to configure the step.
Enter the following details:
- Authorization: Microsoft OneDrive
- Method: POST
- URL: https://graph.microsoft.com/v1.0/me/drive/items/DRIVE_ITEM_ID/workbook/worksheets/
- Content-Type: application/json
- In the Body enter the values you want to add in the table row.
```
{
    "name": "SHEET_NAME" //this is a placeholder
}
```
Save your changes.

Use Vlookup

Use VLOOKUP to find things in a table or a range by row. For example, look up the price of an automotive part by the part number, or find an employee name based on their employee ID. For details, see Vlookup.

In your Next Matter workflow, click +Add step and select Integration > Custom integration.
Click Settings to configure the step.
Enter the following details:
- Authorization: Microsoft OneDrive
- Method: PATCH
- URL: https://graph.microsoft.com/v1.0/drives/DRIVE_ID/items/DRIVE_ITEM_ ID/workbook/worksheets/WORKSHEET_NAME/range(address='RANGE')
  Tip: The worksheet name is the name of the current tab
  Provide the range in the format of A1:A1 (it defines the cell or cells containing VLOOKUP formula)
- Content-Type: application/json
- In the Body enter the values you want to add in the table row.
```
{
"formulas" : [  ["=VLOOKUP(\"LOOKUPVALUE\",SEARCH_RANGE,INDEX,FALSE)"] ]//these are all placeholders; 
}// if you want to lookup a number field instead of a text field, remove the \" characters from Vlookup
```
Add a variable for the Vlookup result with the value of $.text[0][0]
Save your changes.