Sheet Language Global Helper

A VS Code extension that fetches multilingual data from Google Sheets and displays it via hover in your code.

🌐 Languages

English (Current)
한국어 (Korean)

🔗 Links

📦 VS Code Marketplace
💻 GitHub Repository

✨ Features

📊 Google Sheets Integration: Fetch data via Service Account JSON, Google Sheets API key, JSON API URL, or CSV URL
🔍 Hover Feature: Hover over keys starting with WD, ST, CD in your code to see multilingual information
🏷️ Inline Translation (Inlay Hints): Show translations inline next to keys and translation calls (no hover needed)
💾 Local Caching: Data is stored in local storage for offline use
🔄 Manual Sync: Update to the latest data only when needed
📝 Multi-Sheet Support: Fetch multiple sheets (WD, ST, CD, etc.) at once
📤 Export to workspace JSON: Under workspaceExportJsonPath (required): targetSheetNames is treated as key code prefixes for all_language.json and {prefix}_lang.json. With allSheetNames on, the merged dictionary includes every tab, so e.g. cd_lang.json is not “CD tab rows only”—any CD… key from any tab is included. Keys that match no prefix are not written to disk.

Complete Workflow

flowchart LR
    A[Google Sheets] -->|API or CSV| B[Data Sync]
    B --> C[Local Storage<br/>Save]
    C --> D[Write Code<br/>in VS Code]
    D --> E[Hover over Keys<br/>WD000001, ST000001, etc.]
    E --> F[Hover Popup<br/>Multilingual Info]
    
    style A fill:#c8e6c9,color:#000000
    style B fill:#b3e5fc,color:#000000
    style C fill:#e1bee7,color:#000000
    style D fill:#ffe0b2,color:#000000
    style E fill:#ffe0b2,color:#000000
    style F fill:#c8e6c9,color:#000000

🚀 Getting Started

Installation

Open VS Code Extensions Marketplace with Ctrl + Shift + X (or Cmd + Shift + X on Mac)
Search for "Sheet Language Global Helper"
Click Install

Configuration

Open VS Code settings with Ctrl + , (or Cmd + , on Mac) and search for "Sheet Language Global Helper".

If hint-related settings (Show Inline Translation, Inline Translation Language, Hover Key Patterns) do not appear:

Scroll down in the extension settings — they may be below the sheet/API options.
Search for inline or hoverKey in the settings search box to jump to them.
Add manually — Cmd + Shift + P → "Open User Settings (JSON)", then add:

"languageHelper.showInlineTranslation": true,
"languageHelper.inlineTranslationLanguage": "ko",
"languageHelper.hoverKeyPatterns": "WD,ST,CD"

Also ensure Editor: Inlay Hints is set to on in VS Code settings so inline hints are visible.

Data source priority: Service Account JSON → API Key → JSON API URL → CSV URL (first available is used).

Method 1: Service Account JSON (Priority 1, recommended for private sheets)

Create a Service Account
- Go to Google Cloud Console → IAM & Admin → Service Accounts → Create Service Account
- Create a key (JSON) and download the file
Share the spreadsheet with the service account email (e.g. xxx@project.iam.gserviceaccount.com) as Viewer
VS Code Settings
- Sheet Service Account Json: Paste the entire contents of the JSON key file (not the file path)
- Sheet Id or Sheet Url: Spreadsheet ID or full URL
- All Sheet Names: When on, all tabs are fetched and merged into one dictionary. When off, only tabs listed in targetSheetNames are fetched (as Google Sheet tab names).
- Target Sheet Names: Always used as key prefixes for JSON export and for which tabs appear in Sync Output row-count lines—not “export this tab’s rows only” when all sheets are on.

Method 2: Google Sheets API Key (Priority 2)

Get API Key
- Go to Google Cloud Console → APIs & Services > Library → Enable "Google Sheets API"
- APIs & Services > Credentials > Create API Key
Share Sheet Settings ⚠️ Required
- Open your Google Spreadsheet → Share → Anyone with the link → Viewer
VS Code Settings
- Sheet Api Key: Enter your API key
- Sheet Id: Spreadsheet ID or full URL
- All Sheet Names / Target Sheet Names: Same as Method 1—full merge vs listed tabs, and export splits by key prefix, not by tab ownership.

