Run an E-Commerce App on Google Sheets


Our developers used Google Apps Script to automate a Google Sheets-based warehouse management system. Learn how to do it, too

By Ronny Shani | Technical consultants: Stefanie Engel, Niklas Gümmer, Merlin Carter

Imagine yourself sitting at your desk, staring at your screen. There’s an open spreadsheet. Dozens of rows and columns with truncated words stare back at you. You and your CTO are busy researching databases, ERPs, and backend systems for the app you’re building. You were almost ready to make a decision when she reminded you that you needed to discuss the frontend at your next meeting with the designers. Then it hits you: why not use the spreadsheet for that?

If this sounds familiar, even partly, you can reach out and turn off the light bulb hanging over your head: Using Google Sheets as a layer of your app or website tech stack is an actual thing. Whether an inventory data management app or a makeshift database, the service supports API calls, CRUD operations, and complex workflows that interact with other tools (Gmail, Calendar, Forms, etc.).

There are, of course, crucial issues to verify before moving forward:

  • Can Google Sheets handle the task?
  • What are the limitations and drawbacks?
  • Is it scalable, and how difficult would it be to migrate?

One indication of the platform’s business potential is Google’s 2020 acquisition of AppSheet — a low-code platform designed for these use cases. Check out a tutorial that will walk you through setting up an elaborate app using this paid service that’s integrated into the Google Cloud framework.

Keep reading to learn more about the pros and cons, or skip to the tutorial to develop your custom project.


Spread around

Running an e-commerce app without a full-fledged dev team might seem impossible, but there are ways around it, especially during prototyping but also in production. If you’re familiar with Google Sheets and Google Apps Script, you can handle quite advanced use cases without spending money and tackle both front- and backend challenges.

In fact, we helped one of our B2C e-commerce portfolio companies to do just that. Implementing a front- and a backend system that interacts with a database and an ERP system requires a considerable investment upfront — on off-the-shelf products or a team of developers, and they didn’t have either, especially in this early stage, when their business flow is not yet mapped.

A man holding a tablet connected to a handheld scanner, logging items in a warehouse
Scanned products should reflect the stock movement (Photo by Tiger LilyPexels)

Our portfolio company already used a third-party app to scan the products in the warehouses. When they scan the inventory, it should reflect the stock movement: the item was ordered, received, and delivered to the customer. However, their version of the app only integrates with spreadsheets (Microsoft Excel and Google Sheets), not databases, so they based their whole inventory management on Google Sheets.

Besides being a free service, Google Sheets provides an often overlooked benefit: A familiar UI that eliminates the need to design a dashboard or some sort of frontend while providing lots of built-in functionality: You can search, filter, sort, calculate, and apply any formula functions.

It worked well for a while, but there were two challenges:

  1. The process was manual, meaning app users saw an outdated product catalog; items could be out-of-stock, new products were logged but hadn’t yet appeared online, etc. One employee was responsible for manually syncing the changes to the backend, effectively updating the inventory status twice a day. We needed to improve the situation and ensure speedy, automatic sync between the reality reflected in the warehouse (i.e., the spreadsheet) and what the customer sees in the app.
  2. The barcode scanning app only supports storing data in a single sheet, so there’s no built-in solution for splitting data between several sheets. When we wanted to create a workflow that syncs two sheets and reflects purchase orders, we had to solve this one-sheet limitation.

MVP to the ERP

At first, our developers thought about using a service runner or a Lambda function running on Google Cloud Platform. They soon realized that there was no need for any external solution. Everything can be done with regular functions using Google Apps Script, the company’s cloud-based JavaScript platform that “integrates with and automates tasks across Google products”.

As a temporary solution, the script we implemented eliminates that manual step and synchronizes the data much faster. It also serves as a workaround for splitting the data: The scanned data goes into one sheet, and the script splits the data by purchase order.

An ERP system can certainly handle all of these challenges and requirements, but it’s difficult to set it up when you’re just starting and unsure how complex your process will be. Instead, our portfolio company now has a clear picture of the process and can either prototype and develop an in-house solution or decide which ERP would best fit their needs.

Not every app needs a database in the backend. There are ways around that, and this is one of them

Using Google Sheets with Google Apps Script to handle front- and backend tasks proved faster, easier to bootstrap, and cheaper — particularly compared to building your own solution or implementing an ERP system. It’s also more inclusive — a streamlined UI that most employees are comfortable with not only guarantees adoption but also means less time and money spent on training. In short, it allows you to create a quick, affordable, secure, and intuitive MVP.

Balance the sheets

But a clever solution that allows rapid development isn’t enough. When choosing a mission-critical framework, we need to make sure it’s sustainable in the long term. It’s time to break down those questions mentioned earlier because, like every solution, this one also has trade-offs.

