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,
  }
}

About Starsheet

Project Setup

Managing Content

Advanced Topics

Data Types

How types are determined

Booleans

Arrays

Objects

Excluding Data from Output

Hiding Worksheets or Columns

Hiding Rows

Modifying JSON Document Format

Outputting a Worksheet as objects indexed by a key

Outputting a Worksheet as Key-Value Pairs

​Data Types

​How types are determined

​Booleans

​Arrays

​Objects

​Excluding Data from Output

​Hiding Worksheets or Columns

​Hiding Rows

​Modifying JSON Document Format

​Outputting a Worksheet as objects indexed by a key

​Outputting a Worksheet as Key-Value Pairs

Data Types

How types are determined

Booleans

Arrays

Objects

Excluding Data from Output

Hiding Worksheets or Columns

Hiding Rows

Modifying JSON Document Format

Outputting a Worksheet as objects indexed by a key

Outputting a Worksheet as Key-Value Pairs