While grocery shopping at Wegmans, I saw a poster that promoted the use of an app to scan products and bag them while shopping, to reduce the time to pay for the groceries.

Then, I thought that something similar could be easily done with a Twnel Superbot.

I explain how to create such a chatbot flow in the How to create a chatbot to scan products at the supermarket & pay at the self-checkout cashier article

However, before doing that flow we need a repository with the barcode information, including the barcode number, the product name, and the price, so the supermarket scan app can query that data when adding items to the cart.

The following video shows the Superbot in action and how the Google Sheet is populated on the fly.

So, we need to populate a Google Sheet with at least the following information (the simpler version):

  • Barcode value. This is the numeric value that represents a product
  • Full name of the product
  • Price

You might want to have some other information on that table (on a more advanced version), such as:

  • Identifier of the person that added a product to the sheet. In our case, is her cell phone number. Twnel uses the phone number as the unique identifier of a user.
  • The date the product was added.
  • A description of the product
  • The manufacturer of the product
  • The category of the product
  • The brand of the product
  • Ingredients of the product
  • Nutrition facts
  • Promotion columns. For example, discounts on certain dates and so on.
  • And maybe the image of the product

The simpler version

For our little exercise, we will use the first three, plus the identifier of the person that added the product and the date the product was added.

The flow will be something like this:

The Google Sheet table will have the following info:

We could populate this table manually. Typing the barcode value and the rest of the information directly on the sheet. However, that would be tedious and prone to errors as each barcode can have many digits and in some cases, the font below the barcodes is quite small.

And, of course, that would defeat the purpose of this article, wouldn’t it?

Thus, to create the Superbot to capture the barcode and the name and save that info into the sheet, we need to:

  1. Create a script that can receive the data form the Superbot (using Google Apps Script)
  2. Generate a webhook (endpoint) to be triggered when there is data from the Superbot to be saved.
  3. To create the Superbot using the Twnel bot framework

Creating an Apps Script that can receive the data from the Superbot

To create an Apps Script, you need to have a Google Workspace account. If you have  G Suite Free Edition, you can use that one too.

Google Workspace includes Sheets, Docs, Slides, Meet, Calendars, and more. Apps Script is part of that and it allows you to automate tasks involving one or more of those services. It also gives you access to other services, such as access to Maps APIs and more.

How to create a new script 

First, you need to launch the Apps Script. Simply go to the Google Workspace menu and select Apps Script to open it, as shown below.

Alternatively, visit script.google.com to open the script editor. (You’ll need to be signed in to your Google account.) 

If this is the first time you’ve been to script.google.com, you’ll be redirected to a page that introduces Apps Script. 

Click “Start Scripting” to proceed to the script editor.

A welcome screen will ask what kind of script you want to create. Click Blank Project or Close.

Delete any code in the script editor and paste in the code below.

function doPost(e) {
  return handleRequest(e);
}

function handleRequest(e) {
  // Service for returning text content from a script
  const output = ContentService.createTextOutput();
  
  // open your spreadsheet by passing id or active spreadsheet
  const ss = SpreadsheetApp.openById("The id of your Google Sheet")
  var sheet = ss.getSheetByName("Barcodes");
  
  const jsonString = e.postData.contents;
  const jsonData = JSON.parse(jsonString);
    
  var barcode = jsonData.barcode;
  var product = jsonData.product;
  var price = jsonData.price;
  var phone = jsonData.phone;

  const date = new Date();
  
  sheet.appendRow([phone, date, barcode, product, price]);
  
  const msg = "Done. Your " + product +" with barcode value " + barcode + " was added successfully."
  const result = {"msg": msg }
 
  var callback = e.parameters.callback;
  if(callback === undefined) {
    output.setContent(JSON.stringify(result));
  } else {
    output.setContent(callback + "(" +JSON.stringify(result) + ")");
  }
  
  output.setMimeType(ContentService.MimeType.JSON);
  
  return output;
}

You can get the id of your Google Sheet from the URL

In the image above is the text after d/ and before /edit as shown below

