Google Sheets Custom Functions

日本語

Google Sheets custom functions for Japanese data validation — email, phone number, and postal code.

Built from apps-script-fleet template.

Available Functions

Function	Description	Example
=IS_VALID_EMAIL(value)	Validates email address format	=IS_VALID_EMAIL(A1)
=IS_VALID_PHONE_JP(value)	Validates Japanese phone number format (landline, mobile, toll-free)	=IS_VALID_PHONE_JP(B1)
=IS_VALID_POSTAL_CODE(value)	Validates Japanese postal code format (with or without hyphen)	=IS_VALID_POSTAL_CODE(C1)

Usage Examples

Enter the following formulas in spreadsheet cells:

=IS_VALID_EMAIL("user@example.com")      -> TRUE
=IS_VALID_EMAIL("invalid")               -> FALSE

=IS_VALID_PHONE_JP("03-1234-5678")       -> TRUE
=IS_VALID_PHONE_JP("090-1234-5678")      -> TRUE
=IS_VALID_PHONE_JP("0120-123-456")       -> TRUE
=IS_VALID_PHONE_JP("1234567890")         -> FALSE

=IS_VALID_POSTAL_CODE("123-4567")        -> TRUE
=IS_VALID_POSTAL_CODE("1234567")         -> TRUE
=IS_VALID_POSTAL_CODE("123-456")         -> FALSE

Sample Google Sheets

Custom functions require a container-bound script — the Apps Script project must be created from within the Google Sheets spreadsheet. The dev/prod scripts below are bound to this spreadsheet:

Resource	Link
Google Sheets	custom-functions-sample
Apps Script (dev)	custom-functions-dev
Apps Script (prod)	custom-functions-prod

Quick Start

1. Create a Google Sheets Spreadsheet

Create a new spreadsheet in Google Sheets. Custom functions will be available in this spreadsheet.

2. Create a Container-Bound Script

Open the spreadsheet → Extensions → Apps Script. This creates an Apps Script project bound to the spreadsheet. Copy the scriptId from the project URL (https://script.google.com/d/<scriptId>/edit).

3. Configure clasp

Set the scriptId in .clasp-dev.json and .clasp-prod.json:

{ "scriptId": "YOUR_SCRIPT_ID_HERE" }

Dev and prod can share a single spreadsheet — use the same scriptId or separate ones as needed.

4. Set CI/CD Secret

Set the CLASPRC_JSON secret in your GitHub repository or at the organization level.

5. Deploy

Run pnpm run deploy to deploy to the dev environment. The custom functions become available in the bound spreadsheet.

Project Structure

src/
├── index.ts          # GAS entry points (IS_VALID_EMAIL, etc.) — no export keyword
└── validators.ts     # Validation logic as pure functions
test/
└── validators.test.ts  # Unit tests (100% coverage)

Development

Command	Description
pnpm run check	lint + typecheck + test (all checks)
pnpm run build	Bundle TypeScript and output to dist/
pnpm run test	Jest with coverage
pnpm run test -- --watch	Jest watch mode
pnpm run deploy	check → build → deploy to dev
pnpm run deploy:prod	check → build → deploy to production

CI/CD

CI runs on every push and PR. CD deploys on merge to dev or main — configured via GitHub Actions secrets/variables per environment. See apps-script-fleet docs for details.

Notes

• Functions in src/index.ts must not have the export keyword — the GAS runtime does not support ES module syntax
• src/index.ts is excluded from test coverage (GAS globals cannot run in Node.js)
• Coverage threshold: 80% for all metrics (configurable in jest.config.json)

License

MIT