How to work with Google Spreadsheet

This page of the document describes and helps you to set up and use Unity-QuickSheet with 'Google Spreadsheet'.

Setting up OAuth2 for accessing Google Drive

Google has changed the authentication scheme since May 5, 2015. Now, to access a google spreadsheet you need to set up OAuth2. To set this up, visit Google Developer Console then create a new project, enable the Drive API, create a new client ID of type "service account" and download json file.

See this page for setting up credentials and getting OAuth2 'client_ID' and 'client_secret' those are needed to set up google sreadsheet setting file.

Create a spreadsheet and worksheet

Create a google spreadsheet on your 'Google Drive' after logging in your 'Google Drive' with your account.

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 a name of cells on the first row will be used as member field's name of a generated class, you will see later on this page. So, be careful not to use such a name like 'int', 'string', which are keywords of C#.

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.

If you not set 'OAuth2 Service Acccout' yet, see OAuth2 setting on Google APIs page found on this page. You must set that up first before importing data into Unity editor.

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 with the following image:

Select 'Allow' button 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

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.
  3. Editor inidicates a path where generated script files which are used on editor mode will be put.
  4. Spread Sheet Name is what the name of the spreadsheet which is created on google drive.
  5. Work Sheet Name is one of the worksheet name of the spreadwheet you want to get data from.

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

    public string Key { get {return key; } set { key = value;} }

    [SerializeField]
    string text;

    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!

If you encourter any troubles or problems see the TroubleShoting page on here.

results matching ""

    No results matching ""