Here is how I clear a sheet of values. One of the things that I encountered
is when you are getting the total number of existing cells in a sheet it
only counts the ones with values in them! It has to be done in 'batch' mode
otherwise it will time out. Remember that in 'batch' mode the maximum
number of rows that can be processed is 1000 so be sure to check for that.
Obviously, this code knows the row count will never exceed this so it is
not checked (I know, bad assumption but I was in a hurry!).
// Set the credentials
string userID = "userID";
string PW = "password";
// Initialize the spreadsheet
SpreadsheetsService service = new
SpreadsheetsService("TelephoneListing-1");
service.setUserCredentials(userID + "@domain.com", PW);
SpreadsheetQuery WBquery = new SpreadsheetQuery();
WBquery.Title = "Title of Spreadsheet";
SpreadsheetFeed feed = service.Query(WBquery);
// Point to the workbook
SpreadsheetEntry telephoneListWB =
(SpreadsheetEntry)feed.Entries[0];
AtomLink link =
telephoneListWB.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel,
null);
WorksheetQuery sheetquery = new
WorksheetQuery(link.HRef.ToString());
WorksheetFeed sheetfeed = service.Query(sheetquery);
// Point to the Pink Pages (first tab)
WorksheetEntry pinkSheet = (WorksheetEntry)sheetfeed.Entries[0];
AtomLink listFeedLink =
pinkSheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
ListQuery query = new ListQuery(listFeedLink.HRef.ToString());
ListFeed pinkfeed= service.Query(query);
// Get a cell feed for that sheet
AtomLink cellFeedLink =
pinkSheet.Links.FindService(GDataSpreadsheetsNameTable.CellRel,
null);
CellQuery cellquery = new
CellQuery(cellFeedLink.HRef.ToString());
CellFeed cells = service.Query(cellquery);
// Clear all of the values in the sheet
ClearAllValsInSheet(service, pinkSheet, cells);
private void ClearAllValsInSheet(SpreadsheetsService service,
WorksheetEntry sheetName, CellFeed cells)
{
int ctr = 0;
CellEntry toUpdateA;
CellEntry toUpdateB;
AtomFeed batchFeed = new AtomFeed(cells);
// This is for the header row which has two cells with values in
them - 'Name of User' and 'Extension'
ctr = 2;
// Skip all of this if there are no cells with values in them
other than those in the header row
if (cells.Entries.Count > ctr)
{
// Process through all of the cells that have a value in
them starting at the cell below the Header
while (true)
{
toUpdateA = (CellEntry)cells.Entries[ctr];
toUpdateA.Cell.InputValue = "";
toUpdateA.BatchData = new GDataBatchEntryData("A",
GDataBatchOperationType.update);
batchFeed.Entries.Add(toUpdateA);
ctr++;
if (ctr >= cells.Entries.Count)
{
break;
}
toUpdateB = (CellEntry)cells.Entries[ctr];
toUpdateB.Cell.InputValue = "";
toUpdateB.BatchData = new GDataBatchEntryData("B",
GDataBatchOperationType.update);
batchFeed.Entries.Add(toUpdateB);
ctr = ctr + 1;
if (ctr >= cells.Entries.Count)
{
break;
}
}
// Erase all cells
CellFeed batchResultFeed =
(CellFeed)service.Batch(batchFeed, new Uri(cells.Batch));
}
}
