Automating the retrieval of a french company information from a Google Spreadsheet

Zip codes (image from geopostcodes.com)
Introduction
I recently worked with the Sirene API to retrieve information about French companies.
The goal was to fetch a company’s headquarters’ zip code based on its name in a Google Sheet.
Apps Script to the rescue
You can define custom spreadsheet functions using Apps Script in Google Sheets.
Simply go to Extensions > Apps Script
, then write your code inside a JavaScript-like function. The function name will be available in your spreadsheet as a formula, such as =FUNCTION_NAME(args…)
.
How to use the Sirene API
This PDF file was particularly helpful in understanding how to work with INSEE’s API: How to use the Sirene API?.
In short, you need to:
- Register an application on the developer portal.
- Subscribe your app to the Sirene API.
- Obtain an API token for your app, which will be passed in the
'X-INSEE-Api-Key-Integration'
header.
API Reference
The API reference is available here: Swagger API Reference.
Browse the API documentation to find the method you need.
In our case, we need to retrieve a company’s address using only its name. This means finding a method that returns the address and accepts a company name as input.
The GET /siret
method appears to be the right choice. We can use the q
parameter to filter results by company name, specifically using the 'denominationUniteLegale'
attribute.
To ensure we get only the headquarters’ address, we add a filter on the 'etablissementSiege'
attribute.
After experimenting with the API, I found that filters can be combined using the 'AND'
operator.
In the end, our query will look like this: q=denominationUniteLegale="companyName" AND etablissementSiege=true
.
This query needs to be properly templated and URL-encoded before being used.
Code implementation
With that in mind, I wrote a function that fetches the company’s data from the API and returns the zip code of the headquarter. Here is an example snippet:
const API_URL_TEMPLATE =
"https://api.insee.fr/api-sirene/3.11/siret?q=denominationUniteLegale%3A%22{companyName}%22%20AND%20etablissementSiege%3Atrue";
const API_TOKEN = "REDACTED_API_TOKEN";
function fetchCompanyData_(companyName) {
let apiUrl = API_URL_TEMPLATE.replace(
"{companyName}",
encodeURIComponent(companyName)
);
let headers = { "X-INSEE-Api-Key-Integration": API_TOKEN };
let response = UrlFetchApp.fetch(apiUrl, {
method: "GET",
headers: headers
});
let responseData = response.getContentText();
return JSON.parse(responseData);
}
let companyData = fetchCompanyData_(companyName);
if (companyData?.etablissements?.length === 0) {
throw new Error("No establishments found for the given company name.");
}
let postalCode =
companyData.etablissements[0]?.adresseEtablissement
?.codePostalEtablissement;
Improvements
To optimize performance and reduce the number of API requests, I implemented caching using Apps Script’s CacheService
.
Since the API is queried for each company name in the spreadsheet column, caching helps prevent redundant requests.
The cache key is set to the company name, and the formula includes an optional second argument to force cache invalidation. By default, this argument is false
.
Potential Enhancement: Rate Limiting
Currently, there is no rate limiter in place. Given the expected usage pattern:
- Pasting a list of company names into a column.
- Writing the formula in the first cell of the next column.
- Dragging the formula down to apply it to all rows.
This could result in a large number of API requests in a short period.
For now, I’ll monitor the actual API usage and determine if a rate limiter is necessary. The caching mechanism should already help reduce the load.
Conclusion
You can find the full code in the repository: GitHub Repository