posts tagged with: google

Google Spreadsheet Sync

Google Spreadsheet Sync
Have you ever wanted to take the contents of a Google Spreadsheet and drop it right into your game project? So have I! That's why I made a tool that will take the contents of a Google Spreadsheet and turn it into a C# code file full of data!

Demo


Let's say we have a spreadsheet full of data for our video game written in C#, and it looks something like this.

Image


The resulting output of that spreadsheet will be this.
class Sheets {

public static Dictionary<string, CharactersRow> Characters = new Dictionary<string, CharactersRow>() {
{"Adventurer", new CharactersRow() {
Name = "Adventurer",
Health = 100,
Magic = 100,
Attack = 100,
Defense = 100,
Speed = 100,
VictoryPhrase = "Nice try, \"chump!\"",
FailurePhrase = "Ah... well done..."
}},
{"Thief", new CharactersRow() {
Name = "Thief",
Health = 75,
Magic = 100,
Attack = 100,
Defense = 50,
Speed = 200,
VictoryPhrase = "I take what I want!",
FailurePhrase = "Not fair!"
}},
{"Assassin", new CharactersRow() {
Name = "Assassin",
Health = 100,
Magic = 50,
Attack = 200,
Defense = 50,
Speed = 100,
VictoryPhrase = "You're lucky to be alive.",
FailurePhrase = "How could you defeat me?!"
}}
};

public class CharactersRow {
public string Name;
public int Health;
public int Magic;
public int Attack;
public int Defense;
public int Speed;
public string VictoryPhrase;
public string FailurePhrase;
}

public static Dictionary<string, WeaponsRow> Weapons = new Dictionary<string, WeaponsRow>() {
{"Bronze Sword", new WeaponsRow() {
Name = "Bronze Sword",
Attack = 45,
Defense = 0,
Type = WeaponType.Sword,
Value = 100,
CanUpgrade = true
}},
{"Silver Bow", new WeaponsRow() {
Name = "Silver Bow",
Attack = 20,
Defense = 0,
Type = WeaponType.Bow,
Value = 250,
CanUpgrade = true
}},
{"Fire Wand", new WeaponsRow() {
Name = "Fire Wand",
Attack = 35,
Defense = 15,
Type = WeaponType.Staff,
Value = 400,
CanUpgrade = false
}},
{"Lasso", new WeaponsRow() {
Name = "Lasso",
Attack = 30,
Defense = 0,
Type = WeaponType.Whip,
Value = 150,
CanUpgrade = true
}}
};

public class WeaponsRow {
public string Name;
public int Attack;
public int Defense;
public WeaponType Type;
public int Value;
public bool CanUpgrade;
}
}

With a class like that in my project now it becomes very simple to get data from the spreadsheet with the key of the row (the first column.)
var charName = "Adventurer";
var health = Sheets.Characters[charName].Health;
var victoryPhrase = Sheets.Characters[charName].VictoryPhrase;
Console.WriteLine(victoryPhrase);

Download


Download GoogleSpreadsheetSync.zip (0.02MB)

Usage


I use Visual Studio, so I can only really tell you about that.

It's easiest to set this up as an EXTERNAL TOOL in Visual Studio.
* In Visual Studio go to the Tools menu at the top, select "External Tools..."

Image

* Click "Add"
* Name it something like "Google Spreadsheet Sync"
* For Command, press the "..." button to the right and find the exe.
* For arguments you can use "$(TargetName)" "$(ProjectDir)GoogleSheet.key"
* Include the quotes in those arguments!
* For Initial Directory put $(ProjectDir)
* No quotes in that one.
* Below that check the "Use Output Window" option.

Image


Before you run it though you're going to need to create a GoogleSheet.key file. That file should only contain google spreadsheet key.

For this example here's a URL to a published google spreadsheet: https://docs.google.com/spreadsheets/d/11Ia3wr-Pon1180M0_KRR1hywEITLn_P1BoUedbNQeqw/pubhtml

The key is that big long section: 11Ia3wr-Pon1180M0_KRR1hywEITLn_P1BoUedbNQeqw So in this example the contents of your GoogleSheet.key file should only be:

11Ia3wr-Pon1180M0_KRR1hywEITLn_P1BoUedbNQeqw

Save the file as GoogleSheet.key inside your root project folder. (So probably the same folder as your Program.cs)

Image


You should be all set to now run it from Visual Studio! It should appear in your Tools menu. You can even set a keyboard shortcut for it later if you want.

Run the tool from the Tools menu and what should happen is a Sheets.cs file is generated with all the data from your spreadsheets.

Image


Source


Of course it would be way better if you're able to take this tool and craft it into your own needs, so here's the source code!

I've started a public repository where I'll keep any useful tools I create right here.

Trouble?


If you run into any trouble feel free to post a comment and I'll try to help! Or you can reach me at hi@kpulv.com, or the contact form at the bottom of my site.