An in-memory lookup service for product prices, item names, and SKU / part-number / product-code cross-references — built for Google Sheets batch formulas.
Pull product price, name, and codes straight into your sheet by typing one short command. No scripts, no setup.
Look at the menu bar across the top.
This opens the Google Workspace Marketplace. If your admin already set it up for everyone, you'll instead see PIM V3 directly under Extensions — then skip to “Use it”.
Approve the permission screen (it only lets the tool fetch product data). Done.
Google needs 2–10 minutes to switch the commands on. Try too early and you'll see Unknown function — that's normal. Reload the sheet and wait. Details →
Fastest way to try without installing anything: open the playground sheet — the commands already work there. Use File → Make a copy to play in your own copy.
For example, paste your SKUs down column A, starting at cell A2.
For example, cell B2.
The whole column fills by itself. Pick what you want below and copy it in.
These assume your codes are in A2:A100. Different spot? Just change that part — see the table below.
💰 Get the price (Rupiah)
=PIM_PRICE(A2:A100,"sku","het","IDR")🏷️ Get the product name
=PIM_ITEM_NAME(A2:A100,"sku")🔁 Turn a part number into a SKU
=PIM_FIELD(A2:A100,"partNumber","sku")✅ Check if a code exists
=PIM_IS_FOUND(A2:A100,"sku")| A — your codes | B — you typed =PIM_ITEM_NAME(A2:A4,"sku") | |
|---|---|---|
| 2 | 1KY-F11D0 | Brake Pad | Front |
| 3 | 5TL-E5350 | Oil Filter |
| 4 | … | … (fills automatically) |
| A2:A100 | Where your codes are. Codes in column C, rows 5–200? Write C5:C200. |
| "sku" | What kind of code you pasted: "sku", "partNumber", or "productCode". |
| "het","IDR" | Price kind & currency (price command only). Kind: het / hlp / hot. Currency: IDR, USD, SGD, THB, MYR, VND, BDT… |
3 rules to remember
The old sheet hit the API once per cell — 100 rows meant hundreds of calls, each running a heavy database view.
One array formula resolves a whole column in a single HTTP request. 100 rows = 1 call.
All product data is loaded into RAM and refreshed every few minutes. Lookups are microseconds; the database is barely touched.
Part numbers are matched by whole token — no missed sets, no substring false-positives.
Every data endpoint needs Authorization: Bearer <token> — a static API key or a valid HS256 JWT (same secret as the main API). /healthz and this page are public.
Put one array formula at the top of an output column — it fills the whole column. Identifier = sku, partNumber, or productCode.
Just installed the add-on? Give it a few minutes. Google registers the custom functions per document, and it can take 2–10 minutes (longer for org-wide installs) before PIM_PRICE, PIM_FIELD & co. appear.
If you see Unknown function: 'PIM_FIELD' — it's just the registration lag, not an error in your formula:
=PIM_ and wait until it shows in autocomplete before using it.Try it in the playground sheet ↗
Unit price. type: het·hlp·hot·marketplace. currency: IDR·USD·SGD·THB·MYR·VND·BDT·EUR·PHP.
=PIM_PRICE(A2:A101,"sku","het","IDR")Item name incl. variant labels.
=PIM_ITEM_NAME(A2:A101,"sku")Cross-reference identifiers.
=PIM_FIELD(A2:A101,"partNumber","sku")TRUE / FALSE existence check.
=PIM_IS_FOUND(A2:A101,"sku")| Old (per cell) | New (per column) |
|---|---|
| GET_PRODUCT_PRICE(id,val,type,cur) | PIM_PRICE(range,id,type,cur) |
| GET_ITEM_NAME(id,val) | PIM_ITEM_NAME(range,id) |
| GET_SKU_BY_PARTNUMBER(val) | PIM_FIELD(range,"partNumber","sku") |
| GET_PARTNUMBER_BY_SKU(val) | PIM_FIELD(range,"sku","partNumber") |
| GET_PRODUCT_CODE_BY_SKU(val) | PIM_FIELD(range,"sku","productCode") |
| IS_FOUND(val,type) | PIM_IS_FOUND(range,type) |
Base URL = this host. Results in a batch response align 1:1 with the input order.
| Endpoint | Auth | Purpose |
|---|---|---|
| GET/healthz | — | Health + snapshot stats |
| POST/v1/lookup | bearer | Batch lookup (used by Sheets) |
| GET/v1/price | bearer | Single price (query params) |
| GET/v1/name | bearer | Single item name |
| GET/v1/field | bearer | Single cross-reference |
| POST/admin/refresh | refresh token | Force a snapshot rebuild |
POST /v1/lookup
{
"identifier": "sku", // sku | partNumber | productCode
"values": ["ABC1", "ABC2"],
"want": "price", // price | name | field
"priceType": "het",
"currency": "IDR",
"select": "partNumber" // when want=field
}{
"want": "price",
"builtAt": "2026-06-27T..Z",
"results": [
{ "value":"ABC1", "found":true, "result":125000 },
{ "value":"ABC2", "found":false, "result":null }
]
}Runs a live POST /v1/lookup against this server. Token stays in your browser.
Part numbers are slash-joined sets. They're matched by whole token, case-insensitively.
| 33203-BPV01-SLDRD | ✅ resolves 33203-BPV01-SLDRD/33203-B7401-SLDRD |
| 62300-BPV77-8AAC1 | ✅ does not match 62300-BPV77-8AAC13 |
When a token maps to several products, the lowest idProduct wins. sku is a single value (never split).
Snapshot refreshes automatically every few minutes. Force it after a price update:
curl -X POST "https://<host>/admin/refresh?token=$REFRESH_TOKEN"