flowchart LR
id1(Google sheet) -->|parser| id2(Json) -->|data model| id3(Unity runtime)
This package converts data from Google sheets
to Json
files.
The json file structure is generated using custom parsers, which allows you to generate a json file of any structure from a Google sheet of any structure.
-
UniGSC required Newtonsoft Json Unity Package.
-
Install UniGSC. Use UPM to install the package via the following git URL:
https://github.com/dkoleev/UniGHC.git
To set up OAuth or API Key authorization, follow these steps:
- Open Google API Console and select the Credentials section under APIs & Services.
- Select Google APIs and Services project for your Unity application. If you do not have project select CREATE PROJECT to create new project.
- Enable the Google Sheets API support.
- Select the CREATE CREDENTIALS option and select either API Key or OAuth Client ID.
Wigh OAuth 2.0 authentication you can read and write from a public and private sheets. For more information, see Google’s OAuth 2.0 documentation.
When generating OAuth credentials, set the application type to Desktop (because plugin uses the service only through the Unity Editor).
After the credentials have been created, download the JSON file.
API keys are a simple encrypted string that can be used only for read data from public Google Sheets.
After the key has been created, click SHOW KEY and copy key to clipboard.
- Go to Enabled APIs & services
- Find Google Sheets Api and Enable it.
-
Create Google Sheets service Provider.
-
Right Click in Project tab.
-
Select
Create -> Yogi -> Google Sheets Configs -> Provider
-
Select created file.
-
Choose authentication type
-
OAuth
- Click
Load Credentials
and select credentials .json file downloaded from Google Sheets API. - Click
Authorize
.- Unity launches your web browser and opens a Google authentication page. Log in your Google account and allow Unity access to the account. If you don't click Authorize Unity opens the web browser when you pull data from the Google sheet.
- Click
-
Api Key
- Insert Api Key to field.
-
Create Google Sheets Configs file.
- Right Click in Project tab.
- Select
Create -> Yogi -> Google Sheets Configs -> Configs
- Select created file.
- Assigen the provider created in the previous step.
-
Add your first config
Name
- You can give it any name you want. Doesn't affect anything.Spread Sheet
- Spreadsheet id.
-
Sheets
Config Name
- The path where generated config .json file will be saved.Sheet Id
- The id of the sheet used to load the data.
Range
- Range of sheet used for loading. Examples: 'A1:E1000', '2:1000'. Leave empty to loading the entire sheet.Parser
- The way how to parse data loaded from sheet. Use 'default' parser or create your own.
-
Click
Pull Configs from Google Drive
to load google sheets configs into local json files.
-
Example
We have sheet config with monsters.
Setup config in Unity.
After pulling Monsters.json config will be created.
How you can see - with the 'default' parser, the first column is used as the key in json config.
💥 You can write any unique parser for each table to generate json files of the desired format 💥
Json.Net is used to parse google sheet data.
Default parser has id default
and parse sheet data to the next structure
{
"[first_column_current_row_value]" : {
"[first_column_first_row_value]" : "[first_column_current_row_value]",
"[second_column_first_row_value]" : "[second_column_second_row_value]",
...
"[n_column_first_row_value]" : "[n_column_n_row_value]",
}
}
Example
This sheet
will be parsed in next json structure
{
"monster_0": {
"id": "monster_0",
"name": "Big Boss",
"damage": 10
},
"monster_1": {
"id": "monster_1",
"name": "Small Boss",
"damage": 20
}
}
To use default parser set field Parser
in sheet config to default
.
For example - we have this Google sheet config
And we want parse it to this json format
{
"reward_0": {
"id": "reward_0",
"resources": [
{
"resource_id": "gems",
"amount": 10
},
{
"resource_id": "gold",
"amount": 5
}
]
},
"reward_1": {
"id": "reward_1",
"resources": [
{
"resource_id": "gold",
"amount": 100
}
]
}
}
...
Make next steps:
- Create new class
RewardsParser
and implement an interfaceISpreadsheetParser
.
using System.Collections.Generic;
using Yogi.GoogleSheetsConfig.Editor.Parsers;
namespace Editor {
public class RewardsParser : ISpreadsheetParser {
public string Parse(int sheetId, IList<IList<object>> sheetData) {
return string.Empty;
}
}
}
- Add
ParserType
attribute toRewardParser
class and name it for examplereward_parser
.
using System.Collections.Generic;
using Yogi.GoogleSheetsConfig.Editor.Parsers;
namespace Editor {
[ParserType("reward_parser")]
public class RewardsParser : ISpreadsheetParser {
public string Parse(int sheetId, IList<IList<object>> sheetData) {
return string.Empty;
}
}
}
- Parse sheetData to json object
using System.Collections.Generic;
using Framework.Editor.Google;
using JetBrains.Annotations;
using Newtonsoft.Json.Linq;
namespace GameGarden.Florescence.Editor.Configs.Parsers {
[UsedImplicitly]
[ParserType(SpreadsheetParserType.ByIdMultiplyRows)]
public class SpreadsheetParserByIdMultiplyRows : ISpreadsheetParser {
public string Parse(int sheetId, IList<IList<object>> sheetData) {
JObject dicJson = new JObject();
var itemList = new JArray();
var item = new JObject();
var key = "";
//go by rows
for (int i = 1; i < sheetData.Count; i++) {
//set key by first column
if (!string.IsNullOrEmpty(sheetData[i][0].ToString())) {
item.Add(new JProperty(sheetData[0][0].ToString(), sheetData[i][0].ToString().Replace(',', '.')));
key = sheetData[i][0].ToString();
}
var itemListItem = new JObject();
//go by columns for current row and add data to JObject
for (int j = 1; j < sheetData[i].Count; j++) {
itemListItem.Add(new JProperty(sheetData[0][j].ToString(), sheetData[i][j].ToString().Replace(',', '.')));
}
//add generated item to list items for key
itemList.Add(itemListItem);
//If we have reached the next key then add current to dictionary
if (i == sheetData.Count - 1 || !string.IsNullOrEmpty(sheetData[i + 1][0].ToString())) {
item["items"] = itemList;
dicJson[key] = item.DeepClone();
itemList.Clear();
item = new JObject();
}
}
return dicJson.ToString();
}
}
}
❕ Read Json.Net Documentation if you don't know how to generate json object.
- Set this parser to your Google sheets config
- Click
Pull
and json config will be generated.