How To Create A Chatbot To Scan Products At The Supermarket & Pay At The Self-Checkout Cashier

Recently, when I was waiting in line to get into the local Wegmans supermarket, I saw a poster showing how customers could scan and bag their products using an app, called Wegmans Scan.

Reading the poster I thought, hum, that should be fairly easy to implement using a Twnel Superbot (chatbot).

Thus, I went home and decided to create it and document the whole process. I hope it serves as inspiration, so you can create your own Superbots to solve many other problems or improve current processes.

General Idea

In this post, I will show the whole process. Since I don’t work at a supermarket chain and I don’t have access to their ERP / inventory system, I will simulate that using Google Sheets.

Thus, I will put some products’ data on a sheet. It will include the barcode information, the name of the product, and the price. 

To get the barcode info, I will create a simple bot to capture the barcode information and then to enter the product name. The bot will save that data to the sheet. 

The flow will be something like this:

Another approach is to use an API to get the data from the barcode and save that info to the Google Sheet. In that case, the flow looks like this:

If you have access to the ERP of the supermarket or any other repository with that information, or if you care only to see how to create the scan Superbot, you can ignore this extra step. 

However, if you want to learn a few things and tricks, I recommend you to read Creating a chatbot to capture a product barcode to create a repository of products for other applications.

Next, I show how to create a bot (or flow to be more precise) to scan the products and add them to the “cart”. 

When the user is ready to pay, the bot will generate a QR code that will be displayed on the bot (on the phone), which can be scanned at the self-checkout cashier to retrieve the content of the cart and to proceed to pay.

The flow will be something similar to this:

As you can see from this diagram, the flow is quite simple. I guess that’s the beauty of the Twnel Superbots.  They can be very simple and easy to create, but they can improve processes in very powerful ways.  In this case, it can replace the need to create custom mobile apps, which can take a while to develop and are expensive to build and maintain.

In order to save and to retrieve data from a Google Sheet, I am creating a webhook using Google Apps Script. Don’t worry I will explain everything.

The nice thing about this exercise is that the same chatbot can be used by you to try to sell the chatbot to a retail chain in your market.  Or, if you work for a supermarket chain, you can take the initiative to set it up yourself.  Who knows, maybe you will get a promotion 🙂

You may say, yeah but they would need to have it connected to their ERP or inventory system. And I would say…DEFINITIVELY

However, with this bot, you can show how it will work for them with little effort, like a pilot project.  Then, after they can test it in one supermarket, you could get the contract to refine it and consume the data from their ERP system.

And once inside you will be able to offer them more services and automation using Twnel. A win-win for everyone. lol.

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

Google Apps Script code for the webhook used by the chatbot flow to get product information

The data on the spreadsheet looks like this:

So, the webhook will need to receive the barcode number and retrieve the name and the price. 

Additionally, the same Apps Script code can create a new sheet to store the content of the cart.

For the cart, we won’t need any other information.

Thus, the Apps Script will be simply:

function doGet(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")
  const sheet = ss.getSheetByName("Barcode info");
  const values = sheet.getDataRange().getValues();
  
  const barcode = e.parameter.barcode_number;
  const obj = {};
  
  for(let i=0; i<values.length; i++) {
    const value = values[i][2];
    
    if(value == barcode) {
      obj.product_name = values[i][3];
      obj.price = values[i][11];
    }
  }
  
  const callback = e.parameters.callback;
  if(callback === undefined) {
    output.setContent(JSON.stringify(obj));
  } else {
    output.setContent(callback + "(" +JSON.stringify(obj) + ")");
  }
  
  output.setMimeType(ContentService.MimeType.JSON);
  
  return output;
}

I refer to the Google spreadsheet and the sheet where I have the data for the products. Then, I get all the values on the sheet with 

const values = sheet.getDataRange().getValues();

Then, looping through the rows and comparing the barcode number scanned with the bot with the barcode number in the spreadsheet (the third column has an index 0f 2 – since the index starts at 0).

