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

These audit, generated-asset, runtime, source-claim, secret, portfolio, and deployment-package gates also run every Monday in GitHub Actions so newly disclosed dependency issues are found when the repository is otherwise quiet. This package has no production npm dependencies: clasp, image generation, and validation packages are development/release tooling, while the shipped runtime is the reviewed Apps Script deployment package.

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