DEV Community: Yoshi

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.

References

What I Learned from Tracking My Child’s YouTube Activity

Yoshi — Tue, 18 Feb 2025 23:30:00 +0000

In modern parenting, it’s difficult to completely block YouTube. When I was a child, I had limited options for video content, either watching TV in real-time or viewing pre-recorded videos on VHS or DVD. Today's children, however, are exposed to endless content from platforms like YouTube from a young age. While this has its benefits, such as discovering new genres or learning niche knowledge that even adults may not be aware of, there are times when the sheer addictiveness of YouTube is surprising. Kids ask to watch it during spare moments, and even when we're out and about.

Until children reach around elementary school age, parents can usually manage their YouTube usage, but once they grow older, they start searching for new videos and exploring recommendations on their own. In our household, we noticed that our child was gradually watching only game YouTubers, and we began to think about what to do next. Fortunately, YouTube allows you to extract tracking data, so I decided to create a tool that would let us monitor their activity by processing that data.

Concept

The information available from YouTube history includes three main points: title, uploader, and the timestamp of the start time. Due to my professional background, I've created numerous dashboards and reporting systems, and I’ve found that, in most cases, usage tends to decrease over time. When building a reporting system, it's easy to get caught up in visualizations and enriching the data with additional details. However, for this particular purpose, I kept the idea of "simple is best" in mind and refined the necessary information through two perspectives:

Viewing Time Perspective
1. Total viewing time
2. Daily viewing time
Content Perspective
1. Viewing time by channel
2. Extraction of undesirable content

Viewing Time

Since the only data available was the timestamp of the start time, I calculated the viewing time by comparing the difference with the previous data. I also set a rule to treat any viewing gap of 90 minutes or more as the end of a session. If there was no data for a session end or if only one video was watched in a day, I couldn’t calculate the difference, so I filled in these gaps with the session median or pre-set approximate values.

Content

Once the viewing time is calculated, viewing time by channel is simply aggregated. For extracting undesirable content, I used the OpenAI API to assess whether a title could potentially harm children born in "yyyy/mm." This judgment is made on a 4-level scale, and titles rated as "Potentially Harmful (Level 3)" or "Harmful (Level 4)" are reported.

Implementation

For this project, I used GAS (Google Apps Script), a simple automation tool from Google. While I considered integrating dashboards and linking with messenger apps, the primary goal was to monitor numbers and content. Therefore, I kept it simple and mostly completed the task within Google's ecosystem.

Delivery Content

Using GAS, I set up a simple system to send reports to a specified email address with text and graphs attached. When the command is executed, the report content is sent via email to both my wife and me, as shown in the example below (the title/channel names have been changed to fictitious ones).

Here is your latest YouTube watch time report.
Period: 2025-01-24 (Thu) - 2025-02-13 (Wed)

++++++++++++++++++++++++++++++
Total Watch Time: 11h 41m
++++++++++++++++++++++++++++++

■■■ Overview:
YouTube Watch History Analysis:

Safe (Level 1): 131 videos
Mildly Inappropriate (Level 2): 3 videos
Potentially Harmful (Level 3): 1 videos
Harmful (Level 4): 0 videos

Collaboration Project: "The Hide-and-Seek Game where You are Attacked by a Maniacal Clown at a Private Hotel is Insane... [Screaming]"

■■■ Top 10 Most-Watched Channels:

Craft Life: 6h 30m
Craft World: 0h 58m
Game Master XYZ: 0h 51m
Akane-Iro: 0h 39m
MEN Play: 0h 32m
Sports NOW: 0h 24m
Creators TV: 0h 18m
Odin Studio: 0h 16m
Hikaru's Play: 0h 8m
Football Highlights: 0h 7m

■■■ Daily Watch Time:
Date: TotalWatchTime (StartTime)

2025-01-24 (Thu): 0h 40m (11:27)
2025-01-25 (Fri): ---- (--:--)
2025-01-26 (Sat): 0h 57m (08:49)
2025-01-27 (Sun): ---- (--:--)
2025-01-28 (Mon): ---- (--:--)
2025-01-29 (Tue): 0h 10m (17:04)
2025-01-30 (Wed): ---- (--:--)
2025-01-31 (Thu): ---- (--:--)
2025-02-01 (Fri): 2h 11m (15:52)
2025-02-02 (Sat): 0h 45m (15:30)
2025-02-03 (Sun): 1h 23m (15:59)
2025-02-04 (Mon): ---- (--:--)
2025-02-05 (Tue): 0h 21m (17:00)
2025-02-06 (Wed): ---- (--:--)
2025-02-07 (Thu): 1h 9m (14:55)
2025-02-08 (Fri): ---- (--:--)
2025-02-09 (Sat): 2h 26m (06:53)
2025-02-10 (Sun): ---- (--:--)
2025-02-11 (Mon): ---- (--:--)
2025-02-12 (Tue): ---- (--:--)
2025-02-13 (Wed): 1h 39m (15:30)

====================

Pros and Cons

Points of Success

The children's viewing time has been visualized with a reasonable level of quality.
- It allows discussions with the child based on data, removing subjectivity.
- We could confirm that the child was waking up early in the past to watch YouTube.
- We gained insight into the child’s main viewing channels.
- While I knew the child was watching craft games, it turned out they were mostly watching games.
- Surprisingly, soccer, another hobby, wasn’t being watched much.
We can check for titles that might have a harmful impact.
- Since it also affects suggestions, I remove any harmful titles from the history once checked.

Points for Improvement

