Gapminder-specific custom functions and related menu item actions for Google Spreadsheets.

• GM_CLEAN_TEXT
• GM_DATA
• GM_DATAPOINT_CATALOG_STATUS
• GM_DATA_AGGR
• GM_GEO_LOOKUP_TABLE
• GM_GROWTH
• GM_ID
• GM_INTERPOLATE
• GM_LARGEST_REMAINDER_ROUND
• GM_NAME
• GM_PER_CAP
• GM_PROP
• GM_PROP_AGGR
• GM_UNPIVOT
• GM_WEIGHTED_AVERAGE
• menuRefreshDataCatalog
• menuRefreshDataDependencies
• menuValidateDatasetSpreadsheet

▸ GM_CLEAN_TEXT(range_with_headers: string[][]): string[][]

Defined in src/GM_CLEAN_TEXT.ts:11

Converts to lowercase, then removes diacritics and any special characters outside of "[^a-z0-9 ()]". Use for fuzzy matching such as "Foo " == "foo" and "Fóo*" == "Foo".

Returns: string[][]

An array containing the cell/column contents described above in the summary.

▸ GM_DATA(input_table_range_with_headers: string[][], concept_data_table_range_with_headers: string[][]): any[][]

Defined in src/GM_DATA.ts:13

Inserts a concept column, including a header row, with a common Gapminder concept matched against the input column/table range.

Note: Requires that the concept data to match against is first imported using the "Gapminder Data -> Import/refresh data dependencies".

Returns: any[][]

A two-dimensional array containing the cell/column contents described above in the summary.

▸ GM_DATAPOINT_CATALOG_STATUS(concept_id_and_catalog_reference: string, time_unit: string, geo_set: string, verbose: boolean): string[][]

Defined in src/GM_DATAPOINT_CATALOG_STATUS.ts:32

Checks if the referenced concept data is available remotely for import.

Runs the basic validation checks against the referenced dataset making sure that

• it is listed in the fasttrack catalog or is part of the Open Numbers World Development Indicators
• (fasttrack catalog only) the relevant "data-" worksheet in the dataset source document is published

Returns "GOOD" or "BAD" (Or "BAD: What is bad... " if the verbose flag is TRUE).

Note: The function results are not automatically re-evaluated as changes are made to the source documents or the catalog. You can trigger a manual update by deleting the cell and undoing the deletion immediately.

Returns: string[][]

A two-dimensional array containing the cell/column contents described above in the summary.

▸ GM_DATA_AGGR(input_table_range_with_headers: string[][], concept_data_table_range_with_headers: string[][]): any[][]

Defined in src/GM_DATA_AGGR.ts:23

Aggregates an input table by a time-dependent indicator and time, returning a table with the aggregated values of the input table.

The input table must be at least four columns wide.

• Column 1: geo_ids
• Column 2: geo_names (isn’t part of the calculation)
• Column 3: time
• Column 4+: values to be aggregated

Note: Uses GM_DATA internally

Returns: any[][]

A two-dimensional array containing the cell/column contents described above in the summary.

▸ GM_GEO_LOOKUP_TABLE(geo_set: string): string[][]

Defined in src/GM_GEO_LOOKUP_TABLE.ts:19

Inserts a table with Gapminder’s geo ids together with their aliases (all spellings we have seen before), including lower cased variants without diacritics and special characters to allow for somewhat fuzzy matching.

To be used as the source range for VLOOKUP where the dataset is too large for GM_ID or GM_NAME to be used directly.

Returns: string[][]

A two-dimensional array containing the cell/column contents described above in the summary.

▸ GM_GROWTH(input_table_range_with_headers: string[][], concept_data_table_range_with_headers: string[][]): string[][]

Defined in src/GM_GROWTH.ts:14

Inserts the growth per time unit of a common Gapminder concept column, including a header row, matched against the input table range.

Note: Uses GM_DATA internally

Returns: string[][]

A two-dimensional array containing the cell/column contents described above in the summary.

▸ GM_ID(column_range_with_headers: string[][], geo_set: string, verbose: boolean): string[][]

Defined in src/GM_ID.ts:14

Inserts a matching column, including a header row, with Gapminder’s geo ids matched against the input column range, based on all spellings we have seen before. It should be entered in the header cell under which you want the first first id to appear and it uses as input another range of cells, which should start with the header of the column with names of a geo_set you want to identify. Note: Automatically adds geo ids as aliases in geo lookup tables, so that "USA" matches "usa" even though no specific alias "usa" is mapped to "usa".

Returns: string[][]

A two-dimensional array containing the cell/column contents described above in the summary.

▸ GM_INTERPOLATE(input_table_range_with_headers: string[][], method: string, page_size: number, page: number): any[][]

Defined in src/GM_INTERPOLATE.ts:26

Interpolates an input table, inserting a sorted table with additional rows, where the gaps (missing rows or empty values) in the input table have been filled in. This function works on data with two primary key columns: usually geo and time. (If we want to use this on data that has more keys: geo, time, age, gender, etc - we need a different formula)

The range must be four columns wide.

• Column 1: geo_ids
• Column 2: geo_names (isn’t part of the calculation)
• Column 3: time
• Column 4+: values to be interpolated

Returns: any[][]

A two-dimensional array containing the cell/column contents described above in the summary.

