Integrate with Excel
You can add rows to tables, add worksheets, and use Vlook.
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 code-free
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, add rows
Prerequisites
- Get your file drive ID. You'll need it in the step configuration. To get the ID
- Sign in to https://developer.microsoft.com/graph/graph-explorer.
- 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 drives data for a specific user. - 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 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 a row with values to Excel
- In your Next Matter process, 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/beta/drives/DRIVE_ID/items/DRIVE_ITEM_ID/workbook/tables/TABLE_NAME/rows
- Content-Type: application/json
- In the Body enter the values you want to add in the table row.
{ "values" : [["VALUE1", "VALUE2"]],//these are placeholders }
- Create a variable for the row index with the value of
$.index
. - Save your changes.
When needed, you can create a step to update the row. In this case, call PATCH to
https://graph.microsoft.com/beta/drives/DRIVE_ID/items/DRIVE_ITEM_ID/workbook/tables/TABLE_NAME/rows/index/itemAt(index={1. Add row to excel table - Integration step - rowindex (plain)})
with the Body format as above.
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 a 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.
Updated 6 days ago