Use Google Spreadsheets as a CMS for Your Unity Game

readme.md

Step 1: Make a Google Doc With Your Models

• Row 1: Name of your properties
• Row 2: Class of your properties
• Col 1: id (an int that you fill in 1,2,3....n) -- kind of annoying but pretty easy to automate. if it's a model where you don't care about the ID and don't use the ID as a foreign_key somewhere else, you can also set the id column's type to "auto" and leave that column blank
• Col 2-?: your properties! as many as you want

Sample file: https://docs.google.com/spreadsheets/d/1VvoMnqDg_dtGN4lp0A-NnBIBgAPKxePnG9gHwypSLOY/edit?usp=sharing (this is our level model for Defend the Dam)

Supported property types:

• auto: auto-incrementing integer (it'll fill in 1,2,3 etc)
• int
• string
• float
• bool
• enum: this will use reflection to examine the enum type from the type of the property

Step 2: Get a CSV With Your Models

put your CSVs in the Resources folder

on Google spreadsheet you can just download a tab as a csv, but then you have to browse to the right path and it's a bit cumbersome. i wrote a ruby script to automate it (see below). i like to check this file into my project, but it's a big no-no to put your gmail email/password in cleartext in your repository... so I make a dummy account (like your_email_api@gmail.com) and then share the document from my main account with the dummy email.

to use the script, you need to go into the script and update the names hash to be {"name_of_your_desired_csv" => "name_of_the_tab_on_your_spreadsheet"} and then at the bottom of the file update the path to your resource folder from wherever the script lives (and make sure you have a Models folder in your Resources folder)

(I'm on a Mac, god help you if you're on Windows) -- now from a terminal you can run: ./update_models -- this will download every single sheet in your names hash -or- ./update_models name_of_csv -- this will update just one of the csvs

Step 3: Make a C# Model and Read in Your CSV

add BaseModel.cs (below) somewhere to your project. this is the base class for a CSV-loaded model and uses a lot of reflection magic to hook things up right. this will mean that you need to use a C# runtime that supports reflection (if you go down in Unity export settings to some of the really small ones this will stop working).

now you just need to extend BaseModel with a new class for each file you want to read in from CSV

you need to explicitly define every field from your CSV of the proper type (including id) or BaseModel won't be able to hook everything up properly. you can include any derived properties or extra run-time properties you want (as well as any methods or helpers you might need).

BaseModel by default provides:

• an All method that returns a typed list of your sublass (List all = LevelModel.All();)
• a Find method that takes an ID and returns a specific item (LevelModel level_23 = LevelModel.Find(23));
• a FindByProperty method that takes a property and find the first item that matches (it's totally on you to keep those properties unique, though) -- (LevelModel level_23 = LevelModel.FindByProperty("name", "Level 23");)
• a FindAllByProperty which does the same thing but returns a List

See below for a sample implementation based on that google spreadsheet I linked above

BaseModel.cs

using UnityEngine;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Reflection;

public abstract class BaseModel<T> where T : BaseModel<T>
{
	private static bool SHOW_DEBUGGING_MESSAGES = false;

	protected static Dictionary<string, Dictionary<object, T>> cache;

	/**
	 * Populates the static cache of class T with values interpreted
	 * from the CSV file at "filename."  Expects CSV to be unquoted,
	 * whitespace sensitive, and composed of only ints, floats, bools,
	 * strings, and enums.
	 */
	public static void LoadFromData(string filename)
	{
		cache = new Dictionary<string, Dictionary<object, T>>();
		cache.Add("id", new Dictionary<object, T>());

		string[] text = (Resources.Load("Models/" + filename, typeof(TextAsset)) as TextAsset).text.Split('\n');
		
		if(SHOW_DEBUGGING_MESSAGES) Debug.Log("Preparing to load " + (text.Length-2) + " models from " + filename + ".csv");
		
		string[] props = text[0].Trim().Split(',');
		string[] types = text[1].Trim().Split(',');
		string[] row;
		
		List<int> ignoreCols = new List<int>();

		for(int i = 0; i < props.Length; i++)
		{
			if(CheckForField(props[i], types[i]))
			{
				if(SHOW_DEBUGGING_MESSAGES) Debug.Log("Including column " + i + " named " + props[i] + " in import.");
			}
			else
			{
				ignoreCols.Add(i);
				if(SHOW_DEBUGGING_MESSAGES) Debug.Log("Error parsing column " + i + ", named \"" + props[i] + "\" of type " + types[i] + "; column will be ignored.");
			}
		}

		T item;
		for(int j = 2; j < text.Length; j++)
		{
			item = System.Activator.CreateInstance<T>();
			row = text[j].Trim().Split(',');

			if(row.Length >= props.Length)
			{
				for(int i = 0; i < props.Length; i++)
				{
					if(!ignoreCols.Contains(i))
					{
						item.PopulateField(props[i], types[i], props[i], row[i]);
					}
				}
				if(SHOW_DEBUGGING_MESSAGES) Debug.Log("CACHING MODEL WITH ID " + typeof(T).GetField("id").GetValue(item));
				cache["id"].Add((int)typeof(T).GetField("id").GetValue(item), item);
			}
			
			MethodInfo onCreationComplete = typeof(T).GetMethod("OnCreationComplete");
			onCreationComplete.Invoke(item, null);
		}
	}
	
	//OVERRIDE ME TO DO POST-INIT FUNCTIONALITY
	//using the constructor, you won't have access to any of the loaded data yet
	virtual public void OnCreationComplete()
	{
	}
	
	private static List<T> _allCached;
	public static List<T> All
	{
		get
		{
			if(_allCached == null)
			{
				_allCached = new List<T>(cache["id"].Values);
			}
			return _allCached;
		}
	}

	/**
	 * Returns the item of the cache with the given id, if such an
	 * item exists.  Can return a single T item or can fail with an
	 * invalid dictionary call.
	 */
	public static T Find(int id)
	{
		if(SHOW_DEBUGGING_MESSAGES) Debug.Log("Found " + cache["id"].Count + " things.");
		if(cache["id"].ContainsKey(id))
		{
			return cache["id"][id];
		}else{
			return null;
		}
	}

	public static bool ContainsId(int id)
	{
		if(SHOW_DEBUGGING_MESSAGES) Debug.Log("ID tested: " + id + " || Result: " + cache["id"].ContainsKey(id));
		return cache["id"].ContainsKey(id);
	}

	/**
	 * Returns the item whose field named "prop" has the value
	 * provided as "value" iff such an item exists AND all items
	 * in the cache have unique values for "prop."  If these 
	 * conditions are met, builds a cache on the provided "prop"
	 * and returns the requested value.  Can return null or a
	 * single T object, or can throw an error.
	 */
	public static T FindByProperty(string prop, object value)
	{
		// Filter invalid queries.
		if(typeof(T).GetField(prop) == null || typeof(T).GetField(prop).FieldType != value.GetType())
			return null;

		// Return the query value, if available.
		if(cache.ContainsKey(prop))
		{
			if(cache[prop].ContainsKey(value))
			{
				return cache[prop][value];
			}else{
				return null;
			}
		}

		// Build the new cache lazily, if necessary.
		cache.Add(prop, new Dictionary<object, T>());
		foreach(T item in cache["id"].Values)
		{
			object key = typeof(T).GetField(prop).GetValue(item);
			if(key == null || cache[prop].ContainsKey(key))
			{
				throw new Exception("Illegal attempt to fetch models on non-unique or non-ubiquitous property \"" + prop + "\" (value = '" + key + "'.");
			}else{
				cache[prop].Add(key, item);
			}
		}
		
		if(cache[prop].ContainsKey(value))
		{
			return cache[prop][value];
		}else{
			return null;
		}
	}

	/**
	 * Returns a list of all items in the cache that have "value"
	 * in their field "prop."  Can return null, an empty list, or
	 * a list of values.
	 */
	public static List<T> FindAllByProperty(string prop, object value)
	{
		// Filter invalid queries.
		System.Reflection.FieldInfo field = typeof(T).GetField(prop);
		if(field == null || field.FieldType != value.GetType())
			return null;
		
		List<T> matches = new List<T>();
		
		// Return the query value, if available.  If cache exists, then there is certainly at most one match.
		if(cache.ContainsKey(prop))
		{
			matches.Add(cache[prop][value]);
			return matches;
		}
		
		foreach(T item in cache["id"].Values)
		{
			if(field.GetValue(item).Equals(value))
				matches.Add(item);
		}
		return matches;
	}

	private static bool CheckForField(string prop, string type)
	{
		return typeof(T).GetField(prop) != null && typeof(T).GetField(prop).FieldType == ParseType(type, prop);
	}

	private static System.Type ParseType(string type, string prop)
	{
		if(type == "auto")
			return typeof(int);
		if(type == "int")
			return typeof(int);
		if(type == "string")
			return typeof(string);
		if(type == "float")
			return typeof(float);
		if(type == "bool")
			return typeof(bool);
		if(type == "enum" && typeof(T).GetField(prop) != null && typeof(T).GetField(prop).FieldType.IsEnum)
			return typeof(T).GetField(prop).FieldType;
		return System.Type.GetType(type);
	}
	
	private static int _autoincrement;
	private static int GetNextAutoIncrement()
	{
		return _autoincrement++;
	}

	private static int ParseInt(string value)
	{
		int ret = -1;
		try {
			ret = Convert.ToInt32(value);
		} catch(Exception) {
			if(SHOW_DEBUGGING_MESSAGES) Debug.Log("Parse error attempting to import value " + value + " as int; reverting to default of -1.");
			ret = -1;
		}
		return ret;
	}

	private static float ParseFloat(string value)
	{
		float ret = -1f;
		try {
			ret = Convert.ToSingle(value);
		} catch(Exception) {
			if(SHOW_DEBUGGING_MESSAGES) Debug.Log("Parse error attempting to import value " + value + " as float; reverting to default of -1f.");
			ret = -1f;
		}
		return ret;
	}

	private void PopulateField(string prop, string prop_type, string fieldName, string value)
	{
		System.Reflection.FieldInfo field = this.GetType().GetField(fieldName);

		if(SHOW_DEBUGGING_MESSAGES) Debug.Log("POPULATING " + prop + "/" + prop_type + " FIELD " + field + " WITH VALUE " + value);
		if(field.FieldType == typeof(int) && prop_type == "auto")
			field.SetValue(this, GetNextAutoIncrement());
		else if(field.FieldType == typeof(int))
			field.SetValue(this, ParseInt(value));
		else if(field.FieldType == typeof(string))
			field.SetValue(this, value);
		else if(field.FieldType == typeof(float))
			field.SetValue(this, ParseFloat(value));
		else if(field.FieldType == typeof(bool))
			field.SetValue(this, (value == "true" || value == "TRUE") );
		else if(field.FieldType.IsEnum)
		{
			System.Reflection.FieldInfo val = field.FieldType.GetField(value.Replace(' ', '_'));
			if(val != null)
				field.SetValue(this, val.GetRawConstantValue());
			else
				field.SetValue(this, field.FieldType.GetField("nil").GetRawConstantValue());
		}
	}
}

PlayerLevelModel.cs

using UnityEngine;
using System.Collections;

//when subclassing, you need to pass in your own class name to hook up all the nice FindBy etc methods w/the right type
public class PlayerLevelModel : BaseModel<PlayerLevelModel>
{
	//all of the properties defined in our CSV
	public int id, threshold, skillPointReward, totalSkillPoints, gemReward;
	
	public PlayerLevelModel nextLevel
	{
		get
		{
			if(threshold > 0)
			{
				return PlayerLevelModel.Find(id + 1);
			}else{
				return null;
			}
		}
	}
	
	public PlayerLevelModel prevLevel
	{
		get
		{
			if(id > 1)
			{
				return PlayerLevelModel.Find(id - 1);
			}else{
				return null;
			}
		}
	}
}

update_metadata.rb

#!/usr/bin/env ruby

require 'google_drive'
require 'csv'

session = GoogleDrive.login("YOUR_API_EMAIL","YOUR_API_EMAIL_PASSWORD")
spreadsheet = session.spreadsheet_by_title("The DAM Economy")

#filename to worksheet name. the first part is what you want the CSV to be called
# and the second part is the name of the tab on your spreadsheet
# i.e. turrets.csv will pull from the sheet named "turret model"
names = {"turrets" => "turret model", "creeps" => "creep model", "levels" => "level model", "waves" => "wave model", "playerLevels" => "level up model", "challenges" => "challenge model", "boosts" => "boost model", "level_challenges" => "level challenge model", "tutorials" => "tutorial model"}

names.each do |filename, sheetname|
  
  if ARGV.length > 0
    next unless ARGV.include?filename
  end
  
	sheet = spreadsheet.worksheet_by_title(sheetname)
	data = sheet.rows
  	
  	#make a Models folder in your Resources directory and update the path
  	#i usually have this RB file in the top of my project folder
	CSV.open("Assets/Resources/Models/#{filename}.csv", "w") do |csv|
		data.each do |line|
      quote_free = line.collect{ |item| item.gsub('"','') }
			csv << quote_free
		end
	end
end