Scott Hanselman

NuGet Package(s) of the Week #12 - Accessing Google Spreadsheets with GData from C# *and* hosting Razor Templates to generate HTML from a Console App

January 10, '13 Comments [22] Posted in ASP.NET MVC | Back to Basics | NuGet | NuGetPOW | Open Source
Sponsored By

The Red Pump Project

Sometimes I write apps for charities on the side. Recently I've been doing some charity coding on the side for The Red Pump Project. They are a non-profit focused on raising awareness about the impact of HIV/AIDS on women and girls. I encourage you to check them out, donate some money, or join their mailing list.

Side Note: Folks often ask me how they can get more experience and wonder if Open Source is a good way. It is! But, charities often need code too! You may be able to have the best of both worlds. Donate your time and your code...and work with them to open source the result. Everyone wins. You get knowledge, the charity get results, the world gets code.

Anyway, this charity has a Google Spreadsheet that holds the results of a survey of users they take. You can create a Form from a Google Spreadsheet; it's a very common thing.

In the past, they've manually gone into the spreadsheet and copied the data out then painstakingly - manually - wrapped the data with HTML tags and posted donors names (who have opted in) to their site.

It got the point where this tedium was the #1 most hated job at The Red Pump Project. They wanted to recognize donors but they aren't large enough yet to have a whole donation platform CRM, instead opting to use Google Apps and free tools.

I figured I could fix this and quickly. Over a one hour Skype last night with Luvvie, one of The Red Pump Founders, we 'paired' (in that I wrote code and she validated the results as I typed) and made a little app that would loop through a Google Spreadsheet and make some HTML that was then uploaded to a webserver and used as a resource within their larger blogging platform.

Yes there are lots of simpler and better ways to do this but keep in mind that this is the result of a single hour, paired directly with the "on site customer" and they are thrilled. It also gives me something to build on. It could later to moved into the cloud, automated, moved to the server side, etc. One has to prioritize and this solution will save them dozens of hours of tedious work this fund raising season.

Here's our hour.

Step 1 - Access Google Spreadsheet via GDATA and C#

I was not familiar with the Google GData API but I knew there was one.  I made a console app and downloaded the Google Data API installer. You can also get them via NuGet:

image

I added references to Google.GData.Client, .Extensions, and .Spreadsheets. Per their documentation, you have to walk and object model, traversing first to find the Spreadsheet with in your Google Drive, then the Worksheet within a single Spreadsheet, and then the Rows and Columns as Cells within the Worksheet. Sounds like moving around a DOM. Get a reference, save it, dig down, continue.

So, that's Drive -> Spreadsheet -> Worksheet -> Cells (Rows, Cols)

The supporters of the Red Pump Project call themselves "Red Pump Rockers" so I have a class to hold them. I want their site, url and twitter. I have a "strippedSite" property which will be the name of their site with only valid alphanumerics so I can make an alphabet navigator later and put some simple navigation in a sorted list.

public class Rocker
{
public string site { get; set; }
public string strippedSite { get; set; }
public string url { get; set; }
public string twitter { get; set; }
}

Again, this is not my finest code but it works well given constraints.

var rockers = new List<Rocker>();

SpreadsheetsService myService = new SpreadsheetsService("auniquename");
myService.setUserCredentials(gmaillogin@email.com, "password");

// GET THE SPREADSHEET from all the docs
SpreadsheetQuery query = new SpreadsheetQuery();
SpreadsheetFeed feed = myService.Query(query);

var campaign = (from x in feed.Entries where x.Title.Text.Contains("thetitleofthesheetineed") select x).First();

// GET THE first WORKSHEET from that sheet
AtomLink link = campaign.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery query2 = new WorksheetQuery(link.HRef.ToString());
WorksheetFeed feed2 = myService.Query(query2);

var campaignSheet = feed2.Entries.First();

// GET THE CELLS

AtomLink cellFeedLink = campaignSheet.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null);
CellQuery query3 = new CellQuery(cellFeedLink.HRef.ToString());
CellFeed feed3 = myService.Query(query3);

uint lastRow = 1;
Rocker rocker = new Rocker();

