DEV Community: Yoshi

PTA Digital Transformation with Google Workspace: A One-Year Journey

Yoshi — Thu, 18 Jun 2026 02:52:12 +0000

Since April 2025, I have been handling IT operations for our PTA. The PTA uses Google Workspace, and there was a volunteer position called “Account Administrator” that required PC skills. Thinking it would involve managing accounts and working with spreadsheets, I applied for the role. However, the reality turned out to be quite different.

While Google Sheets was indeed being used, most of the work relied heavily on manual processes, which was honestly discouraging. A thick operations manual had been prepared, covering everything from monthly tasks to procedures performed only once a year. Some of the original documentation referenced throughout this article remains in Japanese as it was created for local PTA operations.

Over time, I started building small tools to gradually reduce the monthly workload.

Reading through handover documents was difficult. Distributing information to individual classes was tedious. Tracking the status of inquiries was cumbersome. I wanted to eliminate as many of these repetitive manual tasks as possible. Fortunately, I had some experience with cloud technologies and databases, which allowed me to experiment and gradually build a collection of cloud-based tools centered around the Google ecosystem.

In this article, I will look back on what I built over the course of a year, the order in which I built it, and the reasoning behind the overall architecture. Originally, I intended to write this retrospective at the end of my term in late March, but as often happens, I kept putting it off. Eventually, I found myself publishing it at this somewhat unexpected time.

Timeline

April 2025
- Started developing a suite of PTA operational tools
- Consolidated handover documents and operational knowledge using NotebookLM
- Built a bulk Google Drive distribution tool
May 2025
- Implemented a workflow that automatically adds users to Google Groups from a mailing list registration form and sends confirmation emails
- Added automatic ticket numbering, acknowledgment emails, and Google Chat notifications to the inquiry form
- Created a simple ticket management interface to track inquiries and their resolution status
June 2025
- Established the core PTA registration workflow, including parent registration, new entry processing, and notification delivery
- Built the data management foundation using BigQuery tables, views, and stored procedures for membership and role information
- Developed a web application for browsing the PTA directory, including search, filtering, and export functionality
- Officially adopted Google Chat as the primary organizational communication platform
July 2025
- Evaluated the effectiveness of a volunteer assignment preparation tool for library duty scheduling
- Improved the process for synchronizing Google Group memberships based on BigQuery data changes
- Developed a tool that automatically generates cultural material loan request forms and distributes them as Excel files
August 2025
- Added support for PTA role changes and various response-processing workflows
- Automated the parent information update pipeline
- Migrated and launched the PTA website within Google Workspace
- Added automated Google Analytics 4 reporting and aggregation
September 2025
- Established a local development workflow using Apps Script and clasp
- Improved user-facing directory update notifications and export functionality
- Began designing and implementing a PTA membership fee collection system
- Created BigQuery tables for billing and payment tracking and started the initial Stripe integration
October 2025
- Completed the core implementation of the payment collection system
- Added Stripe Checkout, webhooks, payment verification, signed payment links, billing emails, and payment reminder workflows
- Refined the BigQuery schema and improved deployment and operations with Cloud Run and Docker
- Continued improving parent information updates and directory synchronization accuracy
November 2025
- Focused primarily on ongoing operations and minor enhancements
December 2025
- Implemented a volunteer recruitment form for library duty assignments
- Added support for multi-slot selection, instructional messaging, and volunteer notification emails
January 2026
- Developed selection logic for next year's officer nomination process
- Created SQL queries and Google Apps Script tools to support officer selection
February 2026
- Built the full library duty assignment system
- Implemented a family ID–based registration interface, first-come-first-served and lottery assignment logic, recruitment period controls, directory views, and shift replacement workflows
- Enhanced the membership fee system with additional interfaces, including payment completion reports and billing email logs
March 2026
- Improved the library duty replacement workflow
- Refined replacement requests, candidate selection, user interface elements, notifications, and authentication processes
- Developed a customized email distribution tool for targeted user groups

Looking Back, the Year Fell into Three Distinct Phases

Looking back, the improvements made over the year can be broadly divided into three phases.

The first phase, from April to May 2025, was about replacing individual manual tasks. I built tools for consolidating handover documents, distributing files to class shared drives, handling inquiries through a help desk system, and managing mailing list registrations. At this stage, the goal was not to integrate data across systems but simply to eliminate recurring weekly and monthly manual work one task at a time.

The second phase, from June to October 2025, focused on building a foundation. The challenge shifted toward managing information shared across multiple business processes: membership directories, parent registrations, officer assignments, mailing list synchronization, access control, and payments. This was when BigQuery entered the picture. Google Group updates became automated, access permissions were organized, and responsibilities that spreadsheets alone could no longer support gradually moved into a separate data layer.

The final phase, from late 2025 through March 2026, was the transition toward an operational platform. Volunteer recruitment, shift scheduling, replacement requests, notifications, and campaign-style email distributions gradually evolved from simple forms into services that users actively interacted with on an ongoing basis. By this point, the collection of tools felt less like a set of convenient scripts and more like a small ecosystem of business applications.

It All Started with Eliminating Painful Manual Work Using GAS

My first impression when I took over directory administration in March 2025 was simple: this is incredibly manual.

Although the PTA had already adopted Google Workspace, nearly every operational process had been designed around manual work. To be fair, that may have been unavoidable given the nature of a PTA organization. Most positions rotate annually, and procedures are intentionally designed so that anyone can perform them regardless of technical background.

The first tool I built was a handover document consolidation tool.

Handover documents were created every year, but their quality and level of detail varied significantly depending on who wrote them. Some years were excellent; others contained only minimal information. To make the accumulated knowledge easier to access, I created a system that searched Google Drive for .docx handover documents scattered across folders organized by year and class.

The tool extracted documents whose filenames contained the Japanese term for "handover," collected the contents into a spreadsheet, and organized them by school year, grade, and class. I then used NotebookLM to generate summaries and compile frequently asked questions from the collected material.

This was not a flashy automation project, but it solved a recurring problem: every year, someone had to spend hours figuring out where information was stored and what had been done previously. Since both the source data and output destination already lived within Google Workspace, Google Apps Script (GAS) was the obvious choice. Being able to work directly with Drive, Docs, and Sheets within a single environment made development remarkably straightforward.

The next tool was a bulk distribution system for class shared drives.

The organization supported students ranging from preschool through high school, resulting in roughly forty shared folders organized by grade and class. Previously, documents were distributed manually, one folder at a time, with a surprising amount of care and effort devoted to what was essentially repetitive file copying.

It was not difficult work, but it was undeniably tedious.

The new tool allowed documents to be distributed to multiple class shared drives at once. PTA operations frequently involve sending the same file to several classes or committees, and repeating that process manually increases the risk of missed deliveries or accidental distribution to the wrong destination. To address this, I automated the entire workflow: identifying recipients, distributing files, recording logs, and archiving the distribution history afterward.

The design philosophy during this period was quite simple:

If the workflow already lives entirely inside Google Workspace, GAS is usually enough.

There was no need to provision infrastructure, Google handled the underlying platform, and future maintenance would be easier for whoever inherited the system next.

Automating Intake and Registration Workflows

By May 2025, the focus began to shift from automating tasks that I personally handled to building tools that could be used across the organization.

One example was improving visibility into inquiries.

When someone submitted a Google Form, the system would automatically generate a ticket number, copy the submission into a logging spreadsheet, send an acknowledgment email, and post a notification to Google Chat. Later, I added a simple web interface that allowed staff to track and update the status of each ticket.

It was not a full-fledged support platform by any means, but I wanted to provide three capabilities that were essential for PTA operations:

Confirmation that an inquiry had been received
The ability to track requests without losing anything
Shared visibility among multiple volunteers

The volume of inquiries was not large enough to justify introducing an external SaaS solution, but relying solely on a shared email inbox was becoming difficult. This system occupied the middle ground, using forms, spreadsheets, and notifications to provide lightweight process management.

In practice, PTA inquiries are relatively infrequent. Each request tends to be small, making it tempting for a volunteer to handle it privately and move on. However, that creates a black box where nobody else knows who received the request, how it was handled, or whether it was ever resolved.

At this stage, the goal was straightforward: establish a reliable foundation through ticket numbers, activity logs, and notifications so that inquiry handling became visible and traceable.

I described the implementation details of this system in a separate article:

Yoshi

May 21 '25

DIY Ticketing System with Google Apps Script for Handling Inquiries

#gas #beginners #tooling

5 min read

Around the same time, I also released an automated mailing list registration system.

The workflow took responses submitted through a form, added members to Google Groups, sent a confirmation email, and updated the processing status in a spreadsheet. As shown in the meeting document below, the previous process had surprisingly little verification. If someone requested a change, their information would often be updated without any confirmation of identity. The new system was far from perfect, but it was certainly an improvement. Looking back at the implementation, it appears I also included basic validation to check whether the requester and the registration email address matched.

Another change that proved valuable during this period was the introduction of Google Chat.

The PTA had a deeply ingrained email culture. People would send messages like "Understood, thank you!" and include everyone on CC, resulting in overflowing inboxes filled with low-value email traffic. This was the point at which we started encouraging a simple rule: internal communication should happen in chat whenever possible.

That cultural shift turned out to be important later. Once Google Chat became part of daily operations, building notification bots and automated alerts became much easier because the communication channel was already in place.

Perhaps the most important lesson during this phase was learning to separate tasks that require human judgment from tasks that can safely be delegated to software.

The content of an inquiry still requires a human to interpret and respond. However, assigning ticket numbers, sending acknowledgment emails, posting Chat notifications, and recording status updates can all be automated.

The same principle applied to mailing list registration. Exceptional cases still required human review, but receiving requests, returning processing results, and maintaining logs could be handled automatically.

I found myself constantly asking the same question:

"What kinds of exceptions can occur, and can the system handle them automatically?"

Thinking through those edge cases again and again turned out to be one of the most valuable experiences of the entire project. It shifted my focus from simply automating routine tasks to designing workflows that could remain reliable even when real-world operations became messy.

It Became Clear That Forms and Email Alone Were Not Enough for Directory Management

The development of the PTA directory registration system from June 2025 onward was one of the biggest turning points of the entire year.

For the first time, the focus shifted from building standalone automation tools to designing an end-to-end process: how parent information should be collected, updated, and ultimately reflected in communication channels and operational systems.

Before this redesign, the workflow was surprisingly fragmented. Student data changes received from the school were sent to an external vendor, which updated its own database and returned the updated records as Excel files. Mailing list updates were then performed manually using those spreadsheets.

While this approach technically worked, it came with several problems. Updates were slow, duplicate records accumulated, outdated information remained in circulation, and the operational burden on volunteers was significant. In particular, even when a new parent registration or contact information change was submitted, the update would not reach the mailing lists until the next monthly processing cycle. In the worst case, a parent could go more than a month without receiving important communications.

Frankly, that raised an obvious question:

Can you really call it a directory system if updates take over a month to become effective?

It was also one of the major sources of parent inquiries.

This was the point where I finally introduced what I had wanted from the beginning: a proper database.

The core directory management workflow was moved into BigQuery. Google Forms and Google Apps Script continued to handle data entry, but normalization, change detection, historical tracking, and mailing list eligibility calculations were all moved into BigQuery.

For new registrations, GAS performed validation checks such as UUID verification, email address matching, and confirming that identity verification had occurred within a fifteen-minute window. Rather than writing directly into the production directory, submissions were first accepted as validated data.

From there, BigQuery stored procedures updated tables such as students, guardians, and ml_member_current, with the final step synchronizing eligible members to Google Groups.

One of the most important components introduced during this transition was the mailing list uploader.

This was the first time I was able to automate Google Groups management itself.

Saving form responses and sending notifications had always been possible with GAS, but automating the addition and removal of group members required Google Workspace administrative privileges. Once those permissions were incorporated into the workflow, the system moved beyond merely automating record-keeping and into automating actual administrative operations.

For the first time, the entire process—from registration to mailing list synchronization—became a single connected workflow.

As a result, registrations could be accepted immediately, and mailing list updates could typically be completed the next day. A process that previously had a lead time of up to a month was reduced to roughly one day.

That was the point where the system finally began to feel like a real system rather than a collection of helper scripts.

The reasons for adopting BigQuery were equally straightforward.

Spreadsheets alone had reached their limits for managing change detection and historical records. Comparing student data received from the school against form submissions, recalculating enrollment status and validity flags, and deriving mailing list membership from those results are all tasks that are dramatically easier to understand and maintain in SQL.

Equally important, BigQuery integrates naturally with the rest of the Google ecosystem. It connected smoothly with GAS, Google Forms, Google Cloud Storage, and Google Groups, making it a particularly practical foundation for this type of operational workflow.

Even within the Git repository, I established naming conventions such as load_, refresh_, and update_ to distinguish between ingestion, recalculation, and synchronization processes. This was not driven by technical necessity so much as maintainability. Months later, anyone looking at the code should be able to understand what a process does simply by reading its name.

That may seem like a small detail, but it reflects one of the lessons I appreciated most from my professional experience working with data platforms and operational databases.

Another important consideration was how student data was received from the school.

From a purely technical perspective, an API integration would have been ideal. In reality, however, the school's existing workflow revolved around email, and changing that process would have created unnecessary friction for everyone involved.

Instead, I designed the system around the current operational reality. At the beginning of each month, a scheduled email request is sent automatically. The school's reply email contains an attachment, which is stored in Google Cloud Storage. The latest file is then picked up by the BigQuery pipeline and processed automatically.

It is not the most technically elegant architecture.

However, it is an architecture that people can actually continue using.

Throughout this project, that consideration consistently outweighed technical perfection.

These changes transformed a monthly process that depended on volunteers sacrificing personal time to perform repetitive manual updates into one where data pipelines handle most of the work and people primarily review the results.

