/**
* Prompts the user for a set of operations and submits them in a batch request.
*
* @param reader to read input from the keyboard.
* @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.
*/
public void processBatchRequest(BufferedReader reader) throws IOException, ServiceException {
final String BATCH_PROMPT =
"Enter set operations one by one, "
+ "then enter submit to send the batch request:\n"
+ " set row# col# value [[add a set operation]]\n"
+ " submit [[submit the request]]";
CellFeed batchRequest = new CellFeed();
// Prompt user for operation
System.out.println(BATCH_PROMPT);
String operation = reader.readLine();
while (!operation.startsWith("submit")) {
String[] s = operation.split(" ", 4);
if (s.length != 4 || !s[0].equals("set")) {
System.out.println("Invalid command: " + operation);
operation = reader.readLine();
continue;
}
// Create a new cell entry and add it to the batch request.
int row = Integer.parseInt(s[1]);
int col = Integer.parseInt(s[2]);
String value = s[3];
CellEntry batchOperation = createUpdateOperation(row, col, value);
batchRequest.getEntries().add(batchOperation);
// Display the current entries in the batch request.
printBatchRequest(batchRequest);
// Prompt for another operation.
System.out.println(BATCH_PROMPT);
operation = reader.readLine();
}
// Get the batch feed URL and submit the batch request
CellFeed feed = service.getFeed(cellFeedUrl, CellFeed.class);
Link batchLink = feed.getLink(Link.Rel.FEED_BATCH, Link.Type.ATOM);
URL batchUrl = new URL(batchLink.getHref());
CellFeed batchResponse = service.batch(batchUrl, batchRequest);
// Print any errors that may have happened.
boolean isSuccess = true;
for (CellEntry entry : batchResponse.getEntries()) {
String batchId = BatchUtils.getBatchId(entry);
if (!BatchUtils.isSuccess(entry)) {
isSuccess = false;
BatchStatus status = BatchUtils.getBatchStatus(entry);
System.out.println(
"\n" + batchId + " failed (" + status.getReason() + ") " + status.getContent());
}
}
if (isSuccess) {
System.out.println("Batch operations successful.");
}
}
/**
* 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;
}
/**
* Writes (to stdout) a list of the entries in the batch request in a human readable format.
*
* @param batchRequest the CellFeed containing entries to display.
*/
private void printBatchRequest(CellFeed batchRequest) {
System.out.println("Current operations in batch");
for (CellEntry entry : batchRequest.getEntries()) {
String msg =
"\tID: "
+ BatchUtils.getBatchId(entry)
+ " - "
+ BatchUtils.getBatchOperationType(entry)
+ " row: "
+ entry.getCell().getRow()
+ " col: "
+ entry.getCell().getCol()
+ " value: "
+ entry.getCell().getInputValue();
System.out.println(msg);
}
}
/**
* Sets the application attribute using the name passed to the constructor on a batch feed.
*
* @param batchFeed
*/
@SuppressWarnings("unchecked")
private void addApplicationAttribute(IFeed iFeed) {
if (!(iFeed instanceof BaseFeed)) {
throw new IllegalArgumentException("Unexpected feed type: " + iFeed);
}
BaseFeed<?, ?> batchFeed = (BaseFeed<?, ?>) iFeed;
BatchOperationType defaultType = BatchUtils.getBatchOperationType(batchFeed);
if (defaultType == null) {
defaultType = BatchOperationType.INSERT;
}
List<? extends BaseEntry> entries = batchFeed.getEntries();
for (BaseEntry<?> entry : entries) {
BatchOperationType type = BatchUtils.getBatchOperationType(entry);
if (type == null) {
type = defaultType;
}
if (type == BatchOperationType.INSERT || type == BatchOperationType.UPDATE) {
addApplicationAttribute(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();
}
}
/** Adds Google Base extensions (g:namespaces) to a Google data service. */
private void addExtensions() {
ExtensionProfile extensionProfile = getExtensionProfile();
GoogleBaseNamespaces.declareAllExtensions(extensionProfile);
BatchUtils.declareExtensions(extensionProfile);
}
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);
}
