TrueRoyaltiesTrueRoyalties
API/Use the API from Google Sheets

Use the API from Google Sheets

Import one line per book — royalties, ad spend, net profit — into a Google Sheet with a short Apps Script, with your API key kept out of the sheet.

This script fills a sheet named Books with one row per book for the last 30 days: title, ASIN, format, marketplace, units, royalties, ad spend, net profit and TACOS.

1. Create a key

Settings → API → Create a key. Name it "Google Sheets" and copy it. How keys work: Authentication and API keys.

2. Store the key in the script's properties

  1. In your Google Sheet, open Extensions → Apps Script.
  2. Click Project Settings (the gear), then Add script property.
  3. Property: TRUEROYALTIES_API_KEY. Value: your key. Save.

Never paste the key into a cell: anyone the sheet is shared with could read it. Never put it in the URL either — the API refuses the request.

3. Paste the script

In the Editor, replace the content of Code.gs with:

const API_URL = "https://author.trueroyalties.com/api/v1/reports/books";

function importBooks() {
  const key = PropertiesService.getScriptProperties().getProperty(
    "TRUEROYALTIES_API_KEY",
  );
  if (!key) {
    throw new Error("Add TRUEROYALTIES_API_KEY in Project Settings.");
  }

  const rows = [];
  let cursor = null;
  let currency = "";

  while (true) {
    let url = API_URL + "?range=last_30_days&limit=100";
    if (cursor) url += "&cursor=" + encodeURIComponent(cursor);

    const response = UrlFetchApp.fetch(url, {
      headers: { Authorization: "Bearer " + key },
      muteHttpExceptions: true,
    });
    const status = response.getResponseCode();

    // Limit reached: wait as long as the API asks, then retry the same page.
    if (status === 429) {
      const headers = response.getHeaders();
      const wait = Number(headers["Retry-After"] || headers["retry-after"] || 1);
      Utilities.sleep(wait * 1000);
      continue;
    }

    const body = JSON.parse(response.getContentText());
    if (status !== 200) {
      throw new Error(body.code + ": " + body.detail + " (" + body.request_id + ")");
    }

    currency = body.currency;
    for (const line of body.data) {
      rows.push([
        line.book.title,
        line.book.asin,
        line.book.format,
        line.book.marketplace,
        line.units,
        line.revenue,
        line.ad_spend,
        line.net_profit,
        line.tacos === null ? "" : line.tacos,
      ]);
    }

    if (!body.has_more) break;
    cursor = body.next_cursor;
  }

  const spreadsheet = SpreadsheetApp.getActive();
  const sheet =
    spreadsheet.getSheetByName("Books") || spreadsheet.insertSheet("Books");
  sheet.clearContents();
  sheet.getRange(1, 1, 1, 9).setValues([[
    "Title", "ASIN", "Format", "Marketplace", "Units",
    "Royalties (" + currency + ")", "Ad spend", "Net profit", "TACOS",
  ]]);
  if (rows.length > 0) {
    sheet.getRange(2, 1, rows.length, 9).setValues(rows);
  }
}

4. Run it

  1. Select importBooks in the toolbar and click Run.
  2. The first time, Google asks you to allow the script to reach an external service and to edit your sheet. Accept.
  3. Go back to the sheet: the Books tab is filled.

To refresh it on its own, open Triggers (the clock), Add Trigger, choose importBooks and a Time-driven day timer.

Change what you import

Edit the query in the url line:

You wantAdd or change
Another periodrange=last_month, or start_date=2026-01-01&end_date=2026-06-30
Every sale since the startrange=all_time
Another currency&currency=EUR
Another order&sort=revenue (also ad_spend, units, title; net_profit by default)

Good to know:

  • Books with no activity in the period are listed too, with zeros, like the Books page.
  • Each page of 100 books is one request. The script waits by itself when it meets the 60-requests-per-minute limit, so a large catalogue simply takes a little longer.
  • The script does not check meta.truncated. If it is true, some rows could not be read: import a shorter period.
Limits and errorsConnect an AI assistant