I am working on a project with a rather unique requirement: it needs to collect data that was entered via Google Forms interface. As you may know, Google Forms works hand in hand with another member of the Google Docs family - Google Spreadsheets - to store the data entered by users. My task was to to find a best solution to read this data from Google spreadsheets and store it to a local database using .NET.
Using Google Spreadsheets API
Download the latest version of the Google Data Api. After you install the Google Data Api on your computer, you will find everything that you need in the folder \Program Files\Google\Google Data API SDK. You will need to add the following assemblies to your project as project references: Google.GData.Client.dll, Google.GData.Extensions.dll, Google.GData.Spreadsheets.dll.
Retrieving a list of spreadsheets
Let's start by creating a page for the Google Docs user authentication. After our user logs in, we'll show all his spreadsheets to him. When he selects a desired spreadsheet, we will bind the grid gvSpreadsheet to the spreadsheet data.
<asp:PlaceHolder ID="phGoogleConect" runat="server">
<fieldset>
<asp:ValidationSummary ID="validationSummary" runat="server" CssClass="failureNotification"
ValidationGroup="GoogleSpreadsheetValidationGroup"/>
<legend>Connect to Google Docs</legend>
<p>
<asp:Label ID="lblUsername" runat="server" AssociatedControlID="txtUsername">Username:</asp:Label>
<asp:TextBox ID="txtUsername" runat="server" CssClass="textEntry"></asp:TextBox>
<asp:RequiredFieldValidator ID="rfUsername" runat="server" ControlToValidate="txtUsername"
CssClass="failureNotification" ErrorMessage="Username is required." ToolTip="Username is required."
ValidationGroup="GoogleSpreadsheetValidationGroup">*</asp:RequiredFieldValidator>
</p>
<p>
<asp:Label ID="lblPassword" runat="server" AssociatedControlID="txtPassword">Password:</asp:Label>
<asp:TextBox ID="txtPassword" runat="server" CssClass="passwordEntry" TextMode="Password"></asp:TextBox>
<asp:RequiredFieldValidator ID="rfPassword" runat="server" ControlToValidate="txtPassword"
CssClass="failureNotification" ErrorMessage="Password is required." ToolTip="Password is required."
ValidationGroup="GoogleSpreadsheetValidationGroup">*</asp:RequiredFieldValidator>
</p>
</fieldset>
<p class="submitButton">
<asp:Button ID="btnConnect" runat="server" CommandName="Login" Text="Connect" ValidationGroup="GoogleSpreadsheetValidationGroup"/>
</p>
</asp:PlaceHolder>
<asp:PlaceHolder ID="phSpreadsheets" runat="server">
<fieldset>
<p>
<asp:Label ID="lblSpreadsheet" runat="server" AssociatedControlID="ddlSpreadsheets">Spreadsheet:</asp:Label>
<asp:DropDownList ID="ddlSpreadsheets" runat="server" AutoPostBack="true">
</asp:DropDownList>
</p>
<p>
<asp:GridView ID="gvSpreadsheet" runat="server">
</asp:GridView>
</p>
</fieldset>
</asp:PlaceHolder>
Authorizing requests with ClientLogin
They are two easy ways to connect user with his Google Docs account. First one is to ask user for his/her credentials, and the alternative is to use OAuth 2.0 with a stored refresh token or OAuth 1.0a with a stored access token. For testing purposes we will ask user for the credentials, but real scenarios will most often use OAuth infrastructure.
The Google Spreadsheets API supports fetching a list of spreadsheets for the authenticated user. However, you cannot create or delete spreadsheets via this API.
For such operations, you must use the
Google Documents List API. This API operation is accessible only form authorized requests. Requests that have not been authorized cannot access this feed (i.e. this feed is not public).
The following code fetches a list of all spreadsheets owned or shared by the authorized user.
this.Password = txtPassword.Text;
SpreadsheetsService service = new SpreadsheetsService("Spreadsheet-GData-Sample-App");;
service.setUserCredentials(txtUsername.Text, txtPassword.Text);
SpreadsheetQuery query = new SpreadsheetQuery();
SpreadsheetFeed feed = service.Query(query);
ddlSpreadsheets.Items.Clear();
ddlSpreadsheets.Items.Insert(0, new ListItem("Select spreadsheet", Guid.Empty.ToString()));
AtomEntryCollection entries = feed.Entries;
for (int i = 0; i < entries.Count; i++)
{
// Get the worksheets feed URI
AtomLink worksheetsLink = entries[i].Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, AtomLink.ATOM_TYPE);
ddlSpreadsheets.Items.Add(new ListItem(entries[i].Title.Text, worksheetsLink.HRef.Content));
}
phSpreadsheets.Visible = true;
phGoogleConect.Visible = false;
Retrieving information about worksheets
After user selects the spreadsheed, we will retrive information and populate the grid view control.
void ddlSpreadsheets_SelectedIndexChanged(object sender, EventArgs e)
{
if (ddlSpreadsheets.SelectedValue != Guid.Empty.ToString())
{
GoogleSpreadsheetUtility gs = new GoogleSpreadsheetUtility(txtUsername.Text, this.Password, ddlSpreadsheets.SelectedValue);
DataTable dataSet = gs.GenerateDataTable();
gvSpreadsheet.DataSource = dataSet;
gvSpreadsheet.DataBind();
}
}
The main logic of this example is defined in the
GoogleSpreadsheetUtility class. We used it to authenticate and generate data source for the gvSpredsheet control. In the class constructor we defined a
ClientService. We also fetched the worksheet via the provided spreadsheetUri from the class constructor.
public GoogleSpreadsheetUtility(string username, string password, string spreadsheetUri)
{
this.ClientService = new SpreadsheetsService("Spreadsheet-GData-Sample-App");;
this.ClientService.setUserCredentials(username, password);
// Get the worksheet feed from the selected entry
this.Worksheet = new WorksheetQuery(spreadsheetUri);
SetWorksheet(this.ClientService.Query(this.Worksheet));
}
After that, we have created a seperate method SetWorksheet for setting the worksheet cells.
private void SetWorksheet(WorksheetFeed feed)
{
AtomEntryCollection entries = feed.Entries;
if (entries.Count > 0)
{
// Get the cells feed URI
AtomLink cellsLink = entries[0].Links.FindService(GDataSpreadsheetsNameTable.CellRel, AtomLink.ATOM_TYPE);
this.WorksheetCells = new CellQuery(cellsLink.HRef.Content);
}
}
Now we have everything we need and can will read the data from the object cellLinks. The method
GenerateDateTable will return DataTable object. We are first checking are there any records inside the
WorksheetsCells object. If some data exists there, we will read the records from the
AtomEntryCollection. It contains all the cells from the worksheet. We know that the column names are inside the first row, so first thing to do is to collect the column names. After we have added the columns, the next step is to add all the remaining records.
public DataTable GenerateDataTable()
{
DataTable dt = new DataTable("Spreadsheet");
if (this.WorksheetCells != null && this.ClientService.Query(this.WorksheetCells).Entries.Count > 0)
{
AtomEntryCollection entries = (AtomEntryCollection)this.ClientService.Query(this.WorksheetCells).Entries;
int columnsCount = entries.Where(cell => ((CellEntry)cell).Row == 1).Count();
int rowCount = entries.Select(cell => ((CellEntry)cell).Row).Distinct().Count();
for (int i = 0; i < columnsCount; i++)
{
dt.Columns.Add(new DataColumn(((CellEntry)entries[i]).Cell.Value));
}
if (dt.Columns.Count > 0)
{
uint currentRow = 2;
string[] rowValues = new string[columnsCount];
foreach (CellEntry cellEntry in entries)
{
if (cellEntry.Row > 1)
{
if (currentRow != cellEntry.Row)
{
dt.Rows.Add(rowValues);
rowValues = new string[columnsCount];
}
rowValues[cellEntry.Column - 1] = cellEntry.Value;
currentRow = cellEntry.Row;
if(currentRow == rowCount && cellEntry.Column == columnsCount)
dt.Rows.Add(rowValues);
}
}
}
}
return dt;
}
What have we learned?
We have learn to communicate with the Google Docs services and fetch the data from our spreadsheets using Google Api for .Net. You can read, write and delete worksheets with this api, but you can not create or delete the spreadsheets List APIs will be your tool of choice for such tasks.