New registrations, contact information changes, officer assignments, and student data updates all enter the system through different paths, but ultimately converge into the same BigQuery update pipeline. From there, updates flow outward to mailing lists, directory applications, and other downstream systems.

Looking back, one design principle made the entire system sustainable:

Many entry points, but a single source of truth.

Establishing that architecture became the foundation that allowed directory management to continue evolving throughout the year.

Making Information Accessible Across the Organization

Once the directory system was in place, the next challenge was making the information usable.

In June 2025, I released a web-based PTA Directory Viewer. The architecture was intentionally simple: the latest data was periodically exported from BigQuery views into a spreadsheet, and the cached data was then displayed through a GAS web application.

From a technical standpoint, there was nothing particularly sophisticated about the solution. One small but important design decision was implementing role-based access control. Board members could view the entire directory, transportation coordinators could see only the bus routes they managed, and class representatives could access information for their own classes. Row-level filtering and column restrictions were applied based on account identity and assigned roles.

Directories are useful, but they also become a risk if too much information is exposed. From the beginning, the principle was straightforward:

Show only the data that each person actually needs.

Although it would have been possible to connect directly to BigQuery using something like Connected Sheets, I intentionally chose a scheduled export and cache-based architecture. The reasons were simple: predictable performance, stable costs, and operational simplicity.

A directory viewer should be lightweight, reliable, and easy to explain. Unless real-time updates are essential, a cached approach is often much easier to operate.

An equally important challenge was user education.

Before this system existed, individual departments would download copies of the directory spreadsheet and maintain their own versions. As a result, nobody knew which file contained the latest information. Contact changes were sometimes communicated through unofficial channels without the directory administrator even knowing about them.

I vividly remember spending a significant amount of time encouraging people to abandon the familiar copy-and-paste workflow and use the directory viewer as the single source of truth.

Following the same philosophy, I introduced class-specific mailing lists for class representatives in September 2025.

Groups were created on a per-class, per-school-year basis, and memberships were synchronized automatically from the master directory. The key difference was that synchronization was based on the desired end state rather than simply adding new members. Enrollment and withdrawal status were continuously evaluated, and each mailing list was updated to reflect the current roster.

This marked another shift in thinking. Rather than treating mailing list management as an isolated administrative task, the communication infrastructure became something generated directly from the directory platform itself.

One immediate benefit was that class representatives no longer had to manually maintain long BCC lists every time they sent an email.

Around the same period, we also revisited the PTA website.

Historically, the organization had rented a server and maintained a WordPress installation. When we evaluated the actual communication needs of the PTA, however, the setup was clearly overengineered.

Fortunately, our communications team raised the issue as well, and we eventually migrated the site to Google Sites. This reduced both ongoing costs and operational complexity.

Once again, the primary decision criterion was not feature richness.

It was sustainability.

Could the next volunteer maintain it without specialized technical knowledge?

That question mattered far more than whether the platform supported advanced functionality.

To be honest, Google Sites lacks the flexibility of something like a static site hosted on Amazon S3. Content updates are largely performed through the graphical interface rather than code. However, when considering future maintenance, the tradeoff was worthwhile. Updating the site became something that even volunteers with no IT background could realistically manage.

As part of the website refresh, I also integrated Google Analytics and created a chatbot that automatically posted weekly traffic reports to Google Chat.

During the same period, I revisited another unique PTA process: cultural resource lending.

The PTA maintains a collection of several hundred educational and cultural materials that members can borrow. Previously, users had to download an Excel file, fill in the items they wanted to borrow, and then send the file via email. The process involved multiple steps and frequently resulted in incomplete submissions.

For reasons nobody could fully explain, requests were also being sent to a custom email address that only existed for this purpose.

The workflow was redesigned around a Google Form. Users now enter the required information once, and the system automatically generates and sends a loan request spreadsheet with the submitted information pre-filled.

The result was a simpler experience for users and a more consistent format for administrators receiving requests. By standardizing the process, we reduced procedural friction on both sides.

Another major change during this period was adopting local development with clasp.

When everything lives exclusively inside Google Apps Script, it becomes difficult to track what changed, when it changed, and why it changed. Moving the code into a Git repository allowed me to manage changes through commits and preserve the intent behind each modification.

That was the point where the project started to feel less like a collection of utility scripts and more like a maintainable software project.

In hindsight, I honestly have no idea how I managed to develop GAS applications for so long without clasp.

If you are building anything substantial with Google Apps Script, I strongly recommend using it from the beginning.

For anyone completely new to clasp, I previously wrote about my own setup struggles in the article:

Yoshi

Sep 7 '25

My Experience Fixing clasp Login Errors on Google Workspace

#gas #tutorial #security

4 min read

The Hardest Challenge: Building a Payment System

From late September through October 2025, I built the PTA membership fee payment platform.

Of all the projects completed during the year, this was the one that required the most thought about system design rather than simple automation.

The initial implementation was straightforward. Billing records were stored in BigQuery, while Google Apps Script managed the association between invoices and Stripe Checkout URLs. On paper, the design seemed reasonable.

In practice, it failed almost immediately.

The problem was that pre-generated checkout_url values and session_ids eventually expire. Parents would sometimes open billing emails days or weeks later, only to discover that the payment link was no longer valid.

That realization forced a major redesign.

Instead of embedding Stripe URLs directly in emails, I switched to a model where emails contained only PTA-issued signed links. When a user clicked the link, the server would generate a fresh Stripe Checkout Session on demand and immediately redirect the user to Stripe.

Payment completion was treated as a separate source of truth. Rather than trusting redirects or browser behavior, the system relied on Stripe Webhooks. When a payment event arrived, the webhook updated the corresponding payment_status and paid_at fields in BigQuery.

In addition, every generated session, webhook event, and outbound transaction log was stored in separate tables to create a complete audit trail.

The resulting architecture looked much closer to a real payment platform than the simple integration I had originally envisioned.

I documented the implementation details in a separate article:

Yoshi

Oct 19 '25

How I Automated Membership Fee Collection Using Stripe and Google Services

#stripe #googlecloud #beginners

5 min read

This was also the first major component where I intentionally moved away from GAS and adopted Cloud Run with Python.

The reason was simple: I had reached the practical limits of Google Apps Script.

Webhook signature verification, raw request body handling, secure secret management through Secret Manager, on-demand session generation, and idempotency controls are all technically possible with GAS if you work hard enough. However, once security, maintainability, and operational reliability become priorities, a server-side application is a much more natural fit.

One lesson from this project was learning where each technology belongs.

GAS is excellent for workflow automation inside Google Workspace.

Cloud Run is a better fit when security-sensitive integrations and external payment systems become the core of the application.

The payment system became an exercise in drawing that boundary clearly.

Around the payment platform, I also built supporting operational tools. These included email workflows for initial billing notifications, payment reminders, and escalation alerts for unpaid balances. I previously wrote about some of the challenges involved in those email processes in another article:

Yoshi

Nov 10 '25

Let's Stick to GmailApp Over MailApp in Google Apps Script

#gas #beginners #security #learning

6 min read

All outbound email activity was logged into BigQuery as well.

A payment system is not simply about whether someone can successfully pay. Just as important is being able to answer operational questions such as:

Who received which email?
When was it sent?
Which step of the process is currently blocked?
What evidence exists if someone claims they never received a notification?

Because of that, auditability became a first-class design requirement throughout the project.

Looking back, this was probably the point where the system stopped being a collection of productivity tools and started resembling an actual business application. The technical challenge was not collecting money. It was building a process that could be trusted, monitored, and explained when something inevitably went wrong.

Helping Other Departments Become More Efficient Through Technology

By November, most of the core directory and payment systems were in place. As the end of the school year approached, I shifted my attention toward building tools that supported day-to-day operational activities across the organization.

One of the first areas targeted was the library volunteer program.

Parents volunteer to help with book lending and returns, but each shift lasts four to five hours, making it a significant commitment. Every year, the PTA collected availability from a large number of parents and manually created schedules.

On the surface, it looked like a simple scheduling task.

In reality, the workload extended far beyond creating the initial schedule. Collecting preferences, assigning volunteers, handling cancellations, coordinating replacements, and communicating with the school all created a surprisingly complex operational process.

Initially, I thought about creating something similar to a SignUpGenius-style volunteer registration form. However, the requirements turned out to be much more complicated than simply allowing people to sign up for open dates.

Some slots needed to be assigned on a first-come, first-served basis. Others required lottery-based selection after the application deadline. The system also needed to handle absences, replacement requests, and notifications to school staff. In short, there were many organization-specific rules that had accumulated over the years.

As a result, beginning in February 2026, I developed a dedicated library volunteer platform using Google Apps Script. The system handled volunteer registration, assignment processing, replacement requests, and notification logging as part of a single workflow.

Part of the user manual is shown below. Unfortunately, the manual is available only in Japanese.

The platform was designed so that most day-to-day operations could be managed directly by the users themselves. Rather than relying on administrators to coordinate every step, participants could view their status, understand what actions were required, and manage their own involvement.

In that sense, it evolved beyond a simple administrative tool and became something closer to a self-service operational platform.

The system is already in production and currently supports the full process from volunteer recruitment through assignment and replacement management.

Around the same time, preparations began for selecting the following year's PTA officers.

Officer selection is far more complicated than it might initially appear. Eligibility and selection probabilities vary significantly based on bylaws, internal policies, prior service history, and numerous other conditions.

Historically, the selection process relied on externally generated spreadsheets, extensive manual filtering, email distribution, and follow-up phone calls to confirm responses. Every year, a considerable amount of effort went into producing and managing candidate lists.

I automated the entire workflow.

Candidate selection, email notifications, acceptance confirmations, and tracking were all integrated into a single process.

A task that previously consumed nearly a month of work, involving multiple volunteers and significant administrative overhead, could now be executed with essentially a single button press once the underlying rules were configured.

At the same time, I added a generalized email distribution platform.

For each project, administrators can prepare a data sheet containing the target audience. The system joins that data against the central family master using unique_id, then merges personalized information into email templates before sending messages.

This tool was only possible because several foundational pieces were already in place:

Each family could be uniquely identified through unique_id
Contact information had been centralized and maintained
Target populations could be generated from the directory system as structured datasets

Because those prerequisites already existed, the email distribution platform itself was surprisingly quick to build.

Looking back, that was perhaps the most satisfying aspect of the entire year.

Each improvement made the next improvement easier.

The directory platform enabled mailing list automation. The mailing list infrastructure enabled communication tools. The communication tools enabled volunteer management and officer selection. The centralized data model enabled personalized outreach.

In other words, every project became a building block for the next one.

Viewed from that perspective, the email distribution platform was not just another tool. It was the culmination of a year's worth of accumulated infrastructure, process improvements, and architectural decisions finally coming together.

The Technology Decisions Were Driven Not by Trendiness, but by What the PTA Could Sustain

One thing I found myself thinking about repeatedly throughout the year was not whether a technology was good, but whether it was a reasonable fit for the organization.

In a typical product development environment, priorities might center around scalability, separation of concerns, reusability, type safety, deployment strategies, and other architectural considerations. Within a PTA, however, different constraints mattered more. The system needed to be manageable by a small group of volunteers, affordable to operate, easy to hand over to future administrators, and compatible with the existing Google Workspace environment.

Those requirements naturally led to extensive use of Google Apps Script.

GAS integrates closely with Forms, Sheets, Gmail, Drive, and Google Chat, allowing entire workflows to be implemented with relatively little code. Personally, I often find it more approachable than AWS Lambda for this type of operational automation.

That said, GAS is not the right solution for everything.

Complex change detection, historical data management, and cross-system data analysis are much better handled by BigQuery. Likewise, systems involving payment processing, security boundaries, and webhook-driven integrations are generally more reliable when implemented on Cloud Run.

The key was understanding where each tool fit best.

At least in my experience, staying within the Google ecosystem wherever possible reduces both implementation effort and long-term maintenance costs. The tools work well together, and the learning curve remains manageable for future volunteers.

Cost management was another factor that could not be ignored.

The decision to migrate from WordPress to Google Sites came from the same line of thinking. Likewise, whenever a solution could be implemented using existing Google Workspace services, GAS, or BigQuery, I generally preferred to keep it there rather than introduce additional platforms.

For a PTA, the most important question is not whether something is cost-effective today.

It is whether the solution will still be practical and maintainable next year.

A system that saves time but introduces significant hosting costs, licensing fees, or maintenance overhead often fails to survive leadership transitions.

In that sense, technology selection was really a form of operational design.

Building Systems That Can Survive Leadership Turnover

The idea of handover was always in the back of my mind throughout this project.

In organizations where the same people maintain systems for many years, it is sometimes acceptable for knowledge to remain concentrated in a few individuals. As long as those people understand the system, operations continue.

PTAs do not work that way.

A completely different person may inherit responsibility a year later. In some cases, the handover process begins only a few months after a system is introduced.

Because of that, I tried to avoid creating solutions that required specialized administrative interfaces or extensive training.

Most of the systems were intentionally built around tools people already knew: Google Forms, Google Sheets, email, and Google Chat.

Rather than asking every new volunteer to learn a sophisticated custom application, I preferred to extend familiar tools and workflows whenever possible.

However, an even more important lesson emerged over time:

Before automating a process, ask whether the process should exist at all.

As these systems were introduced, many responsibilities within the PTA gradually disappeared. In several cases, entire positions became unnecessary because the work they were created to perform no longer existed.

One example was the class representative role.

A major responsibility of class representatives had been collecting PTA membership fees. Across all grades, from preschool through high school, this involved roughly eighty volunteers. Once fee collection became automated, the need for that responsibility disappeared. As a consequence, several associated administrative tasks—including Google account management responsibilities assigned to those positions—also disappeared.

