Pull Trends API into Excel

Excel talks to Trends API through Power Query Web.Contents. There is no ribbon connector. The POST is https://api.trendsapi.ai/api. Json.Document must run twice because body is a string. Sheets does this in Apps Script, on the Sheets page. Power BI reuses the same M, on the Power BI page. Fields: the API reference. Caps: pricing. Refresh is a successful POST, so a workbook that hits the API on every open will spend lookups.

Web.Contents posts the JSON

let
  url = "https://api.trendsapi.ai/api",
  payload = Text.ToBinary("{""mode"":""get_growth"",""source"":""google search"",""keyword"":""bitcoin"",""percent_growth"":[""12M""]}"),
  raw = Web.Contents(url, [
    Headers = [
      Authorization = "Bearer " & apiKey,
      #"Content-Type" = "application/json"
    ],
    Content = payload
  ]),
  envelope = Json.Document(raw),
  inner = Json.Document(envelope[body]),
  rows = Table.FromRecords(inner[results])
in
  rows

apiKey is a query parameter or a credential, not a cell that recalculates into the .xlsx. Official product docs say not to ship the key in a public file. A workbook on OneDrive is a public file if the link is.

source stays google search. A live board uses mode get_top_trends and type Google Trends. Those calls are on get_top_trends.

Two Json.Document calls

The first Json.Document yields statusCode and body. body is text. The second yields results or a list of points. Expanding body as if it were a record fails. That rule is on parse the body.

Refresh is a successful POST. A workbook that refreshes on open every minute will hit the monthly cap. Current limits sit on pricing. 401 and 429 text is on errors.