foreach (CellEntry curCell in feed3.Entries) {

if (curCell.Cell.Row > lastRow && lastRow != 1) { //When we've moved to a new row, save our Rocker
rockers.Add(rocker);
rocker = new Rocker();
}

//Console.WriteLine("Row {0} Column {1}: {2}", curCell.Cell.Row, curCell.Cell.Column, curCell.Cell.Value);

switch (curCell.Cell.Column) {
case 4: //site
rocker.site = curCell.Cell.Value;
Regex rgx = new Regex("[^a-zA-Z0-9]"); //Save a alphanumeric only version
rocker.strippedSite = rgx.Replace(rocker.site, "");
break;
case 5: //url
rocker.url = curCell.Cell.Value;
break;
case 6: //twitter
rocker.twitter = curCell.Cell.Value;
break;
}
lastRow = curCell.Cell.Row;
}

var sortedRockers = rockers.OrderBy(x => x.strippedSite).ToList();

At this point I have thousands of folks who "Rock The Red Pump" in a list called sortedRockers, sorted by site A-Z. I'm ready to do something with them.

Step 2 - Generate HTML (first wrong, then later right with Razor Templates)

They wanted a list of website names linked to their sites with an optional twitter name like:

Scott's Blog - @shanselman

I started (poorly) with a StringBuilder. *Gasp*

This is a learning moment, because it was hard and it was silly and I wasted 20 minutes of Luvvie's time. Still, it gets better, keep reading.

Here's what I wrote, quickly, and first. Don't judge, I'm being honest here.

foreach (Rocker r in sortedRockers){
string strippedName = r.strippedSite;

if (char.ToUpperInvariant(lastCharacter) != char.ToUpperInvariant(strippedName[0])) {
sb.AppendFormat("<h2><a name=\"{0}\">{0}</a></h2>", char.ToUpperInvariant(strippedName[0]));
}

sb.AppendFormat("<a href=\"{1}\" target=\"_blank\">{0}</a>", r.site, r.url);

if (!string.IsNullOrWhiteSpace(r.twitter)){
r.twitter = r.twitter.Replace("@", "");
sb.AppendFormat(" &mdash; <a href=\"http://twitter.com/{0}\">@{0}</a>", r.twitter);
}
sb.AppendFormat("<br>");

lastCharacter = strippedName[0];
}
sb.AppendFormat("</body></html>");

This works fine. It's also nuts and hard to read. Impossible to debug and generally confusing. Luvvie was patient but I clearly lost her here.

I realized that I should probably have used Razor Templating from within my Console App for this. I asked on StackOverflow as well.

UPDATE: There's a great answer by Demis from ServiceStack on StackOverflow showing how to use ServiceStack and Razor to generate HTML from Razor templates.

I ended up using RazorEngine, largely because of the promise of their first two lines of code on their original home page.  There is also RazorMachine, Nancy, and a post by Andrew Nurse (author of much of Razor itself) as other options.

RazorEngine in NuGet

But, these two lines right at their top of their site were too enticing to ignore.

string template = "Hello @Name.Name! Welcome to Razor!";
string result = Razor.Parse(template, new { Name = "World" });

