In this post we will learn how we can add new row into Google Spreadsheet from our .NET interface. For demonstration purpose, I have uploaded following Google Spreadsheet file into Google Drive. We have a spreadsheet with the name "ProductDetails".
This Spreadsheet has following data into it. We will be adding new row in this sheet from .NET interface.
To start with .NET interface, I have following .NET form. The form contains two text boxes where we will supply Google credentials. To interact with Google Spreadsheet we have to provide our Gmail credentials to identify ourself to Google, if we are using Client Login authentication mechanism.
We have four buttons and three Listview controls on the form. The idea is to when user enter their Google credentials and click on button1 i.e. “Pull Spreadsheet List”, the .NET code logic will interact with Google Drive and pull all the available Google Spreadsheet details into Listview1.
From ListView1 user will select the Spreadsheet where they want to add new row and click on button3 i.e. "Pull Worksheet List". The .NET code logic written on button3 will pull all the worksheets details under the selected Google Spreadsheet from ListView1. The worksheet details will be shown into ListView2.
From ListView2 user will select the worksheet where they want to add new row and click on button 4 i.e. "Display Worsheet Data". The .NET code logic written on button4 will pull all the data available on selected Google worksheets and show it under Listview3.
The "Add new Row" button contains the code logic to add new row into Google Spreadheet. In our example the code logic written on "Add new Row" will add new data into ProductDetails spreadsheet.
We are using following namespace and in this program.
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using Google.GData; using Google.GData.Client; using Google.GData.Extensions; using Google.GData.Spreadsheets;
The .NET code logic written on this form is following.
namespace SpreadSheet_API_New_Row { public partial class Form3 : Form { SpreadsheetsService GoogleExcelService; public Form3() { InitializeComponent(); GoogleExcelService = new SpreadsheetsService("Vikash-GData-Sample-App"); } //Code logic written on button “Add New Row” private void button2_Click(object sender, EventArgs e) { //Pass Google credentials GoogleExcelService.setUserCredentials(textBox1.Text, textBox2.Text); ListEntry entry = new ListEntry(); //Add new row data with productname bottles and quantity 100. entry.Elements.Add(new ListEntry.Custom() { LocalName = "productname", Value = "Bottles" }); entry.Elements.Add(new ListEntry.Custom() { LocalName = "quantity", Value = "100" }); AtomEntry retEntry = GoogleExcelService.Insert(new Uri(this.listView2.SelectedItems[0].SubItems[2].Text), entry); System.Windows.Forms.MessageBox.Show("Record added Successfully"); } //Code logic written on button “Pull Spreadsheet List” private void button1_Click_1(object sender, EventArgs e) { //Pass Google credentials GoogleExcelService.setUserCredentials(textBox1.Text, textBox2.Text); SpreadsheetQuery query = new SpreadsheetQuery(); SpreadsheetFeed myFeed = GoogleExcelService.Query(query); this.listView1.Items.Clear(); listView1.Columns.Add("SpreadSheetName"); listView1.Columns.Add("SpreadSheetURL"); listView1.FullRowSelect = true; AtomEntryCollection entries = myFeed.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); ListViewItem item = new ListViewItem(new string[2] { entries[i].Title.Text, worksheetsLink.HRef.Content }); this.listView1.Items.Add(item); } } //Code logic written on button “Pull Worksheet List” private void button3_Click_1(object sender, EventArgs e) { //Pass Google credentials GoogleExcelService.setUserCredentials(textBox1.Text, textBox2.Text); WorksheetQuery query = new WorksheetQuery(listView1.SelectedItems[0].SubItems[1].Text); WorksheetFeed wsFeed = GoogleExcelService.Query(query); AtomEntryCollection entries = wsFeed.Entries; this.listView2.Items.Clear(); listView2.Columns.Add("Worksheet1"); listView2.Columns.Add("CellFeedURL"); listView2.Columns.Add("ListFeedURL"); listView2.FullRowSelect = true; for (int i = 0; i < entries.Count; i++) { // cells feed URI AtomLink cellsLink = entries[i].Links.FindService(GDataSpreadsheetsNameTable.CellRel, AtomLink.ATOM_TYPE); // list feed URI AtomLink listLink = entries[i].Links.FindService(GDataSpreadsheetsNameTable.ListRel, AtomLink.ATOM_TYPE); ListViewItem item = new ListViewItem(new string[3] { entries[i].Title.Text, cellsLink.HRef.Content, listLink.HRef.Content }); this.listView2.Items.Add(item); } } //Code logic written on button “Display Worksheet Data” private void button4_Click(object sender, EventArgs e) { //Pass Google credentials GoogleExcelService.setUserCredentials(textBox1.Text, textBox2.Text); ListQuery query = new ListQuery(listView2.SelectedItems[0].SubItems[2].Text); ListFeed LFeed = GoogleExcelService.Query(query); this.listView3.Clear(); listView3.Columns.Add("SR#"); listView3.Columns.Add("ProductName"); listView3.Columns.Add("Quantity"); AtomEntryCollection entries = LFeed.Entries; for (int i = 0; i < entries.Count; i++) { ListEntry entry = entries[i] as ListEntry; ListViewItem item = new ListViewItem(); item.Text = (i + 1).ToString(); if (entry != null) { ListEntry.CustomElementCollection elements = entry.Elements; for (int j = 0; j < elements.Count; j++) { item.SubItems.Add(elements[j].Value); } listView3.Items.Add(item); } } } } }
After setting up the code logic, I connected with the internet and run the program. The results were in line with the expectation. A new row with the productname “bottle” and quantity 100 was added into productDetails spreadsheet.
I open the ProductDetails spreadsheet by login into google drive and new row data was into it.
So our objective to add new row into Google Spreadsheet has been achived.
The one important thing I want to mention here which I learn during this program is following:
Google expects that the column headings in Google Spreadsheets are all in lower case and without spaces into it. If you have any issue with the column headings like this, you might face errors while inserting your new data through .NET inferface.
Thanks for reading this post.
If you are want to explore with this Source code, you can download this sample program from Downlaod Zone