Ironically, that meant the original "Account Administrator" role I had volunteered for effectively eliminated itself.

Looking ahead, the biggest challenge is ensuring that the system remains maintainable.

Even during the past year, the amount of time I spent writing entirely new code gradually decreased. More and more of the work shifted toward organizing requirements, making decisions, and coordinating improvements rather than implementation itself.

My current area of interest is exploring whether future maintenance can be handled differently.

When bug reports or feature requests arrive, perhaps humans only need to decide whether the request is worthwhile. The implementation details, troubleshooting, documentation updates, and even user support responses might eventually be delegated to AI agents.

Whether that vision proves practical remains to be seen.

But after spending a year automating administrative work, it seems only natural to start asking how much of the software maintenance process itself can also be automated.

Understanding Automata Theory Through Route Maps

Yoshi — Sun, 28 Dec 2025 04:35:23 +0000

Formal verification is a technique used to guarantee that software behaves correctly. In formal verification, the behavior of a system is modeled mathematically, and the specifications it must satisfy are expressed as logical formulas.

Rather than simply listing states, the goals are to:

Represent specifications as mathematical models
Mechanically determine whether those specifications are always satisfied or violated
Guarantee safety, including cases that are easy to miss with testing

Automata theory is used as a tool to achieve these goals.

In this field, many technical terms such as DFA, NFA, NBA, and LTL appear one after another and can be confusing. Abstract concepts like “state transitions,” “nondeterminism,” and “infinite executions” did not make much sense to me at first. However, my understanding improved by visualizing these ideas using a train route map as an analogy.

By using the familiar image of a route map, I believe it becomes easier to develop an intuitive understanding of how formal verification works.

Intended Audience

People who have just started learning automata theory or formal verification at university
People who feel “I kind of understand it, but it doesn’t really click” in classes on model checking or LTL
Engineers who are interested in system verification

Two Axes for Classifying Automata

When trying to understand automata, there are actually only two viewpoints you need to keep in mind.

Whether the behavior is deterministic or nondeterministic
Whether the execution is finite or infinite

By combining these two axes, automata can theoretically be classified into four types.
Abbreviations such as DFA, NFA, DBA, and NBA represent the differences in these combinations.

Deterministic × Finite
DFA (Deterministic Finite Automaton)
Nondeterministic × Finite
NFA (Nondeterministic Finite Automaton)
Deterministic × Infinite
DBA (Deterministic Büchi Automaton)
Nondeterministic × Infinite
NBA (Nondeterministic Büchi Automaton)

What determinism and nondeterminism mean, and what infinite execution refers to, will be explained later with concrete examples. In addition, DBA, which handles deterministic infinite executions, will be omitted in this explanation from the perspective of expressiveness and practical usefulness.

With that said, let us start by looking at the simplest and most intuitive case: DFA (deterministic, finite execution).

Start with the Simplest DFA: A Single-Line Route

What Is a DFA (Deterministic Finite Automaton)?

A DFA (Deterministic Finite Automaton) is the simplest type of automaton. In terms of a route map, it corresponds to a single-line route.

For example, consider the following route.

This route has the following characteristics.

Stations (states): Start, G0, Goal
Tracks (transitions): connections from each station to the next
Ticket (input): the action “take the Green Line”

The important point is that when you are at a given station, there is always exactly one next station you can move to. If you take the Green Line from the Start station, you will always arrive at station G1. There are no other choices.

Mathematical Definition

Formally, a DFA is defined as the following tuple.

A = (Q, q₀, Σ, δ, F)

Q: a set of states (the set of stations)
q₀: the initial state (the starting station)
Σ: a set of input symbols (types of routes)
δ: the transition function (which station you can go to from which station)
F: a set of accepting states (destination stations)

This mathematical definition can be visualized as a state transition diagram. In practice and in introductory textbooks, this diagram itself is often referred to as “the automaton.” Strictly speaking, an automaton is a single definition that combines the set of states, transitions, initial state, and acceptance condition. If there are multiple such definitions, they are called automata (the plural form).

Extension to NFA: A Multi-Track Route

What Is an NFA (Nondeterministic Finite Automaton)?

An NFA is an extension of a DFA. The “D” in DFA stands for “Deterministic,” which changes to “Nondeterministic” in an NFA. In other words, there can be multiple possible next states. In terms of a route map, this corresponds to a multi-track route.

For example, consider a case where there are multiple ways to go from the Start station to the Goal station.

In this route network, starting from the same Start station, the path can branch into multiple routes under the same condition. From G1, you can go directly to Goal via the Green Line, or you can go via B1 on the Blue Line. From B1 as well, there are multiple choices: going through G1 or going directly to Goal.

This is what “nondeterminism” means. Which route is taken is not determined at that moment. However, the key idea of an NFA is that it is acceptable as long as there exists at least one route that reaches the goal.

Extension to NBA: Circular Lines and Infinite Time

What Is an NBA (Nondeterministic Büchi Automaton)?

An NBA is a further extension of an NFA. In terms of a route map, it corresponds to a network that includes circular lines.

For example, consider the following circular route, the Circle Line.

The key feature of this route is that you can keep going around it indefinitely. The train can continue running forever.

An NBA deals with exactly this kind of infinite behavior. Programs and systems often “run forever once they are started.” To reason about such infinite executions, NBA is required.

Clearing Up a Common Misunderstanding: Time Is Infinite, States Are Finite

There is a very important point here.

What is infinite is the execution time; the number of states is finite.

The fictional Circle Line has only 8 stations (a finite number), but you can loop around it indefinitely (infinite time). In the same way, an NBA represents infinite behavior using a finite number of states.

As mentioned at the beginning, let us restate the key points for understanding automata.

Whether the behavior is deterministic or nondeterministic
Whether the execution is finite or infinite

When I first started learning automata, I mixed up points 1 and 2. As a result, I mistakenly thought that if the number of executions is infinite, then the behavior—that is, the outcomes of actions—must also have infinitely many patterns.

In reality, even if there are multiple possible transitions, the number of states themselves remains finite once those transitions are properly grouped.

In other words, what we are doing here is not “turning infinity into finiteness.”
More precisely,

we are making infinite-time behavior decidable using an automaton with a finite number of states.

Looking at a Concrete Example with a Traffic Light System

Let us now walk through everything discussed so far using a single concrete example.

System: Traffic Lights at an Intersection

A traffic light has three states.

🔴 Red
🟡 Yellow
🟢 Green

The state transitions are as follows.

[Green] --time passes--> [Yellow] --time passes--> [Red] --time passes--> [Green] --> ...

This forms a cycle and repeats infinitely.

Representation as a DFA

If we represent this traffic light as a DFA, the state at each moment is deterministically defined. If the current state is “Green,” the next state is always “Yellow.”

Representation as an NBA

Because the traffic light continues operating forever, it is natural to represent it as an NBA. The infinite loop “Green → Yellow → Red → Green → ...” can be expressed using just three states.

LTL Is a Language for Writing “Rules of Change”

What Is LTL (Linear Temporal Logic)?

So far, we have described how a system behaves. The next step is to check whether the system is behaving correctly.

For this purpose, LTL (Linear Temporal Logic) is used.

LTL is a logic for writing specifications about time. Using the route map analogy, it describes “operating rules”; for traffic lights, it describes “rules about how colors change,” written using symbols.

A Simple Explanation of LTL Operators

LTL uses the following operators to write specifications precisely. This avoids ambiguities that can arise in natural language.

□ = G (Globally): always
◇ = F (Finally): eventually
〇 = X (neXt): at the next moment
U (Until): until

Note that Globally and Finally are not primitive operators. They are derived from U and negation ¬.

G p ≡ ¬F ¬p
F p ≡ true U p

By combining these operators, complex temporal properties can be expressed.
Let us now look at concrete examples using the traffic light system.

Specification 1: “After red, it must always become green”

For a traffic light, this means: “Once red is on, green must turn on afterward.”

In LTL: G(Red → X Green) (green at the next moment)

Specification 2: “Green and red must not be on at the same time”

For a traffic light, this means: “There must be no state where red and green are on simultaneously.”

In LTL: G ¬(Green ∧ Red) (always not (green and red))

Specification 3: “Green appears infinitely often”

For a traffic light, this means: “No matter how much time passes, green should keep appearing repeatedly.”

In LTL: GF Green (always, eventually green)

From LTL to NBA

Why Is the Conversion Necessary?

LTL is a language that is easy for humans to write specifications in. However, for a computer to check them, a more mechanically manageable form is required.

That form is the NBA.

LTL Can Be Converted into an NBA

In fact, it has been mathematically proven that any specification written in LTL can always be converted into a corresponding NBA. This follows from the property that LTL belongs to the class of ω-regular languages. ω-regular languages are an extension of regular languages that can handle infinite-length strings (infinite sequences of symbols), but we will omit the details here.

For example, the specification “green appears infinitely often,” written as GF Green, can be converted into the following NBA.

This NBA checks at S1, starting from Start, whether the current symbol is “Green” or “not Green.” If it is Green, the automaton transitions to S2, accepts the occurrence of Green, and then transitions back to S1 in the next step. If it is not Green, it loops back to S1. As a result, this NBA accepts exactly those runs that pass through Green infinitely often.

Checking the System Against the Specification

So how do we actually check whether a system satisfies a given specification?

Overall Flow

Represent the actual system as a state transition diagram

Represent the traffic light system as an NBA: “Green → Yellow → Red → Green → ...”
Write the specification in LTL

Write “green appears infinitely often” as GF Green
Convert the specification into an NBA

Mechanically generate an NBA corresponding to GF Green
Take the product of the two NBAs

Combine the NBA of the actual system with the NBA of the specification
Check whether there exists a path that satisfies the condition

Search for an accepting infinite path in the product automaton

In terms of the traffic light example:

The actual behavior of the traffic light: Green → Yellow → Red → Green → ... (the system)
A “checking pattern” that represents the operational rule (the specification NBA)

By overlaying these two, we check whether there exists a behavior that does not violate the rule.

If a violating path is found, that path represents a “bug.” Formal verification tools (model checkers) present such a path as a counterexample.

Practical Value: Why Formal Verification Matters

Bug detection
- It enables exhaustive exploration of complex timing-dependent and rare-case bugs that are difficult to find with conventional testing.
Safety guarantees
- In systems that affect human lives, such as aircraft or medical devices, it is necessary to mathematically prove the absence of bugs. Formal verification provides a powerful means to do so.
Clarification of specifications
- The process of writing specifications in LTL helps clarify what the system is truly required to guarantee.

Summary

By using the route map analogy, the specialized terminology of automata theory becomes more intuitive. Although formal verification may appear difficult at first, its essence is simply to describe “how a system behaves” and “which rules it must satisfy” in mathematical terms, and then check them mechanically. Understanding that automata serve as the tools for this purpose makes the subject feel much more approachable.

References

Let's Stick to GmailApp Over MailApp in Google Apps Script

Yoshi — Mon, 10 Nov 2025 15:07:49 +0000

In Google Apps Script (GAS), there are two standard services for sending emails: MailApp and GmailApp. Both features are built-in and can be used immediately in your code without any library setup.

As mentioned in the title, when I initially used MailApp to implement an external email notification function for a report, emails to domains outside the organization resulted in bounces. After struggling with this and doing some research, I ultimately just switched to GmailApp, and the emails were sent smoothly.

This experience gave me a deeper understanding of MailApp, GmailApp, and the domain authentication mechanism known as DMARC. I'm leaving this as a memorandum for future reference.

MailApp or GmailApp: Which is Better?

The features and suggested use cases for the standard GAS email services are as follows.
As the title suggests, in practical terms, you should prioritize GmailApp if you can use it.

Item	MailApp	GmailApp
Sending Path	Sends anonymously using a shared Google server	Sends directly using the script-executing user's Gmail account
Benefits	- Basic email sending is possible - Narrow scope, requires minimal permission granting	- Reliable external sending is possible - Allows advanced sending like aliases (alternate addresses), HTML emails, and attachments
Drawbacks	- Prone to bouncing on external servers	- Requires granting a broad range of user permissions

MailApp is a simple communication method designed for the purpose of "just sending an email" with minimal permissions. Because of this, external servers with strict security checks tend to regard the sender as anonymous and are more likely to reject (bounce) the email.

GmailApp sends the email from the user's authenticated mailbox (Sent folder), which gives it higher sending reliability and reduces the risk of bounces. However, using it requires broad permissions to access the user's entire Gmail data.

My initial use of MailApp in this case was simply because "it doesn't show a huge authorization UI for granting Gmail access." I thought simplifying the user flow might reduce the need for user explanations or support inquiries.

You might consider using MailApp if the email recipients are all within the same domain (for a fully internal report) or if permission management is so strict that granting Gmail data access is absolutely not possible. Otherwise, using GmailApp is less likely to cause trouble.

Why Did the Emails Bounce?

As mentioned, emails sent via MailApp were rejected when sent externally, and this is due to a mechanism called DMARC (Domain-based Message Authentication, Reporting, and Conformance). Furthermore, DMARC acts like a supervisor that leverages the results of two authentication methods, SPF and DKIM, to determine the final processing (action).

Therefore, we must first understand SPF and DKIM, which are responsible for the actual authentication process.

Both are authentication technologies designed to prevent "spoofed emails," but they fundamentally differ in what they verify.

Authentication Technology	Verification Target	Mechanism Analogy
SPF (Sender Policy Framework)	Validity of the Sender's IP Address	Checks if the postal "sender address" is on the list of allowed sources for that address.
DKIM (DomainKeys Identified Mail)	Validity of the Email Content	Checks if the postal "envelope" has a digital signature applied by the domain (sender) and if it hasn't been tampered with during transit.

SPF (Sender Policy Framework): Verification of the Sender's IP Address