(Open Source Developers Take Heed: Where's the easy quickstart code sample on your home page?)

This allowed me to change all that StringBuilder goo above into a nice clear Razor template in a string. I also added the alphabet navigation and the letter headers easily.

<html><head><link rel="stylesheet"" href="style.css" type="text/css" media="screen"/></head><body>

//Navigation - A B C D, etc.
@foreach(char x in ""ABCDEFGHIJKLMNOPQRSTUVWXYZ"".ToList()) {
<a href=""#@x"">@x</a>
}

@functions {
//need @functions because I need this variable in a wider scope
char lastCharacter = '0';
}

@foreach(var r in Model) {
var theUpperChar = char.ToUpperInvariant(r.strippedSite[0]);

//Make a capital letter "heading" when letters change
if (lastCharacter != theUpperChar) {
<h2><a name="@theUpperChar">@theUpperChar</a></h2>
}

<a href="@r.url" target="_blank">@r.site</a>

if (!string.IsNullOrWhiteSpace(r.twitter)) {
var twitter = r.twitter.Replace("@", String.Empty);
<text>&mdash;</text> <a href="http://twitter.com/@twitter">@twitter</a>
}
<br/>
lastCharacter = theUpperChar;
}
</body></html>

And the "do it" code ended up being:

string result = Razor.Parse(template, sortedRockers);
File.WriteAllText("2013list.html", result);

StringBuilders are fine, to a point. When it gets hairy, consider a templating engine of some kind.

Step 3 - Upload a File with FTP with C#

Now what? They want the little app to upload the result. Mads Kristensen to the rescue 7 years ago!

private static void Upload(string ftpServer, string userName, string password, string filename)
{
using (System.Net.WebClient client = new System.Net.WebClient())
{
client.Credentials = new System.Net.NetworkCredential(userName, password);
client.UploadFile(ftpServer + "/" + new FileInfo(filename).Name, "STOR", filename);
}
}

Then it's just

Upload("ftp://192.168.1.1", "UserName", "Password", @"2013list.html");

Conclusion

You can see that this is largely a spike, but it's a spike that solves a problem, today. Later we can build on it, move it to a server process, build a front end on it, and come up with more ways for them to keep  using tools like Google Spreadsheet while better integrating with their existing websites.

Consider donating your coding time to your favorite local charity today!

About Scott

Scott Hanselman is a former professor, former Chief Architect in finance, now speaker, consultant, father, diabetic, and Microsoft employee. He is a failed stand-up comic, a cornrower, and a book author.

facebook twitter subscribe
About   Newsletter
Sponsored By
Hosting By
Dedicated Windows Server Hosting by ORCS Web
Thursday, January 10, 2013 1:01:28 AM UTC
Very help full nicely compiled article.
Amit Pandey
Thursday, January 10, 2013 1:24:37 AM UTC
Thanks for sharing this. Did you by any chance record your pairing session? Would have made for a nice screen cast.
José G.
Thursday, January 10, 2013 1:36:34 AM UTC
How did you go about getting involved with the charity?
Kevin Whalley
Thursday, January 10, 2013 7:10:28 AM UTC
Awesome!!
Stephen Tierney
Thursday, January 10, 2013 10:21:50 AM UTC
To get involved in charities in the UK, there is a good site:
http://www.it4communities.org.uk/

Not just for devs, but sys admins, general tech as well!
Thursday, January 10, 2013 10:41:00 AM UTC
Just imagine the impact you could have on so many charities with the application of proper IT solutions!
Thursday, January 10, 2013 2:06:40 PM UTC
Awesome. Participated in GiveCamp the one year it happened near my area and have been looking forward to doing it again soon. Any tips for finding local charities that need work?

I've used @functions for defining reusable methods but simply @{ ... } has also worked fine for me when defining a variable scoped to the view. Are there advantages to using @functions{} instead of @{}? (Note: If we flipped the question around, I'm not aware of any advantages to @{} over @functions{}, so not trying to call something out here - honestly wondering if I've missed a reason for using one over the other).

@{
char lastCharacter = '0';
}
Thursday, January 10, 2013 4:33:49 PM UTC
Coding organizations like this is not only a good way for us to give to a good cause, but I've found that it's also a good way to exercise my brain in ways I don't at my job.

I built a tool for a school music program to run the fundraiser auction that they host every year. It's nothing like what I do for my job so I had to actively think outside my day-to-day box.
Thursday, January 10, 2013 8:26:08 PM UTC
David - Ah! I suspect they are the same. I didn't try just @{}.

Kevin - I just asked them about their process and had them explain in, and when they described something that sounded tedious I said, hm, do you have any interest in me helping write a program to fix that for you?

Jose - I should have. Good idea!
Thursday, January 10, 2013 8:31:48 PM UTC
nice ,but why only women and girls , what about men and boys with HIV , are they not important ?
Sam
Thursday, January 10, 2013 8:35:22 PM UTC
Every charity has their focus. There are charities for specific groups, specific locations. As these are ladies that organized the charity, I suspect that women and girls are an area that they feel they can speak to and help with. There's nothing wrong with specialization.
Friday, January 11, 2013 12:04:12 PM UTC
Dude, you must be the coolest guy in town. Definitely got me inspired to try and do the same...

Kudos!
Roman
Friday, January 11, 2013 1:49:51 PM UTC
> "Don't judge, I'm being honest here."

Not so fast Scott. I reserve the right to judge a man who spends his personal time in service of people who need him.

:-)
John Cronan
Friday, January 11, 2013 9:05:45 PM UTC
Hi Scott,
Is there a way to access Google Cloud SQL or Storage?
Thanks!
Steve
Steve
Saturday, January 12, 2013 5:23:39 PM UTC
I think the stringbuilder way was better. It was simply constructing a couple chunks of HTML in a simple way and I found it easy to understand.

