Skip to content

FUNCTIONS.md

Latest commit

 

History

History
222 lines (170 loc) · 9.1 KB

FUNCTIONS.md

File metadata and controls

222 lines (170 loc) · 9.1 KB
Raw

Function reference

Complete reference for the four worksheet functions exposed by the US Census Calc add-in. For setup (API key, build, install) see INSTALL.md; for a worked example spreadsheet see demo/Census-Demo.ods (generated by tools/build_demo.py).

All formulas below use Calc's semicolon argument separator. Arguments in [brackets] are optional and may be omitted entirely (trailing optional arguments can simply be left off the end of the formula).

Shared behavior

  • year accepts either a number or a text cell (e.g. 2022 or "2022").
  • dataset is the API's dataset path, e.g. "acs/acs5" (ACS 5-year estimates), "acs/acs1" (ACS 1-year), or "dec/pl" (decennial redistricting data). Leading/trailing slashes are stripped automatically.
  • geography maps to the API's for= clause, e.g. "state:*" (every state), "state:06" (California only), or "county:*" (every county — combine with in_geography to scope to one state).
  • in_geography (where present) maps to the API's in= clause, e.g. "state:06". Required whenever geography is below the state level (county, tract, block group, ...).
  • api_key (where present) overrides the CENSUS_API_KEY environment variable for that one call. Type it literally or reference a cell, e.g. $B$1. CENSUS_GET and CENSUS_VALUE require a key — one or the other must be set, or the call raises a Calc error value (the API answers a keyless data request with a redirect to an HTML "missing key" page rather than data; see INSTALL.md). CENSUS_VARLABEL and CENSUS_DATASETS are metadata/discovery endpoints and never need a key, so they have no api_key argument.
  • Caching: every distinct request URL is cached for the life of the LibreOffice session, so recalculating a sheet does not re-hit the API. Restart LibreOffice to clear the cache.
  • Errors: invalid input, an API error response, or a network failure all surface as a genuine Calc error value in the cell (e.g. Err:502), never as a raw exception string or a silent wrong answer.
  • Array formulas: CENSUS_GET and CENSUS_DATASETS can return more than one row. LibreOffice has no dynamic spill, so select an output range large enough for the result, type the formula, and confirm with Ctrl+Shift+Enter (or tick Array in the Function Wizard). A single-cell entry shows only the top-left value of the result.

CENSUS_GET

Runs a Census API data query and returns the full result table.

CENSUS_GET(year; dataset; variables; geography; [in_geography]; [api_key])
Argument Type Required Description
year number or text yes The data year, e.g. 2022.
dataset text yes The dataset path, e.g. "acs/acs5".
variables text yes Comma-separated variable codes, e.g. "NAME,B01003_001E". Mapped to the API's get= parameter.
geography text yes The for= clause, e.g. "state:*". May list several specific geographies, e.g. "state:06,36,48".
in_geography text no The in= clause, e.g. "state:06".
api_key text yes, or via env var Overrides CENSUS_API_KEY for this call. A key must come from one or the other.

Returns: a rectangular array. Row 1 is the column headers exactly as the API returns them (variable codes, then geography identifier columns such as state or county). Each subsequent row is one geography's values. Every cell is returned as text, matching the API's own JSON-array-of-arrays shape with minimal parsing — wrap numeric columns in VALUE() if you need them as numbers for arithmetic.

Errors: dataset/variables/geography empty, no API key available (neither the argument nor CENSUS_API_KEY set), an unknown dataset or variable, a malformed geography clause, or a network failure all raise a Calc error value. A query that legitimately returns zero rows (nothing matched) is also an error, since a table with no data is never useful in a sheet.

Examples:

=CENSUS_GET(2022; "acs/acs5"; "NAME,B01003_001E"; "state:*")
    -> 53 rows (header + 52 states/territories): name, total population

=CENSUS_GET(2022; "acs/acs5"; "NAME,B01003_001E"; "state:06,36,48")
    -> 4 rows: header + California, New York, Texas

=CENSUS_GET(2022; "acs/acs5"; "NAME,B01003_001E"; "county:*"; "state:06")
    -> 59 rows: header + every California county (in_geography scopes to CA)

