[Release] Google v13 pin and clean-install certification

[karlwaldman]

Outcome

Certify and release the current Google Sheets runtime through one controlled immutable Apps Script version, preserve the working version 11 rollback, and obtain a clean-account published-artifact install/formula receipt. This is the single canonical tracker for Google OAuth, Marketplace version selection, installed-account certification, and the missing clean-account identity. Do not open parallel release/OAuth/Marketplace tickets.

Owner: @karlwaldman

Verified production state - 2026-08-11

• Public Marketplace listing: https://workspace.google.com/marketplace/app/oilpriceapi_for_google_sheets/991152473434
• Google Cloud project/app ID: oilpriceapi-sheets-addon / 991152473434.
• Production Apps Script project: 1rlVWvciYu-wzqnY009I3oW-08ZPazYK1snrrMg9NNY7c5WBSkUK8W2Hb.
• Marketplace App Configuration is Public and references immutable Apps Script version 11.
• Public runtime represented by version 11: 1.2.2.
• Google Auth Platform currently reports: Your app has been verified by Google.
• Marketplace/OAuth scope parity is current: default userinfo.email and userinfo.profile, plus the only three functional scopes spreadsheets.currentonly, script.external_request, and script.container.ui.
• Immutable version 12 contains runtime 1.3.0, failed later credential/cache-isolation review, and was never selected for the public listing.
• Current repository release candidate: runtime 1.3.1, main 5eb7121c80c90279b2c08f4c231db18161b30958.
• Workflow hardening PR ci: harden Sheets release workflows #34 changed only CI/test dependencies and merged as 5eb7121c80c90279b2c08f4c231db18161b30958; the four Apps Script runtime files remain byte-identical.
• Main validation: https://github.com/OilpriceAPI/google-sheets-addin/actions/runs/31499079799
• Main docs deployment: https://github.com/OilpriceAPI/google-sheets-addin/actions/runs/31499079777
• Fresh-clone proof: 98/98 tests, dependency audit 0 at moderate, clasp 3.3.0, four-file deployment package, assets, portfolio packages, public claims, and secret scans green.
• Current deployable file SHA-256 values:
  • Code.gs: 03582b77bd1604366a6fcd44e1fff23d67c792bc9abcd9f36af299f3b655a09a
  • Sidebar.html: a9bdf7c923a0d38636bec9b04a280b1945500a07fea52f2ff236e7d74efea3ee
  • FetchDialog.html: 78f4f2fad0f846b347dc27ac74160624e327599709b854981b3517c2ae61e542
  • appsscript.json: 45f51456777cc4f9760bc4998c2f24492688817d3182850e54d3d2433844521e

Do not create an immutable version or change Marketplace App Configuration until the API v2/runtime release owner provides an explicit production-green signal. The public product contract must say 50 requests per day; never infer a monthly window from legacy fields.

Immutable v13 release checklist

• Record the explicit API v2 production-green receipt. Independently verify the canonical limits endpoint reports 50 requests per day and that public product facts no longer expose the stale 50-per-month semantics.
• Verify origin/main is still the exact reviewed SHA above, main CI is green, open release PRs are zero, and the four deployable hashes still match. If main changes, stop and re-run the full gate; do not cut from an unreviewed SHA.
• Confirm the protected apps-script-production environment is targeting the unchanged production Script ID and that local/CI clasp credentials are not printed or committed.
• Dispatch apps-script-release.yml from main with description OilPriceAPI for Google Sheets 1.3.1 cache-isolation recovery.
• Require the run to report audit 0, 98/98 tests, exactly four pushed files, and Created version 13. If Google returns any other version, record the actual immutable number and stop calling it v13.
• Clone the created immutable version into a new scratch directory and compare its four files byte-for-byte with the reviewed package/hashes above. Do not rely only on the successful push log.
• Capture the workflow URL, head SHA, immutable number/description, version-list receipt, and remote-clone comparison here.
• Preserve Marketplace version 11 and its current App Configuration values as the rollback receipt before any draft edit.
• Change only the Marketplace draft script-version field from 11 to the proven candidate; preserve Script ID, Public visibility, install settings, scopes, and developer fields. Save Draft only. Do not publish the Store Listing yet.
• Verify the installed tester actually loads runtime 1.3.1; a Latest Code editor deployment or a #NAME? formula result is not candidate evidence.