Pros

  • Affordable — assuming you already code in JavaScript, Apps Script is free and requires zero maintenance.
  • Fast — you can set up the whole project in a couple of hours.
  • Standalone — no need for third-party services, APIs, or SDKs.
  • Inclusive — the frontend UI is familiar and intuitive, requiring little training.
  • Platform agnostic — works on any device and OS. If you have people familiar with Google Sheets, it’s an easy integration that you can use on Android or iOS.
  • Rich functionality out-of-the-box — Google handles everything: Storage, security, upgrades, user management (roles, access permissions, authorization, etc.), backups, automation, and more.
  • Flexible — you define what data you collect and in which format. You can ensure type validation (only allow numbers in the amount field, for example), receive notifications, set up CRON jobs, review logs, etc.

Cons

  • Scalability — Google Sheets supports up to 10 million cells, so you can’t have unlimited rows. There are also quotas for database connections (including failed attempts), triggers, script and custom function runtime, and more. If your operation becomes large-scale in a short time, or you increase your inventory and have to log a lot of products each day, you might hit the quota.
  • Performance — the longer the sheet, the slower it gets. Consequently, the more rows you add, the harder it is to use the built-in filters and searches, and there’s no way to upgrade performance. It’s only as fast as Google can make it.
  • Fragile — you can use Google Sheets as a provisional relational database, but taking advantage of advanced spreadsheet formula functions and working with multiple interdependent sheets renders this solution more vulnerable. As those Google sheets are so tightly linked to each other, it’s pretty hard even to add a new column because you might break the script’s logic.
  • Migration — unless you’re using a third-party library that converts your data to JSON, the easiest way to move data away from Google Sheets is to export it in CSV format. However, depending on the complexity of your setup and sheet structure, the migration might be slow and painful. It’s most likely not going to be a process where you switch off one thing and switch on another.

Take stock

So is it a viable solution? Unsurprisingly, the short answer is “it depends” — mainly on the scale: when you’re scaling, you quickly hit the limits. Otherwise, it would be feasible, especially for a small team running a medium-sized app.

Understanding your app’s data flow and architecture is much more important than the technical solution you choose

Not every app needs a database in the backend. There are ways around that, and this is one of them. Going back to our portfolio company, it’s easy to see that if they decided to build a custom system (front- and backend), it would have taken much longer and cost much more; they wouldn’t even be live yet.

Understanding your app or service’s data flow and architecture is much more important than the technical solution you choose. The challenge is to outline your logistics before deciding which system you plan to build.

The technical challenge isn’t that big. It’s more about the different domains that come together and having a complete overview: What data do you need? When do you need it? Where does it need to go? You should examine how the warehouse runs, how the operations and content teams work, how their data is stored, and what you want to do with it.

Test drive

Google Apps Script offers an extensive ecosystem featuring both official and community resources. If you’ve never worked with the platform, consider reading Google’s comprehensive 5-part crash course titled “Fundamentals of Apps Script with Google Sheets”.

This interactive codelab will walk you through creating macros, custom formula functions or menus, working with the Spreadsheet Service (Google’s custom Classes and Methods), connecting to third-party APIs, and other aspects of working with data in Google Sheets.

What are we going to build?

This tutorial will walk you through writing a script that pulls new data entries from the sheet and writes them in a MySQL database. We’re going to explore automatic and manual options, how to add menu items to the sheet to allow users to perform actions, and how to leverage your preferred local development environment.

Disclaimers and clarifications

  • For the sake of simplicity, we’ll be using the database root user, but you should always set up a dedicated database user for your app.
  • As we’ve noted in the article, you should add validation/error handling when using this in production.
  • Spreadsheet formula functions might interrupt the script, so consider how complex your sheet is.
  • Usually, you wouldn’t want your frontend to access the database directly due to security concerns.

Prerequisites

  • Development environment: You can develop Google Apps Scripts locally using CLASP, Google’s Node-based Apps Script CLI. Check out the official repository for more information.
  • Database: Apps Script can connect to external databases through the JDBC service, which supports its proprietary Cloud SQL MySQL, Microsoft SQL Server, Oracle databases, or MySQL. Your script must open a connection to the database and then interact with it via SQL statements.
    Note: If you’re not using Google’s SQL instance or our recommended solution (see below), please consult Google’s tutorial before you begin.

Tutorial

  1. Set up a MySQL database via Railway. It’s a fast, free, and reliable cloud deployment service where you can set up a MySQL database within seconds.
  2. Create a new Google Sheet document and fill it up with your data. Our example uses a 3-columns inventory sheet, listing a barcode, product name, and the number of items.
  3. Enable Apps Script — Select Extensions > Apps Script. Note that this will make the script “bound” — it will only run in the context of this particular sheet. Creating a web app or a public add-on script that’s available in any sheet is beyond the scope of this article and requires setting up a Google Sheets API account. You’re welcome to explore the possibilities.
  4. Rename your project > Click Untitled project on the top left and type in the new name.
  5. Copy the code below and paste it into the editor, deleting the content of the file that was created automatically (code.gs):
