All geek questions in one place

How to download Google SpreadSheets using C #?

I can log in to Google with my application with the following code that I found from the Internet. It returns an authorization code as a response. A Google hint says that this auth code should be used to send future POST / GET requests.

I need to download an Excel spreadsheet from http://spreadsheets.google.com/feeds/download/spreadsheets/Export?key=DOCUMENT_ID&fmcmd=4 , which I usually do from my browser when I log in to Google.

How can I send a request for the above file with an authorization code in C #? I saw a stream in SO that used the Google data API. I do not want to use it.

The following is a sample login code. It works great.

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));

Editorial: This is what I get as an answer, when I did it according to what miffTheFox answered:

 <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> amp; gausr = username% 40gmail.com & # <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 a browser, it will ask you to download the Excel file, which I need to download directly.

+4
source share
1 answer

I really did a project that previously did something similar, only it used Google Reader, although I assume that the Google authorization process is the same.

Firstly, for each key / value pair in the response you receive from logging in, you must turn this 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 must set the cookie container for the request you make.

 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 it!

EDIT : how to decode returned HTML!

You need to use Regex to parse the URL, and then use the method to decode the HTML. Fortunately for us, Microsoft offers one at System.Web. Just add a link to it in your project.

Be sure to use System.Text.RegularExpressions at the top of the 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 run finalurl using your CookieContianer! (This is not tested, but should work!)

+2
source

Source: https://habr.com/ru/post/1285656/

More articles:


All Articles