Installed-account disposable-sheet smoke

Use the already-installed authorized tester account and a new blank spreadsheet named OPA Marketplace Candidate v13 Smoke 2026-08-11. Do not uninstall the existing add-on, touch existing sheets, use customer data, put an API key in a cell/URL/screenshot, or expose account/contact information.

• Open the installed OilPriceAPI add-on, choose Use in this document when offered, refresh once, and confirm exactly one add-on context/menu.
• Open the sidebar and confirm runtime 1.3.1/candidate identity. The new spreadsheet must initially report No key is stored; a key from another spreadsheet must not cross into it.
• Before saving a key, =OILPRICE_PRICE("WTI_USD") must return actionable #AUTH_REQUIRED without an API request.
• Save only the approved synthetic non-customer key. Confirm the input clears, the stored value is never displayed, and Test connection reports Connection and response schema verified.
• Run =OILPRICE_PRICE("WTI_USD"); require a finite numeric value.
• Run =OILPRICE_UNIT("WTI_USD"), =OILPRICE_STATUS("WTI_USD"), and =OILPRICE_INFO("WTI_USD"); require non-empty unit/source, parseable source timestamp, freshness state, and no fabricated metadata.
• Run =OILPRICE_HISTORY("WTI_USD",1); require timestamp/value rows with parseable timestamps and finite numeric prices.
• Run =OILPRICE_CODES() and =OILPRICE_GET("/v1/prices/latest","by_code=WTI_USD"); require readable non-empty tables.
• Put WTI_USD, BRENT_CRUDE_USD, and one valid third code in a three-cell range and run =OILPRICE_TABLE(range); require one stable spilled batch table and verify execution/request evidence does not show per-cell request fan-out.
• Run =OILPRICE_GET("/v1/users/me","") and a query containing api_key; both must fail locally before an API request.
• Save a deliberately invalid synthetic key, run Test connection, and require actionable invalid/revoked-key recovery. Restore the valid fixture, run Test connection, and require the same price formula to recover immediately; this proves generation invalidation after key replacement.
• Run an invalid commodity code and require readable #INVALID_CODE/#NO_DATA, not a blank success or raw exception.
• If approved non-customer entitlement and exhausted-quota fixtures already exist, verify 403 upgrade and 429 retry-later recovery plus Test Connection cache clearing. Do not create a customer identity, change billing, or burn the 50/day allowance merely to manufacture these states; the exact 98-test suite remains the required deterministic proof otherwise.
• Confirm the sidebar does not display a monthly request counter or reinterpret a legacy requests_this_month value.
• Refresh Last request diagnostic and verify it contains bounded code/status/duration/path/timestamp only: no API key and no query string.
• Delete the key. Confirm the sidebar returns to No key is stored, diagnostics clear, and the formula returns #AUTH_REQUIRED again.
• Review Apps Script Executions and API attribution for the smoke window: no raw key, query values, unexpected exception loop, repeated retry storm, unexplained 4xx/5xx, or excessive request count.
• Capture redacted browser screenshots of sidebar connection, numeric/info/history/batch results, invalid-key recovery, and final no-key state. Record spreadsheet ID/URL only in an access-controlled receipt if it reveals the tester identity.
• Trash the disposable sheet after evidence is preserved.

This installed-account smoke is necessary candidate evidence, but it does not replace a clean-account install.

Publication and clean-account gate

• Only after every candidate check passes, publish/select the tested immutable version in Marketplace. Record the exact console state and timestamp; do not claim the new runtime is public from a repo merge or Apps Script version alone.
• Repeat the formula/recovery smoke through the public listing and review production executions/logs.
• Use a pre-authorized dedicated clean test identity for a new Marketplace install and consent flow. No such separate identity is currently available in the repository, environment, or authorized browser session; do not create/enroll a customer or personal identity to fill this gap.
• Attach the clean-account install, consent, Use in this document, connection, formula, recovery, and key-deletion receipts.
• Update repository/operator/website version claims only from verified console and public-runtime state.

