Integrating Google Sheets with WhatsApp for Order Status Updates

When you are selling products through e-commerce or delivery, providing customers with real-time order status updates is crucial. One effective way to achieve this, avoiding your support team wasting time with recurrent questions from customers,  is by using a chatbot to integrate Google Sheets with WhatsApp. This blog post will guide you through setting up an automated system to allow customers to check their order status via WhatsApp using 2Chat to orchestrate all your customer conversations.

What you will need:

• 2Chat Account with a phone number connected
• Google Sheets Template

This video will explain the process step by step and you can also find some details below.

Setting Up Google Sheet Template

• Start by copying a Google Sheet Template to your own account
• Populate this sheet with the information for every active order adding Order ID, OrderStatus, Notes, and CustomerName.

Enable App Script:

• Go to the App Script section in Google Sheets.
• Enable execution permissions for the code by selecting the doPost method and clicking on Debug.
• Review and allow the necessary permissions using your Google account.
• Stop the code execution once permissions are granted.

Deploy the Code as a Web App:

• Click on Deploy and then New Deployment.
• Select Web App as the type.
• Add a name for the deployment and ensure the execution account is the same as the one used for permissions.
• Set access to Anyone and click on Deploy.
• Authorize access using the same Google account.
• You will now have a public URL to use as an API.

Configuring the WhatsApp Chatbot

Create a Flow in 2Chat:

• In the initial step, select the phone number and use Message Received as the trigger.
• Attach a text menu where one of the options allows users to ask for their order status.

Request Order ID:

• Attach a Message and Wait for Reply component to the menu option.
• Ask for the Order ID and store it in a custom field named OrderId.

Integrate Google Sheets API:

• Add an HTTP component and paste the Google Sheets script URL.
• Select POST as the request type.
• In the body, add a JSON structure with the Orderid and select the custom field created earlier.

{
    "orderId": orderId_selected_from_custom_fields
}

Map the Response:

• Use the response section to map the return structure to 2Chat custom fields.
• Use JSON evaluation paths to map Order Status, Notes, Customer Name, and Order ID.

Handle Responses:

• Attach a filter component to check if the order is found.
• If the Order ID is not zero, return the order information to the user.

• If the order is not found, return a message informing the user.
• Add components to handle errors and inform the customer to contact customer service if something goes wrong.

Save and Publish the Flow:

• Save and publish the flow to make it live.
• Test the flow to ensure it works as expected.

Testing the Flow

Initiate the Chat:

• Start the chat and select the option to check the order status.
• Enter the OrderID when prompted.
• The chatbot will use the HTTP component to retrieve the order status data from Google Sheets.
• The order status information will be displayed to the user.

Conclusion

Integrating Google Sheets with WhatsApp using a chatbot created in 2Chat is a powerful way to provide real-time order status updates to your customers. This setup not only enhances customer satisfaction but also streamlines your order management process. By following the steps outlined above, you can create an efficient and user-friendly system to keep your customers informed about their orders.