Integrate with Excel
You can add rows to tables, add worksheets, and use Vlook.
Before you begin
- Make sure you first connect Microsoft Excel in your Next Matter portal.
In your Next Matter portal, go to Company > Integrations, and click Connect for Microsoft Excel.
- 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:
https://graph.microsoft.com/v1.0/sites/users/ID_OR_USER_PRINCIPAL_NAME/drive
- 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.
- 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.
Good to know: Check out our ready-made Microsoft OneDrive 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.
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:
- 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:
- 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:
- 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 23 days ago