Sender's Setup: The administrator of the sending domain publishes a special text record called an SPF record on the DNS server.
SPF Record: This record specifies that "emails for this domain are only permitted to be sent from mail servers with IP addresses listed in this record."
Recipient's Verification: The receiving server checks the domain in the Envelope From (Return-Path) field in the email header and queries the DNS for that domain's SPF record.
Authentication: If the IP address of the server that actually sent the email is included in the allowed list of the SPF record, the result is Pass.

DKIM (DomainKeys Identified Mail): Verification by Digital Signature

Sender's Setup: The administrator of the sending domain registers a public key on the DNS server and stores a private key on the mail sending server.
Signature Application: The sending server uses the private key to encrypt part of the email header and body, and attaches that encrypted data (a digital signature) to the email header.
Recipient's Verification: The receiving server checks the signature in the header and queries the DNS for the public key associated with the domain used for the signature.
Authentication: The digital signature is decrypted with the public key. If it matches the original email content, the result is Pass. If it does not match (e.g., if the content was tampered with), the result is Fail.

DMARC and the SPF/DKIM Relationship

DMARC is the framework that utilizes the authentication results of SPF and DKIM to determine the final processing (action).

Alignment Check: DMARC's most critical function is to check if the domain used for SPF or DKIM authentication aligns with the domain in the From address that the user sees in their email client.
Policy Setting: The sending domain's administrator publishes a DMARC policy on the DNS, instructing the recipient on "how to handle emails that fail the SPF and DKIM alignment check." The policy primarily includes the following three options:

- p=none: Take no action and send a report to the sender.
- p=quarantine: Place the email in the spam folder (quarantine).
- p=reject: Refuse to accept the email (bounce it).

Reporting Feature: DMARC sends detailed reports (DSN reports) to the sending domain's administrator about failed authentications, including where the emails came from. This allows for a grasp of the spoofing situation and improvement of authentication settings.

In conclusion, SPF and DKIM are the individual authentication "tools," while DMARC acts as the "rulebook" that synthesizes the results from those tools to ultimately decide and control "whether to accept, discard, or how to process this email."

Tools Used for the Investigation

Here are the tools I utilized during this investigation.

Email Log Search
Accessed from the Admin console with Google Workspace Admin privileges.
Allows checking the delivery status of a selected email.

SPF Record Check Tool
Used mxtoolbox.com.
An easy way to check the publicly available DNS information for an SPF record.

DMARC Aggregate Report
Set up a custom DNS record with the value: "v=DMARC1; p=none; rua=mailto:admin@example.com".
When an email is bounced, a report detailing the rejection reason, similar to the one below, is sent to admin@example.com.

