Spreadsheet
Intro
Read data from a Google Sheet or .csv file.
UI
File - Drag in a Google Sheet or .csv asset from the Assets Window.
Column Title - Select a column from the spreadsheet. The first row of the spreadsheet is reserved and used to populate this list.
A Spreadsheet Utility can only output data from a single column. In order to use several columns from the same .csv or Google Sheet create a Spreadsheet Utility for each column to output.
Factorize - When checked, a column of text can be converted into values. For example, a column of data containing the text Summer, Spring, Summer, Summer can be converted to a list of values 1, 0, 1, 1. The resulting list is ordered alphabetically.
When using Factorize, the asset (.csv or Google Sheet) must also be 'factorized' for performance reasons. Right click on the asset in the Assets Window and check Factorize.
Remapping - Remap numerical values relative to the lowest and highest number in the data. For example, if you want to create a bar chart and need a value of 1 to be represented by a rectangle with a height of 300px, you can.
- None - Remapping disabled.
- Area Range - See Area Range.
- Number Range - See Number Range.
- Number Range to Color - See Number Range to Color.
If you have a minimum value of 0 and a maximum of 10 within the data and a simple black to white gradient, a value of 0 will be Black, a value of 10 will output White and a value of 5 will be remapped to 50% grey.
Fixed Row - When checked it will output the contents of the row specified in Row Index (not including the title row). Useful for Dynamic Rendering.
Row Index - Specify the row to output. A value of 0 outputs row 1 of the spreadsheet (row 0 is reserved for Column Title).
Row Offset - Add or subtract a value to Row Index.
Use Every Nth Row - Only sample the first and then every Nth row where N = the value.
Row Count - A read only attribute that outputs the number of rows in the selected column.
Column Count - A read only attribute that outputs the number of columns in the data. Note that the count starts at the first column and ends on the last column that contains data. For example, if importing the data below via a Google Sheet, a Column Count of 7 (columns A-G) would be returned:
| A | B | C | D | E | F | G | H | I |
|---|---|---|---|---|---|---|---|---|
| 1 | 1 | 1 |
Note this may vary for a .csv file where empty columns can still be defined. For example, the columns defined by the commas after 'ColumnB' in ColumnA,ColumnB,,,,, would be included in the Column Count.
Interpolate - When checked, data can be blended across columns. This requires the Column Title to be animated which can be achieved by connecting a Value Behaviour to the Column Title and keyframing the Value's Value attribute. Note - a value of 0 represents the first column.
Output Mode - Set the output of the Layer:
- Data - Output the raw data.
- Sort Order (Ascending) - Output the sort order of the column's data from the smallest to the largest value.
- Sort Order (Descending) - Output the sort order of the column's data from the largest to the smallest value.
Sort Order takes the values within a column and outputs their sort order. For example, if a column called Data contains the values below and the Output Mode is set to Sort Order (Ascending), the Spreadsheet will output the values in Sort Order.
| Data | Sort Order |
|---|---|
| 56 | 3 |
| 3 | 0 |
| 17 | 1 |
| 40 | 2 |
If the Output Mode is set to Sort Order (Descending) the Sort Order is reversed. This can be useful for things like leaderboards.
| Data | Sort Order |
|---|---|
| 56 | 0 |
| 3 | 3 |
| 17 | 2 |
| 40 | 1 |
The output can then be used, for example, to set Shape Position Y of a Duplicator in order to position the largest bar of a bar chart at the top and the smallest at the bottom of a Linear Distribution. Remapping can then be used to convert the Sort Order into meaningful position values. For example, convert the Sort Order of 0, 1, 2 to values of 0, 50, 100.
- Create a Text Shape, a Duplicator and a Spreadsheet Utility.
- Create a
.csvfile containing a row titled 'Name' with several names listed below.
Name,
Ringo,
John,
George,
Paul
- Import the
.csvfile to the Assets Window. - Drag the
.csvinto the File attribute of the Spreadsheet Utility. - Set Column Title to 'Name' on the Spreadsheet Utility.
- Connect textShape.id→duplicator.inputShapes.
- Set the Duplicator's Distribution to Linear.
- Set the Duplicator's Direction to Vertical.
- Set the Duplicator's Count to 4.
- Connect spreadsheet.id→textShape.string.
The first 4 names from the .csv (not including the title) will appear. For more or less than 4 names just adjust Count Y on the Duplicator.
- Create a
Google Sheetor.csvcontaining the data below:
| Label | Value | Color |
|---|---|---|
| 235 | #00acee | |
| 1135 | #c13584 | |
| YouTube | 101 | #ff0000 |
| Vimeo | 2301 | #1ab7ea |
| TikTok | 764 | #69c9d0 |
- If using a Google Sheet, ensure Link Sharing is set to Anyone with the link.
- Import the Google Sheet or .csv via the Assets Window.
- Drag the Google Sheet or .csv asset into the Viewport (this will create a Spreadsheet Utility and connect the asset).
- On the Spreadsheet Utitlity, set Column Title to Value.
- Create a Rectangle.
- Connect spreadsheet.id→rectangle.size.height.
- Select the Rectangle and click the Duplicator icon in the Shelf.
- Set Distribution to Linear.
- Set Count to 5.
At this point you should have 5 rectangles each with a height set as per each row of the Value column in the data. In order to pin the rectangles to a 'floor' you can use an Align Behaviour.
- Create an Align.
- Connect align.id→rectangle.deformers.
- On the Align Behaviour, set Y to 1.
If the data contains small/large values then you may need to remap the values to something that produces results that better fit your Composition.
- On the Spreadsheet Utility set Remapping to Number Range.
- Set Source Minimum to 0.
- Set Source Maximum to 2500. These two values roughly contain the range of the data.
- Set Minimum to 0.
- Set Maximum to 500.
A value of 0 in the data will now output as 0 while a value of 2500 will be output at 500. In this example, we are effectively dividing all values by 2500/500 = 5 so a value of 1000 would be remapped to 200.
Fields that contain currency symbols (e.g. £ € $) are recognised as numeric values. For example, £123.45 will be interpreted as 123.45 when connected to a numeric attribute.
If values are suffixed by a k, M or B then the the result is multiplied by a thousand, million or billion respectively. For example, 1.5k will be converted to 1500 when connected to a numeric attribute.