/**
* Returns a CellEntry with batch id and operation type that will tell the server to update the
* specified cell with the given value. The entry is fetched from the server in order to get the
* current edit link (for optimistic concurrency).
*
* @param row the row number of the cell to operate on
* @param col the column number of the cell to operate on
* @param value the value to set in case of an update the cell to operate on
* @throws ServiceException when the request causes an error in the Google Spreadsheets service.
* @throws IOException when an error occurs in communication with the Google Spreadsheets service.
*/
private CellEntry createUpdateOperation(int row, int col, String value)
throws ServiceException, IOException {
String batchId = "R" + row + "C" + col;
URL entryUrl = new URL(cellFeedUrl.toString() + "/" + batchId);
CellEntry entry = service.getEntry(entryUrl, CellEntry.class);
entry.changeInputValueLocal(value);
BatchUtils.setBatchId(entry, batchId);
BatchUtils.setBatchOperationType(entry, BatchOperationType.UPDATE);
return entry;
}
public static void gogogo(
String username,
String password,
int itemsPerBatch,
String spreadsheetName,
String worksheetName,
String data)
throws Exception {
System.out.println("# Initializing upload to Google Spreadsheets...");
System.out.print("# Logging in as: \"" + username + "\"... ");
ImportClient client = new ImportClient(username, password, itemsPerBatch, spreadsheetName);
System.out.println("Success!");
Pattern delim = Pattern.compile(DELIM);
try {
int row = 0;
String[] allLines = data.split("\n");
int currentCell = 1;
int allRow = allLines.length;
System.out.println("# Preparing " + allRow + " rows to be updated... ");
List<CellEntry> updatedCells = new LinkedList<CellEntry>();
Worksheet workSheet = client.getWorksheet(spreadsheetName, worksheetName);
ProgressBar.updateProgress(0, allRow);
for (String line : allLines) {
// Break up the line by the delimiter and insert the cells
String[] cells = delim.split(line, -1);
for (int col = 0; col < cells.length; col++) {
// old way - send the change
// client.insertCellEntry(spreadsheet, worksheet, row + 1, col + 1,
// cells[col]);
// prepare change
CellEntry cellEntry = workSheet.getCell(row + 1, col + 1);
String value = cells[col];
cellEntry.changeInputValueLocal(value);
updatedCells.add(cellEntry);
}
// Advance the loop
ProgressBar.updateProgress(++row, allRow);
}
// send the batches
int allBatches = updatedCells.size();
int currentBatch = 0;
List<List<CellEntry>> batches = chunkList(updatedCells, ITEMS_PER_BATCH);
System.out.println("\n\n# Uploading changes in " + batches.size() + " chunks, ");
System.out.println("# containing a total of " + allBatches + " operations... ");
for (List<CellEntry> batch : batches) {
CellFeed batchFeed = new CellFeed();
for (CellEntry cellEntry : batch) {
ProgressBar.updateProgress(++currentBatch, allBatches);
Cell cell = cellEntry.getCell();
BatchUtils.setBatchId(cellEntry, "R" + cell.getRow() + "C" + cell.getCol());
BatchUtils.setBatchOperationType(cellEntry, BatchOperationType.UPDATE);
batchFeed.getEntries().add(cellEntry);
}
Link batchLink = workSheet.getBatchUpdateLink();
CellFeed batchResultFeed = client.service.batch(new URL(batchLink.getHref()), batchFeed);
// Make sure all the operations were successful.
for (CellEntry entry : batchResultFeed.getEntries()) {
if (!BatchUtils.isSuccess(entry)) {
String batchId = BatchUtils.getBatchId(entry);
BatchStatus status = BatchUtils.getBatchStatus(entry);
System.err.println("Failed entry");
System.err.println("\t" + batchId + " failed (" + status.getReason() + ") ");
return;
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
public void writeBatchRows(
WorksheetEntry worksheetEntry, List<Map<Integer, Object>> rowList, int rowOffset)
throws IOException, ServiceException {
long startTime = System.currentTimeMillis();
URL cellFeedUrl = worksheetEntry.getCellFeedUrl();
CellFeed cellFeed = spreadsheetService.getFeed(worksheetEntry.getCellFeedUrl(), CellFeed.class);
CellFeed batchRequest = new CellFeed();
logger.info("Get Row Count: " + cellFeed.getRowCount());
int rowToBegin = rowOffset;
addEmptyRows(worksheetEntry, rowList.size());
logger.info("Row To Begin: " + rowToBegin);
// Build list of cell addresses to be filled in
List<CellAddress> cellAddrs = new ArrayList<CellAddress>();
CellAddress cellAddress = new CellAddress();
String formula;
for (Map<Integer, Object> row : rowList) {
for (Map.Entry<Integer, Object> entry : row.entrySet()) {
int column = entry.getKey();
if (!(entry.getValue() instanceof String)) {
formula = entry.getValue().toString();
} else {
formula = (String) entry.getValue();
}
logger.info("********************Column: " + column + "Formula: " + formula);
cellAddress.setCol(column);
cellAddress.setRow(rowToBegin);
cellAddress.setIdString(String.format("R%sC%s", rowToBegin, column));
cellAddrs.add(cellAddress);
for (CellAddress cellAddr : cellAddrs) {
CellEntry batchEntry = new CellEntry(cellAddr.row, cellAddr.col, formula);
batchEntry.setId(String.format("%s/%s", cellFeedUrl.toString(), cellAddr.idString));
BatchUtils.setBatchId(batchEntry, cellAddr.idString);
BatchUtils.setBatchOperationType(batchEntry, BatchOperationType.UPDATE);
logger.fine("Batch Entry: " + batchEntry);
batchRequest.getEntries().add(batchEntry);
batchEntry = null;
}
cellAddrs.clear();
}
// batch per row
if (rowToBegin % 100 == 0) {
long startBatchTime = System.currentTimeMillis();
logger.info("\n\n\nEvery 100 rows batch call: " + rowToBegin);
performBatchUpdate(batchRequest, cellFeedUrl);
batchRequest.getEntries().clear();
logger.info("\n\n ms elapsed for batch: " + (System.currentTimeMillis() - startBatchTime));
}
rowToBegin++;
}
logger.info(
"\n\n\n\nms elapsed to create batch request: " + (System.currentTimeMillis() - startTime));
// for the stragglers
logger.info("\n\n\nLast rows batch call: " + rowToBegin);
performBatchUpdate(batchRequest, cellFeedUrl);
}