▸ GM_LARGEST_REMAINDER_ROUND(input_table_with_rows_of_floats_that_should_be_summed: string[][], target_total_sum: number, decimals: number): number[][]

Defined in src/GM_LARGEST_REMAINDER_ROUND.ts:13

Rounds floats/fractions in that ensures that the total remains a specific target. Use for rounding percentages and ensuring that the total is always 100%.

Returns: number[][]

A two-dimensional array containing the cell/column contents described above in the summary.

▸ GM_NAME(column_range_with_headers: string[][], geo_set: string, verbose: boolean): string[][]

Defined in src/GM_NAME.ts:14

Inserts a matching column, including a header row, with Gapminder’s common name for the geo matched against the input column range, based on all spellings we have seen before. (Like GM_ID but inserts Gapminder’s common name for the geo instead of its id.) Note: Automatically adds geo ids as aliases in geo lookup tables, so that "USA" matches "usa" even though no specific alias "usa" is mapped to "usa".

Returns: string[][]

A two-dimensional array containing the cell/column contents described above in the summary.

▸ GM_PER_CAP(input_table_range_with_headers_and_concept_values: string[][], population_concept_data_table_range_with_headers: string[][]): string[][]

Defined in src/GM_PER_CAP.ts:14

Divides the concept-value column(s) of the input table range by the population of the geo_set.

Note: Uses GM_DATA internally

Returns: string[][]

A two-dimensional array containing the cell/column contents described above in the summary.

▸ GM_PROP(input_column_range_with_headers: string[][], property_id: string): any[][]

Defined in src/GM_PROP.ts:11

Inserts a property column, including a header row, with a common Gapminder property matched against the input column/table range.

Returns: any[][]

A two-dimensional array containing the cell/column contents described above in the summary.

▸ GM_PROP_AGGR(input_table_range_with_headers: string[][], aggregation_property_id: string): any[][]

Defined in src/GM_PROP_AGGR.ts:23

Aggregates an input table by a time-independent property and time, returning a table with the aggregated values of the input table.

The input table must be at least four columns wide.

• Column 1: geo_ids
• Column 2: geo_names (isn’t part of the calculation)
• Column 3: time
• Column 4+: values to be aggregated

Note: Uses GM_PROP internally

Returns: any[][]

A two-dimensional array containing the cell/column contents described above in the summary.

▸ GM_UNPIVOT(input_table_range_with_headers: string[][], time_label: string, value_label: string, page_size: number, page: number): string[][]

Defined in src/GM_UNPIVOT.ts:18

Unpivots a standard pivoted Gapminder table [geo, name, ...time-values-across-columns], converting the data column headers into time units and the column values as concept values.

Returns: string[][]

A two-dimensional array containing the cell/column contents described above in the summary.

▸ GM_WEIGHTED_AVERAGE(input_table_range_with_headers: string[][], aggregation_property_id: string, population_concept_data_table_range_with_headers: string[][]): any[][]

Defined in src/GM_WEIGHTED_AVERAGE.ts:25

Aggregates an input table by a time-independent property and time, returning a table with the population-weighted average values of the input table.

The input table must be at least four columns wide.

• Column 1: geo_ids
• Column 2: geo_names (isn’t part of the calculation)
• Column 3: time
• Column 4+: values to be aggregated

Note: Uses GM_PROP internally for the property lookup, and GM_DATA internally for the population lookup

Returns: any[][]

A two-dimensional array containing the cell/column contents described above in the summary.

▸ menuRefreshDataCatalog(): void

Defined in src/menuActions/menuRefreshDataCatalog.ts:13

Menu item action for "Gapminder Data -> Refresh data catalog"

Imports the data catalog from the fasttrack catalog to the current spreadsheet, setting the relevant selectable options in the data-dependencies spreadsheet.

Details:

• Creates the data-dependencies and data-catalog spreadsheets if they don't exist
• Verifies that the first headers of the data-dependencies spreadsheet are as expected

Returns: void

▸ menuRefreshDataDependencies(): void

Defined in src/menuActions/menuRefreshDataDependencies.ts:25

Menu item action for "Gapminder Data -> Import/refresh data dependencies"

Imports data sets from the fasttrack catalog to the current spreadsheet, allowing GM_-functions to reference local data instead of importing data on-the-fly.

Details:

• Creates the data-dependencies and data-catalog spreadsheets if they don't exist
• Verifies that the first headers of the data-dependencies spreadsheet are as expected
• Does not attempt to import data with bad catalog status
• Communicates import status as the import progresses

Returns: void

▸ menuValidateDatasetSpreadsheet(): void

Defined in src/menuActions/menuValidateDatasetSpreadsheet.ts:54

Menu item action for "Gapminder Data -> Validate this dataset spreadsheet" (only shown if the spreadsheet contains an "ABOUT" sheet and a named range called "dataset_id")

Validates if the current dataset spreadsheet conforms to the comments found in the template and writes the validation results in the Validation table at the bottom of the About sheet.

Details:

• Checks the row headers of the output sheets (the so called "data-for-world/region/countries-etc/income-levels-by-year")
• Checks the about sheet (to see if it follows the requirements in col A in the template)
• Checks that filter mode is not turned on in output sheets (since it breaks the CSV endpoint)

Returns: void