=CENSUS_GET(2020; "dec/pl"; "NAME,P1_001N"; "state:*")
    -> 2020 decennial redistricting total population by state

CENSUS_VALUE

Returns a single scalar value for one variable at one specific geography.

CENSUS_VALUE(year; dataset; variable; geography; [in_geography]; [api_key])
Argument Type Required Description
year number or text yes The data year, e.g. 2022.
dataset text yes The dataset path, e.g. "acs/acs5".
variable text yes A single variable code, e.g. "B01003_001E".
geography text yes The for= clause identifying one specific geography, e.g. "state:06" — not a wildcard (see Notes).
in_geography text no The in= clause, e.g. "state:06".
api_key text yes, or via env var Overrides CENSUS_API_KEY for this call. A key must come from one or the other.

Returns: the requested value from the first (and normally only) data row — a number when it parses as one (most ACS estimates), text for string fields (e.g. NAME), or an empty cell when the API returns a null value for that variable/geography.

Errors: dataset/variable/geography empty, no API key available (neither the argument nor CENSUS_API_KEY set), no data row returned for the query, or the requested variable not present in the response header (e.g. a typo in the variable code) all raise a Calc error value.

Notes: internally this runs the same kind of query as CENSUS_GET with variables set to just variable, then reads the first data row. If geography resolves to more than one row (e.g. a wildcard like "state:*"), you get whichever row the API happened to return first — pass a specific geography instead, or use CENSUS_GET to pull every matching geography at once.

Examples:

=CENSUS_VALUE(2022; "acs/acs5"; "NAME"; "state:06")
    -> California

=CENSUS_VALUE(2022; "acs/acs5"; "B01003_001E"; "state:06")
    -> 39356104

=CENSUS_VALUE(2022; "acs/acs5"; "B19013_001E"; "county:075"; "state:06")
    -> median household income for San Francisco County, CA

=CENSUS_VALUE(2022; "acs/acs5"; "B01003_001E"; "state:06"; ""; $B$1)
    -> same query, API key taken from cell B1

CENSUS_VARLABEL

Returns the human-readable label for a variable code, from the dataset's variables.json metadata.

CENSUS_VARLABEL(year; dataset; variable)
Argument Type Required Description
year number or text yes The data year, e.g. 2022.
dataset text yes The dataset path, e.g. "acs/acs5".
variable text yes The variable code to look up, e.g. "B01003_001E".

Returns: the variable's label field (e.g. "Estimate!!Total"), or its name if the dataset has no label for it.

Errors: dataset/variable empty, or the variable code not found in that dataset/year, raises a Calc error value.

Notes: this endpoint is metadata, not data — it never needs an API key, so there is no api_key argument.

Examples:

=CENSUS_VARLABEL(2022; "acs/acs5"; "B01003_001E")
    -> Estimate!!Total

=CENSUS_VARLABEL(2022; "acs/acs5"; "B19013_001E")
    -> Estimate!!Median household income in the past 12 months (in 2022 inflation-adjusted dollars)

Pair it with CENSUS_GET/CENSUS_VALUE to build a self-documenting sheet: put variable codes in column A and =CENSUS_VARLABEL(2022;"acs/acs5";A2) in column B.

CENSUS_DATASETS

Lists the datasets available for a year, from the API's discovery endpoint.

CENSUS_DATASETS(year)
Argument Type Required Description
year number or text yes The data year, e.g. 2022.

Returns: a two-column array. Row 1 is a Dataset / Title header row; each subsequent row is one available dataset's path (e.g. "acs/acs5", joined from the API's c_dataset segments) and its title. A given year typically has ~100 datasets, so select a generous range (or a full column) when entering this as an array formula.

Errors: year empty, or no datasets listed for that year (e.g. a year far outside the API's coverage), raises a Calc error value.

Notes: this is the discovery endpoint, not a data query — it never needs an API key. Row order matches whatever order the API itself returns (not alphabetical); use CENSUS_GET/CENSUS_VALUE once you've found the dataset path you want.

Examples:

=CENSUS_DATASETS(2022)
    -> spills ~100 rows: (Dataset, Title) for every 2022 dataset

=CENSUS_DATASETS(2020)
    -> includes "dec/pl" ("Decennial Census: Redistricting Data (PL 94-171)")