Return multiple results from a spreadsheet

Knowledge Level: Advanced

We often deal with scenarios where a Spreadsheet search has multiple results. By default, a webhook response will only push one result to the bot user. To be able to collate multiple spreadsheet results as a response to a bot user, we will have to utilize an Integromat module called 'Array Aggregator'.

The Array Aggregator will allow us to summarize our search results and pass that into a webhook response as one message. The complete scenario will look as below:

15411541

After your Google Sheet search results, setup an Array aggregator module:

730730

As the output is going to have multiple results, we will need some sort of formatting tool to store the various pieces of information, summarize them in human-readable format and store them in various variables. Hence we will utilize JSON as our framework for structuring our incoming information. For more information about JSON, please visit this link https://www.json.org/json-en.html

Setup a JSON module in Integromat which will feed information from the array aggregator. To do so, add another module and look for 'Create JSON'

730730

We now need to add a structure to our JSON module to format the information in a carousel as we have to present multiple results through the webhook response. Click on 'Add' in the JSON module, give it a suitable name and click the 'Generator' button. In the sample data section insert the code below:

{
  "message":{
    "raw":{
      "attachment":{
        "type":"template",
        "payload":{
          "elements":[
            {
              "title":"A ticket to the theatre",
              "buttons":[
                {
                  "type":"postback",
                  "title":"View this lot",
                  "payload":"@BP:MESSAGE:83510"
                },      
                {
                  "type":"postback",
                  "title":"Place a bid",
                  "payload":"@BP:MESSAGE:83510"
                }
              ],
              "subtitle":"Did you want to bid?"
            },
            {
              "title":"A £100 voucher",
              "buttons":[
                {
                  "type":"postback",
                  "title":"View this lot",
                  "payload":"@BP:MESSAGE:83510"
                },
                {
                  "type":"postback",
                  "title":"Place a Bid",
                  "payload":"@BP:MESSAGE:83510"
                }
              ],
              "subtitle":"Did you want to bid?"
            }
          ],
          "template_type":"generic"
        }
      }
    }
  },
  "recipient":{
    "id":"{{1.fbuser.fbid}}"
  }
}

We are using a carousel with two buttons in this case. Once you have saved the data structure, your module will look as follows:

444444

Press 'OK' to close the module for now, we will came back to this in a bit.

Save your scenario and go back to the array aggregator module. Under the 'Target structure type' dropdown, look for select the first JSON module. Here we will configure the message parts that need to be summarised from our Google Sheet result.

448448

In the 'buttons' section, configure the information you would like to show to the bot user.

In the 'paylod' section input the message id from The Bot Platform where the user should be directed after clicking on the button.

862862

After configuring the 'Array Aggregator' we need to configure our JSON module. In the JSON module, use the array output from the previous module and the recipient ID.

441441

The last step is to add a webhook response module. In the body, use the output from the JSON module and that should complete your scenario.

671671