docs.google.com/spreadsheets/d/1FNT8Z2QIuYnmJYDegGgZ7Wik-3-s8ptjtHl3aOKmtuY/edit#gid=0

The Sheet name (the tab at the bottom) is any name that you want to name it. I named mine Barcodes.

When creating a webhook with Apps Script, you need to have a function doPost() or doGet() for when your webhook will be called with a POST or GET method respectively.

function doPost(e) {
  return handleRequest(e);
}

The code below allows referring to the Google Sheet and specific sheet where you want to save the data.

// open your spreadsheet by passing id or active spreadsheet
  const ss = SpreadsheetApp.openById("The id of your Google Sheet")
  const sheet = ss.getSheetByName("Barcodes");

With this code we tell the program what data to get from the chatbot.

const jsonString = e.postData.contents;
const jsonData = JSON.parse(jsonString);
    
const barcode = jsonData.barcode;
const product = jsonData.product;
const price = jsonData.price;
const phone = jsonData.phone;

In this case, we want to get the barcode number, the name or description of the product, the price of the product, and the phone of the person who sent the information. In Twnel, the phone number is the digital id of the users.

sheet.appendRow([phone, date, barcode, product, price]);

In this line, we append a row with those five values to the sheet.

The remainder of the program creates the response that the webhook will return to the chatbot flow. 

Fairly simple, isn’t it?

Getting the webhook URL (endpoint)

Follow the steps in the following animation to save your project as a web app (webhook).

You will need the resulting URL for the chatbot.

In the next section, I am showing how to create the chatbot that consumes that URL to send the barcode info to the Google Sheet.

How to create the Superbot using the Twnel bot framework 

Note: At this time, I am discussing the use of the bot spec (JSON). However, soon we will be releasing our own Twnel Chatbot Visual Builder. The following video shows a preview of it. I hope you are as excited as I am about its upcoming release.

Probably this is the first time exploring the Twnel Bot Spec. Thus, I recommend reading the Twnel Bot Specs Basics article first.

The full JSON spec for this flow is:

{
	"version": "1.0",
	"variables": {
		"user": {
			"phone": {
				"type": "string",
				"value": ""
			},
			"country": {
				"type": "string",
				"value": ""
			},
			"name": {
				"type": "string",
				"value": ""
			}
		},
		"chatbot": {
			"save_products_url": {
				"type": "string",
				"value": "YOUR WEBHOOK URL from the Google Apps Script"
			}
		},
		"session": {
			"barcodes": {
				"type": "array",
				"value": []
			}
		}
	},
	"messages": {},
	"files": {},
	"storage": {
		"twnel": {
			"enable": true,
			"mode": "public"
		}
	},
	"entrypoint": "barcode",
	"transitions": {
		"barcode": {
			"action": "barcode",
			"next": "product"
		},
		"product": {
			"action": "product",
			"next": "price"
		},
		"price": {
			"action": "price",
			"next": "save"
		},
		"save": {
			"action": "save",
			"next": "scan_other"
		},
		"end": {
			"action": "end"
		}
	},
	"actions": {
		"barcode": {
			"type": "send_message",
			"vars": {},
			"messages": [
				"Please scan a product"
			],
			"input": {
				"method": "keyboard",
				"type": "barcode",
				"display": "vertical",
				"data": [
					{
						"response": "Your Barcode was sent!"
					}
				]
			}
		},
		"product": {
			"type": "send_message",
			"messages": [
				"Enter the name of the product"
			],
			"input": {
				"method": "keyboard",
				"type": "text",
				"data": [
					{
						"placeholder": "Enter name of product"
					}
				]
			}
		},
		"price": {
			"type": "send_message",
			"messages": [
				"Enter the price of the product"
			],
			"input": {
				"method": "keyboard",
				"type": "number",
				"data": [
					{
						"placeholder": "Enter the price of the product"
					}
				]
			}
		},
		"save": {
			"type": "call_api",
			"vars": {},
			"eval": {
				"method": "POST",
				"content_type": "application/json",
				"url": "{{ variables.chatbot.save_products_url }}",
				"params": {},
				"headers": {},
				"body": {
					"phone": "{{ variables.user.phone }}",
					"barcode": "{{ transition.barcode.output.answer.value }}",
					"product": "{{ transition.product.output.answer.value }}",
					"price": "{{ transition.price.output.answer.value }}"
				}
			}
		},
		"end": {
			"type": "send_message",
			"vars": {},
			"messages": [
				"{{ transition.save.eval_response.msg }}"
			],
			"close_chat": true
		}
	},
	"functions": {}
}

