SpreadSheet API with .NET:How to create new worksheet in Google Spreadsheet?

To continue our learning towards Google Spreadsheet API, in this post we will learn how we can create a new worksheet in an existing Google Spreadsheet using Google Spreadsheet API. Till this point we have explored how we can pull spreadsheet list from Google Doc and how we can read individual spreadsheet properties.

To start with I have added one more button to our .NET interface called "Add new Worksheet". We will pull the spreadsheet list into our Listview control when we click on button "Pull SpreadSheet List". After selecting the individual Spreadsheet from Listview control, the user will click on "Add new Worksheet" button and program will create a new worksheet into that selected Spreadsheet.

To begin with the code I am using following namespace into our Form.

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

We are storing the selected spreadsheet name into a form level string variable "spreadsheetName".

public partial class Form1 : Form
    {
        string spreadsheetName;

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

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

}

}

We have following code logic to store the selected Spreadsheet name on ListView1_SelectedIndexChanged event.

private void listView1_SelectedIndexChanged(object sender, EventArgs e)
{
spreadsheetName = "";
ListView.SelectedListViewItemCollection SpreadsheetList = listView1.SelectedItems;

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

}
}

The .NET code logic on button "Add new Worksheet" is following. We are adding a new worksheet with the name EmpSalaryDetails.

private void button4_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)
{
//Add new worksheet with the name EmpSalaryDetails with 10 rows and 5 columns
WorksheetEntry newWrkEntry = new WorksheetEntry(10, 5, "EmpSalaryDetails");

WorksheetFeed wfeed = mySpread.Worksheets;
GoogleExcelService.Insert(wfeed, newWrkEntry);
}
}
System.Windows.Forms.MessageBox.Show("New Worksheet added Successfully");
}
}

After setting up the code, I connected with the internet and run the .NET program. I clicked on the button "Pull SpreadSheet List" and the program interacted with the Good Doc using Google Spreadsheet API and pulls the spreadsheet list.

I selected the Spreadsheet EmpDetails and clicked on button Add new Worksheet. This time the .NET code logic written on the button interacted with the Google Doc and created a new worksheet with the name EmpSalaryDetails into Spreadsheet(EmpDetails).

I log into my Google Doc account and open the SpreadSheet EmpDetails and sure enough the new worksheet was created successfully.

So we learnt how we can add a brand new worksheet into a Google SpreadSheet using Google SpreadSheet API using client login authentication methodology.

Thanks for reading this post.

Related Article:

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