Tutorial · Google Sheets

Company lookup in Google Sheets

An Apps Script custom function that takes a country and a register number and returns one field of the company record: its name, status or city. The API key stays in Script Properties, never in a cell.

1. Store the key in Script Properties

In the sheet open Extensions, Apps Script, then Project Settings (the gear), and under Script Properties add a property named PROMETIAM_API_KEY with your rk_live_... key as its value. The function reads it when it runs, so the key is in no cell. Anyone who can edit the script can read it, and a copy of the spreadsheet copies its script too, so share the sheet only with people who may use your key.

2. Paste the function

Replace the contents of Code.gs with this and save. It calls GET /companies/search with country and company_number, sends the key in the Authorization: Bearer header, and caches each answer for up to six hours so that a recalculation does not spend another credit.

const BASE = 'https://api.prometiam.com/functions/v1/risk-api';

/**
 * One field of a company record from the country's official register.
 * @param {string} country Two-letter country code, for example "GB".
 * @param {string} companyNumber The register number, for example "00445790".
 * @param {string} field Optional: company_name (default), status, local_status, city, founding_date ...
 * @return The value, "not found", or an error.
 * @customfunction
 */
function COMPANY(country, companyNumber, field) {
  const key = PropertiesService.getScriptProperties().getProperty('PROMETIAM_API_KEY');
  if (!key) throw new Error('Add the script property PROMETIAM_API_KEY');
  const number = String(companyNumber).trim();
  const cache = CacheService.getScriptCache();
  const id = ['company', country, number].join(':');
  let json = cache.get(id);
  if (!json) {
    const url = BASE + '/companies/search?country=' + encodeURIComponent(country)
      + '&company_number=' + encodeURIComponent(number);
    const res = UrlFetchApp.fetch(url, {
      headers: { Authorization: 'Bearer ' + key },
      muteHttpExceptions: true,
    });
    if (res.getResponseCode() !== 200) {
      throw new Error('API ' + res.getResponseCode() + ': ' + res.getContentText().slice(0, 120));
    }
    const row = JSON.parse(res.getContentText()).data[0];
    if (!row) return 'not found';
    json = JSON.stringify(row);
    cache.put(id, json, 21600); // up to six hours (Google may drop it sooner): a recalculation does not spend another credit
  }
  const value = JSON.parse(json)[field || 'company_name'];
  return value === null || value === undefined ? '' : value;
}

The same request from a terminal, to see what the function sees:

curl 'https://api.prometiam.com/functions/v1/risk-api/companies/search?country=GB&company_number=00445790' \
  -H 'Authorization: Bearer rk_live_...'

3. Use it in a cell

=COMPANY("GB", "00445790")              -> TESCO PLC
=COMPANY("GB", "00445790", "status")    -> active
=COMPANY(A2, B2, "city")                -> the city of the company in row 2

Depending on your sheet's locale the argument separator is a comma or a semicolon. Format the column that holds register numbers as plain text: a number cell drops the leading zeros of 00445790 and then looks up a different company. Try one row before you fill a column.

The third argument is any field of the standard company object: company_name, status (the same list in every country), local_status (the register's own word), legal_form, founding_date, city, vat_number and so on. The same object comes back for every country, so only the country code and the register number change.

What it costs

A successful company lookup costs 1 credit (the response carries X-Credits-Cost: 1); an error costs nothing. Starter is EUR 9.99 a month with 1,000 credits, Professional EUR 29.99 with 10,000, Scale EUR 99.99 with 50,000, and Enterprise is custom. Starter is the only plan with a trial: 14 days and 500 credits, card required, nothing charged during the trial.

Credits are counted per account, so a long column is a long bill. The cache above stops repeated recalculation of the same row for up to six hours, not for ever. Starter allows 60 requests a minute, so a column of a few hundred rows fills in several passes; a row that hits the limit shows an error, and re-entering the formula retries it.

What the call returns, and what it does not

You get	You do not get
The company record as the country's register publishes it: name, identifier, status, legal form, address, founding date where published	Directors, shareholders or beneficial owners: this call returns no people
The same field names for every live country	The same fields: each register publishes different ones, and a field it does not publish is empty
A standardised `status` and the register's own `local_status`	A guarantee of solvency or good standing, or a certified extract from the register
A `vat_number` with its source (`register` or `rule`)	A VAT check: a rule-built number is only the correct format; confirm an EU number with `GET /vat/{vatNumber}`

Each country has its own scope limits (for example which legal forms the register serves, and whether sole proprietors are included); they are in the country guides, such as the UK and Brazil company data guides. Check the country you look up before relying on an empty cell.

FAQ

Where do I put the API key in Google Sheets?
In the script properties of the Apps Script project (Project Settings, then Script Properties), under the name PROMETIAM_API_KEY. The function reads it when it runs, so the key is in no cell. Anyone who can edit the script can read it, and a copy of the spreadsheet copies its script too, so share the sheet only with people who may use your key.

How much does one lookup cost?
A successful company lookup costs 1 credit and an error costs nothing. Starter is EUR 9.99 a month with 1,000 credits, Professional EUR 29.99 with 10,000, Scale EUR 99.99 with 50,000. The 14-day trial includes 500 credits, needs a card and charges nothing during the trial.

Why did my company number lose its leading zero?
Sheets stored it as a number. Format the column as plain text (Format, Number, Plain text) before you type the numbers; the UK number 00445790 is a string, and 445790 is a different lookup.

Does the lookup return directors or shareholders?
No. This call returns the company record of the register and nothing about people. Fields differ by country and are null where the register does not publish them; each country guide states what the country does not serve.

The 14-day trial needs a card, nothing is charged during it, and it then continues as Starter (EUR 9.99/month) unless you cancel; it includes 500 credits.