Method 3: JSON API URL (Priority 2 alternative)

Use a URL that returns JSON (e.g. your own API or published JSON).
Sheet Json Url: Enter the URL. Response must be an array [{ "key", "ko", "en", ... }] or an object { "WD001": { "ko": "...", "en": "..." }, ... }.

Method 4: CSV URL (Priority 3)

In Google Spreadsheet, go to File > Share > Publish to web → Select CSV format
Sheet Url: Enter the generated CSV URL

💡 Priority: Service Account JSON → API Key or JSON URL → CSV URL. The first one you configure is used.

Configuration Method Comparison

flowchart TD
    A[Start Configuration] --> B{Service Account JSON?}
    B -->|Yes| C[Method 1: Service Account JSON]
    B -->|No| F{API Key?}
    F -->|Yes| D[Method 2: Google Sheets API]
    F -->|No| G{JSON URL?}
    G -->|Yes| H[Method 3: JSON API URL]
    G -->|No| I[Method 4: CSV URL]
    
    C --> C1[Paste full JSON key in<br/>Sheet Service Account Json]
    C1 --> E[Run Sync]
    D --> D1[Enter sheetApiKey, sheetId]
    D1 --> E
    H --> H1[Enter Sheet Json Url]
    H1 --> E
    I --> I1[Enter Sheet Url - CSV]
    I1 --> E
    
    style C fill:#c8e6c9,color:#000000
    style D fill:#b3e5fc,color:#000000
    style H fill:#e1bee7,color:#000000
    style I fill:#ffe0b2,color:#000000
    style E fill:#b3e5fc,color:#000000

📖 Usage

Command Palette

Open the Command Palette (Ctrl + Shift + P / Cmd + Shift + P) and run:

Command What it does

Sheet Language Global Helper: Sheet Connect Sync Fetches data from your configured source (Service Account JSON, API Key, JSON URL, or CSV URL) and saves it to extension local storage for hover and inlay hints. The Output channel ("Sheet Language Global Helper") logs the total key count, then one line per tab listed in targetSheetNames (body row count and first-column non-empty row count). The same lines are mirrored to the Debug Console.

Sheet Language Global Helper: Sheet Sync to JSON Automatically fetches the latest sheet data first (same path as Sheet Connect Sync), then writes under workspaceExportJsonPath: all_language.json (union of keys matching targetSheetNames prefixes) and one {prefix}_lang.json per prefix that has at least one key. Unmatched keys are omitted. Longer prefixes win when overlapping. Empty workspaceExportJsonPath → error. If the fetch step fails, no JSON files are written and in-memory data is cleared (see below). The Output channel prints the folder path (json 생성경로 :), total key count, a JSON별 키 수 header, then one indented line per JSON file (all_language.json, wd_lang.json, …), then 저장 완료. The Debug Console gets the same information as a short multi-line block.

Data Synchronization

Press Ctrl + Shift + P → Run Sheet Language Global Helper: Sheet Connect Sync
On success you should see an information toast and/or the Output panel (channel Sheet Language Global Helper) with a line like: 동기화 완료! (N개 데이터, … 사용).
On failure, an error message appears; details are also logged to the same Output channel. After a failed remote fetch, the extension does not keep the previous dictionary: langData in extension storage is cleared to {}, the command returns an empty dictionary, and hover/inlay hints have nothing until the next successful sync.

Cursor / some editors: If the success toast does not appear, open View → Output, choose Sheet Language Global Helper in the dropdown, and check the latest log line (or the Debug Console for the same sync lines).

Synchronization Process

flowchart LR
    A[Run Command<br/>Ctrl+Shift+P] --> B{Data source?}
    B -->|Service Account JSON| C[Sheets API - OAuth]
    B -->|API Key| C
    B -->|JSON URL| D[JSON API<br/>Fetch Data]
    B -->|CSV URL| E2[CSV URL<br/>Fetch Data]
    
    C --> C1{allSheetNames?}
    C1 -->|Yes| C2[Fetch All Sheets]
    C1 -->|No| C3[targetSheetNames]
    C2 --> E[Parse & Save]
    C3 --> E
    D --> E
    E2 --> E
    
    E --> F[Local Storage]
    F --> G[Sync Complete]
    
    style A fill:#b3e5fc,color:#000000
    style C fill:#c8e6c9,color:#000000
    style D fill:#e1bee7,color:#000000
    style E2 fill:#ffe0b2,color:#000000
    style G fill:#c8e6c9,color:#000000