Once it finds a match (barcode is equal to the value of that column in a given row), it grabs the name of the product and the price, and it puts them on an object that it returns to the bot.

Running the query on the browser, when sending a parameter barcode_number=28000517205, the result is (as expected):

So, the Apps Script code is working fine.

If you don’t know how to save this code as a webhook, refer to Creating a chatbot to capture a product barcode to create a repository of products for other applications article, where I explain that in detail.

The same Apps Script. However, can be used to create a new sheet for every user, to keep track of the content of their cart.  For the name of each of these sheets, we can use the phone number of the user, since it’s a unique identifier.

Thus, it makes sense to make the webhook a POST request and replace the doGet() function with a doPost() one. The Apps Script code should look like this then:

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("1FNT8Z2QIuYnmJYDegGgZ7Wik-2-E8ptjtHl3aOKmtuY")
  const sheet = ss.getSheetByName("Barcode info");
  const values = sheet.getDataRange().getValues();
  
  const jsonString = e.postData.contents;
  const jsonData = JSON.parse(jsonString);

  const phone = jsonData.phone;
  const barcode = jsonData.barcode_number;
  const remove = jsonData.remove || false;
  
  const product = {};
  let products;
  let response = {};
  
  for(let i=0; i<values.length; i++) {
    const value = Number(values[i][2]);
    
    if(value == barcode) {
      product.value = barcode;
      product.product_name = replaceAll(values[i][3],"&#39;", "'" );
      product.price = values[i][11];
    }
  }
  
  let uSheet = ss.getSheetByName(phone);
  
  if (uSheet == null) {
    ss.insertSheet(phone);
    uSheet = ss.getSheetByName(phone);
    uSheet.appendRow(["Phone", "Date", "Barcode", "Product Name", "Price"]);
  }

I added a new variable, remove, to be sent by the bot: It’s a boolean that indicates if the user wants to add a product to the cart or remove it.

As you can see, the code from line 37 to 41 checks whether the sheet by the name (phone number) exists or not.  If it doesn’t, it creates it and appends a row with the headers.

Then, we need to add a function to add a product to the cart (by adding a new row on the sheet), or remove a product from it (by deleting a row with that product barcode).

So the full script is:

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("1FNT8Z2QIuYnmJYDegGgZ7Wik-2-E8ptjtHl3aOKmtuY")
  const sheet = ss.getSheetByName("Barcode info");
  const values = sheet.getDataRange().getValues();
  
  const jsonString = e.postData.contents;
  const jsonData = JSON.parse(jsonString);

  const phone = jsonData.phone;
  const barcode = jsonData.barcode_number;
  const remove = jsonData.remove || false;
  
  const product = {};
  let products;
  let response = {};
  
  for(let i=0; i<values.length; i++) {
    const value = Number(values[i][2]);
    
    if(value == barcode) {
      product.value = barcode;
      product.product_name = replaceAll(values[i][3],"&#39;", "'" );
      product.price = values[i][11];
    }
  }
  
  let uSheet = ss.getSheetByName(phone);
  
  if (uSheet == null) {
    ss.insertSheet(phone);
    uSheet = ss.getSheetByName(phone);
    uSheet.appendRow(["Phone", "Date", "Barcode", "Product Name", "Price"]);
  }
  
  if(remove) products = removeProductFromCart(uSheet, barcode);
  else products = saveProductToCart(uSheet, product, phone);
  
  const qrCode = "https://api.qrserver.com/v1/create-qr-code/?size=250x250&data="+phone;
  
  response = {"product": product, products: products[0], "total_price": products[1], "n_products": products[2], "qrCode": qrCode};
  
  const callback = e.parameters.callback;
  if(callback === undefined) {
    output.setContent(JSON.stringify(response));
  } else {
    output.setContent(callback + "(" +JSON.stringify(response) + ")");
  }
  
  output.setMimeType(ContentService.MimeType.JSON);
  
  return output;
}

function saveProductToCart(sheet, product, phone) {
  const date = new Date();
  
  sheet.appendRow([phone, date, product.value, product.product_name, product.price]);
  
  return getCart2(sheet);
}

