Online store with google apps

Building My Own Store with Google Apps: A Creator's Approach

The Motivation Behind the Store

This blog entry walks through the creation of the intellar online store. The main drive of the store was to give interested creators a streamlined way to accelerate their projects. I was receiving emails asking about hardware availability—and while the parts were usually ready, there was no easy way for visitors to know what was in stock. On top of that, I often gather unused, quality components from past projects presented here, and I know they could benefit someone else.

The solution required a simple, integrated platform: a way for visitors to see current availability, place orders directly, and maintain a real-time inventory.

The KISS Principle & The Google Ecosystem

As with all my projects, I wanted the store to be self-made and strictly adhere to the KISS principle, maintaining a close connection with clients. Using external services like Square wasn't appealing, and since this website is hosted on Google Sites, my options for a custom e-commerce backend were limited.

The answer lay in the Google Apps ecosystem. The entire store functions by integrating three core tools: Google Forms, Apps Script, and Sheets. Each tool manages a distinct and essential part of the store's functionality:

• Google Sheets Inventory Keeper   Manages product data and stock levels.

• Google Forms The Storefront. The dynamic page where visitors view items and place orders.

• Google Apps Script  The Engine.  The custom code that connects the Sheet (inventory) and the Form (storefront). 

Anatomy of the Store: Sheets and Forms

Google Sheets: The Inventory Keeper

This is the central database. The spreadsheet contains two primary tabs: one to hold incoming orders and a second for the inventory. The inventory tab uses five columns to track key data: item name, price, description, number in stock, and a comment. I use the comment column to note a restock date.

Google Forms: The Storefront

The form is the visitor interface. It's a standard form pre-populated with questions, primarily multiple choice for product selection and short answer for details like email and quantity. The key feature is that the content of the multiple-choice questions is dynamically updated to reflect the current inventory, ensuring accuracy for the visitor.

The Glue: Google Apps Script and Triggers

Linking the Form and Sheet

The foundational step is linking the Google Form to the Google Sheet. This is easily done via the Form's Responses tab. Once linked, every submission automatically copies the user's order into the Sheet's designated form responses tab.

The Inventory Update Engine

The final, and most important, piece is ensuring the Form updates when the inventory changes—that is, when the Sheet is edited. This is managed by a custom script I created using Google Apps Script (accessed via Extensions > Apps Script in the Sheet menu).

The script itself is straightforward:

1. It first gets a reference to both the Sheet (which is known because it is the source of the trigger) and the Form (using FormApp.openById() and the Form's unique ID).

2. It then iterates through the inventory data in the Sheet and uses that data to re-populate the choices in the Form's product question.

To see the exact mechanism, you can review the code snippet below, which focuses on updating the first question.

The Installable Trigger: Why It's Necessary

A crucial detail is how the script is triggered. Because the script needs to access the Google Form document, which is external to the spreadsheet where the script lives, it requires an Installable Trigger. This special trigger grants the script the necessary permissions to communicate with the outside Form service.

Once the script is written, and the Installable 'On Edit' Trigger is manually set up and authorized, the store is live! I simply embedded the Form onto the dedicated store page at intellar.ca/store.

Script and procedure

/**

 * Updates Google Form choices from the "inventaire" sheet.

 * Handles both manual run (no 'e') and trigger run (with 'e').

 */

function updateQuestion1Choices(e) {

  // Get Spreadsheet. Use e.source if triggered, otherwise getActiveSpreadsheet.

  const ss = e ? e.source : SpreadsheetApp.getActiveSpreadsheet();

  const inventorySheet = ss.getSheetByName("inventaire");

  if (!inventorySheet) return Logger.log("Error: 'inventaire' sheet not found.");

  // --- Form Setup ---

  const FORM_ID = "xxxxx-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";

  let form = null;

  try {

    form = FormApp.openById(FORM_ID);

  } catch (error) {

    return Logger.log(`Error opening form ${FORM_ID}: ${error.toString()}`);

  }

  // --- Process Data ---

  const data = inventorySheet.getDataRange().getValues();

  const allOptions = [];

  for (let i = 1; i < data.length; i++) {

    const [nom, prix, , , stockRaw, note] = data[i]; // Destructuring for clarity

    const stock = parseInt(stockRaw, 10);

    const dispoStatus = (isNaN(stock) || stock <= 0)

      ? `(rupture de stock)`

      : `(${stock} en stock)`;

    const optionString = `${nom} - ${prix} USD/unit - ${dispoStatus} ${note ? `- ${note}` : ''}`;

    allOptions.push(optionString.trim());

  }

  // --- Update Form Question (Item 0) ---

  const items = form.getItems();

  // Consolidate the checks for item existence and item type

  if (items.length > 0 && items[0].getType() === FormApp.ItemType.MULTIPLE_CHOICE) {

    const question1 = items[0].asMultipleChoiceItem();

    question1.setChoiceValues(allOptions);

    Logger.log(`Updated ${allOptions.length} choices.`);

  } else {

    // Log error if either the item count is zero OR the first item isn't a multiple choice

    Logger.log("Error: Failed to update form. Check if the first item exists and is Multiple Choice.");

  }

}

Procedure 1: Linking a Google Form to a Google Sheet

This process ensures every form submission is immediately captured in a designated spreadsheet.

• Open the Google Form you want to link in Edit mode.

• Navigate to the "Responses" tab at the top of the editing page.

• Click the green Google Sheets icon (the small spreadsheet graphic) located on the right side of the Responses tab.

• In the pop-up window, choose your destination:

  • Create a new spreadsheet (Recommended for a clean setup). Provide a name and click "Create."

  • Select existing spreadsheet (If you want to add the responses as a new tab to a file you already use). Select the file and click "Select."

• Confirm the Link: The form is now active! A new sheet tab will open, and all future submissions will appear there instantly.

Procedure 2: Adding an Installable Trigger

Use this procedure when your script needs special authorization (e.g., sending emails, accessing external files) or when the simple onEdit() trigger is insufficient.

• 1. Rename the Function:

  • Change your function name to anything other than onEdit(e) (e.g., handleEdit(e)). This prevents conflicts with the simple trigger.

• 2. Write the Authorized Code:

  • Place your script logic (like the handleEdit(e) example below) into the function.

function handleEdit(e) {

   // Your authorized code goes here...

}

• 3. Open the Triggers Menu:

  • In the Apps Script editor, click the Triggers icon (looks like a clock or alarm) on the left sidebar.

• 4. Add a New Trigger:

  • Click the + Add Trigger button at the bottom right.

• 5. Configure Settings:

  • A panel will open. Set the following options:

    • Choose which function to run: Select your renamed function (handleEdit).

    • Select event source: Choose From spreadsheet.

    • Select event type: Choose On edit.

• 6. Save and Authorize:

  • Click Save. You will be prompted to review permissions and authorize the script to run with the necessary authority. This step is the key to enabling advanced actions like sending email.

Your script will now run with full authority whenever the spreadsheet is edited.