How to download Google SpreadSheets using C#?

Question

I'm able to login to google with my application with the following code which I found from web. It is returning an Authorization Code as response. Google help says that this auth code should be used to send future POST/GET requests.

I need to download a spreadsheet in Excel format from http://spreadsheets.google.com/feeds/download/spreadsheets/Export?key=DOCUMENT_ID&fmcmd=4 which I can normally do from my browser when I'm logged in to google.

How can I send a request for the above file with the Authorization code in C#? I have seen a thread in SO which used Google Data API. I don't want to make use of it.

Below is the code sample for logging in. It works fine.

        string str = "/accounts/ClientLogin HTTP/1.0 Content-type: application/x-www-form-urlencoded accountType=GOOGLE&Email=myname@gmail.com&Passwd=password&service=cl&source=Gulp-CalGulp-1.05";

        string uri = "https://www.google.com/accounts/ClientLogin";

        HttpWebRequest request = (HttpWebRequest) WebRequest.Create(uri); 
        request.KeepAlive = false;
        request.ProtocolVersion = HttpVersion.Version10;
        request.Method = "POST";

        byte[] postBytes = Encoding.ASCII.GetBytes(str);

        request.ContentType = "application/x-www-form-urlencoded";
        request.ContentLength = postBytes.Length;
        Stream requestStream = request.GetRequestStream();


        requestStream.Write(postBytes, 0, postBytes.Length);
        requestStream.Close();


        HttpWebResponse response = (HttpWebResponse)request.GetResponse();
        StringBuilder sb = new StringBuilder();

        string webresponse = new StreamReader(response.GetResponseStream()).ReadToEnd();
        int AuthIndex = webresponse.IndexOf("Auth=");

        sb.Append(webresponse);
        sb.Append("\n");
        sb.Append(response.StatusCode);

        richTextBox1.Text = sb.ToString();
        string authCode = webresponse.Substring(AuthIndex + 5, webresponse.Length - (AuthIndex + 5));

EDITED: This is what I get as response when I did according to what miffTheFox has replied:

<html><head><title>Redirecting</title>
<meta http-equiv="refresh" content="0; url=&#39;http://spreadsheets.google.com/feeds/download/spreadsheets/Export?key=p_zC6U3bOsLTuXeUdmQI1RA&amp;fmcmd=4&amp;pli=1&amp;auth=DQAAAIoAAAAfbQUnX8EaZzQcBSIRJSeU4xtFF6ITt9069JLJyJsoqFGMzSE8HrvArHmGPoA-Wf2CbhnDQv_bGKXye2_qyL6EAhTEdOs6Alz-VMeYFsqdGlYjxospBokgCO1958kSVuVFRe9UuKkfV2f_6ZX8SROMkMNdMz3MW8Wh3UNmflIX4E92CpnMleSjCRVpH9x5gSQ&amp;gausr=username%40gmail.com&#39;"></head>
<body bgcolor="#ffffff" text="#000000" link="#0000cc" vlink="#551a8b" alink="#ff0000"><script type="text/javascript" language="javascript">
    location.replace("http://spreadsheets.google.com/feeds/download/spreadsheets/Export?key\x3dp_zC6U3bOsLTuXeUdmQI1RA\x26fmcmd\x3d4\x26pli\x3d1\x26auth\x3dDQAAAIoAAAAfbQUnX8EaZzQcBSIRJSeU4xtFF6ITt9069JLJyJsoqFGMzSE8HrvArHmGPoA-Wf2CbhnDQv_bGKXye2_qyL6EAhTEdOs6Alz-VMeYFsqdGlYjxospBokgCO1958kSVuVFRe9UuKkfV2f_6ZX8SROMkMNdMz3MW8Wh3UNmflIX4E92CpnMleSjCRVpH9x5gSQ\x26gausr\x3dusername%40gmail.com")
  </script></body></html>

If I save the stream as HTML and open it in browser it is prompting for downloading the Excel file which I need to download directly.

Dupe of your own question: http://stackoverflow.com/questions/943270/getting-google-spreadsheets-as-excel-using-c. It's better to edit your question instead of asking a new one. — Dirk Vollmar, Jun 03 '09 at 10:21
And this is a related question solving your problem: http://stackoverflow.com/questions/725627/accessing-google-spreadsheets-with-c-using-google-data-api — Dirk Vollmar, Jun 03 '09 at 10:23
I have pt it more clearer after going through Google Data API. Pls discard the other question. — , Jun 03 '09 at 10:23
As I mentioned in the question I dont want to use Google Data API. I just need to download a file as I'm done with logging in. — , Jun 03 '09 at 10:24
Just use a Regex to parse out the URL, then use System.Web.HttpUtility.HtmlDecode to decode it. I've ammended an example to my answer. — MiffTheFox, Jun 04 '09 at 04:44

MiffTheFox · Answer 1 · 2009-06-03T22:56:29.317

I actually did a project that did something like this before, only it used Google Reader, although I'm assuming the Google authorization process is the same.

First off, for each key/value pair in the response you get from logging in, you have to turn that into a cookie.

string loginResponseText = new StreamReader(loginResponse.GetResponseStream()).ReadToEnd();
CookieContainer cookies = new CookieContainer();
foreach (string ln in loginResponseText.Split('\n'))
{
    if (!ln.Contains("=")) continue;
    string tId = ln.Substring(0, ln.IndexOf('=')).Trim();
    string tVal = ln.Substring(ln.IndexOf('=') + 1).Trim();
    cookies.Add(new Cookie(tId, tVal, "/", "www.google.com"));
}

Then, you have to set the cookie container for the request you're making.

string url = string.Format("http://spreadsheets.google.com/feeds/download/spreadsheets/Export?key={0}&fmcmd=4", documentID);
HttpWebRequest rqForFile = (HttpWebRequest)WebRequest.Create(url);
rqForFile.CookieContainer = cookies;
WebResponse respForFile = rUnread.GetResponse();

Enjoy!

EDIT: how to decode the returned HTML!

You need to use a Regex to parse out the URL, then use a method to HTML-decode it. Fortunately for us, Microsoft provides one in System.Web. Just add the reference to it to your project.

Make sure to add using System.Text.RegularExpressions to the top of your file!

Match m = Regex.Match("content=\"0; url=&#39;(.+)&#39;");
if (!m.Success) throw new Exception(); // Or some other method of making sure the result is okay.
string finalurl = m.Groups[1].ToString();
finalurl = System.Web.HttpUtility.HtmlDecode(finalurl);

Then just fetch finalurl using your CookieContianer! (This is untested, but should work!)

Thank you so much. It worked! But I have a small issue. I can't directly get the xls file. I have to write the WebResponse to an html file and then open the file to get the xls. Any idea how to get the xml directly. — , Jun 03 '09 at 10:40

How to download Google SpreadSheets using C#?

1 Answers1

Linked