function removeProductFromCart(sheet, barcode) {
  const values = sheet.getDataRange().getValues();
  
  for(let i=0; i<values.length; i++) {
    const value = values[i][2];
    
    if(value == barcode) {
      sheet.deleteRow(i+1);
      break;
    }
  }
  
  return getCart2(sheet);
}

function getCart2(sheet) {
   const values = sheet.getDataRange().getValues();
  let totalPrice = 0;
  let products = "";
  
  for(let i=1; i<values.length; i++) {
    //const barcode = values[i][2]
    const product_name = replaceAll(values[i][3],"&#39;", "'" );
    const price = Number(values[i][4]);
    products += product_name + " - $" + price +"n";
    totalPrice += price;
  }
  
  const n_products = values.length - 1;
  return [products, totalPrice.toFixed(2), n_products];
}

function getCart(sheet) {
  const values = sheet.getDataRange().getValues();
  let totalPrice = 0;
  const products = [];
  
  for(let i=1; i<values.length; i++) {
    const barcode = values[i][2]
    const product_name = values[i][3];
    const price = Number(values[i][4]);
    products.push({"product_name": product_name, "price": price});
    totalPrice += price;
  }
  
  const n_products = products.length;
  return [products, totalPrice.toFixed(2), n_products];
}

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

Both, the removeProductFromCart and saveProductToCart functions return the result of another function getCart2.

getCart2 creates a string containing all the products and their respective prices and calculates the total price on the cart and the total number of items on it.

Then on line 46, I am using a QR code generator http://goqr.me/. As you can see, it’s fairly simple

const qrCode = "https://api.qrserver.com/v1/create-qr-code/?size=250x250&data="+phone;

You need to pass the size and the data as parameters, and that’s it. In this case, I am passing the phone number as the data contained in it, since it’s also the identifier of the user and the sheet where her cart information is stored.

I could create the QR code with Apps Scripts as well, but in that case I would have to store the image in Google Drive, then make the directory or at least the file public to make it work. With  http://goqr.me/ it’s much simpler.  Besides, its API allows to store several parameters, change the color of the code or the background, and more. See http://goqr.me/api/doc/create-qr-code/ for more information.

Finally, the script returns an object, response, which contains the information about the product just added or removed from the cart, the string with all products in the cart with their respective prices, the total price, the number of items on the cart, and the URL of the QR code.

In the script, I also have a function getCart, which returns an array of objects and all the products in the cart. I am not using it, but I included it there in case you prefer to return its content as an array instead of the string returned by getCart2. That can be useful if you want to do some additional processing of that data in the bot spec directly.

You can test the webhook with postman.

Test API with postman

Now that the webhook is ready, it is time to create the bot flow JSON spec.