// Database variables - called in databaseUpdate.js
var hostAddress = "HOST_ADDRESS:PORT";
var userName = "USER_NAME";
var userPassword = "PASSWORD";
var databaseName = "DATABASE_NAME";
var databaseUrl = "jdbc:mysql://" + hostAddress + "/" + databaseName;
// Create or update table
var tableName = "TABLE_NAME"
// SQL statements
var createTheTable = `CREATE TABLE IF NOT EXISTS ${tableName} (FIELD_1 VARCHAR(255), FIELD_2 VARCHAR(255), FIELD_3 INT, EntryID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(EntryID))`;
var updateTheTable = `INSERT INTO ${tableName} (FIELD_1, FIELD_2, FIELD_3) values (?, ?, ?)`;
Code language: JavaScript (javascript)
// Create the table if it doesn't already exist, and update it with data from the sheet
function updateDatabase() {
  console.log("Setting up the connection...");
  var connection = Jdbc.getConnection(databaseUrl, userName, userPassword);
  var sheet = SpreadsheetApp.getActiveSheet();
  var sheetData = sheet.getDataRange().getValues();
 // Input from databaseVars.js
 // Prepare and execute conditional 
  try {
    var tableCreate = connection.prepareStatement(createTheTable)
    tableCreate.executeUpdate()
    console.log(`Created the ${tableName} table`);
    var tableInsert = connection.prepareStatement(updateTheTable)
  }
  catch (SQLException) {
    console.log(SQLException);
  }
 // Loop over sheet columns and write to the database
  console.log("Starting to write...");
  for (var i = 1; i < sheetData.length; i++) {
    FIELD_1 = sheetData[i][0];
    FIELD_2 = sheetData[i][1];
    FIELD_3 = sheetData[i][2];
    console.log(`${FIELD_1} | ${FIELD_2} | ${FIELD_3}`)
    tableInsert.setString(1, FIELD_1);
    tableInsert.setString(2, FIELD_2);
    tableInsert.setInt(3, FIELD_3);
    tableInsert.addBatch()
    tableInsert.executeBatch()
  }
  tableInsert.close();
  tableCreate.close();
  connection.close();
  console.log(`Finished updating the ${tableName} table in ${databaseName} database`);
}
Code language: JavaScript (javascript)
  1. Click the Save icon and hit the Run button. Note: After you click Run for the first time, you’ll be asked to authorize the script.
  2. A little window titled Authorization required will pop up. Click on the Review permissions button.
  3. Choose the Google account you are using to run the script and click the Allow button.

Once your script is running, the Execution log will be visible below the code editor. Assuming you used our code sample as is, this is what you should see:

Google Apps Script built-in Execution log shows a part of the script and the results of running it
The built-in Execution log lists all the steps performed by the script

As we mentioned above, you can further extend the functionality of your script by adding triggers or sidebars, interacting with third-party APIs or Google’s services, and more. Let’s look at an example:

Add a menu item to clear the uploaded rows

  1. Create a new file in the editor > Click the plus sign (+) on the top left and select Script.
  2. Name your file.
  3. Copy the code below and paste it into the editor, deleting the content of the file that was created automatically (code.gs):
// Create a new menu item to clear the uploaded rows in Google Sheet.
// If the user confirms the action, the deleteUploadedRows function is called from another file
function onOpen() {
    SpreadsheetApp.getUi()
        .createMenu("Clear Sheet")
        .addItem("Delete rows", "clearSheet")
        .addToUi();
}
function clearSheet() {
    var ui = SpreadsheetApp.getUi();
    var result = ui.alert(
        "Please confirm",
        "Would you like to delete the rows that were uploaded to the database?",
        ui.ButtonSet.YES_NO);
    if (result == ui.Button.YES) {
        deleteUploadedRows();
        ui.alert("Done");
    } else {
        return;
    }
}
Code language: JavaScript (javascript)
// The function that gets called when clicking the new menu item to delete the uploaded rows
function deleteUploadedRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var numRows = sheet.getDataRange().getLastRow() - 1;
  for (var i = numRows; i > 0; i--) {
    sheet.deleteRow(numRows);
  }
  console.log(`Deleted ${numRows}`);
}
Code language: JavaScript (javascript)
  1. Click the Save icon and hit the Run button.
  2. Go back to the Google Sheet tab. You’ll see a new menu item on the rightmost section of the toolbar (to the right of Help). Click it, and select the Delete rows option you’ve created.
  3. A little window will pop up, asking you to confirm the action. Hitting No will terminate the script; Yes will run it and clear the rows.

Now that you’ve gotten this far, what do you think about this type of solution? Would you consider implementing a similar workflow? Do you have experience with these platforms? Let’s talk!