SpreadSheet API with .NET: How to read data from Published Google SpreadSheet?

This post is next in string of our learning Google SpreadSheet API with .NET. We have learn some very fine basic things about SpreadSheet API and how to integrate it with your .NET client applications. We have learn to create new Worksheet, read and modify worksheet properties, pull SpreadSheet list, delete worksheet in this series till this point.

In this post, we will learn to read data from Google published Spreadsheet. A Published SpreadSheet is one that is available to you to share with the whole world. Evey published worksheet has a dedicated link that you can share with anyone. When you published a SpredSheet, Google Doc attach a SpreadSheet Key as an unique identifaction.

Publishing a worksheet is very simple with Google Doc. All you have to do is to login into your Google Doc account and select your SpreadSheet and click on the Share button on top right.

This will open the Sharing Setting page with a google generated link. This link contains the SpreadSheet Key which you need to note down for this post. You can click on Access setting to change the setting so that it can be accessed by the world or you can provide emails who will have the access to this shared Spreadsheet.

With Google SpreadSheet API, we can read the data present in the published SpreadSheet. For this post, I published the Google SpreadSheet EmpDetails that I uploaded to Google Doc and noted down the SpreadSheet Key.

We have following .NET GUI interface. We have two Listview control and a Datagrid control and two buttons on the form. ListView 1 will display all the SpreadSheet List available in our Google Doc account when we click on button Pull SpreadSheet List. ListView 2 will display the Worksheets that are availabe in the SpreadSheet which we select from the list view 1. When we click on button Read Data from Spreadsheet, the data of SpreadSheet will be shown in the DataGrid.

To start within the code, we are using following Google Data API namespace in our program.

using Google.GData;
using Google.GData.Client;
using Google.GData.Extensions;
using Google.GData.Spreadsheets;

We have following form-level variable in our program. This variable will store the Google Spreadsheet name that we select from List View 1.

public partial class Form1 : Form
{
 string spreadsheetName;

The .NET code logic for the button “Pull Spreadsheet List” is following. The code is self explanatory. We are creating a Spreadsheet Service and passing our Gmail credentials. If you are using Client Login authentication methodology, then you have e to pass the credentials. We are creating a Spread Sheet query and passing it to Google Spread sheet feed. The Spreadsheet feed connects with the Google Doc and return feed entries. Each of the feed entry represents a Google Spreadsheet.

private void button1_Click(object sender, EventArgs e)
{
   listView1.Columns.Add("SpreadSheet Name");
   listView1.Columns.Add("Summary");
   listView1.Columns.Add("Published Date");
   listView1.FullRowSelect = true;

   SpreadsheetsService GoogleExcelService;
   GoogleExcelService = new SpreadsheetsService("Spreadsheet-Vikash-Test-App");
   GoogleExcelService.setUserCredentials("gmailuserid", "Gmailpassword");

   SpreadsheetQuery query = new SpreadsheetQuery();
   SpreadsheetFeed myFeed = GoogleExcelService.Query(query);

   foreach (SpreadsheetEntry mySpread in myFeed.Entries)
   {
    listView1.Items.Add(new ListViewItem(new string[] { mySpread.Title.Text, mySpread.Summary.Text, mySpread.Updated.ToShortDateString() }));

   }

}

The .NET code logic on ListView 2 SelectedIndexChanged event is following. Here we are showing the storing the selected Google SpreadSheet name and later reading its properties (no. of worksheet, rows and columns)

private void listView1_SelectedIndexChanged(object sender, EventArgs e)
{
   spreadsheetName = "";
   listView2.Clear();
   listView2.Columns.Add("Worksheet Name");
   listView2.Columns.Add("No. of Columns");
   listView2.Columns.Add("No. of Rows");
   listView2.Columns.Add("Summary");
   ListView.SelectedListViewItemCollection SpreadsheetList = listView1.SelectedItems;

   foreach (ListViewItem item in SpreadsheetList)
   {
    spreadsheetName = item.SubItems[0].Text.ToString();

   }

   if (spreadsheetName.Length > 0)
   {
    SpreadsheetsService GoogleExcelService;
    GoogleExcelService = new SpreadsheetsService("Spreadsheet-Vikash-Test-App");
    GoogleExcelService.setUserCredentials("gmailuserid", "Gmailpassword");

    SpreadsheetQuery query = new SpreadsheetQuery();
    SpreadsheetFeed myFeed = GoogleExcelService.Query(query);

    foreach (SpreadsheetEntry mySpread in myFeed.Entries)
    {
     if (mySpread.Title.Text == spreadsheetName)
     {
      WorksheetFeed wfeed = mySpread.Worksheets;
      foreach (WorksheetEntry wsheet in wfeed.Entries)
      {
       listView2.Items.Add(new ListViewItem(new string[] {wsheet.Title.Text, wsheet.Cols.ToString(), wsheet.Rows.ToString(), wsheet.Summary.Text }));

      }
     }
    }
   }   
  }

The .NET code logic on button Read Data from Spreadsheet is following. Here we are using ListQuery. You have to remember that if you want to read individual rows from the Spreadsheet then you have to use ListQuery. Each of the entry inside ListQuery represents individual SpreadSheet row. We are passing the SpreadSheet Key to the ListQuery, which we noted while publishing it. We are creating a DataTable and adding rows and columns into it.

private void button6_Click(object sender, EventArgs e)
{
   if (spreadsheetName.Length > 0)
   {
    SpreadsheetsService GoogleExcelService;
    GoogleExcelService = new SpreadsheetsService("Spreadsheet-Vikash-Test-App");
    GoogleExcelService.setUserCredentials("gmailuserid", "Gmailpassword");
    
    ListQuery query = new ListQuery("0AmYgMIof-5mgdGM2OGxoTmUyc3JRTFlMZ1BTUG5SOVE", "1", "public", "values");
    ListFeed myFeed = GoogleExcelService.Query(query);

    DataTable myTable = new DataTable();
    DataColumn DC;

    foreach (ListEntry mySpread in myFeed.Entries)
    {
     DataRow myDR = myTable.NewRow();
      foreach (ListEntry.Custom listrow in mySpread.Elements)
      {
        DC = myTable.Columns[listrow.LocalName] ?? myTable.Columns.Add(listrow.LocalName);
       myDR[DC] = listrow.Value;
       }
      myTable.Rows.Add(myDR);
     }
    dataGridView1.DataSource = myTable;
    dataGridView1.Refresh();
    }
    System.Windows.Forms.MessageBox.Show("Rows and Columns Added Successfully");
   } 
}

After setting up the code, I connected with the internet and run the form. I clicked on the Pull SpreadSheet List button and selected EmpDetail SpreadSheet. After that, I clicked on button Read Data from SpreadSheet and as expected the code logic runs successfully and read all the data and display it under data grid.

So our objective of reading data from a published Google SpreadSheet is achived. We can enhance the program to return the Google SpreadSheet key and make this interface more dynamic.

Thanks for reading this post.