We do not want to read the entire data. We want to return only those data which fulfill the criteria we send to Google Spreadsheet. For example, if we have following Google Spreadsheet "ProductDetails", we want to read only those products where quantity is greater than 50.
We have following .NET GUI interface. We hav e two text boxes where we will supply our Gmail credentials. To connect to Google Spreadsheets, we need to indentify ourself. In this form we have a data grid which will show the data retrun from Spreadsheet API. We have a button “Read Data” which contains the .NET code logic to connect to Google Spredshee and pull conditional data from respective Spreadsheet.
To use Spreadsheet API, the very first thing a .NET developer has to do is to add Google Spreadsheet API refrences into his program. We are using following namspace into our .NET program.
using Google.GData; using Google.GData.Client; using Google.GData.Extensions; using Google.GData.Spreadsheets;
The .NET code logic on button “Read Data” is following. We are defining a new Spreadsheet service and supplying our gmail credentials to connect with the Google Spreadsheet. We are creating a Spreadsheet query and passing it to Spreadsheet Feed. The Google Spreadsheet Feed returns feeds. Each feed represents a Google Spreadsheet. Later we are creating worksheets feed. Each worksheet feed represents a worksheet. We are using ListFeed to return all rows inside the worksheet. To apply conditional logic we need to set ListFeed.Spreadsheet property. We have to define what are our conditions to read data from LisFeed. In this example we want to read data where quantity is greater than 50. So we have specified the criteria into the code logic.
private void button2_Click(object sender, EventArgs e) { SpreadsheetsService GoogleExcelService; GoogleExcelService = new SpreadsheetsService("Spreadsheet-Vikash-Test-App"); //Connect with Google Spreadsheet services using gmail credentials GoogleExcelService.setUserCredentials(textBox1.Text, textBox2.Text); SpreadsheetQuery query = new SpreadsheetQuery(); SpreadsheetFeed myFeed = GoogleExcelService.Query(query); foreach (SpreadsheetEntry mySpread in myFeed.Entries) { //Run the code logic if the Spreadsheet name is ProductDetails if (mySpread.Title.Text == "ProductDetails") { System.Windows.Forms.MessageBox.Show("inside"); WorksheetFeed wfeed = mySpread.Worksheets; foreach (WorksheetEntry wsheet in wfeed.Entries) { //Run the code logic if worksheet name is Sheet1 if (wsheet.Title.Text == "Sheet1") { System.Windows.Forms.MessageBox.Show("Inside"); AtomLink atm = wsheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null); ListQuery Lquery = new ListQuery(atm.HRef.ToString()); //Apply conditional logic to read data where quantity> 50 Lquery.SpreadsheetQuery="quantity > 50"; 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 part, it is time to execute it. I connected with the internet and run the program. I supplied my Gmail credentials into the first two text boxes and click on the button “Read Data”. The code logic runs successfully and fetched the data into Grid view. Now if you compare the data of spreadsheet and data grid, you will find it has returns only those products where the quantity is greater than 50.
After setting up the code part, it is time to execute it. I connected with the internet and run the program. I supplied my Gmail credentials into the first two text boxes and click on the button “Read Data”. The code logic runs successfully and fetched the data into Grid view. Now if you compare the data of spreadsheet and data grid, you will find it has returns only those products where the quantity is greater than 50.
So our objective for this post is achived. Thanks for reading till this point.
If you wan to explore yourself with this source code, please visit the Download Zone.