Next owner action

@karlwaldman: after the explicit API v2 green signal, execute the immutable workflow and installed-account candidate smoke above. Separately provision or authorize a dedicated non-customer Google test identity for the clean-install gate without using customer data.

Closure rule

Keep this issue open until the tested runtime is selected in public Marketplace, the published-artifact smoke is green, and a separate clean-account install/formula receipt is attached. Do not hide the missing identity or infer Google state without console/public evidence.

[karlwaldman]

Status handoff is now reconciled to the shipped state. PR #21 records immutable Apps Script version 10, the merged public verification pages, production deployment #30434284989, clean cache-busted URL checks, clean runtime logs, and the explicit fact that OAuth verification has not yet been submitted. The issue body now contains the exact remaining publisher-session checklist and remains open pending real Google submission evidence.

[karlwaldman]

Google rejection and live test update — 2026-07-30

Current external state

• Google Workspace Marketplace Reviews rejected the submission on 2026-07-29 and placed review on hold because OAuth verification is still pending.
• Google explicitly instructed: do not resubmit the Marketplace listing until OAuth verification finishes.
• Google requested (1) a reviewer-accessible continuous end-to-end video and (2) a test API key.
• No current api-oauth-dev-verification-reply+…@google.com case exists for Cloud project 991152473434; the OAuth verification submission still needs to be completed in Google Cloud.
• Current reviewed release is Apps Script version 11 / runtime 1.2.2, merge 3747fef2d09474c5b610bfa7154c7134a16e6a9f. Head was restored to that exact source after temporary diagnostics, and all 54 validations passed before push.

Live Editor Add-on evidence

Verified in multiple fresh, owner-created spreadsheets using both immutable version 11 and Latest Code:

• exactly one OilPriceAPI for Sheets test context;
• Config = Installed for current user / AuthMode.NONE, followed by a real menu interaction, authorization, and document enablement;
• API key save and Test Connection succeed;
• sidebar Fetch latest available prices writes live price, currency, unit, source, source timestamp, and retrieved-at data;
• custom functions do not register in fresh test documents: no autocomplete and both =OILPRICE("BRENT_CRUDE_USD") and =OILPRICE_PRICE("BRENT_CRUDE_USD") return #NAME?;
• the failure occurs before Apps Script formula code executes, so it is not an API-key or OilPriceAPI request failure.

Google’s unpublished Editor Add-on test-deployment activation/namespace registration is not exposed through the public Apps Script or Sheets APIs. The repository already automates runtime behavior and production API smoke; API-written formulas cannot bypass an unregistered Sheets function namespace.

Required sequence from here

1. Create a dedicated, non-customer reviewer API key with a conservative quota and no customer data. Do not post it to GitHub or include it in the video.
2. Record one continuous reviewer-accessible video showing the public homepage/privacy page, OAuth consent and all requested permissions, add-on menu/sidebar, masked key save, successful connection, live sidebar fetch into the active spreadsheet, help/support, key deletion, and return to no-key state.
3. Do not claim the custom-formula step passed in the unpublished test host; preserve the #NAME? evidence as a Google test-host blocker.
4. Submit OAuth Branding/Data Access verification for Cloud project 991152473434 with the video URL and exact scope justifications. Preserve the receipt and unique verification email/case ID here.
5. Reply to the existing Marketplace review-start/rejection thread with the accessible video URL and the dedicated reviewer key using an appropriate secure reviewer channel.
6. Wait for OAuth approval. Only then update Marketplace to Apps Script version 11, verify exact scope parity, and resubmit.
7. As soon as Google provides a true installed Marketplace candidate, run the automated production smoke plus the first real installed-add-on formula smoke before public availability claims.

This supersedes the stale issue text that still says Marketplace is in review and version 10 is the prepared release.

[karlwaldman]

Marketplace draft-install E2E verification passed on 2026-07-30.