In the variable section, the user variables are the default ones. Nothing to explain there.

In the chatbot variables, we create one called save_products_url  where we keep the endpoint URL of the webhook to send the data to the Google Sheet. As you can see, we use that variable in the “save” action. 

In this simple flow, we have five transitions and their respective actions. The first one is to ask the user to scan a barcode of a product. The next one to ask the user to write the name (or description) of the product. The following one asks the user to enter the price of the product. The Fourth one is to call an API to send the data to a Google Apps Script to add a row to the sheet. Since the Apps Script is expecting four parameters, this action does that. It sends the phone number, the barcode value, the product name, and its price. And lastly, the end transition doesn’t have the next step because it is the end of the flow. Its action returns the response from the webhook.

As you can see in the “save” action, in this case, we didn’t need to store the barcode value and the product value in session variables and we can retrieve them by referring to the output.answer.value of their respective transitions.

Fairly simple, don’t you think?

This is how it looks in the Chatbot Visual Builder

With it, you will be able to create a flow like this one in just 5 minutes.

A more advanced approach using an API to retrieve information about the product

Since the purpose of the chatbot flow to create a repository of barcodes is to be able to have some data to be consumed by the supermarket users that want to save time at the cashier, that flow should be enough.

However, if for some reason a supermarket or convenience store wants to create such a repository for real, they would like to have something a little more advanced.

One in which after scanning the barcode, it gets the data associated with it using an API from a service such as Barcode Lookup.

In this case, the flow would be like this:

As you can see, even though this time we don’t need to type the name of the product or anything else, we can get a lot of data from the product barcode.

The Google Sheet table will look similar to this:

The Apps Script code will be similar to the one in the simpler version, but passing more variables or parameters from the chatbot flow.

function doPost(e) {
  return handleRequest(e);
}

function handleRequest(e) {
  // Service for returning text content from a script
  const output = ContentService.createTextOutput();
  
  // open your spreadsheet by passing id or active spreadsheet
  const ss = SpreadsheetApp.openById("The id of your Google Sheet")
  var sheet = ss.getSheetByName("Barcode info");
  
  const jsonString = e.postData.contents;
  const jsonData = JSON.parse(jsonString);

  const tel = jsonData.tel;
  const barcode_number = jsonData.barcode_number;
  const product_name = jsonData.product_name;
  const category= jsonData.category;
  const manufacturer= jsonData.manufacturer;
  const brand = jsonData.brand;
  const ingredients = jsonData.ingredients;
  const nutrition_facts = jsonData.nutrition_facts;
  const description = jsonData.description;
  const image = jsonData.image;
  const price = jsonData.price;
  const date = new Date();
  
  
  sheet.appendRow([tel, date, barcode_number, product_name, category, manufacturer, brand, image, ingredients, nutrition_facts, description, price]);
  
  const msg = "Done. Your product " + product_name + " was added successfully."
  const result = {"msg": msg, "product_image": image};
 
  var callback = e.parameters.callback;
  if(callback === undefined) {
    output.setContent(JSON.stringify(result));
  } else {
    output.setContent(callback + "(" +JSON.stringify(result) + ")");
  }
  
  output.setMimeType(ContentService.MimeType.JSON);
  
  return output;
}

Furthermore, on the response, besides sending a message (msg), it sends the URL of the product image as well.

One thing I noticed, though, was that the ‘ character was returned as ' (see row 3 on the spreadsheet image above). So in order to fix that, I created a function replaceAll.

