/**
* Connects to the specified {@link SpreadsheetsService} and uses a batch
* request to retrieve a {@link CellEntry} for each cell enumerated in {@code
* cellAddrs}. Each cell entry is placed into a map keyed by its RnCn
* identifier.
*
* @param service the spreadsheet service to use.
* @param cellFeed the cell feed to use.
* @param cellAddrs list of cell addresses to be retrieved.
* @return a dictionary consisting of one {@link CellEntry} for each address in {@code
* cellAddrs}
*/
private static Dictionary <String, CellEntry> GetCellEntryMap(
SpreadsheetsService service, CellFeed cellFeed, List <CellAddress> cellAddrs)
{
CellFeed batchRequest = new CellFeed(new Uri(cellFeed.Self), service);
foreach (CellAddress cellId in cellAddrs)
{
CellEntry batchEntry = new CellEntry((uint)cellId.Row, (uint)cellId.Col, cellId.InputValue);
batchEntry.Id = new AtomId(string.Format("{0}/{1}", cellFeed.Self, cellId.IdString));
batchEntry.BatchData = new GDataBatchEntryData(cellId.IdString, GDataBatchOperationType.query);
batchRequest.Entries.Add(batchEntry);
}
CellFeed queryBatchResponse = (CellFeed)service.Batch(batchRequest, new Uri(cellFeed.Batch));
Dictionary <String, CellEntry> cellEntryMap = new Dictionary <String, CellEntry>();
foreach (CellEntry entry in queryBatchResponse.Entries)
{
cellEntryMap.Add(entry.BatchData.Id, entry);
Console.WriteLine("batch {0} (CellEntry: id={1} editLink={2} inputValue={3})",
entry.BatchData.Id, entry.Id, entry.EditUri,
entry.InputValue);
}
return(cellEntryMap);
}
private void ClearSheet(string workbookName)
{
var page1 = (WorksheetEntry)GetWorkbookSheets(workbookName)[0];
if (page1 == null)
{
throw new ApplicationException("Worksheet not found");
}
AtomLink listFeedLink = page1.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null);
ListFeed listFeed = spreadsheetService.Query(new ListQuery(listFeedLink.HRef.ToString()));
CellFeed cells = spreadsheetService.Query(new CellQuery(listFeedLink.HRef.ToString()));
int ctr = 0;
CellEntry toUpdateA;
CellEntry toUpdateB;
AtomFeed batchFeed = new AtomFeed(cells);
// This is for the header row
ctr = (int)cells.ColCount.Count;
// 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)spreadsheetService.Batch(batchFeed, new Uri(cells.Batch));
}
}
private static void DoBulkUpdate(SpreadsheetsService service, WorksheetEntry worksheet)
{
Console.WriteLine("Attempting batch update...");
var cellQuery = new CellQuery(worksheet.CellFeedLink); //new CellQuery(result.Id, "od6", "private", "full");
var cellFeed = service.Query(cellQuery);
// Work out those we wish to update
var cellIdentifiers = new List <CellIdentifier>();
for (uint row = 3; row <= 5; row++)
{
for (uint col = 1; col <= 3; col++)
{
cellIdentifiers.Add(new CellIdentifier(row, col));
}
}
// Query Google's API for these cells...
CellFeed cellsToUpdate = BatchQuery(service, cellFeed, cellIdentifiers);
// Update query...
CellFeed batchRequest = new CellFeed(cellQuery.Uri, service);
foreach (CellEntry cell in cellsToUpdate.Entries)
{
CellEntry batchEntry = cell;
batchEntry.InputValue = string.Format("hello {0}", cell.BatchData.Id);
batchEntry.BatchData = new GDataBatchEntryData(cell.BatchData.Id, GDataBatchOperationType.update);
batchRequest.Entries.Add(batchEntry);
}
var batchResponse = (CellFeed)service.Batch(batchRequest, new Uri(cellFeed.Batch));
// Check all is well
foreach (CellEntry entry in batchResponse.Entries)
{
var currentCellId = entry.BatchData.Id;
if (entry.BatchData.Status.Code == 200)
{
Console.WriteLine(string.Format("Cell {0} succeeded", currentCellId));
}
else
{
Console.WriteLine(string.Format("Cell {0} failed: (status {1}) {2}", currentCellId, entry.BatchData.Status.Code, entry.BatchData.Status.Reason));
}
}
}
private static CellFeed BatchQuery(SpreadsheetsService service, CellFeed cellFeed, IEnumerable <CellIdentifier> cellIdentifiers)
{
CellFeed batchRequest = new CellFeed(new Uri(cellFeed.Self), service);
foreach (var cellId in cellIdentifiers)
{
CellEntry batchEntry = new CellEntry(cellId.Row, cellId.Col, cellId.Id);
batchEntry.Id = new AtomId(string.Format("{0}/{1}", cellFeed.Self, cellId.Id));
batchEntry.BatchData = new GDataBatchEntryData(cellId.Id, GDataBatchOperationType.query);
batchRequest.Entries.Add(batchEntry);
}
CellFeed queryBatchResponse = (CellFeed)service.Batch(batchRequest, new Uri(cellFeed.Batch));
return(queryBatchResponse);
}
public void updateScores(int roundNumber, int[,] scores, Boolean debug = false)
{
if (numMatches != scores.GetUpperBound(0) + 1)
{
throw new ArgumentOutOfRangeException("Number of Rows in scores object should be same as number of matches!!!");
}
if (6 != scores.GetUpperBound(1) + 1)
{
throw new ArgumentOutOfRangeException("Number of Columns in scores object should be 6!!!");
}
WorksheetEntry entry = (WorksheetEntry)worksheets["Scores"];
AtomLink cellFeedLink = entry.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null);
CellQuery query = new CellQuery(cellFeedLink.HRef.ToString());
query.ReturnEmpty = ReturnEmptyCells.yes;
query.MinimumColumn = 3;
query.MaximumColumn = 8;
query.MinimumRow = (uint)(2 + (roundNumber - 1) * numMatches);
query.MaximumRow = (uint)(1 + (roundNumber) * numMatches);
CellFeed feed = service.Query(query);
AtomFeed batchFeed = new AtomFeed(feed);
for (uint i = 0; i <= scores.GetUpperBound(0); ++i)
{
for (uint j = 0; j <= scores.GetUpperBound(1); ++j)
{
int index = (int)(i * (scores.GetUpperBound(1) + 1) + j);
CellEntry cellEntry = (CellEntry)feed.Entries[index];
cellEntry.Cell.InputValue = "" + scores[i, j];
cellEntry.BatchData = new GDataBatchEntryData("" + index, GDataBatchOperationType.update);
batchFeed.Entries.Add(cellEntry);
}
}
CellFeed batchResultFeed = (CellFeed)service.Batch(batchFeed, new Uri(feed.Batch));
updateUpdateProgress(roundNumber, debug);
}
private void WriteDataImpl(string spreadsheetUri, string worksheetUri, StandardTableDataCM data, GoogleAuthDTO authDTO)
{
if (String.IsNullOrEmpty(spreadsheetUri))
{
throw new ArgumentNullException("Spreadsheet Uri parameter is required.");
}
if (string.IsNullOrEmpty(worksheetUri))
{
throw new ArgumentNullException("Worksheet Uri parameter is required.");
}
if (data != null && data.Table.Count > 0)
{
int MAX_ROWS = data.Table.Count;
int MAX_COLS = data.Table[0].Row.Count;
SpreadsheetEntry spreadsheet = FindSpreadsheet(spreadsheetUri, authDTO);
if (spreadsheet == null)
{
throw new ArgumentException("Cannot find a spreadsheet", "spreadsheetUri");
}
SpreadsheetsService service = (SpreadsheetsService)spreadsheet.Service;
string worksheetId = worksheetUri.Substring(worksheetUri.LastIndexOf('/') + 1, worksheetUri.Length - (worksheetUri.LastIndexOf('/') + 1));
string spreadSheetId = spreadsheetUri;
if (spreadSheetId.ToLower().Contains("http"))//remove http url
{
spreadSheetId = spreadSheetId.Substring(spreadSheetId.LastIndexOf('/') + 1, spreadSheetId.Length - (spreadSheetId.LastIndexOf('/') + 1));
}
CellQuery cellQuery = new CellQuery(spreadSheetId, worksheetId, "private", "full");
CellFeed cellFeed = service.Query(cellQuery);
// Build list of cell addresses to be filled in
List <CellAddress> cellAddrs = new List <CellAddress>();
for (int row = 0; row < MAX_ROWS; row++)
{
for (int col = 0; col < MAX_COLS; col++)
{
cellAddrs.Add(new CellAddress(row + 1, col + 1, data.Table[row].Row[col].Cell.Value));
}
}
// Prepare the update
// GetCellEntryMap is what makes the update fast.
Dictionary <String, CellEntry> cellEntries = GetCellEntryMap(service, cellFeed, cellAddrs);
CellFeed batchRequest = new CellFeed(cellQuery.Uri, service);
foreach (CellAddress cellAddr in cellAddrs)
{
CellEntry batchEntry = cellEntries[cellAddr.IdString];
batchEntry.InputValue = cellAddr.InputValue;
batchEntry.BatchData = new GDataBatchEntryData(cellAddr.IdString, GDataBatchOperationType.update);
batchRequest.Entries.Add(batchEntry);
}
// Submit the update
CellFeed batchResponse = (CellFeed)service.Batch(batchRequest, new Uri(cellFeed.Batch));
// Check the results
foreach (CellEntry entry in batchResponse.Entries)
{
string batchId = entry.BatchData.Id;
if (entry.BatchData.Status.Code != 200)
{
GDataBatchStatus status = entry.BatchData.Status;
throw new Exception(string.Format("{0} failed ({1})", batchId, status.Reason));
}
}
}
}
static void Main(string[] args)
{
string key;
// Command line parsing
if (args.Length != 1)
{
Console.Error.WriteLine("Syntax: BatchCellUpdater <key>");
return;
}
else
{
key = args[0];
}
// Prepare Spreadsheet Service
SpreadsheetsService service = new SpreadsheetsService("MySpreadsheetIntegration-v1");
// TODO: Authorize the service object for a specific user (see other sections)
CellQuery cellQuery = new CellQuery(key, "od6", "private", "full");
CellFeed cellFeed = service.Query(cellQuery);
// Build list of cell addresses to be filled in
List <CellAddress> cellAddrs = new List <CellAddress>();
for (uint row = 1; row <= MAX_ROWS; ++row)
{
for (uint col = 1; col <= MAX_COLS; ++col)
{
cellAddrs.Add(new CellAddress(row, col));
}
}
// Prepare the update
// GetCellEntryMap is what makes the update fast.
Dictionary <String, CellEntry> cellEntries = GetCellEntryMap(service, cellFeed, cellAddrs);
CellFeed batchRequest = new CellFeed(cellQuery.Uri, service);
foreach (CellAddress cellAddr in cellAddrs)
{
CellEntry batchEntry = cellEntries[cellAddr.IdString];
batchEntry.InputValue = cellAddr.IdString;
batchEntry.BatchData = new GDataBatchEntryData(cellAddr.IdString, GDataBatchOperationType.update);
batchRequest.Entries.Add(batchEntry);
}
// Submit the update
CellFeed batchResponse = (CellFeed)service.Batch(batchRequest, new Uri(cellFeed.Batch));
// Check the results
bool isSuccess = true;
foreach (CellEntry entry in batchResponse.Entries)
{
string batchId = entry.BatchData.Id;
if (entry.BatchData.Status.Code != 200)
{
isSuccess = false;
GDataBatchStatus status = entry.BatchData.Status;
Console.WriteLine("{0} failed ({1})", batchId, status.Reason);
}
}
Console.WriteLine(isSuccess ? "Batch operations successful." : "Batch operations failed");
}
