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

To expand our learning towards Google SpreadSheet API, in this article we will learn to read data from private Google SpreadSheets.

As you know you can store data into your Spreadsheet and publish it to the world. When you publish the spreadsheet, it became available into public domain. In our last article, we learn to read data from publish Google spreadsheet. The SpreadSheet which we have not share or published that are Private Google Spreadsheet and are available to its owner. We will try to read data from these private spreadsheets in this post.

To start with let us understand what we are going to read. I have two Google SpreadSheet available that I have uploaded to Google Doc. The first spreadsheet is called "ProductDetails" and it has one worksheet with following data.

Product Detail:

The second spreadsheet, I have is called “EmpDetails”. This Spreadsheet has two worksheets with following data into it.

EmpDetails:

Our goal is to use SpreadSheet API and read the data from the above two Spreadsheets. We want to read data without opening the browser or login into Google doc. We have following .NET interface to connect, pull and read the data from Google Spreadsheets and worksheets.

Let us talk about the interface first – We have two ListView Control. The ListView 1 shows all the Google Spreadsheets, we have stored into our Google Doc. The code logic for the same is written on button "Pull SpreadSheet List". When user selects an individual Spreadsheet in Listview 1, the Listview 2 shows all the worksheets available in that Google Spreadsheet.

After selecting an individual worksheet from ListView 2, user can click on button "Read Data from SpreadSheet". The code logic written on this button will connect with the Google Spreadsheet and related worksheet, and read the data from it. The data is then shown in the Data grid control.

To use Google Data API in our .NET project, we first need to add references of Google Data API DLLs in our project. After that, we declare that we are using following Google Data API namespaces in our program/form.

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

We have declared following two variables at form level. These variables will store the Spreadsheet and worksheet name selected by the user on the form.

public partial class Form2 : Form
{
  string spreadsheetName;
  string wrkSheetName;

The .NET cod logic on button "Pull SpreadSheet List" is following.

private void button1_Click_1(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 Listview 1 control has following .NET code logic on SelectedIndexChanged event.

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");
   listView2.FullRowSelect = true;

   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 Listview 2 control has following .NET code logic on SelectedIndexChanged event.

private void listView2_SelectedIndexChanged(object sender, EventArgs e)
{
 wrkSheetName = "";
 ListView.SelectedListViewItemCollection WorkSheetList = listView2.SelectedItems;

 foreach (ListViewItem item in WorkSheetList)
 {
 wrkSheetName = item.SubItems[0].Text.ToString();
 }
}

The code logic on button "Read Data from Worksheet" is following.

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");
  
  
  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)
   {
    if (wsheet.Title.Text == wrkSheetName)
    {
    AtomLink atm = wsheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
    ListQuery Lquery = new ListQuery(atm.HRef.ToString());
    ListFeed LFeed = GoogleExcelService.Query(Lquery);

    DataTable myTable = new DataTable();
    DataColumn DC;

    foreach (ListEntry LmySpread in LFeed.Entries)
    {
     DataRow myDR = myTable.NewRow();
     foreach (ListEntry.Custom listrow in LmySpread.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("Data Reading is Completed");
}

After setting up the code, I connected with the internet, and run the form. I clicked on button Pull SpreadSheet List. It showed me the two Google Spreadsheet that I have in my Google Doc account. The next thing, I did was to select Spreadsheet EmpDetails. The worksheets of spreadsheet EmpDetails were shown in ListView 2. I selected Worksheet EmpDetails and clicked on button Read Data from Spreadsheet. The data of worksheet was read and shown in the data grid.

Reading worksheet Sheet 6 data into data grid

Example of reading data from worksheet sheet 1 under Google Spreadsheet ProductDetails.

That is all for this post. We have learnt to read data from private Google spreadsheet and worksheets.

Thanks for reading till this point.

If you want to explore more with the Source code of this post; please visit Download Zone.

Related Article:

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