function replaceAll(str, search, replace) {
  const s = str.split(search).join(replace);
  return s;
}

And changed lines 18, 20 and 20 to:

const product_name = replaceAll(jsonData.product_name, "'", "'");
const manufacturer= replaceAll(jsonData.manufacturer, "'", "'");
const description = replaceAll(jsonData.description, "'", "'");

Probably there are better ways to fix that issue that is more general to any text encoding/decoding. But for this exercise, it’s enough.

The bot JSON spec in this case is:

{
	"version": "1.0",
	"variables": {
		"user": {
			"phone": {
				"type": "string",
				"value": ""
			},
			"country": {
				"type": "string",
				"value": ""
			},
			"name": {
				"type": "string",
				"value": ""
			}
		},
		"chatbot": {
			"save_product_url": {
				"type": "string",
				"value": "YOUR WEBHOOK URL from the Google Apps Script"
			},
			"get_products_details": {
				"type": "string",
				"value": "https://api.barcodelookup.com/v2/products"
			},
			"barcode_key": {
				"type": "string",
				"value": "YOUR Barcode Lookup KEY"
			}
		},
		"session": {}
	},
	"messages": {},
	"files": {},
	"storage": {
		"customer": {
			"enable": true,
			"drivers": {
				"s3": {
					"bucket": "twnelassets",
					"service_account": {
						"credentials": {
							"aws_access_key_id": "YOUR AWS access key id",
							"aws_secret_access_key": "YOUR AWS secret access key"
						}
					}
				}
			}
		},
		"twnel": {
			"enable": true,
			"mode": "secure"
		}
	},
	"entrypoint": "barcode",
	"transitions": {
		"barcode": {
			"action": "barcode",
			"next": "get_product_info"
		},
		"get_product_info": {
			"action": "get_product_info",
			"next": "price"
		},
		"price": {
			"action": "price",
			"next": "save"
		},
		"save": {
			"action": "save",
			"next": "store_image"
		},
		"store_image": {
			"action": "store_image",
			"next": "response"
		},
		"response": {
			"action": "response",
			"next": "show_image"
		},
		"show_image": {
			"action": "show_image",
			"next": "scan_other"
		},
		"scan_other": {
			"action": "scan_other",
			"next": {
				"yes": "barcode",
				"no": "end"
			}
		},
		"end": {
			"action": "end"
		}
	},
	"actions": {
		"barcode": {
			"type": "send_message",
			"vars": {},
			"messages": [
				"Please scan a product"
			],
			"input": {
				"method": "keyboard",
				"type": "barcode",
				"display": "vertical",
				"data": [
					{
						"response": "Your Barcode was sent!"
					}
				]
			}
		},
		"get_product_info": {
			"type": "call_api",
			"vars": {},
			"messages": [
				"Enter the price of the product"
			],
			"eval": {
				"method": "GET",
				"content_type": "application/json",
				"url": "{{ variables.chatbot.get_products_details }}",
				"params": {
					"barcode": "{{ transition.barcode.output.answer.value }}",
					"key": "{{ variables.chatbot.barcode_key }}"
				},
				"headers": {},
				"body": {}
			}
		},
		"price": {
			"type": "send_message",
			"input": {
				"method": "keyboard",
				"type": "number",
				"data": []
			}
		},
		"save": {
			"type": "call_api",
			"vars": {},
			"eval": {
				"method": "POST",
				"content_type": "application/json",
				"url": "{{ variables.chatbot.save_product_url }}",
				"params": {},
				"headers": {},
				"body": {
					"tel": "{{ variables.user.phone }}",
					"barcode_number": "{{ transition.get_product_info.eval_response.products[0].barcode_number }}",
					"product_name": "{{ transition.get_product_info.eval_response.products[0].product_name }}",
					"category": "{{ transition.get_product_info.eval_response.products[0].category }}",
					"manufacturer": "{{ transition.get_product_info.eval_response.products[0].manufacturer }}",
					"brand": "{{ transition.get_product_info.eval_response.products[0].brand }}",
					"ingredients": "{{ transition.get_product_info.eval_response.products[0].ingredients }}",
					"nutrition_facts": "{{ transition.get_product_info.eval_response.products[0].nutrition_facts }}",
					"description": "{{ transition.get_product_info.eval_response.products[0].description }}",
					"image": "{{ transition.get_product_info.eval_response.products[0].images[0] }}",
					"price": " {{ transition.price.output.answer.value }}"
				}
			}
		},
		"store_image": {
			"type": "store_data",
			"eval": {
				"driver": "s3",
				"uri": "{{ transition.save.eval_response.product_image }}"
			}
		},
		"response": {
			"type": "send_message",
			"vars": {},
			"messages": [
				"{{ transition.save.eval_response.msg }}"
			]
		},
		"show_image": {
			"type": "send_message",
			"vars": {},
			"messages": [
				""
			],
			"media_url": "{{ transition.store_image.output.answer }}",
			"metadata": {
				"media": "image"
			}
		},
		"scan_other": {
			"type": "send_message",
			"messages": [
				"Would you like to scan another product barcode?"
			],
			"input": {
				"method": "keyboard",
				"type": "radio",
				"data": [
					{
						"id": "yes",
						"label": "Yes"
					},
					{
						"id": "no",
						"label": "No. I'm good"
					}
				]
			}
		},
		"end": {
			"type": "send_message",
			"vars": {},
			"messages": [
				"No problem. You can launch the chatbot flow at any time to scan more products"
			],
			"close_chat": true
		}
	},
	"functions": {}
}