Not fully automated.
- YouTube Takeout can only be scheduled bi-monthly, up to 6 times.
- For monthly reports, extracting data for two consecutive months requires a specified date setting.
- There are also restrictions with the child's account...
Restrictions with the child’s account:
- Can't automate with GAS for the child’s account.
- Can't forward emails from the child's account either.
Limited information available.
- Created based on title, uploader, and start time, within the available data range.
- If richer information could be fetched via the YouTube API...

After creating the report, I discussed it with my child and was surprised by how much more they were watching than expected. I also realized that as parents, we allowed continuous YouTube watching due to our own busyness. I plan to prepare better alternatives for the child to spend time outside of videos.

Technical Details

Since this was my first time using GAS, I approached it with the principle of simplicity and prioritizing Google services. Below are the services used and their respective purposes:

Google TakeOut: Retrieve YouTube data and set up periodic execution.
Documents: Transfer viewing data between parent and child, and store various data.
Spreadsheets: Expand JSON data and create graphs.
Gmail: Send report emails.
GAS: Integrate and execute services, as well as perform numerical calculations for the report.
OpenAI: Assess the potential danger of video titles.

The data flow proceeds as follows:

Points to Consider During Implementation

The code is available in this Git repository.

Data Retrieval

Since YouTube is viewed on a child-specific account, the viewing history data is retrieved from the child's account. By accessing Google’s TakeOut service through the child's account, YouTube viewing data can be extracted. The extracted data can then be received by attaching a download link to an email or storing it in various storage services. For this implementation, I chose to store it in Google Documents.

Unfortunately, there are two obstacles preventing the full automation of the report at this stage, both of which are present in this step:

Scheduled Distribution Frequency: The extraction schedule can only be set for bi-monthly intervals, with a maximum of one year. While one year is acceptable, the bi-monthly frequency creates gaps that are too large, so if you want monthly schedules, you need to set up extraction for two consecutive months.
Child Account Restrictions: With a child’s account, automatic email forwarding and the use of GAS are restricted. As a result, ideas like automatically forwarding emails to the parent’s account or automatically placing data in the shared folder for parent-child use in Google Docs couldn’t be used. Instead, manual storage became necessary. However, this can be resolved in the future, as external storage like Box is available for storing the data.

Extracting ZIP Files

The data distributed from TakeOut was in a multi-layered ZIP format, which couldn't be extracted using GAS’s standard utilities. Fortunately, there is a ZIP library available for use in GAS. Referring to this Japanese article, I was able to create a library and successfully extract the ZIP files.

Estimating and Filling Missing Viewing Data

Viewing time is extracted by comparing the difference from the previous data. However, the following issues arose. In the following, I use the term session to refer to a continuous viewing period.

Significant viewing time between sessions, ranging from a few hours to dozens of hours.
The final timestamp in the data cannot provide a value.

To address these issues, I took the following measures:

For the final data within a session, I used the median value of the session to fill the gap.
If there was only one data point within a session, I filled the gap with a provisional value of 20 minutes.

By doing this, although the numbers are estimates, I was able to calculate values that are reasonably close to the actual ones.

Harmful Title Evaluation

Although Gemini would be the ideal choice within the Google ecosystem, since I haven't used it much, I decided to leverage OpenAI's API, which I am more familiar with. The threshold for determining harmful content seemed to vary depending on age, so I set the birthYearMonth in GAS's Script Properties to adjust the evaluation based on the actual age.

function analyzeWithOpenAI(titles, birthYearMonth) {
  var apiKey = PropertiesService.getScriptProperties().getProperty("OPENAI_API_KEY");

  if (!apiKey) {
    Logger.log("❌ API key is missing in Script Properties.");
    return { error: "API key is missing. Please set OPENAI_API_KEY in Script Properties." };
  }

  var url = "https://api.openai.com/v1/chat/completions";

  var riskCounts = { "1 (Safe)": 0, "2 (Mildly inappropriate)": 0, "3 (Potentially harmful)": 0, "4 (Harmful)": 0 };
  var harmfulTitles = [];
  var analyzedResults = [];

  titles.forEach(title => {
    var messages = [
      { role: "user", content: `Evaluate the following YouTube video title for harmful content for a child born in ${birthYearMonth}.
                                 Classify it into four levels:
                                 - 1 (Safe)
                                 - 2 (Mildly inappropriate)
                                 - 3 (Potentially harmful)
                                 - 4 (Harmful)

                                 Return only the numeric classification.

                                 Title: ${title}` }
    ];

    var payload = {
      model: "gpt-4o",
      messages: messages,
      temperature: 0.3
    };

GAS Execution Time

When making requests to the OpenAI API, I process each title individually, inserting a 500ms sleep time between each request. Without considering the API request processing, the maximum number of tasks that can be handled on GAS is 720.

Since the volume was about 72 titles over two weeks, this was not an issue. However, if the usage becomes heavier or if I need to search for more videos like in the case of short videos, it may break down. In the future, I plan to filter videos with over 5 minutes of viewing time, classify titles, and process multiple titles in batches.

GAS Module System

I prepared function files for each feature, but GAS lacks a module system, so I had to determine the corresponding module file based on the function name alone. This became a bit cumbersome as the number of files increased. It’s likely because GAS isn't designed for complex applications, but this was a small stumbling point.

Future Plans

Although it’s manual for now, I plan to run the report system at least once a month. This has become a topic for both me and my child to engage with YouTube more thoughtfully. Ideally, the platform would provide such reports as part of its core features, but I’m not holding my breath for that. It's more of a "nice-to-have" feature.