Export to a JSON file (Sheet Sync to JSON)

Use this when you want a file on disk (e.g. for code review, build scripts, or documentation) always reflecting the current sheet state.

Open a folder in VS Code (not only single files without a workspace folder).
Set workspaceExportJsonPath (e.g. language or src/locales). Required — if empty, the export command shows an error. No .. segments; use a path relative to the first workspace folder root.
Adjust targetSheetNames as needed (comma-separated). For export, entries are key code prefixes on the merged dictionary—e.g. WD,ST,MS → wd_lang.json, st_lang.json, ms_lang.json. With allSheetNames on, a CD… key on any tab can land in cd_lang.json. Only prefixes with at least one key create a file.
Run Sheet Language Global Helper: Sheet Sync to JSON. The command fetches the latest data from the sheet first (no separate Sync needed), then the folder (and any missing parent segments) is created if needed:
- all_language.json — only keys whose code starts with one of the targetSheetNames prefixes (same union as the per-prefix files)
- {lowercasePrefix}_lang.json — one per targetSheetNames entry that has at least one matching key (empty prefixes are skipped)

If the fetch step fails, JSON files are not written; you get a warning and the extension’s stored dictionary is cleared (same “no stale data after failure” rule as manual sync).

JSON shape (each file is an object: code key → language code → text):

{
  "WD000001": {
    "ko": "안녕하세요",
    "en": "Hello",
    "ja": "こんにちは"
  }
}

Multi-root workspaces: The export path is resolved under the first folder listed in the workspace. To use another folder, reorder folders or open that folder alone.

When the CD tab row count and cd_lang.json key count differ: The sync log line “tab CD: N body rows” counts rows on that CD sheet tab only. cd_lang.json instead lists every merged dictionary key whose code starts with the CD prefix, across all tabs that were fetched (e.g. with allSheetNames). Keys like CD… on other tabs are still bucketed into cd_lang.json, so the file can have more keys than the CD tab row count. If your Output still shows legacy lines such as 받은 딕셔너리, you are on an older build—update the extension and run Sheet Sync to JSON again.

View Multilingual Info via Hover

Hover over keys matching the patterns specified in hoverKeyPatterns to see multilingual information:

const code = "WD000001";  // Shows multilingual info on hover
getLang("ST000001");      // Also detects inside function calls
t("CD000001");            // Supports getLang, t, i18n, translate, etc.

Display Info: 🇰🇷 KO, 🇺🇸 EN, 🇯🇵 JA

Hover Example

Example: Hovering over WD000527 displays multilingual translations (EN: Client, KO: 클라이언트)

How Hover Works

flowchart TD
    A[Hover over Code] --> B{Pattern Match<br/>WD/ST/CD + Numbers}
    B -->|Matched| C[Query Local Storage<br/>Data]
    B -->|Not Matched| D[No Hover]
    
    C --> E{Data Exists?}
    E -->|Yes| F[Display Multilingual Info<br/>🇰🇷 KO, 🇺🇸 EN, 🇯🇵 JA]
    E -->|No| D
    
    F --> G[Show Hover Popup]
    
    style A fill:#b3e5fc,color:#000000
    style B fill:#ffe0b2,color:#000000
    style C fill:#e1bee7,color:#000000
    style F fill:#c8e6c9,color:#000000
    style G fill:#c8e6c9,color:#000000

Show Inline Translation (Inlay Hints)

After running sync, translations can be displayed inline (without hover) as inlay hints:

t("WD000001");         // → Hello (based on inlineTranslationLanguage)
t("프로그램 등록");      // → Program Registration (if your sheet key is the Korean text)

Inlay Hints Example

Note: Inlay hints are refreshed automatically after sync and when related settings change.

⚙️ Configuration

