Download CSV file from Google Spreadsheet (Google Drive) using minimum c# code

readme.md

- This GIST is outdated. I recommend a different approach here: https://gist.github.com/dhlavaty/9cb9f50aed62320329636e805e654eae

Download CSV file from Google Spreadsheet using c# and minimal code

1. Your Google SpreadSheet (Google Drive, Google Docs) document must be set to "Anyone with the link" can view it
2. To get URL press SHARE (top right corner) on Google SpreeadSheet and copy "Link to share".
3. Now add &output=csv parameter to this link
4. Your link will look like: https://docs.google.com/spreadsheet/ccc?key=1234abcd1234abcd1234abcd1234abcd1234abcd1234&usp=sharing&output=csv

downloadCSV.cs

using System;
using System.Net;

namespace ConsoleApplication1
{
    class Program
    {
        public class WebClientEx : WebClient
        {
            public WebClientEx(CookieContainer container)
            {
                this.container = container;
            }

            private readonly CookieContainer container = new CookieContainer();

            protected override WebRequest GetWebRequest(Uri address)
            {
                WebRequest r = base.GetWebRequest(address);
                var request = r as HttpWebRequest;
                if (request != null)
                {
                    request.CookieContainer = container;
                }
                return r;
            }

            protected override WebResponse GetWebResponse(WebRequest request, IAsyncResult result)
            {
                WebResponse response = base.GetWebResponse(request, result);
                ReadCookies(response);
                return response;
            }

            protected override WebResponse GetWebResponse(WebRequest request)
            {
                WebResponse response = base.GetWebResponse(request);
                ReadCookies(response);
                return response;
            }

            private void ReadCookies(WebResponse r)
            {
                var response = r as HttpWebResponse;
                if (response != null)
                {
                    CookieCollection cookies = response.Cookies;
                    container.Add(cookies);
                }
            }
        }

        static void Main(string[] args)
        {
            /*
             1. Your Google SpreadSheet document must be set to 'Anyone with the link' can view it
             
             2. To get URL press SHARE (top right corner) on Google SpreeadSheet and copy "Link to share".
              
             3. Now add "&output=csv" parameter to this link
             
             4. Your link will look like:
                
                https://docs.google.com/spreadsheet/ccc?key=1234abcd1234abcd1234abcd1234abcd1234abcd1234&usp=sharing&output=csv
            */
            string url = @"https://docs.google.com/spreadsheet/ccc?key=1234abcd1234abcd1234abcd1234abcd1234abcd1234&usp=sharing&output=csv"; // REPLACE THIS WITH YOUR URL

            WebClientEx wc = new WebClientEx(new CookieContainer());
            wc.Headers.Add("User-Agent", "Mozilla/5.0 (Windows NT 6.2; WOW64; rv:22.0) Gecko/20100101 Firefox/22.0");
            wc.Headers.Add("DNT", "1");
            wc.Headers.Add("Accept", "text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8");
            wc.Headers.Add("Accept-Encoding", "deflate");
            wc.Headers.Add("Accept-Language", "en-US,en;q=0.5");

            var outputCSVdata = wc.DownloadString(url);
            Console.Write(outputCSVdata);
        }
    }
}

Thank you very much! ❤️

The new Google spreadsheets use a slightly different URL:
https://docs.google.com/spreadsheets/d/KEY/export?format=csv&id=KEY&gid=0

No idea why the key appears twice in the URL.

Instead of

var outputCSVdata = wc.DownloadString(url);

better to use

byte[] dt = wc.DownloadData(url)
var outputCSVdata = System.Text.Encoding.UTF8.GetString(dt ?? new byte[] {})

to have proper csv Encoding

I was trying to download the file as CSV and with this I found that I would get an HTML file instead. However, following the steps on a post in reddit I could get a URL that downloads me the CSV file.

Instead of using the URL that this post suggest I obtained mine doing the following thing: In your Google Spread, go to: File > Publish to the Web > Link > CSV.

With this new link I did the WWW request using "byte[] dt = wc.DownloadData(url);" instead of the suggested "var outputCSVdata = wc.DownloadString(url);" and saved all the returning bytes into a file using "File.WriteAllBytes("path", dt);"

@guplem > This one is 6 years old. Google changed API since then. I'm not able to support it anymore, sorry.

But I can suggest a different approach (that is officialy supported by Google) that I use:

https://gist.github.com/dhlavaty/9cb9f50aed62320329636e805e654eae

Still works in 2021

The old URL still works if you follow redirects. The new URL looks like this:

https://docs.google.com/spreadsheets/d/${my_key}/export?format=csv&usp=sharing

Demo (live, works)

# The link, as copied from Google Sheets
my_shared_link='https://docs.google.com/spreadsheets/d/1KdNsc63pk0QRerWDPcIL9cMnGQlG-9Ue9Jlf0PAAA34/edit?usp=sharing'

# The key, extracted from the link: 1KdNsc63pk0QRerWDPcIL9cMnGQlG-9Ue9Jlf0PAAA34 
my_key="$(echo "${my_shared_link}" | cut -d'/' -f6)"

# The curl command, translatable into ANY programming language
# curl -fsSL "https://docs.google.com/spreadsheet/ccc?key=${my_key}&usp=sharing&output=csv" -o test.csv

# New version
curl -fsSL "https://docs.google.com/spreadsheets/d/${my_key}/export?format=csv&usp=sharing"

# The proof it still works in 2021
cat test.csv

Article

https://therootcompany.com/blog/how-to-download-a-csv-from-google-sheets/

Discovered another similar way. You can also use https://docs.google.com/spreadsheets/export?id=${my_key}&exportFormat=${format}.

Also you can shrink the code down to

using var client = new WebClient();
client.Headers.Add("accept", "*/*");
byte[] outputData= client.DownloadData(url);