Integrate with Google Sheets

Before you begin

• Set up the integration with Google Sheets by going to your Next Matter portal > Company > Integrations and clicking Connect in the Google Sheets section.
• 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.
• Have a Google Sheets spreadsheet ID at hand. You can find it in the spreadsheet URL.

• Have a sheet name of the sheet which you want to populate with data. You can find it at the bottom of your sheet.

🚧

Good to know: Check out our ready-made Google templates available when you add a step and select Step templates > Integrations. You can use the templates to create a set of pre-configured steps.

Add a row and populate it with process data

1. In your Next Matter portal, click Workflows and click the workflow from which you'll be sending data to Google Sheets.
2. Click + Add step and select Integrations > Custom integration as the step type.
3. Click Settings to configure the step.
4. Enter the following details:

• Method: POST
• URL: https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID/values/SHEET_NAME!CELL_RANGE:append?valueInputOption=user_entered

📘

Note: The range marks the first and the last cell that you want to populate. For example, if your data is supposted to be in row 2 and take up cells A, B, and C, then your range is A1:C.

• Preconfigured integration: Google Sheets
• Headers: Content-Type: application/json
• In the Body enter the values you want to move to Google Sheets. These might be the data references of the data you collected in previous steps. For example, the body might look like the following:
  JSON

  {
  	"values": [//all values are data references that you need to change
  		[
  			"{1. Collect stock data - Name - Input (json)}",
  			"{1. Collect stock data - Email - Input (json)}",
  			"{1. Collect stock data - Address - City (json)}"
  		]
  	]
  }
  

• To make sure Google Sheets uses the next empty row every time a new data set is populated, click + Add variable. Create a Range variable and enter the $.ranges.updatedRange value.

Watch a video tutorial

Add a row and update its data

1. In your Next Matter portal, click Workflows and click the workflow from which you'll be sending data to Google Sheets.
2. Click + Add step and select Integrations > Custom integration as the step type.
3. Click Settings to configure the step.
4. Enter the following details:
   • Method: POST
   • URL: https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID/values/SHEET_NAME!CELL_RANGE:append?valueInputOption=user_entered
   • Preconfigured integration: Google Sheets
   • Headers: Content-Type: application/json
   • In the Body enter the values you want to move to Google Sheets. These might be the data references of the data you collected in previous steps. For example, the body might look like the following:
     JSON

     placehoders with data reference to the value provided ikn {"majorDimension": "ROWS", "values": [ ["VALUE1","VALUE2"]]} //you can replace the VALUE placeholder with the values from your sheet
     

5. Create a variable for range with the value of $.updates.updatedRange.
6. Create a step with a form with an input form field to collect the new field value.
7. Create an integration step to update the value.
8. As Body type values, replace the values placeholder with real values.
9. Click Settings to configure the step.
10. Enter the following details:
    • Method: PUT
    • URL: https://sheets.googleapis.com/v4/spreadsheets/SHEET_ID/values/{2. Insert row into sheet - Integration step - range (plain)}?valueInputOption=USER_ENTERED
      Note that {2. Insert row into sheet - Integration step - range (plain)} is a placeholder. Click {} and select the data reference to the integration step in which you configured the variable.
    • Preconfigured integration: Google Sheets
    • Headers: Content-Type: application/json
    • Body
      JSON

      {"values": [ [null,"{3. New Value - Column B - Input (plain)}"]]
       } //replace the placeholder data reference with the data reference to the updated value
      

11. Save your changes.

Find and replace data in a specific column of a sheet

1. In your Next Matter portal, click Workflows and click the process from which you'll be sending data to Google Sheets.
2. Click + Add step and select Integrations > Custom integration as the step type.
3. Click Settings to configure the step.
4. Enter the following details:

• Method: POST
• URL: https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID/:batchUpdate

📘

Note: The range marks the first and the last cell that you want to populate. For example, if your data is supposted to be in row 2 and take up cells A, B, and C, then your range is A1:C.