There are only a few differences in this flow. The first one is that I replaced the transition and action named product. In the first case, the action was a send_message with an input type of text, whereas in this one, I named the transition and the action as get_product_info and the action was a call_api that sent the barcode number to an API from Barcode Lookup.

Then, some of the response values are sent to the Apps Script webhook. 

You can get a free key from Barcode Lookup for testing purposes, by creating a free account. 

Another difference is that since we get an image of the product from the response when saving the data to the Google Sheet, we are storing that image in an Amazon S3 bucket.

For that we need two things:

To define a bucket in the storage “section” of the spec, such as:

"storage": {
		"customer": {
			"enable": true,
			"drivers": {
				"s3": {
					"bucket": "twnelassets",
					"service_account": {
						"credentials": {
							"aws_access_key_id": "YOUR access KEY ID",
							"aws_secret_access_key": "YOUR secret access KEY"
						}
					}
				}
			}
		},

The second one is  “store_image” transition and action. The action takes the image URL returned by the webhook after saving the barcode information and stores it on the S3 bucket.

"store_image": {
	"type": "store_data",
	"eval": {
		"driver": "s3",
		"uri": "{{ transition.save.eval_response.product_image }}"
	}
},

The last difference is that I added the show_image transition and action to be able to display in the bot the image of the product just saved. The media_url takes the output.url from the transition in which I stored the image to S3.

"show_image": {
	"type": "send_message",
	"vars": {},
	"messages": [
		""
	],
	"media_url": "{{ transition.store_image.output.url }}",
	"metadata": {
		"media": "image"
	}
},

At this point, you may ask WHY?

Why not take directly the URL returned by the Barcode Lookup API, which is the same returned by the webhook?

That’s a great question. On many occasions, images are not displayed when coming from different domains. It’s a CORS issue. So, we solve it by copying the image to a public bucket. In this case, I am using a customer private bucket, but I could have a simpler approach using a public bucket and avoiding the need for the store_image transition and its corresponding action

That’s it. Those are the differences, but now the chatbot is a lot more useful 🙂

In the Twnel Chatbot Visual Builder, the flow looks like this:

Again, something that would take just a little time to create.

Do you have ideas of chatbots that you would like us to discuss how to build them?

If so, please add a comment below.

Additionally, please share this article with anyone that can benefit from it. 

Author: Gilbert Mizrahi

Head of Growth – Twnel

Always looking for problems to solve

Get notified when we publish new articles, use cases, and product updates, as well as when we produce new podcasts and webinars.
Copy link
Powered by Social Snap