Scott Hanselman

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:

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.

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.

About   Newsletter

Hosting By