{
	"version": "1.0",
	"variables": {
		"user": {
			"phone": {
				"type": "string",
				"value": ""
			},
			"country": {
				"type": "string",
				"value": ""
			},
			"name": {
				"type": "string",
				"value": ""
			}
		},
		"chatbot": {
			"get_products_details": {
				"type": "string",
				"value": "https://script.google.com/macros/s/AKfycbwWM4gt6bZe6m8zEpzibu6d-WuyvZMmJ7p-tXk4WaKbWORlubk/exec"
			}
		},
		"session": {
			"cart_content": {
				"type": "object",
				"value": ""
			},
			"barcode": {
				"type": "number",
				"value": 0
			}
		}
	},
	"messages": {},
	"files": {},
	"storage": {
		"customer": {
			"enable": true,
			"drivers": {
				"s3": {
					"bucket": "twnelassets",
					"service_account": {
						"credentials": {
							"aws_access_key_id": "YOUR AWS acess key",
							"aws_secret_access_key": "YOUR secret access key"
						}
					}
				}
			}
		},
		"twnel": {
			"enable": true,
			"mode": "public"
		}
	},
	"entrypoint": "scan_barcode",
	"transitions": {
		"scan_barcode": {
			"action": "scan_barcode",
			"next": "store_code"
		},
		"store_code": {
			"action": "store_code",
			"next": "get_product_info"
		},
		"scan_barcode2": {
			"action": "scan_barcode2",
			"next": "store_code2"
		},
		"store_code2": {
			"action": "store_code2",
			"next": "get_product_info"
		},
		"get_product_info": {
			"action": "get_product_info",
			"next": "store_cart_info"
		},
		"store_cart_info": {
			"action": "store_cart_info",
			"next": "response"
		},
		"response": {
			"action": "response",
			"next": "next"
		},
		"next": {
			"action": "next",
			"next": {
				"scan": "scan_barcode2",
				"remove": "remove_item",
				"view_cart": "view_cart"
			}
		},
		"remove_item": {
			"action": "remove_item",
			"next": "store_code3"
		},
		"store_code3": {
			"action": "store_code3",
			"next": "update_cart"
		},
		"update_cart": {
			"action": "update_cart",
			"next": "store_cart_info2"
		},
		"store_cart_info2": {
			"action": "store_cart_info2",
			"next": "response_after_remove"
		},
		"response_after_remove": {
			"action": "response_after_remove",
			"next": "next"
		},
		"view_cart": {
			"action": "view_cart",
			"next": {
				"qr_code": "store_image",
				"scan": "scan_barcode2",
				"remove": "remove"
			}
		},
		"store_image": {
			"action": "store_image",
			"next": "qr_code"
		},
		"qr_code": {
			"action": "qr_code"
		},
		"end": {
			"action": "end"
		}
	},
	"actions": {
		"scan_barcode": {
			"type": "send_message",
			"vars": {},
			"messages": [
				"Hi I am *Botty* and I will help you with your *shopping* today.nnLet's get started. Please scan a product"
			],
			"input": {
				"method": "keyboard",
				"type": "barcode",
				"geotag": true,
				"display": "vertical",
				"data": [
					{
						"response": "Your Barcode was sent!"
					}
				]
			}
		},
		"store_code": {
			"type": "map_result",
			"vars": {},
			"eval": {
				"targets": [
					{
						"from": "{{ transition.scan_barcode }}",
						"assign": [
							{
								"map": "output.answer.value",
								"to": "variables.session.barcode"
							}
						]
					}
				]
			}
		},
		"get_product_info": {
			"type": "call_api",
			"vars": {},
			"eval": {
				"method": "POST",
				"content_type": "application/json",
				"url": "{{ variables.chatbot.get_products_details }}",
				"body": {
					"phone": "{{ variables.user.phone}}",
					"barcode_number": "{{ variables.session.barcode}}",
					"remove": false
				},
				"headers": {}
			}
		},
		"store_cart_info": {
			"type": "map_result",
			"vars": {},
			"eval": {
				"targets": [
					{
						"from": "{{ transition.get_product_info }}",
						"assign": [
							{
								"map": "output.answer",
								"to": "variables.session.cart_content"
							}
						]
					}
				]
			}
		},
		"response": {
			"type": "send_message",
			"vars": {},
			"messages": [
				"The product {{ variables.session.cart_content.product.product_name }} with a price of {{ variables.session.cart_content.product.price | currency('en_US.UTF-8') }} was added to the cart.nnThe total price of your cart is *{{ variables.session.cart_content.total_price | currency('en_US.UTF-8')}}*nYou have *{{ variables.session.cart_content.n_products }}* items on your cart "
			]
		},
		"next": {
			"type": "send_message",
			"messages": [
				"What would you like to do next?"
			],
			"input": {
				"method": "keyboard",
				"type": "radio",
				"data": [
					{
						"scan": "Scan another product"
					},
					{
						"remove": "Remove a product from the cart"
					},
					{
						"view_cart": "View the cart"
					}
				]
			}
		},
		"scan_barcode2": {
			"type": "send_message",
			"vars": {},
			"messages": [
				"Scan a product",
				"You know the drill. Scan another product",
				"Scan a product to add it to your shopping cart"
			],
			"input": {
				"method": "keyboard",
				"type": "barcode",
				"display": "vertical",
				"data": [
					{
						"response": "Your Barcode was sent!"
					}
				]
			}
		},
		"store_code2": {
			"type": "map_result",
			"vars": {},
			"eval": {
				"targets": [
					{
						"from": "{{ transition.scan_barcode2 }}",
						"assign": [
							{
								"map": "output.answer.value",
								"to": "variables.session.barcode"
							}
						]
					}
				]
			}
		},
		"remove_item": {
			"type": "send_message",
			"vars": {},
			"messages": [
				"Scan the product that you want to remove from your cart"
			],
			"input": {
				"method": "keyboard",
				"type": "barcode",
				"display": "vertical",
				"data": [
					{
						"response": "Your Barcode was sent!"
					}
				]
			}
		},
		"store_code3": {
			"type": "map_result",
			"vars": {},
			"eval": {
				"targets": [
					{
						"from": "{{ transition.remove_item }}",
						"assign": [
							{
								"map": "output.answer.value",
								"to": "variables.session.barcode"
							}
						]
					}
				]
			}
		},
		"update_cart": {
			"type": "call_api",
			"vars": {},
			"eval": {
				"method": "POST",
				"content_type": "application/json",
				"url": "{{ variables.chatbot.get_products_details }}",
				"body": {
					"phone": "{{ variables.user.phone }}",
					"barcode_number": "{{ variables.session.barcode }}",
					"remove": true
				},
				"headers": {}
			}
		},
		"store_cart_info2": {
			"type": "map_result",
			"vars": {},
			"eval": {
				"targets": [
					{
						"from": "{{ transition.update_cart }}",
						"assign": [
							{
								"map": "output.answer",
								"to": "variables.session.cart_content"
							}
						]
					}
				]
			}
		},
		"response_after_remove": {
			"type": "send_message",
			"vars": {},
			"messages": [
				"The product {{ variables.session.cart_content.product.product_name }} with a price of {{ variables.session.cart_content.product.price | currency('en_US.UTF-8') }} was *removed* from the cart.nnThe total price of your cart is: *{{ variables.session.cart_content.total_price | currency('en_US.UTF-8') }}*nYou have *{{ variables.session.cart_content.n_products }}* items on your cart "
			]
		},
		"view_cart": {
			"type": "send_message",
			"messages": [
				"The products in your cart are:nn{{ variables.session.cart_content.products }}nnYou have a total of *{{ variables.session.cart_content.n_products }}* items in itnThe total price of your cart is: *{{ variables.session.cart_content.total_price | currency('en_US.UTF-8') }}*nnWhat do you want to do next?"
			],
			"input": {
				"method": "keyboard",
				"type": "radio",
				"data": [
					{
						"scan": "Scan another product"
					},
					{
						"remove": "Remove a product from the cart"
					},
					{
						"qr_code": "Pay at the self-checkout cashier"
					}
				]
			}
		},
		"store_image": {
			"type": "store_data",
			"eval": {
				"driver": "s3",
				"uri": "{{ variables.session.cart_content.qrCode }}"
			}
		},
		"qr_code": {
			"type": "send_message",
			"vars": {},
			"messages": [
				"Use the following QR code and scan it at the self-checkout cashier to pay"
			],
			"media_url": "{{ transition.store_image.output.answer }}",
			"metadata": {
				"media": "image"
			}
		},
		"end": {
			"type": "send_message",
			"vars": {},
			"messages": [
				"{{ transition.remove_item.output.answer.value }}  {{variables.session.barcode}}"
			],
			"close_chat": true
		}
	},
	"functions": {}
}

