Integrate with Google Sheets

Before you begin

• Set up the integration with Google Sheets by going to your Next Matter portal > Automations library and clicking Connect in the Google Sheets app tile.

• Understand how data references work. See Use data references. This will help you use the captured data in the workflow steps.

• 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 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 and populate it with process data

🚧

You can now use our low-code Google Sheet integration, instead of creating everything from scratch. When creating a step, select Integrations > Google Sheets (low-code). For details, see No-code Google Sheets integration.

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.

• Authorization: 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
   • Authorization: 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.
    • Authorization: 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 workflow from which you'll be sending data to in 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.

• Authorization: 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 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: 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 is 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 and the second item in each record is what the workflow user should see (value).

🚧

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.

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/SPREADSHEET_ID/values/SHEET_NAME!RANGE/
  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.

• Authorization: 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 workflow, 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)
• Authorization: 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.

Add a sheet to an existing spreadsheet

To do so, you'll need the spreadsheet_ID of the spreadsheet you'll be using. See how to get the ID...

1. In your Next Matter portal, click Workflows and click the workflow from which you'll be creating a new sheet in 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
• Authorization: Google Sheets
• Headers: Content-Type: application/json
• In the Body enter the following:
  JSON

  {
      'requests': [
          {
              'addSheet': {
                  'properties': {
                      'title': "SHEET_NAME" //replace the variable
                  }
              }
          }
      ]
  }
  

5. Create a variable to use the newly created ID of the sheet in the next steps. Create the sheet ID variable with the value of $.replies[0].addSheet.properties.sheetId.
6. Save your changes.

Delete a sheet from an existing spreadsheet

To do so, you'll need the spreadsheet_ID of the spreadsheet you'll be using. See how to get the ID...

1. In your Next Matter portal, click Workflows and click the workflow from which you'll be deleting a sheet from 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
• Authorization: Google Sheets
• Headers: Content-Type: application/json
• In the Body enter the following:
  JSON

  {
      'requests': [
          {
              'deleteSheet': {
                  'sheetId': 'SHEET_ID'   //replace the variable
                  
              }
          }
      ]
  }
  

5. Save your changes.

Get data from a batch or rows or columns (multiple sheets)

1. In your Next Matter portal, click Workflows and click the workflow where you want to add the template.
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: GET
• URL: https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID/values:batchGet?ranges=SHEET_NAME1!RANGE1&ranges=SHEET_NAME2!RANGE2
  To get columns, use the following URL: https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID/values:batchGet?ranges=SHEET_NAME!RANGE&ranges=SHEET_NAME2!RANGE2&majorDimension=COLUMNS
  Adjust the URL based on the number of ranges you want to get data from.
  To find out where your sheet ID and name are, see the top of this page.
• Preconfigured integration: Google Sheets
• Headers: Content-Type: application/json

4. Create the variable for each data range. For example, you can create a variable for values_range1 with the value of $.valueRanges[0].values[0][*]and for values_range2 with the value of $.valueRanges[1].values[0][*].
5. Save your changes.

Get data from a range of rows or columns

1. In your Next Matter portal, click Workflows and click the workflow where you want to add the template.
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: GET
• URL: https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID/values/SHEET_NAME!RANGE
  To get columns, use the following URL: https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID/values/SHEET_NAME!RANGE&majorDimension=COLUMNS
  To find out where your sheet ID and name are, see the top of this page.
• Preconfigured integration: Google Sheets
• Headers: Content-Type: application/json

4. Create the variable for the retrieved values with the value of $.values
5. Save your changes.