Add Gender to a LibreOffice Calc Sheet with NameGender

A Python macro for the selected column

LibreOffice runs Python macros with its own bundled Python, so nothing else needs installing. Save the file below as namegender.py in your user macro folder: Scripts/python inside the LibreOffice user profile, which is ~/Library/Application Support/LibreOffice/4/user on macOS, ~/.config/libreoffice/4/user on Linux and %APPDATA%\LibreOffice\4\user on Windows. Create the Scripts and python folders if they are missing.

Put your API key in a file called .namegender_key in your home folder; LibreOffice started from the Dock or Start menu does not see variables from your shell profile. Then select the cells with first names, without the header, and run Tools → Macros → Run Macro → My Macros → namegender → add_gender.

Gender and probability are written to the two columns right of the selection. Empty cells are skipped and cost nothing; names the API cannot answer stay blank. Set COUNTRY at the top of the file when the whole list comes from one country.

namegender.py

# NameGender for LibreOffice Calc. Select a column of first names, then run
# Tools > Macros > Run Macro > My Macros > namegender > add_gender.
# Gender and probability are written to the two columns right of the selection.
import json
import os
import urllib.request

API = "https://namegender.com/api/v1/gender/bulk"
COUNTRY = ""  # optional two-letter code applied to every name, e.g. "IT"


def _api_key():
    key = os.environ.get("NAMEGENDER_API_KEY", "").strip()
    if not key:
        path = os.path.join(os.path.expanduser("~"), ".namegender_key")
        if os.path.exists(path):
            with open(path, encoding="utf-8") as f:
                key = f.read().strip()
    if not key:
        raise RuntimeError("Set NAMEGENDER_API_KEY or put your key in ~/.namegender_key")
    return key


def _lookup(names, key):
    body = {"names": names}
    if COUNTRY:
        body["country"] = COUNTRY
    request = urllib.request.Request(
        API,
        data=json.dumps(body).encode("utf-8"),
        headers={
            "Authorization": "Bearer " + key,
            "Content-Type": "application/json",
            "Accept": "application/json",
            "User-Agent": "namegender-libreoffice/1.0",
        },
    )
    with urllib.request.urlopen(request, timeout=60) as response:
        return json.load(response)["results"]


def add_gender(*args):
    doc = XSCRIPTCONTEXT.getDocument()
    selection = doc.getCurrentController().getSelection()
    sheet = doc.getCurrentController().getActiveSheet()
    address = selection.getRangeAddress()

    names = [str(row[0]).strip() for row in selection.getDataArray()]
    # Empty cells are not sent: they would cost a credit and the API rejects them.
    todo = [i for i, name in enumerate(names) if name]
    output = [("", "")] * len(names)

    key = _api_key()
    for start in range(0, len(todo), 100):
        chunk = todo[start:start + 100]
        results = _lookup([names[i] for i in chunk], key)
        for i, result in zip(chunk, results):
            gender = result.get("gender") or ""
            output[i] = (gender, result.get("probability", 0) if gender else "")

    target = sheet.getCellRangeByPosition(
        address.EndColumn + 1, address.StartRow, address.EndColumn + 2, address.EndRow
    )
    target.setDataArray(tuple(output))


g_exportedScripts = (add_gender,)

Copy failed. Select and copy the text.

The command line, for a column you can copy

With Node.js installed, the namegender command reads one name per line. Copy the name column, paste it into a text file, and run cat names.txt | npx namegender --csv > genders.csv with your key in NAMEGENDER_API_KEY. Open genders.csv in Calc and paste its columns next to the original list; the rows are in the same order.

Add --country IT, or --locale it-IT when you only know the language, to narrow the answer. Andrea is male in Italy and female in Germany, and the country changes the answer only for names like that.

Large files: upload instead

For tens of thousands of rows, save the sheet as XLSX or CSV and upload it in the NameGender dashboard. You pick the name column and an optional country column, see the cost before starting, and download the same file with gender, probability, sample size and match columns added. Calc opens the result directly.

Repeated first names are looked up once, so the cost follows the number of distinct names rather than the number of rows.

Questions

Why not use WEBSERVICE?

WEBSERVICE can only send a plain GET request without headers, and the API key travels in the Authorization header.

Is the macro safe to share?

The macro itself contains no key; the key stays in your own ~/.namegender_key file. Do not paste it into the macro if you share the file.

Does an unknown name cost a credit?

Yes. Every name sent costs one credit, including names that come back unknown. Empty cells are not sent.

Does it work in OpenOffice?

The upload and command line routes do. The macro relies on the Python scripting that LibreOffice ships.

Related pages

Add gender to an Apple Numbers spreadsheet

Add gender and probability to a column of names in Apple Numbers, from the clipboard with the namegender command or by uploading the file.

Add gender data to an Excel file

Add gender, probability and evidence columns to an Excel or CSV file. Upload and download without code, or connect Excel Power Query to the REST API.

Use the gender API in Google Sheets

Enrich names in Google Sheets with the NameGender add-on or Apps Script. Use country hints, preserve confidence fields and protect the API key in shared files.

Add a gender column to a CSV or Excel file

A walkthrough for analysts: upload a name list, pick the right columns, read the confidence fields, and decide what to do with the rows that come back empty.

Check it against your own list

Every number on this page is reproducible with a free key. If your data breaks it, that is the more interesting result.

Create a free key See pricing

Add gender to a LibreOffice Calc sheet