The other way adds external dependencies (RazorEngine), requires knowledge of how the engine works, you have to understand how the templates work for it to even make sense, and inside the template you now have loops, iterators, conditions and many bits of logic.

I think the stringbuilder approach was far simpler. Just my $0.02
Carl B
Tuesday, January 15, 2013 10:02:59 PM UTC
Had a great idea based on two of your "Nuget Packages of the Week" - #2 and #12:

Fork the MvcMailer project on GitHub, update it to use RazorEngine for the templates, and do away with dependencies on HTTP Context.

This would address a pretty big shortcoming of MvcMailer, which prevents your from running it as a background process without a web server (think, Azure Worker Role as a Mailer Service, sending templated, pretty HTML emails).

If I can squeeze some time out of my life, I might look into that, otherwise, I would love for someone else to do that :)
Saturday, January 19, 2013 4:32:24 PM UTC
Just correcting it should to be @Model.Name instead of @Name.Name .
Yakup İpek
Wednesday, February 06, 2013 8:48:31 AM UTC
Hi Scott,

Firstly, thanks for all your hand work! However I just encountered a major problem with one of your NuGet packages.

We have been using EF5 for our solution and decided to include MvcMailer as well as it's such a nice Emailing tool. However MvcMailer has a dependendy on the T4Scaffolding Package (1.0.8) which in turn has a dependency on EF6 Pre-Release. This isn't a good situation as we have stable packages referencing pre-release dependencies.

Do you have any views on this? I repeatedly find this or similar things happening with NuGet. For all its benefits for me personally a number of other devs I've wasted as much time untangling the mess it makes as it has saved me. In fact I think I'm almost certainly DOWN on time overal rather than NuGet being then panacea it might be ...
Wednesday, February 06, 2013 6:30:27 PM UTC
Jammer - I checked the T4Scaffolding and it doesn't have a dependency on EF6Pre. When I installed it, I got this, in fact:

>>nuget install mvcmailer
Attempting to resolve dependency 'T4Scaffolding (≥ 1.0.7)'.
Attempting to resolve dependency 'T4Scaffolding.Core'.
Attempting to resolve dependency 'EntityFramework'.
Successfully installed 'T4Scaffolding.Core 1.0.0'.
Successfully installed 'EntityFramework 5.0.0'.
Successfully installed 'T4Scaffolding 1.0.8'.
Successfully installed 'MvcMailer 4.0'.

Even more, I checked with the team and we explicitly *disallow* stable packages to depend on unstable (pre) ones. The only way you could get a pre package is if you did an install-package with -pre.
Wednesday, February 06, 2013 6:45:53 PM UTC
Hi Scott,

Thanks for the reply. This is very odd, I wonder if someone has run a -pre command. I'll look into this.

Regards,

Jammer
Tuesday, April 09, 2013 5:11:47 PM UTC
Greetings, Scott !

Actually, I too am working some charity, working in the field of Law, somewhere in United States. They want some kind of blog and I want to use .NET ... I have blogged for years but some how can not combine programming and blogging, I'd love to hear some advise from you. I've got no limitations, virtually. No environment/deployment issues/limitations etc. They just want one 'clean' & 'elegant' blog, that's it.

Thanking you in anticipation ...
Sunday, September 29, 2013 6:17:01 PM UTC
I like the stringbuilder way. More pure. Cleaner.
Mike
Comments are closed.

Disclaimer: The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.