CODEWITHSUNDEEP
c#asp.netgoogle-docs-apigoogle-sheets-api
Kyle
Kyle

Reputation: 5537

Downloading all spreadsheets to Excel in a Google account using C#

This is what I have so far:

// calls a new service and authenticate
SpreadsheetsService myService = new SpreadsheetsService("MyDownloader");
myService.setUserCredentials(DownloaderSettings.Default.Username,
    DownloaderSettings.Default.Password);

// get a list of spreadsheets
SpreadsheetQuery query = new SpreadsheetQuery();
SpreadsheetFeed feed = myService.Query(query);

// download them all
foreach (SpreadsheetEntry entry in feed.Entries)
{
    string key = entry.AlternateUri
                      .Content
                      .Substring(entry.AlternateUri.Content.IndexOf("?key="));
    string dlUrl = "https://docs.google.com/feeds/download/spreadsheets/Export"
                   + key
                   + "&exportFormat=" + DownloaderSettings.Default.FileFormat
                   + "&format=" + DownloaderSettings.Default.FileFormat;

    Stream stream = myService.Query(new Uri(dlUrl));

    using (FileStream fstream = new FileStream(entry.Title.ToString() + "."
           + DownloaderSettings.Default.FileFormat),
           FileMode.Create,
           FileAccess.ReadWrite,
           FileShare.ReadWrite))
    {
        stream.CopyTo(fstream);
        fstream.Flush();
    }
}

I want to get a list of all spreadsheets in my account and download them all to xlsx. I have about 10 spreadsheets in my account. They are not getting created when I hit debug.

EDIT

If it helps, screenshot: https://i.sstatic.net/3hTi4.png

Upvotes: 0

Views: 1409

Answers (2)

Kyle
Kyle

Reputation: 5537

Following Mauricio Gracia's suggestion, I found some sheet names had illegal characters. This worked for me.

Stream stream = myService.Query(new Uri(dlUrl));

// declare the file name, replacing invalid characters
string fileName = entry.Title.Text;
string regexSearch = new string(Path.GetInvalidFileNameChars()) + new string(Path.GetInvalidPathChars());
Regex r = new Regex(string.Format("[{0}]", Regex.Escape(regexSearch)));
fileName = r.Replace(fileName, "_");

using (FileStream fstream = new FileStream(fileName +  "." + DownloaderSettings.Default.FileFormat,
    FileMode.Create,
    FileAccess.ReadWrite,
    FileShare.ReadWrite))
{
    stream.CopyTo(fstream);
    fstream.Flush();
}

Upvotes: 0

Mauricio Gracia Gutierrez
Mauricio Gracia Gutierrez

Reputation: 10844

Maybe this expresion..

entry.Title.ToString() + "." + DownloaderSettings.Default.FileFormat

Is not producing a valid file name ? it might contain invalid symbols or similar

Upvotes: 1

Related Questions