<feedback>
  <version>1.0</version>
  <report_metadata>
    <org_name>google.com</org_name>
    <email>noreply-dmarc-support@google.com</email>
    <extra_contact_info>[https://support.google.com/a/answer/2466580](https://support.google.com/a/answer/2466580)</extra_contact_info>
    <report_id>7457835210456705671</report_id>
    <date_range>
      <begin>1762387200</begin>
      <end>1762473599</end>
    </date_range>
  </report_metadata>

  <policy_published>
    <domain>sample.com</domain>
    <adkim>r</adkim>
    <aspf>r</aspf>
    <p>none</p>
    <sp>none</sp>
    <pct>100</pct>
    <np>none</np>
  </policy_published>

  <record>
    <row>
      <source_ip>209.85.222.44</source_ip>
      <count>1</count>
      <policy_evaluated>
        <disposition>none</disposition>
        <dkim>pass</dkim>
        <spf>pass</spf>
      </policy_evaluated>
    </row>

    <identifiers>
      <header_from>sample.com</header_from>
    </identifiers>

    <auth_results>
      <dkim>
        <domain>sample.com</domain>
        <result>pass</result>
        <selector>google</selector>
      </dkim>
      <spf>
        <domain>sample.com</domain>
        <result>pass</result>
      </spf>
    </auth_results>
  </record>
</feedback>

Conclusion

Although the initial use of MailApp was unintentional, as written at the beginning, this experience provided valuable knowledge about the best practice of using GmailApp and an understanding of email authentication technology. So, it was a good experience overall.

I hope to become a developer who can, in the future, intentionally propose MailApp as an option when the situation calls for it.

References

How I Automated Membership Fee Collection Using Stripe and Google Services

Yoshi — Sun, 19 Oct 2025 00:25:19 +0000

I’m in charge of the IT department for a volunteer organization with about 500 members. One day, I was shocked to learn that all the fee collection was being done manually. Even though it’s an annual membership system, the team was relying on manpower at the start of each year, and even mid-year registrations were handled by hand. A quaint, old-fashioned operation manual still existed. So, I decided to build a system that automates all collection, reminders, and aggregation.

The end users are the treasurers, who change every year and have varying levels of IT skills. Therefore, I made the interface as simple as possible. To issue invoices, they just need to enter the member ID into a spreadsheet. The system automatically generates a list of unpaid members, including their names, amounts, email addresses, and payment URLs.

In other words, the only touchpoint is the spreadsheet — everything else happens automatically.

System Architecture

Below is an overview diagram of the payment system architecture.

A trigger is issued from the invoice issuance list.
Through GAS, invoice data is stored in the BigQuery “invoice” table.
A timer sends emails containing invoice URLs and updates the unpaid list.
The user clicks the payment URL.
Cloud Run verifies the secret and issues the invoice via the Stripe API.
The user completes the payment on the Stripe invoice page.
Stripe sends a payment-completed webhook to the Cloud Run endpoint.
The system updates the payment status in both BigQuery and the spreadsheet.

System Components

Cloud Run (Checkout Service)

All sensitive information, such as Stripe secret keys, is managed through environment variables and securely accessed via Secret Manager.
Payment links are protected using HMAC signatures and time-limited URLs to prevent unauthorized access.
Additionally, hashed audit data (such as IP addresses and idempotency keys) are stored in BigQuery, enabling both re-execution prevention and traceability.

GAS Pipeline

The GAS operates from within a spreadsheet, reading environment settings (test/live) and terms directly from the sheet.
It embeds signed links into template emails and sends them, while all sent records are logged in the comms dataset.
When a payment deadline passes, webhook alerts are triggered and automatically recorded in the designated spreadsheet — effectively acting as a “watchdog for unpaid invoices.”

BigQuery

BigQuery functions as both an invoice ledger and a payment tracking system, generating invoice data from existing user records.
When a link is clicked, payment history is checked using the corresponding invoice_id.
With the MERGE statement, it performs “update if exists, insert if not”, ensuring that only one invoice record exists per user.
Separate datasets are used for each environment, allowing seamless switching without code modification.

Lessons Learned

Because of its sensitive nature, the payment system required special attention in several areas. The main points are summarized below.

HMAC Signatures and Time-Limited Links

Since payment links include user identification data, tampering could redirect a payment to another person.

To prevent this, Cloud Run adds an HMAC signature when generating each link.
HMAC (Hash-based Message Authentication Code) uses a shared secret to sign message content.
The invoice_id, user_id, and expiration timestamp are bundled and signed, then verified by Cloud Run.

If any part of the URL is modified, it becomes invalid immediately.
Naturally, the signing key is securely stored in Secret Manager.

Difference Between Stripe Session and Invoice

Initially, invoices were created on the Stripe side, and payment URLs were emailed to users.
However, these links expired after 24 hours — an unexpected limitation.
Further research revealed that Stripe provides two types of payment objects: Session and Invoice.

**Using Invoice
- Payment links never expire.
- Payment status updates automatically and is visible on the dashboard.
- Stripe can automatically send reminders to customers.
- However, external integrations and custom signing are limited.
**Using Session
- A lightweight, temporary payment page object (typically valid for 24 hours).
- Allows full developer control, including HMAC-signed links and BigQuery integration.

While Invoice offers strong management features, it leads to duplicate bookkeeping between Stripe’s customer DB and the internal ledger — and it also incurs extra fees.

Therefore, this project chose the Session-based approach for its flexibility and integration capability.
Stripe serves as the payment gateway, while BigQuery and Cloud Run handle billing management — a clear division of responsibilities.

Unique Payment Links Generated by Cloud Run

Each time a user opens the link, Cloud Run requests Stripe to create a new Session.
To prevent duplicate charges, it includes an idempotency key formatted as stripe_idempotency_prefix:invoice_id.

Stripe returns the same result for repeated requests with the same key, ensuring that the user is never charged more than once, even if the button is clicked multiple times.

BigQuery as the “Billing Ledger”

The invoice_id serves as the unique key in BigQuery for managing payment states.
Before completion, the status is “Unpaid.” Once the Stripe webhook sends a payment notification, the status updates to “Paid.”
Even if the process runs multiple times, only the existing record is updated — no duplicates occur.

Thanks to the use of the MERGE statement, the ledger always reflects the most accurate and up-to-date state.

Link Reissue and Expiration

Since Stripe Sessions cannot be extended, Cloud Run regenerates a new Session using the same billing information.
Technically, it’s a “recreation,” but in practice, it works as a “reissued payment link” for the same invoice.

This approach ensures that even expired links always lead users to a valid, active payment flow.

Cloud Run Webhook Reception and Automation

Cloud Run also serves as the webhook endpoint for Stripe.

When Stripe sends a payment completion event, Cloud Run receives it and updates BigQuery using the invoice_id and family_id included in the metadata.
As a result, the Google Workspace side can monitor payment status in real time.
Unpaid lists, reminder emails, and even chat notifications are fully automated — the tedious task of manually checking “who hasn’t paid yet” is now a thing of the past.

Test Environment

Stripe provides a sandbox environment for testing, allowing separate api_key and webhook_secret for production and testing.

The GAS side is designed to switch environments easily.

By toggling a single script property (live/test), the environment can be changed instantly.
BigQuery is divided into two datasets — finance and finance_test — with identical schemas for safe testing.
Cloud Run uses the same container image, switching only the keys via Secret Manager.
This design enables environment switching without any code modification.

Performance Verification

Since this system operates in a fully serverless Cloud Run environment, cold-start latency may occur on the first access.
Therefore, an actual load test was conducted using a Python (aiohttp) script under the following conditions:

Spike test: 50 concurrent requests
Distributed test: 30 requests over 20 seconds (with jitter)

All requests — including those under cold-start conditions — were successfully processed, with no timeouts or 5xx errors.
Although Cloud Run’s free tier charges for requests triggered from a cold start, the expected scale of about 500 members poses no operational issues in practice.

Conclusion

The system operates in a three-layer structure:

BigQuery serves as the ledger, Cloud Run handles control and integration, and Stripe acts as the payment processor.
The invoice_id ensures uniqueness on the ledger side, while the idempotency_key guarantees uniqueness on the Stripe side.
Additionally, HMAC-signed links secure every transaction.

From unpaid checks to session creation and webhook synchronization, the entire flow runs automatically, ensuring accurate and safe one-time payments, even if the same link is opened multiple times.

Many parts of this implementation were built with the help of generative AI, and the experience of working with payment architecture — including the differences between Stripe Session and Invoice and API idempotency — has been highly valuable.

References

Exploring the World of Packets with Scapy: A Beginner’s Hands-on Journey to Understanding

Yoshi — Wed, 24 Sep 2025 03:10:47 +0000

Playing with Packets

I had a conceptual understanding of how each layer of the network works, but the actual picture was still vague, and my understanding wasn’t progressing much. That’s when I came across a tool called Scapy. With Scapy, you can create packets, observe them, and trace their routes... or so I heard. But I wasn’t sure what I could really learn by observing packets, so I decided to try it myself.

I installed Scapy in a Python virtual environment and experimented with ping → capture → traceroute → ARP scan.

Here, I’ll share the questions I had and the discoveries that made me say, “Oh, I see!”

What is Scapy?

Scapy is a packet manipulation library available in Python.

With it, you can:

Create packets
Send packets
Capture (read) packets
Analyze packets

It’s a very handy tool.

Commonly used classes

IP() : IP header
TCP() : TCP header
UDP() : UDP header
rdpcap() : read pcap files
wrpcap() : write pcap files

Note

If you want to view packets with a GUI, there’s a tool called Wireshark. Both Scapy and Wireshark can read and write packet capture files (pcap).

Wireshark:

GUI-based operations
Decodes and displays packets in a human-readable format
Great for analysis with clicks and filters

Scapy:

Python library
Can create, analyze, and send packets programmatically
Strong for automation and experiments

I once tried looking at packets with Wireshark, but back then I didn’t understand much and gave up—a bitter memory.

1. What is a Packet?

A packet, as the name suggests, is like a small parcel on the network—a small fragment of communication. Large data is divided into small packets and sent across the network.

Here’s a quick recap of the OSI reference model (L2, L3, L4):

Ethernet (L2)
- Address label of a delivery service inside a LAN (MAC address)
- Decides which device in the same network to deliver to
- The MAC gets rewritten when passing through a router
IP (L3)
- Address of the Internet world (IP address)
- Like deciding the destination country or city
- Reaches faraway places through routing
TCP (L4)
- Rules for reliable communication
- Establishes a connection (3-way handshake)
- Numbers data (sequence numbers)
- Confirms delivery (ACK)
- Retransmits if something is missing

Ethernet is LAN-local and can only reach up to the next router.

But inside it, the “parcel label” (IP address) always has the same src/dst info, so the packet can keep being forwarded router by router until it reaches the final destination.

Scapy’s coverage looks like this:

L2 (Data Link Layer)
- Create Ethernet frames
- Specify MAC addresses
L3 (Network Layer)
- Generate IP packets
- Edit IP headers
L4 (Transport Layer)
- Work with TCP/UDP/ICMP headers
- Specify port numbers
L5 (Session Layer equivalent)
- Can sometimes create protocols closer to the application layer
- But not clearly defined as full L5 support

Ethernet (L2), IP (L3), and TCP/UDP/ICMP (L4) are standardized by IETF/IEEE with clearly defined header formats and fields. Scapy can build and parse packets according to those standards.

On the other hand, the session, presentation, and application layers are often application-specific and diverse, even if partially standardized. Scapy supports some of these, but it’s not its main strength. (Honestly, I haven’t fully explored how far it can go yet.)

As explained above, a single packet often shows only a small fragment of communication. Still, even looking at just one packet revealed some surprisingly interesting findings.

summary()

Let’s try capturing packets with Scapy by running test_summary.py.

from scapy.all import sniff

# Capture only 10 packets
packets = sniff(count=10)

# Display a simple summary
packets.summary()

sniff() reads raw packets directly, so on Linux it requires root privileges.

For this test, I gave permission using sudo.

sudo /home/user/sandbox/network/venv/bin/python /home/user/sandbox/network/test_summary.py

Ether / IP / TCP 192.0.2.25:46924 > 203.0.113.4:https S
Ether / IP / TCP 203.0.113.4:https > 192.0.2.25:46924 SA
Ether / IP / TCP 192.0.2.25:46924 > 203.0.113.4:https A
Ether / IP / TCP 192.0.2.25:46924 > 203.0.113.4:https PA / Raw
Ether / IP / TCP 203.0.113.4:https > 192.0.2.25:46924 A / Raw
Ether / IP / TCP 192.0.2.25:46924 > 203.0.113.4:https A
Ether / IP / TCP 192.0.2.25:46924 > 203.0.113.4:https PA / Raw
Ether / IP / TCP 203.0.113.4:https > 192.0.2.25:46924 PA / Raw
Ether / IP / TCP 203.0.113.4:https > 192.0.2.25:46924 PA / Raw
Ether / IP / TCP 192.0.2.25:46924 > 203.0.113.4:https A

We were able to confirm a summary of the first 10 packets.

This list shows the flow from the start of the connection (handshake) to the actual data exchange in a concise way.

S = SYN
SA = SYN+ACK
A = ACK
P = PSH
Raw = payload present

The sequence is: S → SA → A (3-way handshake) → then data (PA / Raw, etc.)

192.0.2.25:46924 > 203.0.113.4:https S

Client → Server: SYN
203.0.113.4:https > 192.0.2.25:46924 SA

Server → Client: SYN+ACK
192.0.2.25:46924 > 203.0.113.4:https A

Client → Server: ACK

→ This completes the 3-way handshake

After that, PA / Raw indicates data transfer (Push + ACK + payload).

A packet with only A is just an ACK response.

The 3-way handshake described in textbooks was clearly confirmed here through the capture.

show()

Let’s take a closer look at the first packet we captured earlier.

packets[0].show()

###[ Ethernet ]###
  dst       = 00:11:22:33:44:55
  src       = 66:77:88:99:aa:bb
  type      = IPv4
###[ IP ]###
     version   = 4
     ihl       = 5
     tos       = 0x0
     len       = 60
     id        = 65307
     flags     = DF
     frag      = 0
     ttl       = 64
     proto     = tcp
     chksum    = 0x6ffc
     src       = 192.0.2.25
     dst       = 203.0.113.4
     \options   \
###[ TCP ]###
        sport     = 46924
        dport     = https
        seq       = 2126599486
        ack       = 0
        dataofs   = 10
        reserved  = 0
        flags     = S
        window    = 64860
        chksum    = 0xcbd2
        urgptr    = 0
        options   = [('MSS', 1380), ('SAckOK', b''), ('Timestamp', (3644677925, 0)), ('NOP', None), ('WScale', 7)]

This is the first SYN packet of the TCP 3-way handshake.

Next, the server replies with SYN+ACK, and then the client sends back ACK to complete the connection.

Ether: src/dst are the sender/receiver MAC addresses
IP: src/dst are the sender/receiver IP addresses, ttl is time-to-live, DF means no fragmentation
TCP:
- sport/dport: source/destination ports (https = 443)
- seq: initial sequence number of this packet
- ack: 0 because this is SYN
- flags: S (SYN)
- window: receive window size
- options: MSS (maximum segment size), SAckOK (SACK allowed), Timestamp, WScale (window scaling)

※ 192.0.2.25:46924 → 203.0.113.4:443 represents client → server communication.

Meaning of TCP fields:

flags = S → SYN flag is set. The first “Can I connect?” signal.
seq = 2126599486 → Initial sequence number chosen by the client. The server uses this as the basis for communication.
ack = 0 → No acknowledgment expected yet.
sport = 46924 / dport = https(443) → Client chose random source port 46924 → server’s HTTPS port 443.

In short: Ethernet delivers within the LAN, IP delivers across the Internet, and TCP ensures reliability.

All of these are stacked together inside a single packet.

2. Sending a Ping with Scapy

Sometimes you may wonder, “Is the network connection really working?”

In such cases, the familiar Ping comes in handy.

Let’s try doing a Ping using Scapy.

from scapy.all import IP, ICMP, sr1

# 1. Create a packet
pkt = IP(dst="8.8.8.8")/ICMP()

# 2. Check the contents of the packet
print("=== Sent Packet ===")
pkt.show()

# 3. Send it and wait for a reply
ans = sr1(pkt, timeout=2)

# 4. Check the reply packet
if ans:
    print("\n=== Reply Packet ===")
    ans.show()
else:
    print("No reply")

Output:

=== Sent Packet ===
###[ IP ]###
  version   = 4
  ihl       = None
  tos       = 0x0
  len       = None
  id        = 1
  flags     = 
  frag      = 0
  ttl       = 64
  proto     = icmp
  chksum    = None
  src       = 192.0.2.25
  dst       = 8.8.8.8
  \options   \
###[ ICMP ]###
     type      = echo-request
     code      = 0
     chksum    = None
     id        = 0x0
     seq       = 0x0
     unused    = b''

Begin emission
.
Finished sending 1 packets
*
Received 2 packets, got 1 answers, remaining 0 packets

=== Reply Packet ===
###[ IP ]###
  version   = 4
  ihl       = 5
  tos       = 0x0
  len       = 28
  id        = 0
  flags     = 
  frag      = 0
  ttl       = 114
  proto     = icmp
  chksum    = 0x6984
  src       = 8.8.8.8
  dst       = 192.0.2.25
  \options   \
###[ ICMP ]###
     type      = echo-reply
     code      = 0
     chksum    = 0xffff
     id        = 0x0
     seq       = 0x0
     unused    = b''

Explanation

Sent Packet (echo-request)

IP Header:
- src = 192.0.2.25 (my local PC)
- dst = 8.8.8.8 (Google DNS)
- proto = icmp
ICMP Header:
- type = echo-request (Ping “please respond”)
- id / seq = 0x0 (identification number)

Reply Packet (echo-reply)

IP Header:
- src = 8.8.8.8 (response from the other side)
- dst = 192.0.2.25 (sent back to my PC)
- ttl = 114 (went through several routers)
ICMP Header:
- type = echo-reply (Ping “reply”)
- id / seq = 0x0 (same as the request → proof of proper matching)

In the code, the / operator means “stack layers”. Think of wrapping Ethernet/IP/TCP layers together.

Sending an echo-request and receiving an echo-reply felt impressive.

Also, the reply packet showed ttl = 114. I had read about TTL in theory, but seeing that number in practice made me wonder—why exactly 114?

What is TTL?

Time To Live (TTL)

A counter that decreases by 1 every time an IP packet passes through a router
When it reaches 0, the packet is discarded (to prevent infinite loops)

In this case, ttl = 114:

Google’s server (8.8.8.8) likely sent the packet with an initial value of 128
When it reached my PC, it was 114
→ 128 - 114 = passed through 14 routers (hops)

Important Notes

TTL itself is not the number of routers
Formula: Initial value − Received TTL = Number of routers passed
Typical defaults: Windows = 128, Linux/Unix = 64, Cisco = 255

This way, TTL helps us estimate the number of hops a packet has traveled.

3. Tracing the Route with Traceroute

Next, let’s try traceroute (which Scapy can also do) to see the actual path of the packets.

Scapy provides a built-in traceroute() function, so I tested it with Google DNS and Yahoo Japan.

from scapy.all import traceroute
import socket

# Destination
target = "8.8.8.8"

# Try only once (retry=0)
ans, unans = traceroute([target], maxttl=30, dport=80, retry=0, verbose=0)

print(f"=== Traceroute to {target} ===")
for snd, rcv in ans:
    hop = snd.ttl
    ip = rcv.src
    try:
        host = socket.gethostbyaddr(ip)[0]
    except socket.herror:
        host = "?"
    print(f"{hop:2d}  {ip:15s}  {host}")

Output:

=== Traceroute to 8.8.8.8 ===
 1  172.19.32.1      LAPTOP-XXXX.EXAMPLE.net
 2  10.0.0.1         ?
 4  96.xxx.xxx.xxx  router1.isp.example.net
 5  68.xxx.xxx.xxx  router2.isp.example.net
 6  96.xxx.xxx.xxx  router3.isp.example.net
 8  96.xxx.xxx.xxx  router4.isp.example.net

1: 172.19.32.1 → Local LAN gateway (the first router)
2: 10.0.0.1 → Provider-side private IP router (NAT or CMTS, etc.)
4, 5, 6, 8 → Intermediate ISP routers. Addresses like 96.x.x.x and 68.x.x.x show up (likely Comcast).
(3, 7) → Routers that didn’t respond, which is common in traceroute.
8.8.8.8 → Final destination, Google Public DNS

Since the last hop shows 8.8.8.8, we can confirm that the route successfully reached Google DNS.

This route represents:

My PC → Local gateway → ISP routers → Internet backbone → Google DNS.

Traceroute works by gradually increasing TTL one by one and collecting the ICMP Time Exceeded messages from each router along the path.

Traceroute to Yahoo Japan

Earlier, the path stayed entirely within the U.S.

But if the destination is set to www.yahoo.co.jp, you can see the route stretching from the U.S. all the way to Japan.

from scapy.all import traceroute
import socket

# Change destination to Yahoo Japan
target = "www.yahoo.co.jp"

# Try only once (retry=0)
ans, unans = traceroute([target], maxttl=30, dport=80, retry=0, verbose=0)

print(f"=== Traceroute to {target} ===")
# Sort by TTL for readability
for snd, rcv in sorted(ans, key=lambda x: x[0].ttl):
    hop = snd.ttl
    ip = rcv.src
    try:
        host = socket.gethostbyaddr(ip)[0]
    except socket.herror:
        host = "?"
    print(f"{hop:2d}  {ip:15s}  {host}")

Output:

 1  172.19.32.1      LAPTOP-EXAMPLE.mshome.net
 2  10.0.0.1         ?
 4  198.51.100.1     router1.isp.example.net
 5  198.51.100.2     router2.isp.example.net
 6  198.51.100.3     router3.isp.example.net
 7  198.51.100.4     router4.isp.example.net
 8  198.51.100.5     router5.isp.example.net
 9  198.51.100.6     router6.isp.example.net
10  198.51.100.7     router7.isp.example.net
11  198.51.100.8     router8.isp.example.net
12  198.51.100.9     router9.isp.example.net
13  198.51.100.10    router10.isp.example.net
14  198.51.100.11    router11.isp.example.net
15  198.51.100.12    router12.isp.example.net
16  198.51.100.13    router13.isp.example.net
17  198.51.100.14    router14.isp.example.net
18  198.51.100.15    router15.isp.example.net
19  198.51.100.16    router16.isp.example.net
20  198.51.100.17    router17.isp.example.net
21  198.51.100.18    router18.isp.example.net
22  198.51.100.19    router19.isp.example.net
23  198.51.100.20    router20.isp.example.net
26 106.187.13.25     ?
27 27.86.xx.xxx      ?
28 27.86.xx.xxx      ?
36 182.22.xx.xxx     ? 
37 182.22.xx.xxx     ?
38 182.22.xx.xxx     ?

(Same IPs continue below…)
(xxx indicates masked digits)

Path Analysis

Up to 23: Inside the ISP. (In the actual capture it went Chicago → Denver → San Jose → West Coast.)
26: 106.187.13.25, 27–28: 27.86.*: Very likely where it enters Japan (carrier/IX).
From 36 onward: 182.22.xx.xxx: The same device is replying (effects of multiple probes or ICMP limits). Reachability itself was achieved earlier.

whois 182.22.xx.xxx shows:

inetnum:      182.22.0.0 - 182.22.127.255
netname:      YAHOO
descr:        LY Corporation
descr:        1-3 Kioicho, Chiyoda-ku, Tokyo, Japan
country:      JP

The 182.22.xx.xxx addresses that repeatedly appeared at the end of the traceroute belong to the range 182.22.0.0 - 182.22.127.255, which is registered to LY Corporation (formerly Yahoo Japan).

In other words, we can confirm the path reached:

USA → Comcast backbone → submarine cable → Japan (LY network).

4. Code to check ports on your own PC

Note: Scanning ports on external sites can violate terms of service or be considered an attack. It’s safer to run these tests against your own server or a local environment.

from scapy.all import IP, TCP, sr

ip = "127.0.0.1"  # My own PC (localhost)
ports = [22, 53, 80, 443, 3306]  # Ports to test

# Send SYN packets and wait for replies
ans, unans = sr(IP(dst=ip)/TCP(dport=ports, flags="S"), timeout=2, verbose=0)

# Analyze responses
for s, r in ans:
    if r.haslayer(TCP):
        if r[TCP].flags == "SA":
            print(f"Port {s[TCP].dport} is OPEN")
        elif r[TCP].flags == "RA":
            print(f"Port {s[TCP].dport} is CLOSED")

Decision Rules

Reply with SYN+ACK → Port is OPEN
Reply with RST → Port is CLOSED
No response → Blocked by firewall

On my PC, all tested ports were closed.

But after starting a server with python -m http.server 8080 and rescanning, port 8080 showed as OPEN, which made perfect sense.

5. ARP Scan for LAN Discovery

ARP (Address Resolution Protocol) is the mechanism for asking within a LAN:

“Which MAC address has this IP address?”

It works at Layer 2 (Data Link Layer).

To figure out the range of the home LAN (subnet), first check your PC’s current IP address.

In my case (using WSL), running ip addr showed:

eth0 → 172.19.35.58/20.

So the network range is 172.19.32.0/20.

from scapy.all import ARP, Ether, srp
target = "172.19.32.0/20"https://zenn.dev/dashboard
pkt = Ether(dst="ff:ff:ff:ff:ff:ff")/ARP(pdst=target)
ans, _ = srp(pkt, timeout=2, verbose=0)
for _, r in ans:
    print(f"IP: {r.psrc}, MAC: {r.hwsrc}")

=== Devices in LAN === IP: 172.19.32.1, MAC: 00:15:5d:4a:02:13

Only one device showed up. “Huh? There should be more devices on my LAN…”

The reason is WSL: from WSL you can only see the virtual gateway.

WSL’s eth0 (172.19.35.58/20) is actually connected to a Hyper-V virtual switch.

So, from inside WSL it looks like there’s only one peer on the same network—the gateway (172.19.32.1).

WSL’s eth0 is a virtual NIC created by Windows. Devices on the real network beyond it cannot be ARP-scanned from WSL.

On a real Linux/Windows host, arp -a should show the other devices.

Summary

By extracting packets with Scapy, the network became more concrete.

Seeing raw data helped the model click for me: Ethernet = local addressing, IP = global addressing, TCP = reliability rules—each layer with its role.

References

My Experience Fixing clasp Login Errors on Google Workspace

Yoshi — Sun, 07 Sep 2025 20:09:52 +0000

Introduction

When I tried to develop using the Google Apps Script CLI tool clasp, I got stuck on authentication.

The conclusion is that security requirements differ between personal accounts and Google Workspace accounts.

Since I had a successful experience with a personal account, I couldn’t understand why errors appeared.

Even when I asked ChatGPT, most of the answers assumed a personal account, which made troubleshooting difficult.

So here, I’ll organize the steps required for Workspace accounts as both a reminder for myself and a reference for others.

The Mysterious Error on Workspace Accounts

For personal accounts, the first clasp login opens a browser and shows the Google authentication screen.

Once approved, the setup is complete, and from the second time on, authentication works instantly using cache.

Personal account (@gmail.com)

clasp login
# → Authentication completes immediately!

But with a Google Workspace account, it doesn’t work like that.

clasp login
# → "We are sorry, but you do not have access to this service"

Different Authentication Mechanisms for Personal vs Workspace

Personal Account (simple)

Uses Google’s default credentials
Few restrictions, no admin approval required
Just clasp login works

Workspace Account (complex)

External apps are blocked by security policy
Explicit administrator approval is required
Custom OAuth credentials must be created

5 Steps

To resolve the login issue with a Google Workspace account, the following five steps were necessary:

Allow API access in the Admin Console (organization-level approval)
Enable the Apps Script API in Google Cloud Console (project-level activation)
Configure the OAuth consent screen (explicit user approval)
Create OAuth credentials (application identity)
Authenticate using custom credentials (organization-specific login)

For personal accounts, steps 1–3 are automatically handled by Google, and step 4 passes with default credentials.

1. Allow Apps Script API in the Admin Console

Explanation

Grants organization-level permission to use Apps Script
Without this, organizational policy blocks access
Analogy: like unlocking the company’s front door

Steps

Log in to the Admin Console
Go to Security → API Controls → App Access Control
Either set clasp to Unrestricted, or set to Restricted and add clasp as a Trusted app

2. Enable Apps Script API in Google Cloud Console

Explanation

The “Apps Script API” must be enabled, otherwise authentication will not proceed
By default, it may be turned off
Analogy: like switching on the building’s access system before issuing employee badges

Steps

Log in to the Google Cloud Console
Go to APIs & Services → Library
Search for Google Apps Script API
Click Enable

3. Configure the OAuth Consent Screen

Explanation

This is the confirmation screen that asks: “clasp wants to access your Apps Script projects. Do you allow it?”
Clearly shows what data will be accessed
Analogy: like a receptionist asking visitors about their purpose before granting entry

Steps

In Google Cloud Console, go to OAuth consent screen
Enter app information (name, support email)

Set User Type to Internal (only users in your Workspace domain can use it)

Add your own account as a test user

4. Create OAuth 2.0 Credentials

Explanation

Allows clasp to identify itself with a Client ID and Client Secret
Analogy: like issuing an official employee ID badge

Steps

Log in to the Google Cloud Console
Go to APIs & Services → Credentials

Create an OAuth Client ID with type Desktop application
Obtain the Client ID and Client Secret

5. Authentication with Custom Credentials

Explanation

Uses customized credentials for the organization instead of default, generic authentication.
Accesses resources in a manner that complies with the organization's security policy.
Example: Similar to using a dedicated employee ID card to enter a building instead of a generic visitor's badge.

Procedure

Log out from the existing authentication.

bash clasp logout
Log in using the downloaded credentials.

bash clasp login --creds ./credentials.json

Important Points

Both Configurations Required: Configuration is needed in both the Cloud Console and the Admin Console.
Time for Settings to Take Effect: It may take anywhere from a few minutes to several hours for the settings to be reflected.
Desktop application is Key: Web application is not sufficient.
Internal User Type: The Internal user type is sufficient (for use only within the organization).

Conclusion

In my case, since I had administrator rights for the organization, I was able to resolve the issue relatively smoothly.

However, if I had been a regular user, asking the admin to investigate and resolve the cause would have been much more troublesome.

Complex configuration is unavoidable in order to meet organizational security requirements.

But once you understand the background, the steps make sense.

I hope this article will serve as a helpful reference for anyone facing the same issue.

Additional Notes

If you can successfully log in or clone using Clasp but push operations fail, it means the API isn't enabled for the Google account's App Script service settings. Please change the API usage to On via the link below.

https://script.google.com/home/usersettings

References

Thinking About Why We Build Dashboards with GAS📊

Yoshi — Fri, 15 Aug 2025 15:24:18 +0000

For one of my projects, I was asked to submit a portfolio sample, so I built a dashboard using Google Apps Script (GAS).

Here’s what it looks like:

If you’re curious, you can check out the live version here:

I have experience building dashboards with BI tools and lightweight web apps using Python + Flask, but this was my first time building one with GAS.

Since this is a relatively niche use case, I thought I’d share my findings.

When Does It Make Sense to Build a Dashboard with GAS?

Google Sheets already offers charting capabilities, and BI tools can make dashboard creation quite straightforward.

Still, there are situations where using GAS makes sense, such as:

Adding functionality not available in standard features
Creating interactive UI components
Integrating with other Google services
Managing the dashboard in code form
Keeping costs low
Fetching data from external services

In other words: a fully automated, customizable dashboard that updates itself, without the licensing cost of BI tools.

Spreadsheets and BI tools trade off flexibility for ease of use, and dashboard projects often balance customization against development/maintenance costs.

Here’s my personal “feature matrix” comparing some common options:

Item	Google Sheets	BI Tools	GAS	Scratch Development
Development Barrier	Excellent	Good	Fair	Poor
Customizability	Poor	Fair	Good	Excellent
Data Volume Handling	Fair	Good	Poor	Excellent
Development Speed	Excellent	Good	Fair	Poor
Additional Cost	Excellent	Fair	Excellent	Poor
Maintenance Load	Poor	Fair	Good	Good

For small organizations such as startups or SMEs, where the dataset isn’t huge, there’s no budget for BI tools or custom apps, and spreadsheet-based manual management is also impractical, GAS dashboards can be a viable middle ground.

Dashboard Design Choices

For this project, I built the dashboard as a web app and used Plotly for rendering.

Why Plotly? I was already comfortable with it from data analysis projects, and since this was a sample, I wanted to highlight interactivity and responsiveness.

That said, Plotly isn’t the most common choice for GAS dashboards.

If you’re building a web app dashboard with GAS, the more typical approach is Google Charts Service (Charts API).

It’s built-in, renders on the server side (reducing browser load), requires no extra libraries, and is straightforward to use with methods like Charts.newAreaChart() or Charts.newLineChart().

Plotly, by contrast, is loaded via CDN through the HTML Service and rendered in the browser.

It offers rich features—hover tooltips, animations, responsive layouts—out of the box, but it requires HTML/CSS/JavaScript knowledge and more complex setup.

Heavy datasets can cause browser freezes.

Also, remember GAS’s limitations: a maximum execution time of 6 minutes and limited memory.

With datasets in the 100k+ range, it’s more practical to preprocess with BigQuery or switch to a BI tool.

In my case, even with ~2,000 rows, I noticed a few seconds of lag during aggregation. It’s borderline acceptable but not ideal UX. For a sample, I decided that was fine.

About the Sample Data

At first, I looked for government datasets, but I needed something that wasn’t too large yet still rich enough for graphing. Overly simple datasets make poor demos, and I didn’t want to spend too much time on data preparation.

I ended up using Kaggle’s Retail Fashion Boutique Data Sales Analytics 2025.

Kaggle datasets are well-documented, tagged, and even provide quick visual previews—very convenient.

However, as is often the case with competition datasets, the data can be biased; here, many records were clustered in a specific month. A quick exploratory check beforehand is always a good idea.

Do You Even Need a Dashboard?

This might sound counterintuitive, but before building a dashboard, you should first ask:

Who will use it?
What information will lead to actionable insights?
Why did “dashboard” become the chosen solution?

Without answering these, you risk creating something no one uses but still requires ongoing maintenance.

In many cases, a dashboard just centralizes metrics that are already calculated elsewhere.

If the goal is simply to reduce reporting workload, a plain text summary could be cheaper and faster.

And when you aggregate metrics, they often become more abstract, meaning you still have to drill down into the raw data.

That’s why I prefer to see dashboards as portal sites to distributed datasets, rather than as the final stop for analysis.

That said, dashboards can feel fresh and engaging for users, and they can automate repetitive reporting tasks.

The key for developers is to maintain a critical eye: is this really the best solution for the problem at hand?

References

How Our Google Drive Practices Were Walking a Dangerous Line

Yoshi — Wed, 11 Jun 2025 00:49:36 +0000

Hey everyone, are you using Google Drive?

It’s one of the core services in Google Workspace, right alongside Gmail and Google Calendar.

Google Workspace is built for everyone—from IT pros to everyday users—so the user interface (UI) is designed to be intuitive and easy to use. I’ve personally been using Google Drive for over ten years without anyone ever teaching me how.

But when I took on the role of a Google Workspace Admin, I realized something troubling:

The very features that make Google Drive "easy to use" were also the source of a lot of potential issues.

Recently, I had a chance to investigate and organize our team's use of Google Drive.

This article shares what I learned—especially for non-IT folks—about what can easily go wrong and why.

So, What Is Google Workspace Anyway?

Chances are, you already have a personal Google account.

Once you have one, you get access to many powerful tools: Gmail, Google Calendar, Google Maps, Google Docs, Google Meet—you name it.

According to Gemini, as of May 2025, Gmail alone has over 1.8 billion active users.

Google Workspace takes these tools and packages them for business use.

It allows organizations to manage things centrally—like using custom domains, setting up user accounts, and controlling security policies.

In short, it’s like renting a fully-equipped virtual office.

If you compare it to a physical office, everything starts to make more sense.

Tool	Function	In a Physical Office
Custom Domain	Set up emails like user@yourdomain.com	Company nameplate or office signage
Gmail	Send and receive emails	Your desk mailbox (internal and external messages)
Drive	Store and share files	Filing cabinet for documents
Meet	Online meetings	Conference room or phone meeting
Calendar	Schedule events and meetings	Room booking board or whiteboard planner
Groups	Mailing lists	Department contact list or circular board
Docs	Create documents	Meeting notes on a whiteboard or shared notebook
Chat	Team text communication	Hallway chats, memos, or sticky notes
User Management	Create/delete accounts, assign permissions	Staff roster or building access management
Admin Console	Policy settings, security, audit logs	Security system and master key control panel

With Google Workspace, your organization gets all essential office functions—bundled in one place.

It’s super convenient and designed to make management smoother.

So, Where Do You Store Your Files?

Now, let’s focus on today’s topic: Google Drive.

Think of it as your digital filing cabinet. When you have lots of documents, you need a good place to organize them.

Here’s the catch: there are actually two kinds of cabinets—one for individuals and one for the organization.

The personal one is called My Drive, and the shared one is called Shared Drives.

If you’re using a regular Google account (not a Workspace account), you won’t see Shared Drives.

It’s a feature unique to Google Workspace.

To use a real office analogy:

Shared Drives are like open shelves in a common room that everyone can access.
My Drive is like the drawer next to your own desk—your personal storage.

My Drive is basically your private space.

If you want to share files, you need to manually choose who can access them—specific users or groups.

Even administrators can’t just peek into someone’s My Drive.

They’d need special tools and a good reason (like an audit).

It’s like having a locked drawer at your desk.

Even your boss wouldn’t open it without a master key—and that’s only for serious cases.

Shared Drives, on the other hand, also have access controls—based on roles and targets.

Roles

Viewer: Can only view (download/print can be restricted)
Commenter: Can comment but not edit
Editor: Can edit, reshare, and delete
Content Manager: Can edit and organize within the Shared Drive
Manager: Full control, including member management

Targets

Specific users (via email)
Google Groups (like team@yourdomain.com)
All users in the domain (anyone with @yourdomain.com)
Anyone with the link (higher security risk)

Actually, both My Drive and Shared Drives allow you to store and share files.

But the flow of control is different.

Pattern 1: Store in My Drive

Share only when needed
Starts narrow → expands

Pattern 2: Store in Shared Drive

Restrict access only when needed
Starts wide → narrows down

The Big Difference: File Ownership

Here’s the key: Who owns the file?

Files in My Drive are owned by the individual.
Files in Shared Drives are owned by the organization.

If a user leaves the company and their account is deleted,

everything in their My Drive disappears—even shared files.

It’s like tossing out a former employee’s entire desk.

On the other hand, files in Shared Drives remain safe—even when users leave the organization.

This kind of structure makes file ownership and collaboration clearer.

In-progress work and personal to-do lists → store in My Drive
Files meant to be shared with your team or org → store in Shared Drives

Sounds simple, right?

But in real-world usage, things often get messy.

Another Google Drive feature—the “Shared with me” list—often leads things in the wrong direction.

The Problem with “Shared with Me”

The “Shared with me” section exists in both personal and Workspace accounts.

It shows a list of files and folders others have shared with you.

For personal accounts, it kind of makes sense:

Your stuff = My Drive
Other people’s stuff = Shared with me

But here’s the catch:

This list is just a notification box showing newly shared links.

There’s no folder structure, no organization—just a time-ordered feed.

And you can’t rename, reorder, or delete anything from it.

So what happens?

People want control.

They want to tidy things up.

So, naturally, they right-click the file...

...and then a menu appears:

Download, Make a copy, Add shortcut, and Add to starred—all seemingly useful for organizing files.

Here’s what each does:

Function	Description	Relation to Original File	Organizing Style
Download	Saves a copy to your computer. Good for offline use or archiving.	No longer connected	Local storage
Make a copy	Saves a new file to your My Drive. Freely editable and shareable.	No longer connected	Personal reuse or resharing
Add shortcut	Adds a link to the original file in your My Drive. Can be placed in folders.	Still linked	Custom folder organization
Add to starred	Marks the file with a star for quick access. Flat list, no folder structure.	Still linked	Bookmark-style, searchable view

Why This Matters for Admins

From an admin’s perspective, when users want to organize shared files, the best practice is:

Use “Add shortcut” or “Add to starred”.

These keep the file structure intact and avoid unnecessary duplicates.

But here’s the catch...

These two options don’t appear right away in the right-click menu.

To find them, users must go deeper—through the “Organize” submenu.

As a result, people often just choose “Make a copy” or “Download”,

which breaks the connection to the original and creates version chaos.

Because these options aren’t shown clearly or intuitively, many users end up clicking “Make a copy” by mistake.

The result?

You end up with dozens of files like:

“Copy of…”

“Copy of Copy of…”

This breaks the benefits of cloud collaboration:

There’s no single source of truth
Files get out of sync
People don’t know which version is the latest

The Strange Case of Shared Accounts

In our organization, we used to share the My Drive of a special account called SharedDrive@... as a workaround for Shared Drives.

Why?

Before March 2017, Shared Drives didn’t exist.

The only way to share files across a team was to use someone’s My Drive as a shared hub.

This workaround had some serious problems:

If the account was deleted, all files disappeared
File ownership was personal, not organizational
Centralized control by admins was nearly impossible

At the time, it was a practical—but imperfect—solution.
But today, Shared Drives are the much safer and more manageable solution.

Recommended Best Practices

Google Drive is incredibly useful,

but if used carelessly, it can cause:

Lost files
Confusion about ownership
Bad handovers

As admins, here’s how we define each space:

My Drive → for temporary personal work
Shared Drives → for official, shared documents
Shared with me → just a notification list, not a workspace

Our Organizational Guidelines

Store important files in Shared Drives

For any document meant to be reused by the team.
Use My Drive as a personal sandbox

Drafts, notes, and temporary files only.

Move finished work to a Shared Drive.
Don’t rely too much on “Shared with me”

Use shortcuts or stars to organize what you need.
Use stars for quick access

Especially useful when you don’t need folder structure.

If your organization has experienced any of these issues,

I strongly recommend shifting to Shared Drives and setting clear usage rules.

Yes, it takes some effort at first—but in the long run,

you’ll gain clarity, control, and peace of mind in your file management.

References

DIY Ticketing System with Google Apps Script for Handling Inquiries

Yoshi — Wed, 21 May 2025 14:58:39 +0000

Started Volunteering as a System Admin for an NPO

About two months ago, I started volunteering for an NPO in a system admin role. The organization is quite pre-modern in how it operates—user inquiries were handled entirely via email. Sometimes our IT-related team handled the issues, and other times we had to forward them to the appropriate department. Of course, that meant forwarding the emails. I hadn’t seen dozens of endless “Re:Re:Re:” threads in a while. It felt like time-traveling back to the 2000s when I first entered the workforce.

Fortunately, the NPO uses Google Workspace for Nonprofits, so we have access to the basic Google apps. I decided to centralize user inquiries using a Google Form. However, form responses are aggregated into a spreadsheet by default, and managing each case individually that way is clearly a pain. At the same time, it didn’t seem worth it to introduce full-fledged ticketing systems like JIRA or Salesforce. So, I built a simple ticket system using Google Apps Script (GAS).

I'm sharing the system structure and the lessons I learned here as a record. I hope it’s helpful for non-engineers, NPO staff, or small business operators who can’t spend much on tools.

(Update: Sample code is available here → GitHub Repository)

System Architecture Overview

Since we're limited to Google Workspace, the setup is very simple. After designing the overall structure and creating a UI mockup in HTML, I had ChatGPT generate the implementation code, then manually tweaked the finer details.

Google Form

Collects user inquiries
Accessible to anyone with the link

Google Spreadsheet

The spreadsheet is split into two files based on purpose:

Sheet(1): FormResponses

Auto-generated when collecting form data
Script is triggered on each new entry
Issues a Unique ID (UID)
Copies the data into the ticket log sheet (TicketLogs)
Sends UID to the user via email
Also posts to a Google Chat thread for the support team

Sheet(2): TicketLogs

Serves as a pseudo-database
Not accessible to non-admins
Edits are appended to the bottom

Google Apps Script

Provides a UI to view and respond to inquiries
Only shows the latest row of data from the logs
Access restricted to users within the organization domain

System Diagram

Web App Functions and Process Overview

UI Screenshots

Here’s a screenshot of the ticket management screen. Basic filtering is implemented.

Clicking the "Edit" button opens a modal where you can edit individual tickets.

Code.gs

Code.gs handles the backend, retrieving data from the spreadsheet.

Function Name	Description
`doGet()`	Returns the UI as a web app. Entry point that displays the HTML page.
`onFormSubmit(e)`	Triggered when a Google Form is submitted. Issues a ticket ID, logs it, sends an email, and posts to Google Chat.
`sendToGoogleChat(ticketId, name, category, inquiry)`	Notifies Google Chat about new tickets using a webhook.
`getTickets()`	Retrieves only the latest state for each ticket from the log sheet. Used to display the list in the UI.
`updateTicket(updateData)`	Appends updated info to the log sheet based on the given ticket data (no overwrite, keeps history).

Ticket Screen (HTML + JS) Functions and Overview

The ticket management screen is displayed via Google Apps Script’s HtmlService.

Built using HTML (Bootstrap + FontAwesome) and JavaScript to create the UI and interact with GAS.

Function / Element Name	Role / Description
`loadTickets(isRefresh)`	Calls `getTickets()` to fetch data, then runs `applyFilters()`
`applyFilters(initialLoad)`	Filters the ticket list by number, category, status, and assignee
`renderTickets(tickets)`	Converts the ticket list into HTML and outputs it to `#ticketList`
`openEditModal(...)`	Populates the modal with ticket data and displays it
`saveModalTicket()`	Gets data from modal, calls `updateTicket()`, closes modal, refreshes UI
`escapeHtml(str)`	Escapes HTML to prevent XSS
`DOMContentLoaded` event	Initialization: sets up event listeners and loads open tickets on first load

HTML Structure Elements

Selector / Element	Description
`.filters`	Filter UI: ticket number, category, status, assignee, etc.
`#ticketList`	Area to display the ticket list
`.modal`	Edit modal (built using Bootstrap Modal)

Notes

Web App with GAS

What this GAS project does is display and manage spreadsheet data through a custom UI. While similar operations can be done directly in a spreadsheet, it's not convenient to record and edit everything in one sheet. When history is written directly into spreadsheet cells, inputs become messy, and it’s unclear whether to overwrite or append—leading to inconsistent user behavior. By providing a unified form, it becomes easier to trace past actions even if responsibilities are handed over to different team members.

Spreadsheet Operation

By separating the input sheet and the log sheet, mixed data is avoided. With a dedicated log sheet, admins can trace the edit history. Non-admins are denied access to the base data, reducing the risk of tampering. Ideally, version control-style diffs would have been great, but that felt like overkill and was left out.

Data Aggregation / Standardization

By centralizing the inquiry channel and standardizing the data format, we should be able to accumulate training data for a future FAQ chatbot. Current FAQs are based mostly on human intuition, so there’s definitely room for improvement using actual data.

AI-Based Implementation

Starting simple and refining based on clearly identified issues is the way to go. One bitter memory: the initial ChatGPT code was too simple, so I asked Claude to review it—only for it to completely rewrite things and break the implementation beyond repair. Since I had little front-end experience, I kept things simple and upgraded step by step. As a result, I picked up the knack for making precise fixes and tracking down bugs early on.

Conclusion

While focusing on design and review, the core implementation was completed in just a few hours. The ability to release only what you need at low cost is a true advantage of building things in-house.

That said, “maintenance” is now the next challenge. Can non-engineers maintain such a system with AI support? Despite the hype that AI can solve everything, as long as systems interact with people and organizations, a minimum level of technical literacy is still necessary to stay accountable—and to avoid building the wrong thing entirely. It’s a tough problem. Perhaps we’ll need a system where AI interviews the current admin to gather maintenance context. But then again—who will maintain that system? The recursion never ends.

How the Liskov Substitution Principle Clarifies Delegation in Organizations

Yoshi — Tue, 15 Apr 2025 01:17:50 +0000

"You can safely leave it to this person"—this is a phrase often heard in business settings. But in reality, it's not uncommon for problems to arise after a handover.

I once experienced this myself when I took over a client account from my manager. I was explicitly told to act autonomously, so I made decisions based on what I thought was best given the client’s situation. In doing so, I ended up modifying the clear guidelines passed down from my manager. For example, while working in corporate sales proposing system solutions to various companies:

I was supposed to follow up on proposal progress every week, but I waited two weeks thinking “the client seems busy”
I failed to push back on some “nice-to-have” requirements and brought them back to the team

These decisions backfired. The process took too long, yielded no results, and the deal didn’t close. My manager gave me direct feedback: “You shouldn't change the criteria on your own.” That’s when I realized I had misunderstood what it really meant to “act autonomously.”

This experience resonated deeply with a software design principle I later learned—the Liskov Substitution Principle (LSP). LSP states that a subclass should be substitutable for its superclass without altering the correctness of the program.

In a business context, if a subordinate is to act in place of a superior, it’s essential to clearly identify the preconditions, postconditions, and invariants—what must be preserved and what can be improved. I made decisions without clarifying those boundaries.

In software, when a subclass extends a superclass without breaking its behavioral contract, the system remains stable. The same applies to organizations: when roles are handed down from managers to subordinates, if expectations, deliverables, and standards aren't preserved, trust and results across the team can collapse.

LSP, therefore, is not just a principle for software design—it also offers practical insight into how we structure role transitions and delegation within organizations.

What is the LSP?

Many of you may already be familiar with it, but the Liskov Substitution Principle (LSP) is an object-oriented design principle proposed by Barbara Liskov in 1987.

"If S is a subtype of T, then objects of type T may be replaced with objects of type S without altering the correctness of the program."

To uphold this principle, the following conditions are crucial. Let’s look at them through the example of an airline mileage program.

Preconditions

A subclass must require the same or weaker conditions than its superclass.
An operation allowed in the superclass must not be disallowed in the subclass.

Postconditions

A subclass must guarantee the same or stronger outcomes than its superclass.
It’s fine for a subclass to return more than what was guaranteed by the superclass, but never less.

Invariants

Any conditions that are always true in the superclass must remain true in the subclass.
Subclasses may strengthen the rules, but must not violate or weaken them.

Concrete Design Example

Here, we'll use a mileage service as a case study to illustrate how the three conditions of the LSP can be applied to the design of a customer class.

class Customer:
    def book_flight(self, seat_class):
        assert seat_class in ["economy", "business"]  # Precondition
        return f"Flight booked in {seat_class} class"

    def get_mileage(self):
        return 100  # Postcondition: always grants 100 miles

    def is_active(self):
        return True  # Invariant: always remains active

class RoyalCustomer(Customer):
    def book_flight(self, seat_class):
        # Looser precondition: allows first class bookings
        assert seat_class in ["economy", "business", "first"]
        return f"Royal booking in {seat_class} class"

    def get_mileage(self):
        # Stronger postcondition: grants more miles
        return 300

    def is_active(self):
        # Maintains the same invariant
        return super().is_active()

As shown, the RoyalCustomer class can be substituted for the Customer class without breaking the system—and in fact, it enhances functionality. This is an example of a design that respects the Liskov Substitution Principle.

Preconditions

Customer: Only "economy" and "business" classes can be booked
RoyalCustomer: Also allows "first" class → Loosening the precondition (✔ OK)

Postconditions

Customer: Grants 100 miles
RoyalCustomer: Grants 300 miles → Enhancing the outcome (✔ OK)

Invariants

Customer: is_active() is always True
RoyalCustomer: Maintains is_active() in the same way → Preserves the invariant (✔ OK)

LSP-Like Behavior in the Sales Field

The relationship between managers and their subordinates closely resembles class inheritance.

Preconditions

Manager: Only attends meetings where decision-makers are present
Subordinate: Will visit even at the staff level → Broadening the scope of engagement = loosening the precondition (✔ OK)

Postconditions

Manager: Presents a quote and clarifies the next action
Subordinate: Presents a quote and nails down contract details for the next visit → Turning next steps into concrete actions = enhancing the outcome (✔ OK)

Invariants

Manager: Always responds fairly and politely, even before a contract is signed
Subordinate: Responds to all inquiries within one business day regardless of contract status → Strengthening response standards while preserving the original invariant (✔ OK)

Examples of Violations (NG cases)

“I won’t take any action unless the client reaches out first.”

→ Making preconditions stricter (✘ Violation)
“We sent the quote, so we’re waiting to hear back.”

→ Weakening postconditions (✘ Violation)
“Whether or not we build trust depends on the situation.”

→ Breaking the invariant (✘ Violation)

Why the LSP Applies to Organizations Too

Software systems and business organizations share several key similarities:

They operate in a hierarchical structure (e.g., parent → child, manager → subordinate)
They involve the delegation of abstract contracts and responsibilities
They are designed for efficiency based on the assumption of substitutability

In other words, the principles behind software design can also be applied to how people and organizations function. Through my experience in sales—particularly the failures—I intuitively came to understand that freedom of action must be balanced with behavioral contracts.

The Liskov Substitution Principle put this into clear and formal language. It struck me deeply. This principle isn’t just about keeping systems stable—it can serve as a powerful guide for human decision-making as well.

References

Understanding the Role of Decision Table Testing

Yoshi — Mon, 31 Mar 2025 02:33:45 +0000

One type of software testing is Decision Table Testing. In real-world projects, it’s often created using Excel. Its visual clarity makes it easy to understand even for non-technical team members who have strong domain knowledge.

On the other hand, when confirming the behavior of the implemented code, structural testing (like unit tests) is commonly used.

(If you'd like to dive deeper into structural testing, feel free to check my previous post: Understanding Software Test Coverage Criteria Step by Step: From Line Coverage to MC/DC)

At first glance, these two approaches may seem completely different. However, understanding why both are necessary for testing the same logic is extremely important in practice. This article explores the reasons and background behind that.

Basic Structure of a Decision Table

A decision table has a very simple structure. Conditions are written vertically, with the outcome listed at the bottom. Each horizontal column represents a variant—a combination of input values for each condition.

Condition	Variant 1	Variant 2	Variant 3
Age > 18	true	false	true
Has ID	true	true	false
→ Result	OK	NG	NG

Conditions: Assumptions or inputs used to make decisions (rows)
Variants: Test cases created from combinations of conditions (columns)

Using this kind of table helps organize test cases in a structured and comprehensive way.

Just like structural testing, there are also various strategies depending on the testing purpose.

Test Strategy Comparison in Decision Tables

Strategy Name	All Conditions Covered	All Results Covered	All Explicit Cases Covered	Check Condition Effectiveness (MC/DC)
all-conditions	✅	❌	❌	❌
all-decisions	❌	✅	❌	❌
all-explicit-variants	✅ (explicit only)	✅	✅	❌
MC/DC (Condition Independence)	✅ (only necessary)	✅	❌	✅

Why Do We Use Different Approaches?

At this point, you might wonder:

“If we confirm specifications using a decision table → implement the code based on those specs → verify behavior using structural testing...

then do we really need decision table-based testing?”

That’s a fair question.

However, even if we’re testing the same logic, the focus of each approach is different — and that’s why we need both.

Goal	Suitable Testing Method
Prevent missing conditions or rules	Model-based (Decision Table)
Confirm there are no implementation mistakes	Structural Testing

Why Is Decision Table Testing Necessary?

Even if specifications are well-organized using a decision table and proper structural tests are conducted, decision table-based testing still brings unique value in the following ways:

1. Implementation Doesn’t Always Match the Specification

There can be overlooked or misunderstood requirements during implementation.
Decision table testing verifies the intended decision logic by testing strictly against the specification.

2. Structural Testing Can’t Detect Missing Logic

Structural testing checks only what’s written in the code.
It cannot detect what’s missing.
A decision table helps reveal decisions that should exist but don’t.

3. Clarifies Test Coverage

With a decision table, it’s clear which conditions and results are being tested.
It makes it easier to find gaps or redundancies in the test cases and enables better reviews among team members.

Relationship Between Decision Tables and Structural Testing

Creating test cases based on a decision table can indirectly cover code branches (like if statements).

However, structural testing alone is not enough in cases like:

Logic using polymorphism (inheritance or dynamic dispatch)
Conditions depending on configuration files or database values

Since these aren’t explicitly shown as branches in the code, they may be missed by structural tests.

The following section provides concrete examples.

When Logic Uses Polymorphism

Polymorphism means that the same method name can behave differently depending on the class. This is a key feature of object-oriented programming.

// Java
class User {
    void showDiscount() {
        System.out.println("No discount");
    }
}

class PremiumUser extends User {
    void showDiscount() {
        System.out.println("20% discount");
    }
}

User u = getUser();  // We don't know if it's a regular or premium user
u.showDiscount();    // Behavior changes depending on the class at runtime

In this case, the branching is not done with if statements but by the type of class.
So, structural testing (which follows code branches like if) may miss this logic.

When Conditions Depend on Config Files or Databases

Sometimes, the behavior of an app is controlled by values in external files, not by conditions written in the code.

// config.json
{
  "isFeatureEnabled": true
}

# Python
if config["isFeatureEnabled"]:
    enable_feature()

In such cases, model-based testing (like decision tables) lets you verify the logic from a specification point of view.
In other words:

Structural testing checks whether the code behaves as written.

Model-based testing checks whether the right behavior or decision is included in the first place, from a user or specification perspective.

It’s especially useful to detect missing logic or misunderstood design that can’t be seen from code alone.

Structural Testing

Focus: Which branches are executed?

Focuses on code structure (if statements, loops)
Does not consider the software’s purpose or meaning
Can be done with the specification and source code

Model-Based Testing

Focus: Are the necessary decisions being made?

Requires domain knowledge and user perspective
Uses models (like decision tables) to represent “what decisions should be made and how”
Accessible to non-engineers and upstream stakeholders

Summary

Decision table-based testing offers several practical strengths and benefits:

Handles non-Boolean values easily (e.g., capacity = none / 0GB / 8GB / unlimited)
Easy to understand for domain experts, helpful for reviews and prioritization
Can detect missing specifications that haven't been implemented

Perspective	Structural Testing	Model-Based Testing (Decision Table)
Goal	Code coverage	Specification accuracy
Required Resources	Code, specifications	Requirements, domain knowledge
Who Runs It	Developers, testers	Upstream members (e.g., consultants)
Strength	Branch coverage	Detects spec gaps, handles non-Boolean logic

Decision tables act as a bridge from upstream to downstream phases.

They clarify specifications, guide design and implementation, and verify that the software behaves as specified.

By combining them with structural testing, we can both “build it right” and “build the right thing.”

This dual approach ensures high-quality software by covering both missing logic and implementation mistakes.

Understanding Software Test Coverage Criteria Step by Step: From Line Coverage to MC/DC

Yoshi — Fri, 07 Mar 2025 04:09:10 +0000

Software testing methods in development often have similar names, which can be quite confusing. Understanding their differences helps avoid confusion when implementing tests and makes it easier to explain them to non-testing team members.

This time, let’s go through five basic coverage criteria in software testing, from fundamental to advanced, step by step. Each of these coverage criteria was developed to address the shortcomings of the previous ones.

Line Coverage helps identify unexecuted code but doesn’t ensure sufficient testing of conditional branches.
Branch Coverage tests both true and false outcomes of conditions but doesn’t consider the impact of individual conditions.
Condition Coverage checks each condition’s true/false outcomes, but short-circuit evaluation can lead to gaps in coverage.
Multiple Condition Coverage tests all possible combinations, but the number of test cases can explode.
MC/DC (Modified Condition/Decision Coverage) minimizes test cases while ensuring each condition's independence is tested, leading to high-quality test results with fewer tests.

Each of these coverage criteria was designed to compensate for the shortcomings of the previous approach, ensuring better overall test effectiveness.

Line (Statement) Coverage

This one is straightforward. It simply measures how much of the code was executed by the test. The formula is number of executed lines / total lines of code. At first, I wondered if this had any real significance, but it turns out that line coverage is useful in the early stages of development to identify unexecuted parts of the code. Especially for humans, it's easy to understand when test tools highlight executed and unexecuted lines in green and red during source code reviews. However, since line coverage alone is not enough to verify software quality, it is usually combined with branch coverage.

Branch (Decision) Coverage

This measures coverage by ensuring that each conditional branch has been executed at least once for both True and False outcomes. The key point here is that the focus is on results.

For example, given the condition if (A && B), there are four possible combinations, but branch coverage requires only two test cases: No.1 and one from [No.2, No.3, or No.4], since that ensures both True and False outcomes.

No	A	B	Result
1	True	True	True
2	True	False	False
3	False	True	False
4	False	False	False

When there are multiple conditions, each condition's effect must be checked independently. Consider the following pseudo-code:

if A==True {
doSomething1();
}
if B==True {
doSomething2();
}
if C==True {
doSomething3();
}

This code contains three independent branches. To satisfy branch coverage, each conditional branch must be executed for both True and False outcomes. At first glance, it might seem that just two cases, "TTT" and "FFF," would be enough, but that would fail to confirm independent effects of each condition. Without this concept of independence, unnecessary tests could be added, or some test cases might be missing.

For example, if the test cases were only "TTT" and "FFF," they wouldn’t separately verify the impact of changing only A or only B. The essence of branch coverage is to confirm the independent behavior of each branch.

Therefore, the following four test cases are necessary:

Case	A	B	C	if execution paths
1	T	T	T	All `if` statements are executed
2	F	T	T	`if(A)` is skipped
3	T	F	T	`if(B)` is skipped
4	T	T	F	`if(C)` is skipped

Condition Coverage

While branch coverage focuses on whether conditional statements like if and else are executed at least once, condition coverage focuses on whether changes in each part of the condition affect the outcome. It ensures that each condition is executed at least once as both True and False.

Let's revisit the example from branch coverage. Given if(A && B), there are four possible combinations. In condition coverage, we need a test pair that ensures A and B each take both True and False values. This can be achieved with either [No.1, No.4] or [No.2, No.3], reducing the required test cases to two.

No	A	B	Result
1	True	True	True
2	True	False	False
3	False	True	False
4	False	False	False

A key issue here is short-circuit evaluation. For instance, in if(A && B), if A is False, B is never evaluated. This is problematic because:

B is not actually tested in execution, making the test incomplete.
If B has side effects (e.g., function calls or variable modifications), skipping its evaluation can cause behavioral differences that go unnoticed.
Bugs related to B might remain hidden.

Additionally, condition coverage does not ensure full branch coverage. This limitation leads to the need for more advanced coverage criteria.

Multiple Condition Coverage

Condition coverage alone can leave gaps in test coverage. To avoid this, multiple condition coverage tests all possible combinations of conditions. This is a stricter criterion than condition coverage and eliminates gaps, but it comes with significant drawbacks:

Exponential growth in test cases
- With N conditions, 2^N test cases are required.
- More conditions mean an explosion in test cases, making it impractical.
Redundant test cases
- It includes cases with identical outcomes.
- Many tests might be functionally meaningless.
Inefficiency due to short-circuit evaluation
- In if(A || B), if A is True, B is never evaluated.
- This leads to unnecessary tests for conditions that are never executed.

In practice, minimizing test cases while ensuring proper condition testing is key. Condition coverage is often sufficient, but for high-reliability systems such as aviation and medical software, MC/DC (Modified Condition/Decision Coverage) is used.

MC/DC (Modified Condition/Decision Coverage)

MC/DC ensures that each condition independently affects the outcome, covering all necessary condition changes with the minimum number of test cases.

To satisfy MC/DC, the following four conditions must be met:

Each condition must take both True and False at least once.
The result must take both True and False at least once.
Each condition must independently affect the overall result.
This must be achieved with the minimum number of test cases.

This might sound abstract, so let’s go through a concrete example. Suppose we are offering apple juice, and the conditions are whether we have a cup, juice, and ice. To serve apple juice, a cup and juice are absolutely necessary, while ice is optional. The full set of possible combinations is as follows:

Case	cup	juice	ice	result
1	T	T	T	OK
2	T	T	F	OK
3	T	F	F	NG
4	F	T	T	NG
5	F	F	T	NG
6	F	T	F	NG
7	T	F	T	NG
8	F	F	F	NG

MC/DC focuses only on conditions that independently affect the result. Since ice does not affect the result, it is excluded from testing. We then check whether changing cup or juice individually alters the result.

Case 1 (T,T,T) is chosen to represent a valid (OK) result.
Case 4 (F,T,T) tests the effect of removing the cup.
Case 7 (T,F,T) tests the effect of removing the juice.

Case	cup	juice	ice	result
1	T	T	T	OK
4	F	T	T	NG (cup's effect)
7	T	F	T	NG (juice's effect)

With these selected test cases, MC/DC criteria are satisfied. Since MC/DC ensures that each condition independently affects the outcome, it optimizes test cases while considering short-circuit evaluation, making it a highly efficient testing method.

Understanding the Strengths and Weaknesses

For those new to software testing, the numerous similar-sounding test names and definitions can be confusing. Many explanations assume that each condition independently affects the outcome, which makes understanding even harder.

When creating real-world test cases, balancing time, cost, and acceptable risk is crucial. Having a solid grasp of what each coverage criterion aims to achieve can be valuable for all stakeholders involved in testing.