Integrate with Excel

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:

1. Sign in to https://developer.microsoft.com/graph/graph-explorer.
2. Select GET as the method, and provide the following URL: https://graph.microsoft.com/v1.0/sites/users/ID_OR_USER_PRINCIPAL_NAME/drive
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 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

1. In your Next Matter process, click +Add step and select Integration > Custom integration.
2. Click Settings to configure the step.
3. 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.
   JSON

   {
   "values" : [["VALUE1", "VALUE2"]],//these are placeholders
   }
   

4. Create a variable for the row index with the value of $.index.
5. 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

1. In your Next Matter workflow, click +Add step and select Integration > Custom integration.

2. Click Settings to configure the step.

3. 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.
   JSON

   {
       "name": "SHEET_NAME" //this is a placeholder
   }
   

4. 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.

1. In your Next Matter workflow, click +Add step and select Integration > Custom integration.

2. Click Settings to configure the step.

3. 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.
   JSON

   {
   "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
   

4. Add a variable for the Vlookup result with the value of $.text[0][0]

5. Save your changes.