How to work with Google Spreadsheet

This post shows and helps you how to set up and use Unity-QuickSheet with Google Spreadsheet.

First, you need to create a google spreadsheet on your Google Drive. Login your Google Drive with your google account and create a new spreadsheet.

Change the title of the created spreadsheet as ‘MySpreadSheet’ like the following:

Next, create a new worksheet and rename it to whatever you want to as the following image shows:

Now, it needs to edit cells for spreadsheet. Insert ‘Key’ and ‘Text’ at the first row of the created worksheet as like that:

IMPORTANT
Note that the first row should not contain any values which are used for your class members.

Google OAuth2 Service Account

Before futher going, you need to create ‘Google OAuth2 Account’ to verity your account and make Unity available to access on your google spreadsheet.

Google has changed the authentication scheme since May 5, 2015. Now it requires OAuth2. To set this up visit http://console.developers.google.com , create a new project, enable the Drive API, create a new client ID of type “service account” and download json file for the private key which is used to verity your credentials.

See the OAuth2 Service Account article to create ‘Google OAuth2 Service Account’. One thing to note is that you should create json type for the private key not the p12 one to use it on Unity-Quicksheet.

If you successfully get the json private key then select ‘GoogleDataSettings.asset’ file which can be found under ‘Assets/QuickSheet/GDataPlugin/Editor’ folder.

1. First, set the downloaded json private key to the ‘JSON File’.
2. Now, you can see 2)‘Client ID’ and 3)‘Client Secret’ will be automatically sepcified
3. Click ‘Start Authentication’ button it will launch your browser.

Select ‘Allow’ then go to next.

Now you will see the ‘access code’. Copy it and paste to the Unity’s 4)‘Access Code’ setting.

The final step is to click ‘Finish Authenticate’ button to verify your credentials.

And set other settings like ‘Runtime Path’ and ‘Editor Path’ for your project.

It may enough to set as the following:

Runtime Path: Data/Runtime
Editor Path: Data/Editor

Note that it assumes that the ‘Data’ folder is under the ‘Assets’ folder.

Step 1) Creating Google Spreadsheet Setting File

First you need thing to do is creating a google spreadsheet setting file. Simply right click on the Project view and select ‘Create > Spreadsheet Tools > Google’. It creates a new file which shows various setting to create script files and get data from the specified google spreadsheet.

Select Google menu item then it creates setting file. It may be shown like the following:

Script Path Setting

1. Template indicates a path where template files which are neccessary to generate script files. In most case you don’t need to change it.
2. Runtime indicates a path where generated script files which are used on runtime will be put. If you already specified it in the ‘GoogleDataSettings.asset’ file, that path will be automatically shown on.
3. Editor inidicates a path where generated script files which are used on editor mode will be put. If you already specified it in the ‘GoogleDataSettings.asset’ file, that path will be automatically shown on.
4. Spread Sheet Name is what the name of the spreadsheet which is created on google drive. Note that the name is case sensitive.
5. Work Sheet Name is one of the worksheet name of the spreadwheet you want to get data from. Note that the name is case sensitive.

After doing done with all path setting, press Import button then it shows all column headers of the page. That are neccessary to let you set the type of the each cells.

Set the proper type of the cells.

Currently the following types are supoorted:

• string
• int
• long
• float
• double
• enum
• bool

Step 2) Generating Script Files

If you’ve done all necessary setting, it’s time to generate some script files which are needed for reading data in from the sheet page of the excel file and to store that within ScriptableObject which is being as an asset file in the Project View.

Press Generate button.

After generating some script files, Unity Editor starts to compile those. Wait till Unity ends doing compile then check the specified Editor and Runtime paths all necessary script files are correctly generated.

In Editor folder should have contain two files:

• your-sheetpage-nameAssetCreator.cs
• your-sheetpage-nameEditor.cs

In Runtime foller should have contain tow files:

• your-sheetpag-name.cs
• your-sheetpage-nameData.cs

See the your-sheetpage-nameData.cs file. The class members of the file represent each cells of the sheet page.

using UnityEngine;
using System.Collections;

///
/// !!! Machine generated code !!!
/// !!! DO NOT CHANGE Tabs to Spaces !!!
///
[System.Serializable]
public class PlayerItemData
{
	[SerializeField]
	string key;
	
	[ExposeProperty]
	public string Key { get {return key; } set { key = value;} }
	
	[SerializeField]
	string text;
	
	[ExposeProperty]
	public string Text { get {return text; } set { text = value;} }

Step 3) Importing Spreadsheet Data

Now you need to create an asset file which will imports and stores all data from google drive. Simply right click on the Project view and select ‘Create > Google’. Now there is a new menu item which has same name with the worksheet name of the google spreadsheet.

Select the menu item then it creates an asset file. It may be shown like the following:

Note that the created asset file has same file name as the specified worksheet name.

1. Type Username and Password of your google account.
2. Specify the same Spreadsheet and Worksheet name as the google setting.
3. Press Download button then it starts to import data from google drive. (It may takes a few seconds.)

Finished downloading shows the imported data on the Inspector View like the following:

It’s done. Hope you enjoy that!

TroubleShoting

Invalid Credential Error

If you met an error which is shown as an invalid credentials when you try to get data by clicking ‘download’ button, check that your google accout page and you have two-stage verification.

If you have Google two-stage verification on, then it doesn’t matter what your Google password is, it won’t be accepted. You need to generate (on Google) what is called an Application Specific Password (ASP). Go to Google Account Page and set up an ASP, enter the password you generate as the password in your code, and you’re done.

Security Error

Google Spreadsheet plugin does not work in the Unity web player’s security sandbox. You should change the Platform to ‘Stand Alone’ or something else such as ‘iOS’ or ‘Android’ platform in the Build Setting.