Integration | Joystick for Google Sheets™ Usage
Transform the way operate your app or game; save hours of time. Ever wish you could make a change in Google Sheets™ then it is instantly reflected in your app or game?
Export content from any Google Sheets™ document as JSON into Joystick with one click. Joystick for Google Sheets™ gives you and your team direct control of your app or game. Make changes in your spreadsheet, then instantly have configuration and parameter updates available to your application at scale over our powerful API platform. No waiting.
Benefits of Joystick for Google Sheets™
Joystick for Google Sheets™ helps you take data in any Google Sheets™ document the exported it seamlessly as JSON into Joystick with one-click. It is then immediately available for delivery at scale so your app, game or system. Access configuration updates as soon as they are made in Google Sheets™!
- One-click export from Google Sheets™ as JSON into Joystick for distribution at scale.
- Define and export to multiple JSON configurations from the same Google Sheets™ document.
- Each config can be built from multiple tables and named ranges of the Google Sheets™ document; you are not restricted to just one data table per config.
- Create and export complex JSON structures, not just single-level tables.
- Schema and data type validation and protection; no headaches about missing data or type mismatches.
Core Concepts and Features
There are three components to setup.
| Component | Description |
|---|---|
| Joystick for Google Sheets™ Add-on | You have to install, give permissions and configure the Google Add-on. |
| Data Transform Definition | The Data Transform Definition config contains the schema and instructions on how to map the tabular data in Google Sheets™ to JSON. This should be a config placed in Joystick, in the same environment that you want to push to. |
| Target Content in Joystick | This is the final output config(s) that is going to available for delivery over the Joystick API. |
Setting Up Joystick for Google Sheets™
Install the Add-on
If you have not installed the add-on, view our installation guide first.
Accessing Settings
Clicking "Settings" when the add-on panel is open.
Settings: Add the API Key
Add an API Key generated in the environment your target config ContentId is held.
API Key Note
- The API key must be created with Read + Write permissions.
Settings: Add the Data Transform Definition
Before you can use Joystick for Google Sheets™, you have to create a data transform definition config, placed in the same environment you plan to push the Google Sheets™ data to. After you have created that config, you need to add it to the connector settings. See "The Data Transform Definition" section below for how to create the data mapping config.
Note
- The environment must already contain a config with the ContentId that you want to push in from Google Sheets. i.e. You cannot create a brand new ContentId using the add-on.
- The ContentId of the Data Transform Definition is not that of your target/output config.
Usage
When everything is setup, you will see two green indicators on the homepage of the add-on. Simply click the orange "Push Content to Joystick" button to use the add-on to push data into Joystick.
The Data Transform Definition
The Data Transform Definition is a configuration (JSON format) that specifies how to transform and compose the tabular data in your Google Sheets™ document as one or more pieces of JSON content.
Saving the Data Transform Definition
You must save the Data Transform Definition in Joystick. Make sure it is in the same environment as the other pieces of content you want to push to from your Google Sheets™ document.
Explaining the Data Transform Definition
The root of the Data Transform Definition is the dataBlocks array of objects.
Each object in the array represents one config or piece of content you want to push to from your Google Sheet
| Parameter | Description |
|---|---|
| contentId | The contentId for the target piece of content to push to. |
| template | The template is the structure of your output JSON. The template can integrate one or more components. It will have dynamic declarations using the convention of #...# where the dynamic data from your google sheets document should placed (see below for details). You must have a template defined. |
| components | A Component defines how to take the tabular data in your Google Sheets™ document then structure it as JSON for export. You can define multiple components, then stich/compose them together in one final output JSON. You must have at least one component defined. |
This is the most basic 'skeleton' setup with a simple template. The location of the value #data_table# will be replaced by a components object defined with the same parameter name, in this case components.data_table.
{
"dataBlocks": [
{
"contentId": "myContent",
"template": {
"myData": "#data_table#"
},
"components": {
"data_table": {}
}
}
]
}
Putting it all Together
Below you can see a simple data table in Google Sheets™ with some annotations.
Please Note
For "table", you must have a row above your data table where there is a label that corresponds to where the data should go within the component. In the screenshot these are in orange. In the Data Transform Definition, you can see them under the properties object.
View Sample Google Sheets™ Document for this example.
{
"dataBlocks": [
{
"contentId": "city-information", // The destination ContentId in Joystick
"template": { // The template with the shape of the destination content.
"cities": "#cities#"
},
"components": {
"cities": {
"type": "table",
"source": "sheet",
"sheetName": "City Information", // If source is "sheet", this is the sheet name on the Google Sheets bottom tab
"propertiesLabelRow": 3, // The row in the data range where the property labels are. If not defined, default is 1.
"dataStartRow": 9, // The row in the data range where the first row of data to be exported is.
"properties": // Matches basic JSON Schema structure.
{
"cityName": {
"type": "string",
"ignoreRowIfEmpty": true
},
"info": {
"type": "object",
"properties": {
"country": {
"type": "string"
},
"population": {
"type": "string"
},
"area": {
"type": "number"
},
"timezone": {
"type": "string"
}
}
}
}
}
}
}
]
}
{
"cities": [
{
"cityName": "Vancouver",
"info": {
"country": "Canada",
"population": "700000",
"area": 115,
"timezone": "UTC -8"
}
},
{
"cityName": "Toronto",
"info": {
"country": "Canada",
"population": "2900000",
"area": 630,
"timezone": "UTC -5"
}
},
{
"cityName": "Valencia",
"info": {
"country": "Spain",
"population": "800000",
"area": 134,
"timezone": "UTC +1"
}
},
{
"cityName": "Venice",
"info": {
"country": "Italy",
"population": "260000",
"area": 414,
"timezone": "UTC +1"
}
},
{
"cityName": "Tokyo",
"info": {
"country": "Japan",
"population": "13960000",
"area": 2194,
"timezone": "UTC +9"
}
}
]
}
Extended Example with All Features
Below is a full example showing the three components: Google Sheet, Data Transform Definition Config and the Output JSON.
What's happening...
- The extended example shows a single piece of content (Final Result) being built from three separate Google Sheets™ document tabs.
- "Level 3" in the final result is skipped because the Opponent Id is empty on that row and
ignoreRowIfEmptyis applied to that field.
Features Available
- Data can come from a sheet/tab.
- Data can come from a named range.
- Build rich, multi-level objects in JSON.
- The final output JSON can be assembled using data from multiple locations within the Google Sheets™ document.
- The data transformation/mapping is described by the straight-forward Data Transform Definition.
View Sample Google Sheets™ Document
{
// This example shows only one output content.
// Each Data Transform Definition can output to multiple different pieces of content/configs.
// Add more items to the "dataBlocks" array to do this.
"dataBlocks": [
{
"contentId": "gsheets_sandbox", // The destination ContentId in Joystick. This must be already created.
"template": { // The template with the shape of the intended JSON content.
"singleData": [
{
"text": "Static text." // You can have static entries in the template.
},
{
"singleDataString": "#my_greeting#", // In the template, the #...# convention declares where dynamic data from the Google Sheets document should be used.
"singleDataNumber": "#my_number#", // Each #...# links to one component defined in the "components" object.
"singleArrayOfBoolean": [
"#my_boolean#"
]
}
],
"myGameData": {
"gameSettings": "#my_game_settings#",
"levels": "#my_levels#"
}
},
"components": {
"my_greeting": {
"type": "string", // can be "table", "dict", "string", "boolean", "number"
"source": "namedRange", // Can be "sheet", "namedRange"
"rangeName": "myGreeting" // If source is "namedRange", the name of the Named Range in the Google Sheet
},
"my_number": {
"type": "number",
"source": "namedRange",
"rangeName": "myNumber"
},
"my_boolean": {
"type": "boolean",
"source": "namedRange",
"rangeName": "myBoolean"
},
"my_levels": {
"type": "table",
"source": "sheet",
"sheetName": "Game Levels", // If source is "sheet", this is the sheet name on the Google Sheets™ bottom tab
"propertiesLabelRow": 3, // The row in the data range where the property labels are
"dataStartRow": 7, // The row in the data range where the first row of data to be exported is.
"properties": { // Matches basic JSON Schema structure.
"level": {
"type": "number",
"ignoreRowIfEmpty": true // if this is set, an entire row will be ignore if this property is empty.
},
"name": {
"type": "string",
"allowEmpty": true // If this is true, the property will be set even if in the Google Sheet it is a blank cell.
},
"theme": {
"type": "object",
"properties": {
"background": {
"type": "string"
},
"foreground": {
"type": "string"
},
"playMusic": {
"type": "boolean",
"allowEmpty": true
},
"trackName": {
"type": "string",
"allowEmpty": true
},
"trackId": {
"type": "number",
"allowEmpty": true
}
}
},
"goals": {
"type": "array",
"items": {
"type": "object",
"properties": {
"name": {
"type": "string"
},
"points": {
"type": "number"
}
}
}
},
"opponent": {
"type": "object",
"properties": {
"name": {
"type": "string"
},
"id": {
"type": "number",
"ignoreRowIfEmpty": true
},
"hp": {
"type": "number"
},
"boosted": {
"type": "boolean"
},
"abilities": {
"type": "array",
"items": {
"type": "string"
}
}
}
}
}
},
"my_game_settings": {
"type": "dict", // Use this for a list of key:value pairs.
"source": "namedRange",
"rangeName": "myGameSettings",
"propertiesLabelRow": "2",
"dataStartRow": 5,
"properties": {
"gravity": {
"type": "number"
},
"movementSpeed": {
"type": "number",
"allowEmpty": true
},
"randomSpawn": {
"type": "boolean"
},
"mainBackgroundImage": {
"type": "string",
"allowEmpty": true
}
}
}
}
}
]
}
{
"singleData": [
{
"text": "Static text."
},
{
"singleDataString": "Hello, world!",
"singleDataNumber": 3.1415,
"singleArrayOfBoolean": [
false
]
}
],
"myGameData": {
"gameSettings": {
"gravity": 200,
"movementSpeed": 12,
"randomSpawn": true,
"mainBackgroundImage": "forest.jpg"
},
"levels": [
{
"level": 1,
"name": "First Level",
"theme": {
"background": "stars.jpg",
"foreground": "purple",
"playMusic": true,
"trackName": "Forest Sounds",
"trackId": 1
},
"goals": [
{
"name": "Good",
"points": 100
},
{
"name": "Great",
"points": 200
},
{
"name": "Excellent",
"points": 300
}
],
"opponent": {
"id": 1,
"name": "Heavy Hippo",
"boosted": true,
"hp": 837,
"abilities": [
"Lightning",
"Cloak"
]
}
},
{
"level": 2,
"name": "Beginner",
"theme": {
"background": "sunnyday.jpg",
"foreground": "green",
"playMusic": null,
"trackName": "",
"trackId": null
},
"goals": [
{
"name": "Good",
"points": 150
},
{
"points": 300
},
{
"name": "Excellent",
"points": 600
}
],
"opponent": {
"id": 2
}
},
{
"level": 4,
"name": "Advanced",
"theme": {
"background": "dusk.jpg",
"foreground": "blue",
"playMusic": true,
"trackName": "Desert Sounds",
"trackId": 3
},
"goals": [
{
"name": "Good",
"points": 500
},
{
"name": "Great",
"points": 900
},
{
"name": "Excellent",
"points": 1900
}
],
"opponent": {
"id": 4,
"name": "Eli the Elephant",
"boosted": true,
"hp": 12000,
"abilities": [
"Earthquake"
]
}
}
]
}
}
Component Types
string, number, boolean, object, array
Specifying any of these types will simply attach the contents of a single cell as that type.
- If you are using a named range or sheet, the first cell in the range will be used.
Setup: Data Transform Definition
dict
The dict component type is used to create a key-value pair structure in the JSON output. The key-value pairs can be sourced from a named range or a sheet/tab. Use dict if you explicitly want to define key-value pairs, and know the schema of your output. Use kv_pairs if you want to ingest arbitrary key:value pairs from the Google Sheet data.
Setup: Google Sheets
- You can use a named range or a sheet/tab as the source. If not specified, by default it will expect the first column to have the key and the second column to have the value.
- By default, the first row of the data range is considered the properties label row, but you can specify a different row in your data range using the
propertiesLabelRowparameter. - By default the first column will be used as the key and second will be value. You can put the keywords
exportKey,exportValueon the properties label row to specify the key and value column. Only these two columns will be processed. All other columns in the data range will be ignored.
- By default, the first row of the data range is considered the properties label row, but you can specify a different row in your data range using the
Setup: Data Transform Definition
typemust be set asdict.source- Can be
namedRangepaired withrangeNameto specify the named range. - Can be
sheetpaired withsheetNameto specify the sheet/tab.
- Can be
propertiesLabelRow: The row in the data range where the property labels are.dataStartRow: The row in the data range where the first row of data to be exported is.- NOTE: with a dict, only the keys you have defined in the
propertiesobject will be exported. - NOTE: with dict, the type will be validated against the schema you have defined in the
propertiesobject.
{
"dataBlocks": [
{
"contentId": "my-config-a",
"template": {
"myDict": "#dictComponent#"
},
"components": {
"dictComponent": {
"type": "dict",
"source": "namedRange",
"rangeName": "configRangeA",
"propertiesLabelRow": 1,
"dataStartRow": 1,
"properties": // Matches basic JSON Schema structure.
{
"keyA": {
"type": "string"
},
"keyB": {
"type": "number"
},
"keyC": {
"type": "boolean"
},
"keyD": {
"type": "object",
"properties": {
"subKeyA": {
"type": "string"
},
"subKeyB": {
"type": "number"
}
}
}
}
}
}
}
]
}
table
The table component is for creating a JSON array of objects from a table in Google Sheets. Use this if you have an array, and want each row to be an object in that array.
typemust be set astable.source- Can be
namedRangepaired withrangeNameto specify the named range. - Can be
sheetpaired withsheetNameto specify the sheet/tab.
- Can be
- You must have a row above your data table where there is a label that corresponds to where the data should go within the component.
- This row number is identified with
propertiesLabelRow.
- This row number is identified with
- The
tablecomponent is a flexible and powerful component type, allowing you to create rich JSON structures with nested objects and arrays. - There is a sub-type you can apply to objects called
nestedDictwhich allows you to create a nested dictionary structure within the table.
{
"dataBlocks": [
{
"contentId": "test-config",
"template": {
"nestedTable": "#component#"
},
"components": {
"component": {
"type": "table",
"source": "sheet",
"sheetName": "test",
"propertiesLabelRow": 4,
"dataStartRow": 6,
"allowEmpty": false,
"ignoreRowIfEmpty": true,
"properties": // Matches basic JSON Schema structure.
{
"cityName": {
"type": "string",
"ignoreRowIfEmpty": true
},
"info": {
"type": "object",
"properties": {
"country": {
"type": "string"
},
"population": {
"type": "string"
},
"area": {
"type": "number"
},
"timezone": {
"type": "string"
}
}
},
"myNested": {
"type": "object",
"subtype": "nestedDict",
// The key for each nested object is the value in column labeled
// with the the path to this object.
"properties": {
"head": {
"type": "string",
"allowEmpty": false,
"ignoreRowIfEmpty": true,
},
"middle": {
"type": "string"
},
"end": {
"type": "string"
}
}
},
"myArray": {
"type": "array",
"items": {
"type": "string"
}
}
}
}
}
}
]
}
{
"nestedTable": [
{
"cityName": "city name here",
"info": {
"country": "a",
"population": "b",
"area": 1,
"timezone": "c"
},
"myArray": [
"a",
"b"
],
"myNested": {
"key1": {
"head": "dataA",
"middle": "moreDataA",
"end": "MoreMoreA"
},
"key2": {
"head": "dataB",
"middle": "moreDataB",
"end": "MoreMoreB"
}
}
},
{
"cityName": "Another city name here",
"info": {
"country": "b",
"population": "c",
"area": 2,
"timezone": "d"
},
"myArray": [
"c",
"d"
],
"myNested": {
"key1": {
"head": "dataA",
"middle": "moreDataA",
"end": "MoreMoreA"
},
"key2": {
"head": "dataB",
"middle": "moreDataB",
"end": "MoreMoreB"
}
}
}
]
}
kv_pairs
The kv_pairs component type is used to create a key-value pair structure in the JSON output. The key-value pairs can be sourced from a named range or a sheet/tab. This is a very flexible component type that can be used to create a wide variety of JSON structures.
- Input
- Output
Setup: Google Sheets
- You can use a named range or a sheet/tab as the source. If not specified, by default it will expect the first column to have the key and the second column to have the value.
- By default, the first row of the data range is considered the properties label row, but you can specify a different row in your data range using the
propertiesLabelRowparameter. - You can put the keywords
exportKey,exportValueon the properties label row to specify the key and value column.
- By default, the first row of the data range is considered the properties label row, but you can specify a different row in your data range using the
Setup: Data Transform Definition
typemust be set askv_pairs.source- Can be
namedRangepaired withrangeNameto specify the named range. - Can be
sheetpaired withsheetNameto specify the sheet/tab.
- Can be
propertiesLabelRow: The row in the data range where the property labels are.dataStartRow: The row in the data range where the first row of data to be exported is.valueType: The type of the value. Can bearray,object,string,number,boolean.
{
"dataBlocks": [
{
"contentId": "my-config-a",
"template": {
"myKvPair": "#kvPairComponent#"
},
"components": {
"kvPairComponent": {
"type": "kv_pairs",
"source": "namedRange",
"rangeName": "configRangeA",
"propertiesLabelRow": 1,
"dataStartRow": 1,
"valueType": "array" // array, object, string, number, boolean
}
}
},
{
"contentId": "my-config-b",
"template": {
"myKvPair": "#kvPairComponent#"
},
"components": {
"kvPairComponent": {
"type": "kv_pairs",
"source": "namedRange",
"rangeName": "configRangeB",
"propertiesLabelRow": 1,
"dataStartRow": 1,
"valueType": "object" // array, object, string, number, boolean
}
}
}
]
}