Take your inventory to Google Sheets with QR/Bar code scanning Web Apps

In today’s fast-paced retail environment, efficiency is key. Small shop owners need a cost-effective solution for managing their inventory without breaking the bank. In this blog post, we’ll explore how you can create a user-friendly QR Code Scanner Web App using Google Apps Script, providing shop owners with a free and efficient tool to take your inventory to Google Sheets.

Video Tutorial

YouTube player

But, why QR codes, web apps, and Google Sheets?

  • Ditch the Paper: Scan product info with QR codes and manage your inventory directly in Google Sheets, accessible from anywhere on any device.
  • Boost Efficiency: Say goodbye to manual data entry errors and expensive software. Enjoy real-time inventory updates and mobile management for a smoother workflow.
  • Embrace the Future: Join the growing trend of QR code and web app adoption, unlocking a cost-effective and customizable solution for effortless inventory control.
  • Free & Customizable: This web app is completely free to use and adapt to your specific needs. Add additional data fields, create custom categories, and make it your own.

About the Web App

This web app empowers shop owners to effortlessly scan product barcodes or QR codes, input additional details, and seamlessly submit the information directly to a Google Sheets inventory list. It reduces the need for manual data entry, saving time and reducing errors.

Demo video:

Demo video: Take your inventory to Google Sheets with QR/Bar code scanning Web Apps

The web app is built with the following tools and library.

The web application primarily comprises two major sections: namely, the QR Scanner and the Data Entry Form. The data entry form is similar to the one we discussed in an earlier blog post. What is new here is that we have integrated a QR and barcode scanner into this web app to scan the products and input the codes into the data entry form. Therefore, users can insert the QR code along with other product details such as name, price, category, and quantity into the Google Sheets. This way, users can easily update their inventory in Google Sheets.

This is a responsive web app. Therefore, users can utilize it on their mobile phones and leverage their cameras to easily scan QR and barcode without needing to purchase additional equipment.

Here’s how to deploy the web app to scan QR/Bar codes to take your inventory to Google Sheets.

To use this, you’ll need to deploy it as a web app in your Google Account. Below, I will briefly explain the deployment process and guide you on how to use it for scanning QR codes and barcodes. Then, in the next section, I will delve into the code and explain how you can customize it to fit your requirements.

Here is a brief overview of the workflow.

  1. Copy the below Google Sheets template to your Google Drive.
  2. Open the script editor and deploy it as a web app (Google Guide).
    • In your Google Sheets, Go to Extensions > Apps Script to open the script editor
    • Go to Deploy > New deployment
    • Go to Select type (⚙️icon)> Web App
    • Enter the information about your web app in the fields under “Configuration.” Select your email and “Anyone” if you want to access the web app without logging in to your Google Account.
    • Click Deploy. (When you run the script for the first time, you will be prompted to grant permissions. To do so, please click on ‘Continue’, select your email, click on ‘Advanced’, click on the name with the ‘Apps Script’ label, and then click ‘Allow’ on the next screen.)
    • Copy the web app URL
  3. Access the web app link in your mobile phone browser (“Try opening the web app in an incognito window if you encounter any difficulties accessing it.”).
  4. Click the “Request Camera Permissions” button and allow access to the camera. Select a back camera if you have multiple cameras on your phone.
  5. Click the Start Scanning button, scan the product QR/ Bar code, and add details such as product name, quantity, price, and category.
  6. Submit the form and your data will be saved in the Google Sheets.
  7. Continue to the next scan…
Google Sheets1

Link to Google Sheets

The Apps Script

As mentioned above, this script uses the Html5-QRCode library to add the QR code scanning feature to the web app. You can access the GitHub project from this link. You can also read their blog and see the demo at this link. And also you can see the source code of the full implementation shown in their blog from this gist.

The following is the complete code for this Google Apps Script project. You can also access this code via the Google Sheets link provided above.

Explaining the code

The above code consists of several files namely, Code.gs, CSS.html, Form.html, Index.html, JavaScript.html, and QrReaderJS.html.

Code.gs

The Code.gs file contains the main functions of the web app, including the doGet() function, which is called when the page loads, and the processForm() function, which is called when the user submits the form.

The doGet() function creates an HTML template and sets the title of the page to “QR Code Scanner”. It also adds a meta tag to set the viewport width to device-width and the initial scale to 1 making it responsive across all devices.

The processForm() function takes the form data as input and appends a new row to a Google Sheet with the data. It first gets the active spreadsheet and then gets the sheet named Data. It then appends a new row to the sheet with the formatted form data, which is converted into strings.

Index.html

The Index.html is the main HTML file for the web app. It includes the CSS, JavaScript, and QrReaderJS files, as well as the HTML for the form, the QR code reader, and the results table.

The JavaScript, CSS, files, and data entry forms have moved to separate files and are included using the serverside Include() function to make it easy to read and develop following the “HTML Service: Best Practices“.

Form.html

This file contains the HTML codes that create the data entry form. You can modify the form using this file. You should update the field names in the processForm() function of the Code.gs file accordingly.

QrReaderJS.html

This file houses the JavaScript code that handles QR code scanning using the user’s webcam. It integrates the html5-qrcode library to achieve this functionality. The code is a modified version of the demo code available at this gist. To explore further customization, refer to the GitHub project.

This script assigns the decodedText is assigned to the productCode field of the data entry form after successfully scanning the QR/ Bar code.

JavaScript.html

This file contains JavaScript code that manages form submission and interacts with the server-side Google Apps Script code (Code.gs) to process form data. It also handles other client-side JavaScript functionalities.

The preventFormSubmit() function applies to all forms on the page. It attaches an event listener to each form element. When a form is submitted, it calls event.preventDefault() to halt the default submission behavior. This is crucial for handling form submission through JavaScript instead of directly refreshing the page.

The handleFormSubmit(formObject) function is invoked when the form is submitted. It receives a JavaScript object representing the form data. It calls the google.script.run.processForm(formObject) method to send the form data to the processForm() function in Code.gs for server-side processing. It clears the form upon the successful submission of the form.

You can add your client-side JavaScript codes in this file.

CSS.html

This file contains the Cascading Style Sheets (CSS) code responsible for visually styling the app’s elements. CSS dictates how the app’s content is presented on the screen, including colors, fonts, spacing, layout, and visual effects.

This project leverages Bootstrap, a popular front-end framework, to streamline styling and enhance user experience.

Wrapping Up

In today’s fast-paced retail environment, efficiency is key. This blog post explored how you can create a user-friendly QR Code Scanner Web App using Google Apps Script. The web app is built with Google Sheets, Google Apps Script, and the Html5-QRCode Library. It empowers shop owners to effortlessly scan product barcodes or QR codes, input additional details, and seamlessly submit the information directly to a Google Sheets inventory list.

The web app is responsive and can be used on mobile phones, allowing users to leverage their cameras to easily scan QR and barcode without needing to purchase additional equipment. The web app is free and customizable. So, shop owners can add additional data fields, create custom categories, and make it their own.

Leave a Comment

Share via
Copy link