Write Trends API rows into Google Sheets

Google Sheets does not speak Bearer from a formula. UrlFetchApp in Apps Script does. POST https://api.trendsapi.ai/api, then parse body twice, then setValues. The key belongs in Script Properties, not in the grid. Excel's path is Power Query, on the Excel page. Fields: the API reference. Caps: pricing.

UrlFetchApp is the client

function pullGrowth() {
  const key = PropertiesService.getScriptProperties().getProperty("TRENDSAPI_API_KEY");
  const resp = UrlFetchApp.fetch("https://api.trendsapi.ai/api", {
    method: "post",
    contentType: "application/json",
    headers: { Authorization: "Bearer " + key },
    payload: JSON.stringify({
      mode: "get_growth",
      source: "google search",
      keyword: "bitcoin",
      percent_growth: ["12M"],
    }),
    muteHttpExceptions: true,
  });
  const code = resp.getResponseCode();
  if (code !== 200) throw new Error(resp.getContentText());
  const envelope = JSON.parse(resp.getContentText());
  const inner = typeof envelope.body === "string" ? JSON.parse(envelope.body) : envelope.body;
  const rows = inner.results.map((r) => [r.period, r.growth, r.direction, r.recent_date]);
  SpreadsheetApp.getActive().getSheetByName("growth").getRange(2, 1, rows.length, 4).setValues(rows);
}

muteHttpExceptions keeps 401 and 429 readable. Throwing the body text is enough for the Executions log. Do not retry in a tight for loop.

Script Properties, not a cell

Editors see every cell. IMPORTRANGE copies them. Script Properties stay in the project. Official product docs say not to ship the key in frontend code or a public repo. A shared spreadsheet is both.

A time-driven trigger calls pullGrowth. Each successful 200 is one lookup. Do not bind the same function to onOpen. Opening the file is not a refresh policy.

Weekly points from get_time_series write date and value on a second sheet. Do not append those columns onto the growth sheet. growth is a signed percent. value is 0-100. That array shape is on get_time_series. The double parse is on parse the body.

A 401 in the Executions log is Script Properties, not source. A 429 is the monthly cap. Error codes sit on errors. Header shape is on authentication.