CNPJAPI

Look up CNPJ in Google Sheets

This guide shows how to look up a CNPJ (the Brazilian company tax ID) right inside Google Sheets by creating a =CNPJ(...) function you use like any spreadsheet formula. It calls the CNPJAPI REST API and brings the legal name, registration status and main activity into the cells next to the CNPJ.

The guide starts from scratch: creating the account and generating your API key.

Before you start: create the account and generate the API key

To use the API you need an API key (a token that identifies your account). It is free to start.

  1. Go to https://app.cnpjapi.com.br and create your account on the free plan (no card required).
  2. Confirm your e-mail and sign in to the portal.
  3. Inside the portal, generate your API key. It starts with cnpj_ (for example, cnpj_a1b2c3...). Copy it and keep it somewhere safe - the key is shown once.
  4. This key goes in the Authorization header, as Bearer cnpj_your_key. The script below handles that.

Details in Authentication. Treat the key like a password: whoever holds it consumes your quota.

Shortcut: a ready-to-copy spreadsheet

Don't want to build it from scratch? Copy our template spreadsheet, which already ships with the function:

Make a copy of the template spreadsheet

After copying (Google creates a copy in your account):

  1. Open Extensions → Apps Script, replace cnpj_sua_chave with your API key and save. The first time, Google asks to authorize the script (it is your own script).
  2. In the sheet, type a CNPJ in the CNPJ column (already formatted as text) - the cells alongside return the legal name, status and activity.

Prefer to understand how it works or build it in your own sheet? Follow the step by step below.

Why =IMPORTDATA() won't work

The built-in Sheets formulas (IMPORTDATA, IMPORTJSON) cannot send headers - and the API requires the Authorization header. The way around it is a small custom function written with Apps Script (the script editor built into Google Sheets). You paste the code once, then use =CNPJ(A2) in the sheet.

Step by step

1. Open the script editor. In your spreadsheet, menu Extensions → Apps Script.

2. Paste the code (clear the default content and paste this):

/**
 * Looks up a CNPJ via CNPJAPI and returns the data in the cells alongside.
 * Usage in the sheet:  =CNPJ(A2)
 */
function CNPJ(cnpj) {
  var key = "cnpj_your_key"; // <- paste your API key here
  var digits = String(cnpj).replace(/\D/g, "");
  if (digits.length !== 14) return "invalid CNPJ";

  var resp = UrlFetchApp.fetch("https://api.cnpjapi.com.br/" + digits, {
    headers: { "Authorization": "Bearer " + key },
    muteHttpExceptions: true
  });

  var code = resp.getResponseCode();
  if (code === 404) return "not found";
  if (code === 429) return "rate limited, try later";
  if (code !== 200) return "error " + code;

  var e = JSON.parse(resp.getContentText());
  return [[
    e.RazaoSocial,
    e.SituacaoCadastral.Descricao,
    e.AtividadePrincipal.Descricao
  ]];
}

Replace cnpj_your_key with your API key. Save (disk icon).

3. Use it in the sheet. In a cell (say the CNPJ is in A2), type:

=CNPJ(A2)

The function returns three columns at once - legal name, status and main activity - spilling into the cells to the right. Drag the formula down to apply it to a whole column of CNPJs.

Tip: format the CNPJ column as Plain text (menu Format → Number → Plain text) before typing. Without it, Sheets treats 00776574000156 as a number and drops the leading zeros - the function gets fewer than 14 digits and returns "CNPJ inválido" (invalid CNPJ).

The first time, Google asks for authorization for the script to reach external services (UrlFetchApp). Approve it with your Google account; it is a permission for your own script, not for CNPJAPI.

Return a single field

If you only want the legal name in one cell, swap the return for a single value:

  var e = JSON.parse(resp.getContentText());
  return e.RazaoSocial;

Then =CNPJ(A2) returns just the name.

Rate limit (and when to upgrade)

As you drag the formula across many rows, Sheets calls the function many times in sequence. On the free plan the API accepts a limited number of lookups per minute; when you exceed it, it returns 429 and the cell shows "rate limited". Sheets custom functions also have Google's own time limit.

For large lists:

  • Recalculate in chunks (apply the formula to blocks of rows); or
  • Upgrade to a plan with a higher limit and a comfortable monthly quota.

See the plan limits. If you use this often, a paid plan pays off in time saved.

Storing the key more safely (optional)

Instead of leaving the key in the code, you can keep it in the Script Properties and read it like this:

  var key = PropertiesService.getScriptProperties().getProperty("CNPJAPI_KEY");

Set the value under Project Settings → Script properties. Useful when more people edit the sheet.

Notes

  • It works anywhere (it is web based) - including where Excel/Power Query does not run (Mac, Chromebook).
  • On Excel for Windows you can do the same without coding, via Power Query: Look up CNPJ in Excel.

Next steps

Create your free account at https://app.cnpjapi.com.br and build your first spreadsheet in minutes.