Enrich a list of French SIREN numbers in Google Sheets

You have a column of SIREN numbers and want the company name, headquarters address, activity code and status next to them. This tutorial does that with an Apify actor and a short Apps Script. It takes about fifteen minutes.

What you need

• A Google Sheet with one SIREN (9 digits) or SIRET (14 digits) per row, in column A.
• An Apify account and its API token (Apify Console → Settings → Integrations).
• The actor: https://apify.com/amaury1729/france-company-enrichment

Format column A as plain text before pasting. Otherwise Sheets may treat a number as a number, which is harmless for most SIRENs but can drop a leading zero in the rare case one starts with 0.

Step 1: Store the token

In the spreadsheet, go to Extensions → Apps Script. In the editor, open Project Settings → Script Properties and add a property named APIFY_TOKEN with your token as the value. Keeping the token out of the code means you can share the sheet without sharing the token.

Step 2: Add the script

Replace the contents of Code.gs with this:

function enrichSirens() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const last = sheet.getLastRow();
  if (last < 2) return;

  const inputs = sheet.getRange(2, 1, last - 1, 1).getValues().flat()
    .map(v => String(v).trim())
    .filter(v => v !== '');

  const token = PropertiesService.getScriptProperties().getProperty('APIFY_TOKEN');
  const url = 'https://api.apify.com/v2/acts/amaury1729~france-company-enrichment/' +
              'run-sync-get-dataset-items?format=json&clean=true';

  const response = UrlFetchApp.fetch(url, {
    method: 'post',
    contentType: 'application/json',
    headers: { Authorization: 'Bearer ' + token },
    payload: JSON.stringify({ items: inputs }),
    muteHttpExceptions: true
  });
  if (response.getResponseCode() !== 200) {
    throw new Error('Apify returned HTTP ' + response.getResponseCode() + ': ' + response.getContentText());
  }

  const rows = JSON.parse(response.getContentText());
  const header = ['SIREN input', 'Status', 'Legal name', 'SIREN', 'HQ SIRET', 'HQ address',
                  'NAF code', 'NAF label', 'Active', 'Creation date', 'Headcount band'];
  const out = rows.map(r => {
    const c = r.company || {};
    return [
      r.input.query, r.status, c.legal_name || '', c.siren || '', c.hq_siret || '',
      c.hq_address ? c.hq_address.line : '',
      c.naf ? c.naf.code : '', c.naf ? c.naf.label : '',
      c.status === 'active' ? 'yes' : (c.status === 'closed' ? 'no' : ''),
      c.creation_date || '', c.headcount ? c.headcount.label : ''
    ];
  });

  // Clear the old output (columns C onward), then write the header and rows. Column A is never touched.
  sheet.getRange(1, 3, Math.max(last, out.length + 1), header.length).clearContent();
  sheet.getRange(1, 3, 1, header.length).setValues([header]);
  sheet.getRange(2, 3, out.length, header.length).setValues(out);
}

The script writes from column C onward and leaves your SIRENs in column A.

Step 3: Run it

Save, then choose Run → enrichSirens in the editor. The first run asks for permission to call an external service; allow it. Switch back to the sheet and the columns fill in.

The actor's synchronous endpoint waits for the run to finish and returns at most about five minutes. Keep each batch to a few hundred rows. A row with a bad number is not a failure of the whole batch: it comes back with its own status, and the rest still complete.

Step 4: Read the output

Each row has these columns, in this order:

Possible Status values:

• found: one company matched.
• ambiguous: a name matched several companies. Enter the right SIREN in column A and run again.
• not_found: no company matches. Check the number.
• invalid_input: the number fails its check digit or has the wrong length. Nothing was looked up or charged.
• source_error: the public source did not answer. Run the sheet again later.

Rows that did not match leave the company columns empty, so you can filter on Status to find them. A row marked establishment_not_confirmed keeps its company columns, because the company exists; check its SIRET before use.

Step 5: Re-run safely

Running the script again overwrites columns C to M and leaves column A alone. A duplicate SIREN in column A is looked up once and marked as a duplicate in the output, so you are not billed twice for it.

Ready-made input for a first test:

110014016
794598813
356000000

Start with that, then paste your own list.

Before using this on a customer spreadsheet

Format the identifier column as text. A SIREN has nine digits and a SIRET has fourteen; dropping a leading zero changes the identifier.

Run a small test and inspect each row's status. Keep ambiguous name matches out of the automatic enrichment path, and preserve source metadata so another person can see where the values came from. Missing accounts are not zero revenue. Public registry data may still be incomplete or delayed.

This example uses the Apify Actor and its token, not a keyless government endpoint or a RapidAPI key. Check the current Actor pricing before a large run.

For an automation-platform version, see the n8n/Make walkthrough.

Disclosure: I maintain the Actor used here. This update was prepared with AI assistance.

Actor: https://apify.com/amaury1729/france-company-enrichment