Settings use the prefix languageHelper. (e.g. languageHelper.sheetApiKey in settings.json).

Setting key	Description	Required	Default
`sheetServiceAccountJson`	Full text of the Google service account JSON key (Priority 1).	If using service account	(empty)
`sheetApiKey`	Google Sheets API key (Priority 2).	If using API without service account	(empty)
`sheetJsonUrl`	URL returning JSON dictionary (array or object shape) (Priority 2 alt.).	If using JSON URL	(empty)
`sheetId`	Spreadsheet ID (optional if `sheetUrl` contains the URL).	For Sheets API	(empty)
`sheetUrl`	Published CSV URL (Priority 3).	If using CSV only	(empty)
`allSheetNames`	If `true`, fetch every tab and merge into one dictionary. If `false`, fetch only tabs named in `targetSheetNames`.	Optional	`true`
`targetSheetNames`	When `allSheetNames` is off: comma-separated tab names to fetch. Always: same strings are key prefixes for JSON export + Sync Output tab lines.	Optional	`WD,ST,CD`
`hoverKeyPatterns`	Comma-separated patterns for hover/inlay (e.g. `WD,ST,CD`).	Optional	`WD,ST,CD`
`showInlineTranslation`	Show inlay hints for translations.	Optional	`true`
`inlineTranslationLanguage`	Language code for inlay text (`ko`, `en`, …).	Optional	`ko`
`workspaceExportJsonPath`	Required for export. Writes prefix files from the merged dictionary; with `allSheetNames` on this is not one JSON file per tab. Relative to first workspace root (e.g. `language`). Empty → error. No `..`.	Export	(empty)

How It Works

flowchart TD
    A[Run Sync Command] --> B{API Key Configured?}
    
    B -->|Yes| C[Use Google Sheets API]
    B -->|No| D[Use CSV URL]
    
    C --> C1{allSheetNames<br/>Checked?}
    C1 -->|Yes| C2[Fetch All Sheets<br/>Auto Query Sheet List]
    C1 -->|No| C3[targetSheetNames<br/>Fetch Specified Sheets Only<br/>e.g., WD,ST,CD]
    
    C2 --> E[Parse CSV Data]
    C3 --> E
    D --> E
    
    E --> F[Save to Local Storage]
    F --> G[Available for Hover]
    
    style B fill:#ffe0b2,color:#000000
    style C fill:#c8e6c9,color:#000000
    style D fill:#ffe0b2,color:#000000
    style C2 fill:#b3e5fc,color:#000000
    style C3 fill:#b3e5fc,color:#000000
    style F fill:#e1bee7,color:#000000
    style G fill:#c8e6c9,color:#000000

Summary:

API Key Available: Use Google Sheets API
- allSheetNames checked → Fetch all sheets
- allSheetNames unchecked → Fetch only sheets specified in targetSheetNames
No API Key: Use CSV URL (single sheet only)
targetSheetNames has two roles: tab list when allSheetNames is off; always the prefix list for JSON export and Sync Output tab lines. With all sheets merged, cd_lang.json is not limited to rows on the tab named CD.

📝 Spreadsheet Format

key	ko	en	ja
WD000001	안녕하세요	Hello	こんにちは
ST000001	감사합니다	Thank you	ありがとう

First row is used as header
key column is required; ko, en, ja are optional

🐛 Troubleshooting

"API Key is Invalid"

Verify Google Sheets API is enabled
Check if the sheet is shared with "Anyone with the link"

"Sheet ID is Incorrect"

Verify the ID is correctly extracted from the spreadsheet URL

"Invalid URL" / URL errors

Use a full URL with https:// for Sheet Json Url and Sheet Url, or a host-only value (the extension may prepend https://).
Do not paste a spreadsheet ID into the CSV/JSON URL fields; use Sheet Id / Sheet Url (full link) for the API path.

Export JSON: "no workspace folder"

Open File → Open Folder so a folder is the workspace root, then run the export command again.

Export JSON: path setting empty / error

Set workspaceExportJsonPath (e.g. language). The export command requires this; it errors if blank.

Export JSON: "no data" / warning about no matching keys

