Building galleries with Google Sheets data
Last updated
Last updated
One of the most powerful features of Activechat’s Google Sheets chatbot integration is the ability to build dynamic Google Sheets chatbot galleries, displaying the data in your spreadsheets based on specific search criteria. There is a whole bunch of possible use cases – product galleries in e-commerce chatbots, events of company directories, team member profiles, etc. As always with Activechat, imagination is the limit.
Use GS-SEARCH block to build these dynamic galleries, setting up specific search criteria to limit the data displayed. There is quite a number of settings for this block, so let’s look into it in more detail.
There is a number of sections in GS-GALLERY block editor settings, let’s walk them through one by one.
Choose your spreadsheet and worksheet here. Similar to GS-SEARCH and GS-UPDATE blocks. Do not forget to connect your Google account first!
Set your search criteria to limit the number of gallery cards shown Again, similar to GS-SEARCH and GS-UPDATE blocks. You can combine up to three criteria for three different columns here.
Choose which columns to use for your dynamic gallery card’s title, image, and description. Embedded Google Sheets images are not supported yet, so upload your images to a website and use direct URLs in the image column.
Attach attribute values to each of your dynamic gallery cards. When a user clicks on a button in one of the cards, these attributes will be available as $_selected_gallery_<attribute_name> in your chatbot (more on this later).
IMPORTANT: do not use whitespaces in attribute names, and use only column indices (A to XX) as values!
In the example above, there will be two attributes (“price” and “weight”) attached to each gallery card, with values from columns E and F respectively. When a user clicks the card, these will be available as $_selected_gallery_price and $_selected_gallery_weight.
Indicate the page number for galleries that contain over 10 cards. The total number of Google Sheet rows that fit your search criteria will be available upon block execution as $_gs_total_results, and the total number of pages – as $_gs_total_pages system attributes. See the real-life pizza delivery chatbot below for a detailed example of using multiple-page galleries.
Choose the sorting order and column to sort your results by. If there’s no column specified, the gallery will be sorted in the order of rows found.
Finally, add up to three buttons to each of the cards in your dynamic gallery. These can be event buttons to trigger chatbot events and run skills when clicked, URL buttons to open specific website pages (quick hint: you can use card attributes to insert dynamic links from a specific column of your Google Sheet to URL buttons), or direct link buttons to connect other chatbot building blocks for simple tasks.
When GS-GALLERY block is executed in your chatbot conversation, Activechat will perform a search on your Google Sheet and display a dynamic gallery built from the information that is available in the Google Sheet columns. There will be a couple of attributes set after each execution so that you can build your conversation correctly:
$_gs_total_results will contain the total number of rows that fit your search criteria (i.e. the number of cards that this block can display)
$_gs_total_pages will contain the total number of rows found, divided by 10 and rounded to the next bigger integer. Actually, this will be the number of pages (10 cards each) that your dynamic gallery will contain. Use it for pagination (see real-life example below for more details).
Once the chatbot user clicks a button in one of the gallery cards, the conversation flow will continue to the skill or block associated with that button, and another set of attributes will be set to help you identify which card was clicked:
$_selected_gallery_title will contain the title of the card that was chosen by the user in the dynamic gallery
$_selected_gallery_subtitle will contain the subtitle (description) of the card that was chosen by the user
$_selected_gallery_image will contain the URL of the image used in this card
$_selected_gallery_<attribute name> will contain attribute values for the card that was set in the “Attributes” section of the GS-GALLERY block. This is useful if you need to send more data to the follow-up conversation than it’s available in title, subtitle or image fields (like price and weight for the specific product, for example).
GS-GALLERY block can seem quite complex for novice chatbot developers, so we’ve made a post in our blog, describing the complete real-life example of a Google Sheets chatbot integration. Please check it here – Building an advanced restaurant delivery chatbot with Google Sheets integration
