Integrate with Excel

You can add worksheets, use Vlook, add rows, or update row data.

Design Workflows Builder Toolbox Enterprise Automation

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.

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:

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

     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 the 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:

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

     JSON

     {
     "formulas" : [  ["=VLOOKUP(\"LOOKUPVALUE\",SEARCH_RANGE,INDEX,FALSE)"] ]//these are all placeholders; 
     }// If you want to look up 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.