Check that targetSheetNames matches the key prefixes in your sheet (e.g. WD,ST,CD). The export fetches fresh data automatically, so a separate Sync is not required.
If you recently had a failed sync or export fetch, stored data may be empty until the next successful Sheet Connect Sync.

Hover Not Working

Run Sheet Connect Sync successfully after install or a failed fetch (see above)
Verify you're using keys starting with WD, ST, CD in your code

🛠️ Development

Requirements

Node.js 20.x or higher (Required)
pnpm (or npm)

Installation and Build

# Install dependencies
pnpm install

# Development mode (watch)
pnpm run watch

# Production build
pnpm run build

Testing

Press F5 to run Extension Development Host
Create a test file in the new window
Hover over codes like WD000001 to verify

📦 Deployment

Prerequisites

Create account/organization on Azure DevOps
Generate Personal Access Token (Marketplace > Manage permission required)

Deployment Process

flowchart TD
    A[Modify Code] --> B[Update package.json<br/>version]
    B --> C[pnpm run build<br/>Production Build]
    C --> D{Build Success?}
    D -->|Failed| E[Fix Errors]
    E --> C
    D -->|Success| F[vsce publish<br/>--no-dependencies]
    F --> G[Enter Personal Access Token]
    G --> H[VSIX Packaging<br/>Auto Execute]
    H --> I[Upload to Marketplace]
    I --> J[Deployment Complete<br/>Reflected in a few minutes]
    
    style A fill:#ffe0b2,color:#000000
    style B fill:#b3e5fc,color:#000000
    style C fill:#ffe0b2,color:#000000
    style F fill:#e1bee7,color:#000000
    style J fill:#c8e6c9,color:#000000

Deployment Commands (local)

@vscode/vsce is a devDependency, so a global vsce install is optional.

pnpm install

# ESLint + webpack (production) + VSIX at repo root (*.vsix)
pnpm run release:vsix

# Publish to the VS Marketplace (Azure DevOps PAT required)
export VSCE_PAT="<YOUR_PERSONAL_ACCESS_TOKEN>"
pnpm run publish:vsix

publish:vsix reads the VSCE_PAT environment variable (see package.json scripts).

VSIX via GitHub Actions

Add repository secret VSCE_PAT under Settings → Secrets and variables → Actions (only needed to publish from CI).
Actions → Release extension → Run workflow
- Default: build VSIX and upload it as a workflow artifact.
- Set publish_marketplace to true to also run pnpm run publish:vsix (requires VSCE_PAT).

Update Deployment

⚠️ Important: Bump package.json version and update CHANGELOG.md before each marketplace release.

pnpm run release:vsix
export VSCE_PAT="..."
pnpm run publish:vsix

Apply Icon

Add icon.png to root folder (128x128 recommended)
Add "icon": "icon.png" to package.json
Update version and redeploy

📄 License

MIT

🤝 Contributing

Issues and pull requests are welcome!

Made with ❤️ for better multilingual development experience

Sheet Language Global Helper

language-global-helper

Sheet Language Global Helper

🌐 Languages

🔗 Links

✨ Features

Complete Workflow

🚀 Getting Started

Installation

Configuration

Method 1: Service Account JSON (Priority 1, recommended for private sheets)

Method 2: Google Sheets API Key (Priority 2)

Method 3: JSON API URL (Priority 2 alternative)

Method 4: CSV URL (Priority 3)

Configuration Method Comparison

📖 Usage

Command Palette

Data Synchronization

Synchronization Process

Export to a JSON file (Sheet Sync to JSON)

View Multilingual Info via Hover

Hover Example

How Hover Works

Show Inline Translation (Inlay Hints)

Inlay Hints Example

⚙️ Configuration

How It Works

📝 Spreadsheet Format

🐛 Troubleshooting

"API Key is Invalid"

"Sheet ID is Incorrect"

"Invalid URL" / URL errors

Export JSON: "no workspace folder"

Export JSON: path setting empty / error

Export JSON: "no data" / warning about no matching keys

Hover Not Working

🛠️ Development

Requirements

Installation and Build

Testing

📦 Deployment

Prerequisites

Deployment Process

Deployment Commands (local)

VSIX via GitHub Actions

Update Deployment

Apply Icon

📄 License

🤝 Contributing