How can I access public spreadsheets with the Google Docs C# API?

Question

I've written a C# program using the Google Docs .NET API to read a Google worksheet into a DataTable given a user name, password, spreadsheet name, and worksheet name. This all works fine, but the programming model seems to revolve around giving the spreadsheet service a set of credentials, and then paring down the resulting feed to get a specific spreadsheet/worksheet, i.e.

        SpreadsheetsService service = new SpreadsheetsService("Application-Name");
        service.setUserCredentials(userName, password);

        SpreadsheetQuery spreadsheetQuery = new SpreadsheetQuery();
        SpreadsheetFeed spreadsheetFeed = service.Query(spreadsheetQuery);

        SpreadsheetEntry spreadsheetEntry = (SpreadsheetEntry)(from entries in spreadsheetFeed.Entries
                                                               where entries.Title.Text == spreadsheetName
                                                               select entries).SingleOrDefault();

Now I'm interested in extending the functionality of my program to read from public Google spreadsheets. That is, given the URL of a public Google spreadsheet (e.g. "https://spreadsheets.google.com/ccc?key=BUNCH_OF_LETTERS_HERE&hl=en"), I want to get the SpreadsheetEntry object corresponding to that document.

The method I've been using thus far obviously does not seem to extend to allow this, so I was wondering if anyone knows the proper way to access a public Google document through their API?

Answer 1

You could simply retrieve the public page and then parse HTML source for the rows.

Request headers:

GET https://docs.google.com/spreadsheet/ccc?key=0Au1XehjMeFfYdEE2d0RDSk1FMEMtRjM0MmllUWdoTkE HTTP/1.1
Host: docs.google.com
Connection: keep-alive
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/535.2 (KHTML, like Gecko)     Chrome/15.0.874.120 Safari/535.2
Accept: text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
Accept-Encoding: gzip,deflate,sdch
Accept-Language: tr-TR,tr;q=0.8,en-US;q=0.6,en;q=0.4
Accept-Charset: ISO-8859-9,utf-8;q=0.7,*;q=0.3
Cookie: PREF=ID=68126eb9eb12adef:FF=0:TM=1331371284:LM=1331371284:S=Bkm9mYX8pYy9a4h9

Spreadsheet grid - Column labels:

<table class="waffle" cellspacing="0" cellpadding="0">

<thead><tr>
<th class="row-header freezebar-top-left"></th>
<th style="width:120px" class="column-headers-background">A</th>
<th style="width:120px" class="column-headers-background">B</th>
<th style="width:120px" class="column-headers-background">C</th>
</tr></thead>

<tbody>

First row as user defined column names:

<tr style='height:16px;'>
<th style="height: 16px;" class="row-headers-background"><div class="row-header-wrapper" style="height: 16px;">1</div></th>

<td class="g-0-GjugEgs0" dir="auto-ltr">username</td>
<td class="g-0-GjugEgs0" dir="auto-ltr">create</td>
<td class="g-0-GjugEgs0" dir="auto-ltr">expire</td>
</tr>

Rest of the rows as user data: Row number:

<tr style='height:16px;'>
<th style="height: 16px;" class="row-headers-background"><div class="row-header-wrapper" style="height: 16px;">2</div></th>

Row data cells:

<td class="g-0-GjugEgs2">3600001</td>
<td class="g-0-GjugEgs2">2012</td>
<td class="g-0-GjugEgs2">2013</td>
</tr>

You could use Html Agility Pack or your own parser.

Another option is downloading the public spreadsheet as file in CSV or text format

File download request headers:

GET https://docs.google.com/spreadsheet/fm?id=tA6wDCJME0C-F342ieQghNA.PREF_08255578241116458508.3736592253424693644&fmcmd=23&gid=0 HTTP/1.1
Host: docs.google.com
Connection: keep-alive
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/535.2 (KHTML, like Gecko) Chrome/15.0.874.120 Safari/535.2
Accept: text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
Referer: https://docs.google.com/spreadsheet/ccc?key=0Au1XehjMeFfYdEE2d0RDSk1FMEMtRjM0MmllUWdoTkE
Accept-Encoding: gzip,deflate,sdch
Accept-Language: tr-TR,tr;q=0.8,en-US;q=0.6,en;q=0.4
Accept-Charset: ISO-8859-9,utf-8;q=0.7,*;q=0.3
Cookie: PREF=ID=68126eb9eb12adef:FF=0:TM=1331371284:LM=1331371284:S=Bkm9mYX8pYy9a4h9; lbcs=0

Notice the id parameter. You have to extract it from the html source like below:

 ...
 var mergedConfig = {"formToken":"Kg2uOS1UniIe0yFks5zcDZDsGQ=","formStatus":false,"id":"tA6wDCJME0C-F342ieQghNA.PREF_08255578241116458508.3736592253424693644",
 ...

I used Fiddler to capture communication including SSL encrypted messages.

Answer 2

I was trying to read a spreadsheet with multiple tabs and the html and javascript that came back when I implemented these other answers(i.e. making a direct web request to the page) was almost unreadable. After further digging, I found that if you really want to get the values of a public Google spreadsheet without having to deal with any OAuth stuff, you first need to go to https://console.developers.google.com/apis, create a project, go to the dashboard, and go to credentials, click 'create credentials' and set up a new service account key(choose json and save the json file. Then you can use it to get any public spreadsheet):

using Google.Apis.Auth.OAuth2;
using Google.Apis.Sheets.v4;
using Google.Apis.Sheets.v4.Data;
using Google.Apis.Services;

GoogleCredential credential;
string spreadSheetId = "k3k5j5nh4h43j5ktb4gf5h5l5"; //https://docs.google.com/spreadsheets/d/k3k5j5nh4h43j5ktb4gf5h5l5/
using(var stream = new FileStream('YOUR_JSON_FILE_PATH', FileMode.Open, FileAccess.Read)) 
{
    credential = GoogleCredential.FromStream(stream).CreateScoped(SheetsService.Scope.SpreadsheetsReadonly);

    var sheetsService = new SheetsService(new BaseClientService.Initializer() 
    {
        HttpClientInitializer = credential,
        ApplicationName = "You application name",
    });

    var range = "A:B";

    var request = sheetsService.Spreadsheets.Values.Get(spreadSheetId, range);

    ValueRange response = request.Execute();
}