Integrate with Google Sheets

​

Before you begin

• Connect Google services in Next Matter with a service account and share data with this account. Learn more…

• Check the required scopes to learn more about which data is shared between the apps.

• Have a Google Sheets spreadsheet ID at hand. You can find it in the spreadsheet URL.

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

​

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

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

   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 }
     

   • Method: POST

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

​

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:

   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.

   For example, the body might look like the following:

   json

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

   • Authorization: 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 Google documentation. If you want to find a number field and not a text field, remove the\from around the \SEARCH_KEY.

   • Method: PUT

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

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

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:

   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

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

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.

​

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:Tip: For more on the Vlookup values, see W3 (external link)

   • 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  
     

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.

   • Authorization: Google Sheets

   • Headers: Content-Type: application/json

5. 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][*].

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

   • Authorization: Google Sheets

   • Headers: Content-Type: application/json

5. Create the variable for the retrieved values with the value of $.values

6. Save your changes.