• Added the publisher Gmail as a Marketplace draft tester with the OilPriceAPI contact form as the opt-out URL.
• Installed the Marketplace draft into the tester account.
• In a fresh spreadsheet, explicitly selected Extensions > Add-ons > Manage add-ons > OilPriceAPI for Google Sheets > Use in this document, then refreshed.
• Custom-function autocomplete registered correctly after the real Marketplace install was enabled for the document.
• Verified the spreadsheet-scoped reviewer key, green connection/response-schema check, live OILPRICE_PRICE("WTI_USD") numeric output, OILPRICE_CODES() spilled catalog, and sidebar batch-fetch path.
• This isolates the earlier #NAME? behavior to Apps Script Editor add-on test-deployment registration; the same source works through the actual Marketplace draft installation path.

Evidence screenshot: local operator capture Screenshot 2026-07-30 at 8.35.22 AM.png (contains no API key). Next action: record/upload the OAuth end-to-end video from this working draft installation, then submit OAuth verification.

[karlwaldman]

OAuth review video ready — 2026-07-30

Reviewer-accessible YouTube URL: https://youtu.be/FakNSmBddhE

Verified upload metadata via YouTube oEmbed:

• title: OilPriceAPI Google Sheets OAuth Review 2026 07 30
• publisher: Karl Waldman

The final 1080p video is 2:33 and demonstrates the Marketplace installation/OAuth grant, the complete English consent screen with the three requested functional scopes, document activation, masked credential state, successful connection/schema validation, formula autocomplete and live WTI result, sidebar batch fetch into the active spreadsheet, and source metadata output.

Local visual QA removed unrelated notifications, password-manager/autofill UI, Terminal content, and macOS capture controls. No API key is exposed.

Next action: enter this URL in Google Auth Platform verification, submit once, and preserve the receipt/status/case ID here. Marketplace resubmission remains blocked until OAuth approval, per Google’s instruction.

[karlwaldman]

OAuth verification submitted — 2026-07-30

Google Auth Platform confirmation received for Cloud project oilpriceapi-sheets-addon (991152473434):

The Trust and Safety team has received your form. They will reach out to you via your contact email if needed. The review process can take up to 4–6 weeks. Expect the first email from our Trust and Safety team within 3–5 days. Your last approved consent screen is still in use.

Current console state:

• Branding: verified and shown to users
• Data Access: submitted / under review
• Homepage requirements: under review
• Demo URL: https://youtu.be/FakNSmBddhE
• Functional scopes submitted: spreadsheets.currentonly, script.external_request, and script.container.ui
• Restricted scopes: none

Local redacted submission proof: Screenshot 2026-07-30 at 9.43.30 AM.png.

Per Google’s Marketplace rejection instruction, do not resubmit the Workspace Marketplace listing until OAuth verification is approved. Monitor the project contact email and respond in the same Trust and Safety case.

[karlwaldman]

Reusable launch documentation is now captured in PR #23: #23

It includes:

• the end-to-end Google Workspace Marketplace/OAuth launch playbook and troubleshooting lessons
• the proven custom-function registration flow (Marketplace draft install + Use in this document)
• reviewer evidence, credential-context, scope, version, and duplicate-install guidance
• a separate public YouTube promotion and attribution plan with trademark-safe copy and UTM measurement

Validation is green (54/54 checks). The OAuth reviewer video remains Unlisted; the proposed public video is a separate search/discovery asset.

[karlwaldman]

Two corrections to this issue's stated plan, verified 2026-07-31. PR #26 updates the docs.

1. The repin target is version 11, not version 10

This issue's step 3 says "update the Marketplace configuration to the reviewed Apps Script release," and the docs named that as v10. That's stale.

	Cut	Runtime	#19	#22
v9 — pinned in the draft	07-28 16:53 EDT	—	❌	❌
v10	07-29 03:58 EDT	1.2.1	✅	❌
v11 — release candidate	07-29 18:01 EDT	1.2.2	✅	✅

PR #22 merged 2026-07-29T22:01:01Z, after v10 was cut. v11 was cut at 18:01 EDT — the same instant (UTC−4) — and its Code.gs reads ADDON_VERSION = '1.2.2' (read directly from Project History).

This matters because #22 fixes custom functions returning #AUTH_REQUIRED after a successful sidebar key save. Publishing v9 would ship the add-on's core formula path in that state.

