Advanced

Google Sheets

=GLOOM and =GLOOMH in Google Sheets: live prices, fundamentals and history with Bloomberg tickers and field names.

Setup

StepDo
1Create a key with Market data only access in Cloud settings, Agents. It needs Gloom Pro. A broader key is refused, since every editor of the sheet can read it.
2In your sheet, open Extensions > Apps Script.
3Replace everything in Code.gs with gloom.sh/sheets/gloom.js and save.
4Reload the sheet, then Gloom > Set API key and paste the key. It is tried once and not saved if it is broader than Market data only, revoked or on the free plan. The first time, Google asks you to authorize your own script and may say it is unverified: choose Advanced, then Go to your project. If the key prompt does not open after that, choose Set API key again.
5Type =GLOOM("AAPL US Equity", "PX_LAST") in any cell.

Sample

A Bloomberg workbook ports with find and replace: BDP( to GLOOM(, BDH( to GLOOMH(. Bloomberg tickers answer as Bloomberg does, down to London prices in pence (CRNCY is GBp), so the workbook's own arithmetic still holds.

Coming from Bloomberg? BDP and BDH next to their Sheets formulas.

ABCD
1TickerPX_LASTCUR_MKT_CAPBEST_PE_RATIO
2AAPL US Equity=GLOOM($A2,B$1)=GLOOM($A2,C$1)=GLOOM($A2,D$1)
3VOD LN Equity=GLOOM($A3,B$1)=GLOOM($A3,C$1)=GLOOM($A3,D$1)
4SPX Index=GLOOM($A4,B$1)=GLOOM($A4,C$1)=GLOOM($A4,D$1)

Or fill the whole table from one cell: =GLOOM(A2:A4, B1:D1). Blank or repeated tickers and fields keep their row or column, so the table stays lined up with its labels.

History, dates down the first column: =GLOOMH("CL1 Comdty", "PX_LAST", "2026-01-01", TODAY(), "weekly").

Dates can be sheet dates or text as 2026-01-01 or 20260101; an empty end is today. The period is daily (default), weekly, monthly, quarterly or annual. Bloomberg's Per=W, Per=M, Per=CW and Per=CM work too.

How it behaves

  • Tickers and fields are the REST API's: Gloom, exchange-suffixed or Bloomberg forms, Gloom field ids or Bloomberg mnemonics. =GLOOM(ticker, field, "quarterly") reads the latest quarter for statement fields.
  • A value that does not exist comes back as #N/A text with the reason, like Bloomberg's, so IFERROR does not hide it. Use ISNUMBER.
  • The first cell that needs data fetches every GLOOM formula in the sheet together, so hundreds of cells cost a few requests (a unit per 20 tickers for prices, per 5 for fundamentals and estimates). Prices refresh after a minute, other values after an hour, and each GLOOMH table after five minutes (six hours when its range ended in the past). Gloom > Refresh data fetches again.
  • A revoked key, a free plan or a rate limit shows in the cells and pauses fetching for a few minutes. Fix the cause, then Gloom > Refresh data; Gloom > Test connection checks the key. A value not ready in time reads #N/A Not ready... and is asked for again on the next recalculation.
  • The key sits in the script's properties, which every editor of the sheet can read. That is why only Market data only keys work here; revoke the key in Cloud settings if the sheet is shared too widely.