OilPriceAPI for Google Sheets™

Deployment-ready Editor add-on for source-aware OilPriceAPI formulas in Google Sheets™.

The add-on is publicly available in Google Workspace Marketplace. The public listing currently points to immutable Apps Script version 11 (1.2.2). Runtime 1.3.1 is a release candidate until its installed-add-on smoke and Marketplace version update are recorded.

Dataset access, history, freshness, and limits depend on the API key, source, and account entitlement. Review the versioned product-facts contract before publishing derived product claims.

Excel-equivalent formulas

Google Sheets custom-function names cannot contain a dot. These underscore names are the direct equivalents of the Excel add-in surface:

Google Sheets	Excel	Result
OILPRICE_PRICE(code)	OILPRICE.PRICE(code)	Latest numeric API value
OILPRICE_GET(path, query)	OILPRICE.GET(path, query)	Allowlisted API table
OILPRICE_CODES()	OILPRICE.CODES()	Available commodity-code table
OILPRICE_STATUS(code)	OILPRICE.STATUS(code)	API freshness state
OILPRICE_UNIT(code)	OILPRICE.UNIT(code)	Currency/unit
OILPRICE_INFO(code)	OILPRICE.INFO(code)	Source, timestamp, unit, and freshness table

Examples:

=OILPRICE_PRICE("WTI_USD")
=OILPRICE_INFO("WTI_USD")
=OILPRICE_GET("/v1/prices/latest", "by_code=WTI_USD")

The original OILPRICE(code) formula remains supported for existing sheets.

Additional Sheets formulas

Function	Behavior	Cache
OILPRICE(code)	Backward-compatible numeric latest price	Tier-aware shared cache
OILPRICE_TABLE(range)	Up to 25 latest prices in one spilled request	Tier-aware shared cache
OILPRICE_HISTORY(code, days)	Source timestamp and price rows	1 hour
OILPRICE_CONVERT(code)	Reference USD/MMBtu conversion for documented mappings	Latest-price cache
BUNKER_PRICE(port, fuel)	Numeric Data Connector bunker price	5 minutes
BUNKER_PORT_PRICES(port)	Bunker-price table with units and timestamp	5 minutes
FUTURES_PRICE(contract)	Numeric first-contract price	5 minutes
FUTURES_CURVE(contract)	Month, price, and change rows	5 minutes
RIG_COUNT(type)	Oil, gas, total, or source-dated table	1 hour

Runtime and security contract

• API keys are stored in Apps Script properties scoped to the current spreadsheet. The primary copy uses document properties; a compatibility copy uses the spreadsheet owner's user properties with the spreadsheet ID in the property name so Google's custom-function authorization context can retrieve the key without making it available to another spreadsheet. The spreadsheet owner should configure the key. Editors of that spreadsheet can cause add-on formulas to make requests with the configured key.
• Unscoped keys saved by releases before Apps Script version 6 are deliberately not read because they cannot be tied to one spreadsheet. After upgrading from such a release, open each intended spreadsheet and save the key again from OilPriceAPI > Configure API Key.
• The sidebar receives only configured/not-configured state; it never reads the stored key into browser-side HTML.
• Generic GET calls are restricted to the same reviewed endpoint catalog as the Excel preview.
• Credential-shaped query keys are rejected before any network request.
• Missing, invalid, locked, rate-limited, timed-out, malformed, and empty responses fail with worksheet-readable recovery text. Terminal failures are negatively cached and a connection check bypasses the cache so a paid upgrade recovers immediately.
• Latest values use a document cache and a lock-protected miss path. Free, paid, and enterprise cache lifetimes follow the API's canonical tier header.
• Latest-request diagnostics contain endpoint path, status, duration, timestamp, and optional request ID—never the API key or query string.
• The manifest requests only current-sheet, external-request, and container-UI scopes and restricts URL fetches to api.oilpriceapi.com.

Validate locally

Node.js 20 or newer is required.

npm ci
npm test
npm run validate

The validation suite covers formula parity, credential lifecycle, negative auth/entitlement/quota paths, response-shape drift, stale cache, source metadata, Data Connector filtering and sheet output, endpoint/query allowlisting, deployment packaging, Marketplace asset dimensions, public claims, and secret scanning.

For a production API smoke:

OILPRICEAPI_KEY="your non-customer test key" npm run test:live

The standard smoke skips the account-gated Data Connector checks. Run those with an entitled non-customer account and known valid filters:

OILPRICEAPI_KEY="your non-customer test key" \
OILPRICEAPI_DATA_CONNECTOR_SMOKE=1 \
OILPRICEAPI_DATA_CONNECTOR_PORT="SINGAPORE" \
OILPRICEAPI_DATA_CONNECTOR_FUEL="VLSFO" \
npm run test:live

The live-smoke script does not print the key or filter values.

Deploy

Follow DEPLOYMENT_GUIDE.md. The short operator sequence after the Apps Script project exists is:

npm ci
npm run clasp:login
read -r "OPA_SCRIPT_ID?Apps Script ID: "
npm run clasp:configure -- "$OPA_SCRIPT_ID"
npm run deploy:push
npm run deploy:version -- "OilPriceAPI for Google Sheets 1.3.1 cache-isolation recovery"

Editor add-on publication uses the Apps Script script ID and version number, not a web-app deployment ID. Test the Editor add-on before entering that version in the Marketplace SDK.

Prepared listing copy, scope justifications, required screenshot shots, and generated assets are in MARKETPLACE_LISTING.md.

The reusable launch and troubleshooting lessons are in GOOGLE_MARKETPLACE_PLAYBOOK.md. The separate public-video acquisition experiment is in YOUTUBE_PROMOTION.md.

The five follow-on products and rollout order are in PORTFOLIO.md. Their immutable Apps Script candidates and remaining Google Console gates are tracked in PORTFOLIO_SUBMISSION_READINESS.md.

Canonical links

• Product facts
• Workspace Marketplace listing
• API documentation
• Pricing and dataset access
• Data usage
• Apps Script quotas

License

MIT