• Preconfigured integration: Google Sheets
• Headers: Content-Type: application/json
• In the Body enter the values you want to move to Google Sheets. These might be the data references of the data you collected in previous steps. For example, the body might look like the following:
  JSON

  { "requests": [
    { "findReplace": {
      "find": "TEXT_TO_FIND", 
      "replacement": "NEW_TEXT", 
      "matchCase": false, 
      "matchEntireCell": true, 
      "searchByRegex": false, 
      "includeFormulas": false, 
      "range": { 
        "sheetId": SHEET_ID, 
        "startRowIndex": 1, //this attribute defines the row where the search starts
        "startColumnIndex": 4, 
        "endColumnIndex": 5 //these attributes define the columns included in the search
      } 
    } 
    }], 
   "includeSpreadsheetInResponse": false }
  

Identify a piece of information in Google Sheets and display it in the process

To do so, you'll be using the Vlookup function.

1. In your Next Matter portal, click Workflows and click the process from which you'll be sending data to Google Sheets.
2. Click + Add step and select Integrations > Custom integration as the step type.
3. Click Settings to configure the step.
4. Enter the following details:

• Method: PUT
• URL: https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID/values/SHEET_NAME!RANGE?valueInputOption=user_entered&includeValuesInResponse=true

📘

The range marks the first and the last cell that you want to work with. For example, if the data is in row 2 and take up cells A, B, and C, then your range is A1:C.

• Preconfigured integration: Google Sheets
• Headers: Content-Type: application/json
• In the Body enter the values you want to look for in Google Sheets. If you need more insights into how the function works, see Microsoft documentation. If you want to find a number field and not a text field, remove the\from around the \SEARCH_KEY.

For example, the body might look like the following:

{
  "majorDimension": "ROWS",
  "values": [
    [
      "=VLOOKUP(\"SEARCH_KEY\",RANGE,INDEX,false)"
  ]
}

5. Save your changes.
6. Create a step with a form that has an Instruction form field and use the data reference to display the found data in the instruction.

Create a dynamic dropdown from a sheet

As part of the workflow, you can create a single or multi-value dropdown from a data source. When the source gets updated, so is the dropdown that displays. The values are supported when they are a 2D array where the first item in each record is the key the second item in each record is what the process user should see (value).

1. Click + Add step and select Integrations > Custom integration as the step type.
2. Click Settings to configure the step.
3. Enter the following details:

• Method: GET
• URL: https://sheets.googleapis.com/v4/spreadsheets/1mr4_b8E5B3-9h7II7DG7fvIKkn3VBiU4mO6zmaynrQY/values/dataset!A1:B61005/
  Make sure to change the dataset values to your own.

📘

Note: We support long data sets however show only the first 250 items. The remaining ones are displayed as search results.

• Preconfigured integration: Google Sheets
• Headers: Content-Type: application/json

4. Create a variable for the dataset result with the value of: $.values.[*].

📘

If the source is a list, the variable is $.values.

5. Save your changes.
6. Create a form step with the single-value dropdown as a form field. Click Settings to configure the step.
7. Select the Dynamic from external data source option as Source of Items.
8. In the Data Source, click {} to select the data reference. This should be the variable from the previous step. For example, it might look the following:

📘

Select the json format to get the values in a dropdown. The plain format will render the values as a list.

9. Save your changes.

📘

We support 1D or 2D arrays at the moment. It can either be:
["test", "test2"]
or
[["1", "test"], ["2", "test2"]]
where “1” and “2" are treated as the actual submitted values and “test” and “test2" would be the display values.

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.

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: PUT
• URL: https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID/values/SHEET_NAME!RANGE?valueInputOption=USER_ENTERED&includeValuesInResponse=true
  Note: Provide the range in the format of A1:A1 (it defines the cell or cells containing VLOOKUP formula)
• Preconfigured integration: Google Sheets
• 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)"] ]//the capitalized values are all placeholders;  
  }// if you want to lookup a number field instead of a text field, remove the \" characters from Vlookup  
  

Tip: For more on the Vlookup values, see W3 (external link)

4. Add a variable for the Vlookup result with the value of $.updatedData.values[0][0]
5. Save your changes.