2. App Configuration does not lock during review

This issue and the docs both said to wait for Google to unlock App Configuration. That gate doesn't exist.

DOM inspection of the console: every App Configuration input reports disabled: false, readOnly: false, no aria-disabled. The Version field is a free-text <input type="text"> holding 9. Only the Store Listing tab locks ("The draft is in review and can't be edited", with a "Cancel review" control).

So step 3 is not blocked and never was.

Recommended sequence

1. Cancel review on the Store Listing tab
2. App Configuration → Version 9 → 11 → Save Draft (Script ID unchanged: 1rlVWvciYu-wzqnY009I3oW-08ZPazYK1snrrMg9NNY7c5WBSkUK8W2Hb)
3. Verify the saved value reads 11
4. Resubmit

Cancel before editing — the two tabs report contradictory states and how the console reconciles a config change against an in-flight listing review is unknown. Cancelling first costs nothing, since nothing has ever been published.

test/public-claims.test.js now guards both corrections so they can't silently regress.

[karlwaldman]

Repin confirmed and resubmitted — 2026-08-01.

Verified in the Cloud console for project 991152473434:

Field	Value
Sheets add-on Project Script ID	1rlVWvciYu-wzqnY009I3oW-08ZPazYK1snrrMg9NNY7c5WBSkUK8W2Hb (unchanged)
Sheets add-on script version	11

The Marketplace draft is no longer pinned to version 9. The submission now references version 11 / runtime 1.2.2, which contains both PR #19 (OAuth verification prep) and PR #22 (custom-function credential fix).

This closes the risk recorded earlier in this issue: publishing version 9 would have shipped the add-on with custom functions returning #AUTH_REQUIRED after a successful sidebar key save.

Listing resubmitted for Google review. Nothing is published yet.

Still open on this issue

1. Search Console ownership confirmation for oilpriceapi.com by a Cloud project owner/editor
2. The continuous end-to-end OAuth demonstration recording
3. OAuth branding and data-access verification submission (still not submitted)
4. Preserve the confirmation receipt here

Docs are current as of PR #26 — DEPLOYMENT_GUIDE.md, MARKETPLACE_LISTING.md, and OAUTH_VERIFICATION.md all name v11/1.2.2 as the release candidate, and test/public-claims.test.js guards that plus the corrected "App Configuration does not lock during review" rule.

[karlwaldman]

OAuth demonstration video located and verified accessible — 2026-08-01.

https://www.youtube.com/watch?v=FakNSmBddhE

Use that watch URL in the verification form, not the studio.youtube.com/video/FakNSmBddhE/edit link (that is the private editing interface and reviewers cannot open it).

Accessibility check (unauthenticated fetch, 2026-08-01)

Property	Value
HTTP	200
isPrivate	false
isUnlisted	true — acceptable; Google reviewers reach it by link
status	OK, playableInEmbed: true
oEmbed	resolves publicly
Title	OilPriceAPI Google Sheets OAuth Review 2026 07 30
Length	153s (2m33s)
Channel	@KarlWaldman

A private video is one of the most common OAuth verification bounce reasons. This one is unlisted, so that risk is cleared.

Two things to check before/if this is submitted

1. Length vs. the runbook. The demo runbook in OAUTH_VERIFICATION.md lists ~10 required beats — public homepage + privacy link, blank sheet, authorization from an account that has not already granted permissions, the complete consent screen in English showing all requested permissions, sidebar data notice, key save, test connection, OILPRICE_INFO, the fetch dialog, and key deletion. 2m33s is tight for all of that. Google commonly rejects demos that skip the full consent screen or omit a scope's justifying flow. Worth checking the recording against that list rather than assuming coverage — I have not watched it.
2. Channel ownership. The video is on the personal @KarlWaldman channel rather than an organization-controlled account. Google accepts this, but it is a bus-factor note given DEPLOYMENT_GUIDE.md §1 calls for org-controlled publisher assets.

Still unconfirmed

Whether OAuth branding and data-access verification have actually been submitted to Google, and whether a submission receipt exists. The video existing is not the same as the verification being submitted. If it has been submitted, capture the confirmation text, date, and any case/reference ID here.