/**
* Chunks a list of items into sublists where each sublist contains at most the specified maximum
* number of items.
*
* @param ts The list of elements to chunk
* @param chunkSize The maximum number of elements per sublist
* @return A list of sublists, where each sublist has chunkSize or fewer elements and all elements
* from ts are present, in order, in some sublist
*/
private static <T> List<List<T>> chunkList(List<? extends T> ts, int chunkSize) {
Iterator<? extends T> iterator = ts.iterator();
List<List<T>> returnList = new LinkedList<List<T>>();
while (iterator.hasNext()) {
List<T> sublist = new LinkedList<T>();
for (int i = 0; i < chunkSize && iterator.hasNext(); i++) {
sublist.add(iterator.next());
}
returnList.add(sublist);
}
return returnList;
}
/**
* Gets the SpreadsheetEntry for the first spreadsheet with that name retrieved in the feed.
*
* @param spreadsheet the name of the spreadsheet
* @return the first SpreadsheetEntry in the returned feed, so latest spreadsheet with the
* specified name
* @throws Exception if error is encountered, such as no spreadsheets with the name
*/
public SpreadsheetEntry getSpreadsheet(String spreadsheet) throws Exception {
SpreadsheetQuery spreadsheetQuery = new SpreadsheetQuery(factory.getSpreadsheetsFeedUrl());
spreadsheetQuery.setTitleQuery(spreadsheet);
SpreadsheetFeed spreadsheetFeed = service.query(spreadsheetQuery, SpreadsheetFeed.class);
List<SpreadsheetEntry> spreadsheets = spreadsheetFeed.getEntries();
if (spreadsheets.isEmpty()) {
throw new Exception("No spreadsheets with that name");
}
return spreadsheets.get(0);
}
/**
* Get the WorksheetEntry for the worksheet in the spreadsheet with the specified name.
*
* @param spreadsheetName the name of the spreadsheet
* @param worksheetName the name of the worksheet in the spreadsheet
* @return worksheet with the specified name in the spreadsheet with the specified name
* @throws Exception if error is encountered, such as no spreadsheets with the name, or no
* worksheet wiht the name in the spreadsheet
*/
public Worksheet getWorksheet(String spreadsheetName, String worksheetName) throws Exception {
SpreadsheetEntry spreadsheetEntry = getSpreadsheet(spreadsheetName);
WorksheetQuery worksheetQuery = new WorksheetQuery(spreadsheetEntry.getWorksheetFeedUrl());
worksheetQuery.setTitleQuery(worksheetName);
WorksheetFeed worksheetFeed = service.query(worksheetQuery, WorksheetFeed.class);
List<WorksheetEntry> worksheets = worksheetFeed.getEntries();
if (worksheets.isEmpty()) {
throw new Exception(
"No worksheets with that name in spreadhsheet "
+ spreadsheetEntry.getTitle().getPlainText());
}
return new Worksheet(worksheets.get(0), service);
}
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();
}
}