It looks complex, but it’s actually quite simple.

In the variables section, there is only one chatbot variable, get_products_details, which is the URL of the webhook created with Google Apps Script.

Then, there are two session variables, cart_content, and barcode. cart_content stores the object returned by the webhook each time is invoked. That is when the user scans a new product to add to the cart or scans a product to remove it from the cart. And barcode, which stores the barcode of the most recent product scanned.

If you read the article about the Twnel Bot Spec. or read the article about creating the repository for the products, you should understand the majority of this JSON spec.

In any case, line 57 indicates that the entry point of the flow is the scan_barcode transition. That means the flow will start with that transition. As you probably know by now, each transition defines an action to be executed and what’s the next transition.

The scan_barcode action, asks the user to scan a product barcode. It is a send_message action type, with an input type of barcode.  As you can see in line 144 it has the attribute “geotag” with a value of true.  That means that besides scanning a barcode we want to get the geolocation of the person when doing so.  I am not using that information here, but I will use it in a follow-up article.  So, subscribe to our newsletter (at the bottom of this article) and follow us on LinkedIn to make sure how and why I want to get the geolocation of the user when she starts using the chatbot flow.

In the next transition and action, I store the barcode number in a variable. Then, in the get_product_info action executed on the transition of the same name, the bot calls the webhook and sends three parameters: the phone of the user, the barcode number, and a boolean indicating whether or not the user wants to remove the item from the cart. If the value is false, it means she wants to add it. 

