SpreadSheet API with .NET:How to read Google SpreadSheet properties?

This post is next in series of our learning Google Spreadsheet API. We have learn what Google Spreadsheet is in first post and pull the spreadsheet list from Google Docs in second post.

In this post we will learn how we can read Spreadsheet properties. We will read the individual spreadsheet properties such as how many worksheets are there in the spreadsheet, how many rows and columns are there in each worksheet.

To continue our example from second post we have changed the design of our .NET windows form and added one more list view (Listview 2) which will show the worksheet, and its related attributes. Listview1 will pull the spreadsheet list and after selecting the individual spreadsheet in listview1, its properties will be shown in listview2.

To use Google Data API in your applications the very first thing we have to do is to add references of Google Data API DLLs. I am using following namespaces in this program.

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

I have declared one variable "Spreadsheetname" which will store the SpreadSheet name we have selected in Listview 1.

public partial class Form1 : Form
    {
        string spreadsheetName;

I have added following .NET code snippet on the "Pull Spreadsheet List" button. The code logic will pull all spreadsheet lists from Google Docs.

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() }));
}  
  }

I have added following .NET code snippet on the Listview1 SelectedIndexChanged event. Every time we will select an individual Spreadsheet in the listview this code logic will run the read the Spreadsheet properties and show it to Listview 2.

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}));
       
      }
     }

    }

   }

  }

After setting up the code I connected to the internet and run the form. The first thing I did was to click on button “Pull Spreadsheet List”. The code logic written on this button runs successfully and pulled all the Spreadsheet we have in Google Doc. The next thing I did was to select EmpDetails Spreadsheet. The EmpDetails Spreadsheet properties were read and it was shown in the listview 2. The EmpDetails SpredSheet have one worksheet with four columns and five rows

I checked the SpreadSheet by logging into my Google Doc account and the information the .NET code logics pulled was right.

So our objective to read SpreadSheet attributes is complete.

Thanks for reading till this point.

Related Article:

• Google Spreadsheet API Series in .NET
• Picasa web Album API Series with .NET