By /

Automate Your Google Sheets with Custom Triggers: Transforming Business Efficiency

Introduction

Image 1

Google Sheets is more than just a spreadsheet tool — it’s a lightweight backend system capable of automating repetitive tasks and managing dynamic workflows. However, constantly polling the sheet to check for changes can waste valuable computing resources.

What if you could trigger actions only when data changes — such as every time a new topic is entered? Fortunately, Google Apps Script offers Triggers, which allow you to run scripts at specific intervals or in response to events (like edits or form submissions).

In this article, we’ll walk through a practical example using the START method (Situation, Task, Action, Result, and Takeaway) to show how you can automatically assign serial numbers to entries in Google Sheets, triggered by real-time updates.

Situation

As part of my content management system, all blog topics are first listed in a Google Sheet. Some topics are entered manually, while others are imported automatically from a Google Doc journal. To track progress and reference each blog uniquely, every topic must be assigned a unique identifier, or as I call it, a Serial ID.

In database terms, this is often called a “Primary Key.” To automate this assignment process, I wrote a JavaScript function using Google Apps Script.

 


function assignUniqueSerialsToTopics() {
  const const SHEET_ID = 'Sheet ID'; // Your Google Sheet ID
  const SHEET_NAME = 'Tab Name'; // Your sheet tab name
  const sheet = SpreadsheetApp.openById(SHEET_ID).getSheetByName(SHEET_NAME);

  const values = sheet.getDataRange().getValues(); // Get all sheet data
  let serial = 1;

  for (let i = 1; i < values.length; i++) {
    const topic = values[i][1];     // Column B (index 1)
    const serialCell = values[i][0]; // Column A (index 0)

    if (topic && !serialCell) {
      // Assign new serial in "001" format
      sheet.getRange(i + 1, 1).setValue(padSerial(serial));
      serial++;
    } else if (serialCell) {
      // Keep serial count in sync even if some serials already exist
      const current = parseInt(serialCell);
      if (!isNaN(current) && current >= serial) {
        serial = current + 1;
      }
    }
  }
}

 

This function iterates through each row in the Google Sheet and assigns a new serial number in Column A, but only when Column B (the topic column) contains a value and no serial number exists yet. To generate the serial, it relies on a helper function called padSerial(), which defines the formatting of the ID.

In the current example — and for my specific use case — a simple numeric format like 0001, 0002, and so on works perfectly. However, the structure of these serial numbers can be easily customized based on your business needs. Think of them like SKU codes used in inventory systems — often encoded with meaningful prefixes or patterns, such as Dry001, MX01-AC, or LOG-2025-001, where each segment might represent category, region, or batch.

To achieve such customization, you can enhance or replace the padSerial() function with more advanced logic that dynamically constructs serial IDs using variables like topic type, date, or user-defined tags. This makes your system not only automated, but also adaptable and meaningful at scale.


function padSerial(num) {
  return num.toString().padStart(4, '0');
}

 

The problem with this program is that it works flawlessly when I am manually editing the google sheet. But when I am importing the topics from my doc file using another parse program. This program does not address the imports.

Task

Image 2

As most of the topics entering the sheet will be through another program and I want the above program to work every time in the background. I will have to set up a trigger for this program to run. In my case the topics from the journal are pulled into column B. Hence, the program must be triggered based on the condition that every time we have something inserted in column B. The program assignUniqueSerialsToTopics() will trigger to generate and assign a new serial key to the topic in column A.

Action

To make sure that our program can identify any changes in the google sheet. We use a function called onChange as listed below:


function onChange(e) {
  const SHEET_ID = 'Sheet ID';
  const SHEET_NAME = 'Tab Name';

  const sheet = SpreadsheetApp.openById(SHEET_ID).getSheetByName(SHEET_NAME);
  if (!sheet) return;

  // Only run when the Content sheet is changed
  const activeSheet = e.source.getActiveSheet();
  if (activeSheet.getName() === SHEET_NAME) {
    assignUniqueSerialsToTopics();
  }
}

 

Here, e.source part of the code is checking for any changes made to the sheet. A change anywhere on the sheet is enough to trigger this program. Further, getActiveSheet() specifies which sheet received the changes. As we dont want a serial number every time we make a change to the sheet. We always want to check if the change was in Column B. For every input in column B, we need the program to generate a new serial id.

Image 3

Next, to make this program functional, we must create a trigger in the app script that calls this function every time we make a change in the google spreadsheet.

Result

Image 4

After implementing this trigger-based automation, the system now reliably assigns serial IDs to new topics, regardless of how the data is added — manually or programmatically. Here’s what the automation handles effectively:

  • Adds serial like Dry001, Dry002 only if column B has a topic and column A is empty.
  • Skips blank rows or rows with duplicate serials.
  • Responds to any structural or content change due to the trigger setup.

This ensures consistency in topic tracking, improves data traceability, and removes the risk of duplicate entries — which is critical when managing blog pipelines or task queues.

Other Uses

Now that we know how triggers work. We can use the same technique to apply automatic triggers for a lot of different areas. Some of them are discussed below. I might discuss these use cases in detail if I find a project in future.

  • Inventory Management and Logistics: Assign SKUs to the inbound inventory items based on a pre existing list. Trigger reports or trigger actions for proactively managing the inventory and logistics operations.
  • Customer Support and Ticketing: Perform an action when customers email or comment a specific key phrase. Automatically assign tickets or assign que numbers.
  • Human Resource and Hiring: Automatically generate candidate ID’s. Can trigger the onboarding process to automate the onboarding process and share all the relevant information automatically.
  • Finance and Accounting: Automatically generate invoices and batch payment IDs when new orders received. Can trigger an action as we hit our budget and generate a report based on a predefined template.

Conclusion

Automating Google Sheets using custom triggers can significantly reduce manual effort and ensure consistent data handling. By assigning unique serial numbers automatically based on specific conditions (like entries in column B), we not only bring structure to our data but also prepare it for further processing or integration. This approach works seamlessly whether data is entered manually or via automated imports. As a next step, you can expand this logic to trigger emails, move rows between sheets, or even interact with external systems — all by leveraging the power of triggers and Google Apps Script.

Scroll to Top