Getting the Value of a Statistical Variable at a Given Place and Time

The=DCGET(dcids, variable, date) formula returns the measurements of a specified statistical variable at a given place and optional time based on a list of parent Place DCIDs. A complete list of variables can be found in the Statistical Variable Explorer.

Note: Be sure to follow the instructions for Installing and Enabling the Sheets Add-On before using this formula.

Formula

=DCGET(dcids, variable, date)

Required Arguments

Optional Arguments

date: The date or dates of interest. If this argument is not specified, the API returns the latest variable observation. You can specify this argument as a single value, row, or column. All dates must be in ISO 8601 format (such as 2017, “2017”, “2017-12”) or as a Google sheets date value.

Returns

The value of the variable at those places on the specified date or on the latest available date, if no date is specified.

Note: It’s best to minimize the number of function calls to =DCGET(dcids, variable, date) by using a single call to get the names for a column of nodes. This is because a spreadsheet will make one call to a Google server per custom function call. If your sheet contains thousands of separate calls to =DCGET(dcids, variable, date) you can expect it to be slow and return with errors.

Examples

This section contains examples of using the =DCGET(dcids, variable, date) formula to returns lists of statistical variable such as “Count_Person” and “Median_Income_Person”. A complete list of variables can be found in the Statistical Variable Explorer.

Example 1: Get the Total Population of Hawaii in 2017

The following formula returns the total population of Hawaii in 2017 using the “geoId/15” DCID and “Count_Person” variable:

=DCGET("geoId/15", "Count_Person", 2017)

Running the preceding formula returns a value of 1425763.

Example 2: Get the Population of Multiple Places with a Single Call

The following sheet returns the population of the five Hawaii counties in 2017. Column A contains the Hawaii state DCID of “geoId/15” and column B contains the county DCIDs. Column C uses the =DCGETNAME() formula to retrieve the county names based on the values in column B. Column D uses the following formula to retrieve the 2017 population:

=DCGET(B2:B6, "Count_Person", 2017)

Getting the population of multiple places with a single call

Here is the output after running the =DCGET(B2:B6, "Count_Person", 2017) formula:

Output after running the =DCGET(B2:B6, "Count_Person", 2017) formula

Example 3: Get the Median Income of a Single Place in Multiple Years

The following sheet demonstrates how to retrieve the median income from a single place in multiple years. Cell C2 uses the DCID for Hawaii (geoId/15) from cell B2 to retrieve the data using the following formula:

=DCGET(B2, "Median_Income_Person", C1:E1)

Getting the median income of a single place in multiple years

Here is the output after running the =DCGET(B2, "Median_Income_Person", C1:E1) formula:

Output after running the =DCGET(B2, "Median_Income_Person", C1:E1) formula

Example 4: Get the Median Age of Multiple Places in Multiple Years

The following sheet demonstrates how to retrieve the median age of multiple places in multiple years, with places as a column and dates as a row. Cell E2 uses the Hawaii county DCIDs from column C to retrieve the data using the following formula:

=DCGET(C2:C6, "Median_Age_Person", F1:H1)

Getting the median age of multiple places in multiple years

Here is the output after running the =DCGET(C2:C6, "Median_Age_Person", F1:H1) formula:

Output after running the =DCGET(C2:C6, "Median_Age_Person", F1:H1) formula

Here’s another example, but this time with places as a row and dates as a column using the formula:

=DCGET(B3:F3, "Median_Age_Person", A5:A9)

Retrieving places as a row and dates as a column using the formula =DCGET(B3:F3, "Median_Age_Person", A5:A9)

Here is the output after running the =DCGET(B3:F3, "Median_Age_Person", A5:A9) formula:

Output after running the =DCGET(B3:F3, "Median_Age_Person", A5:A9) formula

Error Responses

The =DCGET(dcids) formula returns a blank value under the following circumstances:

  • A DCID does not exist (e.g. “geoId/123123123”)
  • You provide a nonexistent statistical variable (e.g. “Count”)
  • You provide an incorrectly formatted date (e.g. “July 12, 2013”)

For example, because the “geoId/123123123” DCID does not exist, no value is returned to cell B1 in the following sheet for the formula =DCGET(A1, "Count_Person"):

No value is returned to cell B1 in the following sheet for the formula `=DCGET(A1, "Count_Person")` because the DCID does not exist

If you fail to provide all required arguments, you will receive an error:

Error returned if you fail to provide all required arguments