Advanced Formatting

Data Types

How types are determined

To avoid parsing issues in applications consuming the data, Starsheet will automatically infer the data type of a particular field based on all the values of that column. For example, if an entire column only contains numeric values (or null/empty values) the data will be output as a number, but if it contains a mix of numeric values and strings, all instances of that field will be output of strings.

The exception to this is when a sheet has been set to be output as key-value pairs, which causes the type of each item to be evaluated independently.

Booleans

The following values are acceptable as booleans and will be converted to a boolean true or false in the output:

• A Ticked or Unticked Checkbox

• The string Y or N

• The string 1 or 0

• The string TRUE or FALSE

• When combined exclusively with the above “truthy” values, a null/empty value will be interpreted as false

Arrays

Multiple columns can be combined into an array, by using the same column title suffixed with square brackets ([]). For example a sheet with the following structure:

name	tags[]	tags[]	tags[]
item_a	category_a	category_b	category_c
item_b	category_b

Would produce this output:

{
  "worksheet_name": [
	{
		"name": "item_a",
		"tags": ["category_a", "category_b", "category_c"]
	},
	{
		"name": "item_a",
		"tags": ["category_b"]
	}
  ]
}

Objects

To output fields as objects, add periods to the column title to indicate how they should be nested. For example a sheet with the following structure:

name	size.height	size.width
item_a	200	100
item_b	400	150

Would produce this output:

{
  "worksheet_name": [
	{
		"name": "item_a",
		"size": {
			"height": 200,
			"width": 100,
		}
	},
	{
		"name": "item_a",
		"size": {
			"height": 200,
			"width": 100,
		}
	}
  ]
}

Excluding Data from Output

Hiding Worksheets or Columns

To prevent a worksheet or column from being included in the output, prefix the column name or worksheet name with a # symbol.

Hiding Rows

To hide a row, create a column with the heading !hide and set it to a true value. A checked tickbox, 1, TRUE or Y are all treated as true.

Alternatively, to hide all rows by default, add a column with the heading !show. All rows that don’t have a true value in this column will be hidden.

Modifying JSON Document Format

Outputting a Worksheet as objects indexed by a key

By default, a worksheet is outputted in JSON as an array of objects.

{
  "worksheet_name": [
	{
		"first_column_name": "a",
		"second_column_name": 1,
	},
	{
		"first_column_name": "b",
		"second_column_name": 2,
	}
  ]
}

Default Output

If you would prefer it to be output as an object, indexed by a key add a column with the title !id. The value of this field will be used as the key.

{
  "worksheet_name": {
	"first_value_from_!id_field": {
		"first_column_name": "a",
		"second_column_name": 1,
	},
	"second_value_from_!id_field": {
		"first_column_name": "b",
		"second_column_name": 2,
	}
  }
}

Modified output using !id field

Outputting a Worksheet as Key-Value Pairs

If a worksheet only contains two columns with the titles !key and !value they will be output in the following format.

You may still use non-outputted columns such as a commented column or !hide in worksheets using this format.

{
  "worksheet_name": {
	"key_1": "value_1",
	"key_2": true,
	"key_3": 100,
  }
}