Google Sheets Configs for Unity game engine.

flowchart LR
id1(Google sheet) -->|parser| id2(Json) -->|data model| id3(Unity runtime)

Loading

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.

Table of contents

• Installation
• Setup
• Creating Credentials in Google API Console
• Connect Unity project to Google Sheets service
• Setup local configs
• Parsers
  • Default parser
  • Create custom parser

Installation

1. UniGSC required Newtonsoft Json Unity Package.

   How to install Newtonsoft Json

2. Install UniGSC. Use UPM to install the package via the following git URL:

   https://github.com/dkoleev/UniGHC.git
   

   

Setup

Creating Credentials in Google API Console

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.

OAuth

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 Key

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.

Activate Google Sheet service

• Go to Enabled APIs & services

• Find Google Sheets Api and Enable it.

Connect Unity project to Google Sheets service

• 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.

  • Api Key

    • Insert Api Key to field.

  Setup local configs.

  • 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.

Parsers

💥 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

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.

Create custom parser

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 interface ISpreadsheetParser.

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 to RewardParser class and name it for example reward_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.