Even though I am using the same names for the pair of transition and action, each one can have its own unique name.

The next action, store_cart_info, stores the content of the cart in a variable named cart_content.

The response action, which is executed in the next step (or transition) shows some feedback to the user, getting different pieces of data from the cart_content variable.

Then, the next action is a send_message action that shows three buttons, to ask the user what she wants to do next: 

  1. Scan another product,
  2. Remove a product from the cart,
  3. View the cart

Thus, it has an input type of radio to allow that behavior.

The next transition on the next section (I know I didn’t choose the name of the transition well, as this can get confusing… sorry), shows the conditional branching depending on which option the user selects.

As you can see, each button on the next action has a label, what the user sees on the button, and an id, which is used on the transition to be able to route the user to the next transition. So, if the user clicks on the Scan another product button, the next step will be scan_barcode2, and so on.

The scan_barcode2 action is similar to the scan_barcode, but with a couple of differences. First, In this case, I don’t want to capture the geolocation, as I expect the user will continue shopping at the same supermarket. The other one is that the message should be a little different. The scan_barcode is also the starting point, so it needs to cheer or inform the user what the chatbot flow is about.

Furthermore, in order to make the flow less “robotic”, I am including an array of possible messages to the scan_barcode2 action. See lines 234 to 236. This means that each time this action is executed, the flow will display one of the messages randomly. I suggest you do the same in many other send_message actions.

The remove_item action is similar, but the message indicates that the user will scan a product that she wants to remove from the cart. When removing an item the flow executes the update_cart action, which is similar to the get_product_info one. However, the remove parameter is true when removing a product.

The view_cart action displays a text with the whole content of the cart, and the qr_code action displays the QR code that contains the phone number of the user. The idea is that the QR code will then be scanned at the self-checkout, so it can retrieve the cart content and charge the customer accordingly.

Additionally, in order to show the QR code that the user will use to pay at the self-checkout cashier, we need to enable a public or private Amazon S3 or Azure bucket.

In a case like this probably you would use a public one. However, for the purpose of this tutorial, I am using a private one. Thus, on lines 38 to 50, I am creating a private S3 bucket, that is referenced in the store_image action and used in the qr_code action.

Unfortunately, I can not show here the scanning of the QR code at checkout functionality, since it has to be programmed to be executed at the self-checkout cashier. The idea then is that once the customer pays, that will trigger the call of a Twnel API that can send the phone number to another webhook that will create a PDF out of the cart content and sends it to the bot flow for the user records.

The last part of the flow can be adjusted, so the user can go to a paying area, to make sure she is not adding more products after payment, scan a QR code that gives her access to a payment flow and continue to pay directly from that flow on her phone. Maybe one day we will see that 🙂

Soon, we will be launching the Chatbot Visual Builder, which will make building chatbots much easier. This chatbot flow looks like this in the visual builder:

Supermarket Scan visual  builder

Quite simple, for such powerful automation, don’t you think?

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

If so, please add a comment below and subscribe to our newsletter, so you won’t miss out on similar content when we publish it.

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

Copy link
Powered by Social Snap