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

  Copy

  Ask AI

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

​

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

     Copy

     Ask AI

     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/RANGE_REFERENCE?valueInputOption=USER_ENTERED
    Replace RANGE_REFERENCE with a data reference to your integration step range variable. Click the button to select the data reference.
    • Authorization: Google Sheets
    • Headers: Content-Type: application/json
    • Body
      json

      Copy

      Ask AI

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

     Copy

     Ask AI

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

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

   Copy

   Ask AI

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

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

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

     Copy

     Ask AI

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

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

     Copy

     Ask AI

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

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