/**
* Shows all cells that are in the spreadsheet.
*
* @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 showAllCells() throws IOException, ServiceException {
CellFeed feed = service.getFeed(cellFeedUrl, CellFeed.class);
for (CellEntry entry : feed.getEntries()) {
printCell(entry);
}
}
/**
* Performs a full-text search on cells.
*
* @param fullTextSearchString a full text search string, with space-separated keywords
* @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 search(String fullTextSearchString) throws IOException, ServiceException {
CellQuery query = new CellQuery(cellFeedUrl);
query.setFullTextQuery(fullTextSearchString);
CellFeed feed = service.query(query, CellFeed.class);
out.println("Results for [" + fullTextSearchString + "]");
for (CellEntry entry : feed.getEntries()) {
printCell(entry);
}
}
/**
* Shows a particular range of cells, limited by minimum/maximum rows and columns.
*
* @param minRow the minimum row, inclusive, 1-based
* @param maxRow the maximum row, inclusive, 1-based
* @param minCol the minimum column, inclusive, 1-based
* @param maxCol the maximum column, inclusive, 1-based
* @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 showRange(int minRow, int maxRow, int minCol, int maxCol)
throws IOException, ServiceException {
CellQuery query = new CellQuery(cellFeedUrl);
query.setMinimumRow(minRow);
query.setMaximumRow(maxRow);
query.setMinimumCol(minCol);
query.setMaximumCol(maxCol);
CellFeed feed = service.query(query, CellFeed.class);
for (CellEntry entry : feed.getEntries()) {
printCell(entry);
}
}
public Vector<Donor> ReadDonors() throws Exception {
if (MyDonorsSheet == null) LoadDonorWorksheet();
Vector<Donor> donors = new Vector<Donor>();
try {
List<WorksheetEntry> worksheets = MyDonorsSheet.getWorksheets();
WorksheetEntry entry = worksheets.get(0);
if (entry == null) return null;
CellFeed cellFeed = MyService.getFeed(entry.getCellFeedUrl(), CellFeed.class);
if (cellFeed == null) return null;
int numCols = cellFeed.getColCount();
int numRows = cellFeed.getRowCount() - 1;
List<CellEntry> cellEntryList = cellFeed.getEntries();
String cells[][] = new String[numRows][numCols];
for (CellEntry cellEntry : cellEntryList) {
int row = cellEntry.getCell().getRow() - 1;
int col = cellEntry.getCell().getCol() - 1;
if (row > 0) {
cells[row - 1][col] = cellEntry.getCell().getValue();
}
}
for (int row = 0; row < numRows; row++) {
Donor donor = new Donor();
donor.DonorID = TrimNonNull(cells[row][0]);
donor.FirstName = TrimNonNull(cells[row][1]);
donor.LastName = TrimNonNull(cells[row][2]);
donor.SpouseName = TrimNonNull(cells[row][3]);
donor.Organization = TrimNonNull(cells[row][4]);
donor.StreetAddress = TrimNonNull(cells[row][5]);
donor.City = TrimNonNull(cells[row][6]);
donor.State = TrimNonNull(cells[row][7]);
donor.Country = TrimNonNull(cells[row][8]);
donor.ZipCode = TrimNonNull(cells[row][9]);
donor.Phone = TrimNonNull(cells[row][10]);
donor.EmailAddress = TrimNonNull(cells[row][11]);
donor.Notes = TrimNonNull(cells[row][12]);
donor.Thanker = TrimNonNull(cells[row][13]);
donors.add(donor);
}
} catch (ServiceException E) {
throw new Exception("Cannot access Huruma House Donors spreadsheet");
}
return donors;
}
public void copyRowsFromCellFeed(CellFeed _cellFeed, WorksheetEntry _targetWorksheet)
throws IOException, ServiceException {
HashMap m = new HashMap();
List<Map<Integer, Object>> rows = new ArrayList<Map<Integer, Object>>();
int currentRow = 1;
for (CellEntry cell : _cellFeed.getEntries()) {
if (currentRow != cell.getCell().getRow()) {
rows.add(m);
currentRow = cell.getCell().getRow();
m = new HashMap();
}
m.put(cell.getCell().getCol(), cell.getCell().getInputValue());
// logger.info(cell.getTitle().getPlainText());
// String shortId = cell.getId().substring(cell.getId().lastIndexOf('/') + 1);
// logger.info(" -- A Cell(" + shortId + "/" + cell.getTitle().getPlainText()
// + ") formula(" + cell.getCell().getInputValue() + ") numeric("
// + cell.getCell().getNumericValue() + ") value("
// + cell.getCell().getValue() + ")");
}
// add last row
rows.add(m);
logger.info("\n\nRows to add: " + rows.size());
writeBatchRows(_targetWorksheet, rows, 1);
}
/*
* Returns a cell feed of the row with the given index. If the flag is set to true, then
* the cell feed will also include empty cells.
*/
private CellFeed fetchCellFeed(int rowIndex, boolean returnEmpty) throws EolRuntimeException {
try {
URL cellFeedUrl =
new URI(
this.worksheetEntry.getCellFeedUrl().toString()
+ "?min-row="
+ Integer.toString(rowIndex)
+ "&max-row="
+ Integer.toString(rowIndex)
+ "&return-empty="
+ Boolean.toString(returnEmpty))
.toURL();
CellFeed cellFeed =
((GSModel) this.model).spreadsheetService.getFeed(cellFeedUrl, CellFeed.class);
return (cellFeed.getEntries().size() == 0) ? null : cellFeed;
} catch (Exception e) {
throw new EolRuntimeException(e.getMessage());
}
}
List<List<Object>> getRowsOfCells(
SpreadsheetService service,
WorksheetEntry worksheet,
int startRow, // 1-based
int rowCount)
throws IOException, ServiceException {
URL cellFeedUrl = worksheet.getCellFeedUrl();
int minRow = startRow;
int maxRow = Math.min(worksheet.getRowCount(), startRow + rowCount - 1);
int cols = worksheet.getColCount();
int rows = worksheet.getRowCount();
CellQuery cellQuery = new CellQuery(cellFeedUrl);
cellQuery.setMinimumRow(minRow);
cellQuery.setMaximumRow(maxRow);
cellQuery.setMaximumCol(cols);
cellQuery.setMaxResults(rows * cols);
cellQuery.setReturnEmpty(false);
CellFeed cellFeed = service.query(cellQuery, CellFeed.class);
List<CellEntry> cellEntries = cellFeed.getEntries();
List<List<Object>> rowsOfCells = new ArrayList<List<Object>>(rowCount);
for (CellEntry cellEntry : cellEntries) {
Cell cell = cellEntry.getCell();
if (cell != null) {
int row = cell.getRow() - startRow;
int col = cell.getCol() - 1;
while (row >= rowsOfCells.size()) {
rowsOfCells.add(new ArrayList<Object>());
}
List<Object> rowOfCells = rowsOfCells.get(row);
while (col >= rowOfCells.size()) {
rowOfCells.add(null);
}
rowOfCells.set(col, cell.getValue());
}
}
return rowsOfCells;
}
public void performBatchUpdate(CellFeed batchRequest, URL cellFeedUrl)
throws IOException, ServiceException {
logger.info("\n Begin Batch Update \n");
long startTime = System.currentTimeMillis();
// printBatchRequest(batchRequest);
CellFeed cellFeed = spreadsheetService.getFeed(cellFeedUrl, CellFeed.class);
// Submit the update
// set header work around for http://code.google.com/p/gdata-java-client/issues/detail?id=103
spreadsheetService.setHeader("If-Match", "*");
Link batchLink = cellFeed.getLink(Link.Rel.FEED_BATCH, Link.Type.ATOM);
// CellFeed batchResponse = spreadsheetService.batch(new URL(batchLink.getHref()),
// batchRequest);
spreadsheetService.batch(new URL(batchLink.getHref()), batchRequest);
spreadsheetService.setHeader("If-Match", null);
logger.info("\n ms elapsed for batch update: \n" + (System.currentTimeMillis() - startTime));
// Check the results
// 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.printf("%s failed (%s) %s", batchId, status.getReason(),
// status.getContent());
//
// }
//
// break;
// }
//
// logger.info(isSuccess ? "\nBatch operations successful." : "\nBatch operations
// failed");
// System.out.printf("\n%s ms elapsed\n", System.currentTimeMillis() - startTime);
}
@Override
protected void loadHeader() throws EolModelLoadingException {
try {
// Ensure the header is loaded for a worksheet that exists in the spreadsheet
if (!this.existsInSpreadsheet) {
throw new Exception(this.nonexistentWorksheetMessage());
}
// First row is the header row
CellFeed cellFeed = this.fetchCellFeed(1, true);
if (cellFeed != null) {
// Determine the size of the header row
int indexOfLast = 0;
for (CellEntry cell : cellFeed.getEntries()) {
if (cell.getCell().getValue() != null) {
indexOfLast = cell.getCell().getCol();
}
}
// Get columns
for (CellEntry entry : cellFeed.getEntries()) {
if (entry.getCell().getCol() > indexOfLast) break;
// Get column name
String name = entry.getCell().getValue();
if (name != null && !name.isEmpty()) {
// Get column position
int index = entry.getCell().getCol();
// Add the column to the worksheet
SpreadsheetColumnMetadata c = new SpreadsheetColumnMetadata();
c.index = Integer.toString(index);
c.name = name;
this.addColumn(c);
}
}
} else {
throw new Exception("Google cell feed failed to load the header row.");
}
} catch (Exception e) {
throw new EolModelLoadingException(e, this.model);
}
}
@BeforeMethod
public void setUp() throws IOException, ServiceException {
spreadsheetService = mock(SpreadsheetService.class);
listFeed = mock(ListFeed.class);
TextConstruct textConstruct =
when(mock(TextConstruct.class).getPlainText()).thenReturn("name").getMock();
when(listFeed.getTitle()).thenReturn(textConstruct);
List<ListEntry> entries = new ArrayList<ListEntry>();
ListEntry entry = mock(ListEntry.class);
CustomElementCollection customElements = mock(CustomElementCollection.class);
when(customElements.getTags())
.thenReturn(new LinkedHashSet<String>(Arrays.asList("col1", "col2", "col3")));
when(customElements.getValue("col1")).thenReturn("val1");
when(customElements.getValue("col2")).thenReturn("val2");
when(customElements.getValue("col3")).thenReturn("val3");
when(entry.getCustomElements()).thenReturn(customElements);
entries.add(entry);
when(listFeed.getEntries()).thenReturn(entries);
cellFeed = mock(CellFeed.class);
List<CellEntry> cells = new ArrayList<CellEntry>();
Cell cell1 = mock(Cell.class);
when(cell1.getRow()).thenReturn(1);
when(cell1.getValue()).thenReturn("col1");
Cell cell2 = mock(Cell.class);
when(cell2.getRow()).thenReturn(1);
when(cell2.getValue()).thenReturn("col2");
Cell cell3 = mock(Cell.class);
when(cell3.getRow()).thenReturn(1);
when(cell3.getValue()).thenReturn("col3");
CellEntry entry1 = when(mock(CellEntry.class).getCell()).thenReturn(cell1).getMock();
CellEntry entry2 = when(mock(CellEntry.class).getCell()).thenReturn(cell2).getMock();
CellEntry entry3 = when(mock(CellEntry.class).getCell()).thenReturn(cell3).getMock();
cells.add(entry1);
cells.add(entry2);
cells.add(entry3);
when(cellFeed.getEntries()).thenReturn(cells);
when(cellFeed.getTitle()).thenReturn(textConstruct);
spreadsheetRepository = new GoogleSpreadsheetRepository(spreadsheetService, "key", "id");
}
public URL createWorksheet(
URL url, String worksheetTitle, int rows, int columns, List<String> columnNames)
throws IOException, ServiceException {
WorksheetEntry worksheet = new WorksheetEntry();
worksheet.setTitle(new PlainTextConstruct(worksheetTitle));
worksheet.setRowCount(rows);
worksheet.setColCount(columns);
WorksheetEntry createdWorksheet = spreadsheetService.insert(url, worksheet);
// create header
CellFeed cellFeed =
spreadsheetService.getFeed(createdWorksheet.getCellFeedUrl(), CellFeed.class);
int i = 1;
for (String columnName : columnNames) {
CellEntry cellEntry = new CellEntry(1, i, columnName);
cellFeed.insert(cellEntry);
i++;
}
return createdWorksheet.getListFeedUrl();
}
/**
* 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.");
}
}
@Override
protected void createInSpreadsheet() throws RuntimeException {
try {
// Create the worksheet
URL worksheetFeedUrl = ((GSModel) this.model).spreadsheetEntry.getWorksheetFeedUrl();
this.worksheetEntry =
((GSModel) this.model).spreadsheetService.insert(worksheetFeedUrl, this.worksheetEntry);
this.existsInSpreadsheet = true;
if (this.headerEmpty) {
// Worksheet is completely empty - insert a value in the header row, otherwise list feed
// uses the header row
CellFeed cf = this.fetchCellFeed(1, true);
CellEntry cellEntry = new CellEntry(1, 1, "TEMP");
cf.insert(cellEntry);
} else {
// Write any known column names
CellFeed cf = this.fetchCellFeed(1, true);
for (SpreadsheetColumn c : this.getHeader()) {
if (c.getName() != null && !c.getName().isEmpty()) {
CellEntry cellEntry = new CellEntry(1, c.getIndex(), c.getName());
cf.insert(cellEntry);
}
}
}
// Update the worksheet entry to reflect the new state
this.worksheetEntry = this.worksheetEntry.getSelf();
// Set up the worksheet
this.initialiseListFeed();
} catch (Exception e) {
throw new RuntimeException(e.getMessage());
}
}
/** Presents the given cell feed as a map from row, column pair to CellEntry. */
private void refreshCachedData() throws IOException, ServiceException {
CellQuery cellQuery = new CellQuery(backingEntry.getCellFeedUrl());
cellQuery.setReturnEmpty(true);
this.cellFeed = spreadsheetService.getFeed(cellQuery, CellFeed.class);
// A subtlety: Spreadsheets row,col numbers are 1-based whereas the
// cellEntries array is 0-based. Rather than wasting an extra row and
// column worth of cells in memory, we adjust accesses by subtracting
// 1 from each row or column number.
cellEntries = new CellEntry[rows][columns];
for (CellEntry cellEntry : cellFeed.getEntries()) {
Cell cell = cellEntry.getCell();
cellEntries[cell.getRow() - 1][cell.getCol() - 1] = cellEntry;
}
}
/**
* 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);
}
}
/**
* Gets a link to the batch update URL for this worksheet.
*
* @return
*/
Link getBatchUpdateLink() {
return cellFeed.getLink(Link.Rel.FEED_BATCH, Link.Type.ATOM);
}
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();
}
}
private boolean uploadOneSubmission(
String id, String instanceFilePath, String jrFormId, String token, String formFilePath) {
// if the token is null fail immediately
if (token == null) {
mResults.put(id, oauth_fail + Collect.getInstance().getString(R.string.invalid_oauth));
return false;
}
HashMap<String, String> answersToUpload = new HashMap<String, String>();
HashMap<String, String> photosToUpload = new HashMap<String, String>();
HashMap<String, PhotoEntry> uploadedPhotos = new HashMap<String, PhotoEntry>();
HttpTransport h = AndroidHttp.newCompatibleTransport();
GoogleCredential gc = new GoogleCredential();
gc.setAccessToken(token);
PicasaClient client = new PicasaClient(h.createRequestFactory(gc));
// get instance file
File instanceFile = new File(instanceFilePath);
// first check to see how many columns we have:
ArrayList<String> columnNames = new ArrayList<String>();
try {
getColumns(formFilePath, columnNames);
} catch (FileNotFoundException e2) {
e2.printStackTrace();
mResults.put(id, e2.getMessage());
return false;
} catch (XmlPullParserException e2) {
e2.printStackTrace();
mResults.put(id, e2.getMessage());
return false;
} catch (IOException e2) {
e2.printStackTrace();
mResults.put(id, e2.getMessage());
return false;
} catch (FormException e2) {
e2.printStackTrace();
mResults.put(id, e2.getMessage());
return false;
}
if (columnNames.size() > 255) {
mResults.put(
id, Collect.getInstance().getString(R.string.sheets_max_columns, columnNames.size()));
return false;
}
// make sure column names are legal
for (String n : columnNames) {
if (!isValidGoogleSheetsString(n)) {
mResults.put(
id, Collect.getInstance().getString(R.string.google_sheets_invalid_column_form, n));
return false;
}
}
// parses the instance file and populates the answers and photos
// hashmaps.
try {
processInstanceXML(instanceFile, answersToUpload, photosToUpload);
} catch (XmlPullParserException e) {
e.printStackTrace();
mResults.put(id, form_fail + e.getMessage());
return false;
} catch (FormException e) {
mResults.put(id, form_fail + Collect.getInstance().getString(R.string.google_repeat_error));
return false;
} catch (FileNotFoundException e) {
e.printStackTrace();
mResults.put(id, form_fail + e.getMessage());
return false;
} catch (IOException e) {
e.printStackTrace();
mResults.put(id, form_fail + e.getMessage());
return false;
}
try {
Thread.sleep(GOOGLE_SLEEP_TIME);
} catch (InterruptedException e3) {
e3.printStackTrace();
}
// make sure column names in submission are legal (may be different than form)
for (String n : answersToUpload.keySet()) {
if (!isValidGoogleSheetsString(n)) {
mResults.put(
id, Collect.getInstance().getString(R.string.google_sheets_invalid_column_instance, n));
return false;
}
}
// if we have any photos to upload,
// get the picasa album or create a new one
// then upload the photos
if (photosToUpload.size() > 0) {
// First set up a picasa album to upload to:
// maybe we should move this, because if we don't have any
// photos we don't care...
AlbumEntry albumToUse;
try {
albumToUse = getOrCreatePicasaAlbum(client, jrFormId);
} catch (IOException e) {
e.printStackTrace();
GoogleAuthUtil.invalidateToken(Collect.getInstance(), token);
mResults.put(id, picasa_fail + e.getMessage());
return false;
}
try {
uploadPhotosToPicasa(photosToUpload, uploadedPhotos, client, albumToUse, instanceFile);
} catch (IOException e1) {
e1.printStackTrace();
mResults.put(id, picasa_fail + e1.getMessage());
return false;
}
}
// All photos have been sent to picasa (if there were any)
// now upload data to Google Sheet
String selection = InstanceColumns._ID + "=?";
String[] selectionArgs = {id};
Cursor cursor = null;
String urlString = null;
try {
// see if the submission element was defined in the form
cursor =
Collect.getInstance()
.getContentResolver()
.query(InstanceColumns.CONTENT_URI, null, selection, selectionArgs, null);
if (cursor.getCount() > 0) {
cursor.moveToPosition(-1);
while (cursor.moveToNext()) {
int subIdx = cursor.getColumnIndex(InstanceColumns.SUBMISSION_URI);
urlString = cursor.isNull(subIdx) ? null : cursor.getString(subIdx);
// if we didn't find one in the content provider,
// try to get from settings
if (urlString == null) {
SharedPreferences settings =
PreferenceManager.getDefaultSharedPreferences(Collect.getInstance());
urlString =
settings.getString(
PreferencesActivity.KEY_GOOGLE_SHEETS_URL,
Collect.getInstance().getString(R.string.default_google_sheets_url));
}
}
}
} finally {
if (cursor != null) {
cursor.close();
}
}
// now parse the url string if we have one
final String googleHeader = "docs.google.com/spreadsheets/d/";
String sheetId;
if (urlString == null || urlString.length() < googleHeader.length()) {
mResults.put(
id, form_fail + Collect.getInstance().getString(R.string.invalid_sheet_id, urlString));
return false;
} else {
int start = urlString.indexOf(googleHeader) + googleHeader.length();
int end = urlString.indexOf("/", start);
if (end == -1) {
// if there wasn't a "/", just try to get the end
end = urlString.length();
}
if (start == -1 || end == -1) {
mResults.put(
id, form_fail + Collect.getInstance().getString(R.string.invalid_sheet_id, urlString));
return false;
}
sheetId = urlString.substring(start, end);
}
SpreadsheetService service = new SpreadsheetService("ODK-Collect");
service.setAuthSubToken(token);
// Define the URL to request.
URL spreadsheetFeedURL = null;
try {
spreadsheetFeedURL =
new URL("https://spreadsheets.google.com/feeds/worksheets/" + sheetId + "/private/full");
} catch (MalformedURLException e) {
e.printStackTrace();
mResults.put(id, form_fail + e.getMessage());
return false;
}
WorksheetQuery query = new WorksheetQuery(spreadsheetFeedURL);
WorksheetFeed feed = null;
try {
feed = service.query(query, WorksheetFeed.class);
} catch (IOException e) {
e.printStackTrace();
mResults.put(id, form_fail + e.getMessage());
return false;
} catch (ServiceException e) {
e.printStackTrace();
if (e.getLocalizedMessage().equalsIgnoreCase("forbidden")) {
mResults.put(
id, form_fail + Collect.getInstance().getString(R.string.google_sheets_access_denied));
} else {
mResults.put(id, form_fail + Html.fromHtml(e.getResponseBody()));
}
return false;
}
List<WorksheetEntry> spreadsheets = feed.getEntries();
// get the first worksheet
WorksheetEntry we = spreadsheets.get(0);
// check the headers....
URL headerFeedUrl = null;
try {
headerFeedUrl =
new URI(
we.getCellFeedUrl().toString()
+ "?min-row=1&max-row=1&min-col=1&max-col="
+ we.getColCount()
+ "&return-empty=true")
.toURL();
} catch (MalformedURLException e1) {
e1.printStackTrace();
mResults.put(id, form_fail + e1.getMessage());
return false;
} catch (URISyntaxException e1) {
e1.printStackTrace();
mResults.put(id, form_fail + e1.getMessage());
return false;
}
CellFeed headerFeed = null;
try {
headerFeed = service.getFeed(headerFeedUrl, CellFeed.class);
} catch (IOException e) {
e.printStackTrace();
mResults.put(id, form_fail + e.getMessage());
return false;
} catch (ServiceException e) {
e.printStackTrace();
mResults.put(id, form_fail + e.getMessage());
return false;
}
boolean emptyheaders = true;
// go through headers
// if they're empty, resize and add
for (CellEntry c : headerFeed.getEntries()) {
if (c.getCell().getValue() != null) {
emptyheaders = false;
break;
}
}
if (emptyheaders) {
// if the headers were empty, resize the spreadsheet
// and add the headers
we.setColCount(columnNames.size());
try {
we.update();
} catch (IOException e2) {
e2.printStackTrace();
mResults.put(id, form_fail + e2.getMessage());
return false;
} catch (ServiceException e2) {
e2.printStackTrace();
mResults.put(id, form_fail + e2.getMessage());
return false;
} catch (UnsupportedOperationException e) {
e.printStackTrace();
mResults.put(
id, form_fail + Collect.getInstance().getString(R.string.google_sheets_update_error));
return false;
}
// get the cell feed url
URL cellFeedUrl = null;
try {
cellFeedUrl =
new URI(
we.getCellFeedUrl().toString()
+ "?min-row=1&max-row=1&min-col=1&max-col="
+ columnNames.size()
+ "&return-empty=true")
.toURL();
} catch (MalformedURLException e1) {
e1.printStackTrace();
mResults.put(id, form_fail + e1.getMessage());
return false;
} catch (URISyntaxException e1) {
e1.printStackTrace();
mResults.put(id, form_fail + e1.getMessage());
return false;
}
// and the cell feed
CellFeed cellFeed = null;
try {
cellFeed = service.getFeed(cellFeedUrl, CellFeed.class);
} catch (IOException e) {
e.printStackTrace();
mResults.put(id, form_fail + e.getMessage());
return false;
} catch (ServiceException e) {
e.printStackTrace();
mResults.put(id, form_fail + e.getMessage());
return false;
}
// write the headers
for (int i = 0; i < cellFeed.getEntries().size(); i++) {
CellEntry cell = cellFeed.getEntries().get(i);
String column = columnNames.get(i);
cell.changeInputValueLocal(column);
try {
cell.update();
} catch (IOException e) {
e.printStackTrace();
mResults.put(id, form_fail + e.getMessage());
return false;
} catch (ServiceException e) {
e.printStackTrace();
mResults.put(id, form_fail + e.getMessage());
return false;
}
}
}
// we may have updated the feed, so get a new one
// update the feed
try {
headerFeedUrl =
new URI(
we.getCellFeedUrl().toString()
+ "?min-row=1&max-row=1&min-col=1&max-col="
+ we.getColCount()
+ "&return-empty=true")
.toURL();
} catch (MalformedURLException e3) {
e3.printStackTrace();
mResults.put(id, form_fail + e3.getMessage());
return false;
} catch (URISyntaxException e3) {
e3.printStackTrace();
mResults.put(id, form_fail + e3.getMessage());
return false;
}
try {
headerFeed = service.getFeed(headerFeedUrl, CellFeed.class);
} catch (IOException e2) {
e2.printStackTrace();
mResults.put(id, form_fail + e2.getMessage());
return false;
} catch (ServiceException e2) {
e2.printStackTrace();
mResults.put(id, form_fail + e2.getMessage());
return false;
}
// see if our columns match, now
URL cellFeedUrl = null;
try {
cellFeedUrl =
new URI(
we.getCellFeedUrl().toString()
+ "?min-row=1&max-row=1&min-col=1&max-col="
+ headerFeed.getEntries().size()
+ "&return-empty=true")
.toURL();
} catch (MalformedURLException e1) {
e1.printStackTrace();
mResults.put(id, form_fail + e1.getMessage());
return false;
} catch (URISyntaxException e1) {
e1.printStackTrace();
mResults.put(id, form_fail + e1.getMessage());
return false;
}
CellFeed cellFeed = null;
try {
cellFeed = service.getFeed(cellFeedUrl, CellFeed.class);
} catch (IOException e) {
e.printStackTrace();
mResults.put(id, form_fail + e.getMessage());
return false;
} catch (ServiceException e) {
e.printStackTrace();
mResults.put(id, form_fail + e.getMessage());
return false;
}
// first, get all the columns in the spreadsheet
ArrayList<String> sheetCols = new ArrayList<String>();
for (int i = 0; i < cellFeed.getEntries().size(); i++) {
CellEntry cell = cellFeed.getEntries().get(i);
sheetCols.add(cell.getPlainTextContent());
}
ArrayList<String> missingColumns = new ArrayList<String>();
for (String col : columnNames) {
if (!sheetCols.contains(col)) {
missingColumns.add(col);
}
}
if (missingColumns.size() > 0) {
// we had some missing columns, so error out
String missingString = "";
for (int i = 0; i < missingColumns.size(); i++) {
missingString += missingColumns.get(i);
if (i < missingColumns.size() - 1) {
missingString += ", ";
}
}
mResults.put(
id,
form_fail
+ Collect.getInstance()
.getString(R.string.google_sheets_missing_columns, missingString));
return false;
}
// if we get here.. all has matched
// so write the values
ListEntry row = new ListEntry();
// add photos to answer set
Iterator<String> photoIterator = uploadedPhotos.keySet().iterator();
while (photoIterator.hasNext()) {
String key = photoIterator.next();
String url = uploadedPhotos.get(key).getImageLink();
answersToUpload.put(key, url);
}
Iterator<String> answerIterator = answersToUpload.keySet().iterator();
while (answerIterator.hasNext()) {
String path = answerIterator.next();
String answer = answersToUpload.get(path);
// Check to see if answer is a location, if so, get rid of accuracy
// and altitude
// try to match a fairly specific pattern to determine
// if it's a location
// [-]#.# [-]#.# #.# #.#
Pattern p =
Pattern.compile(
"^-?[0-9]+\\.[0-9]+\\s-?[0-9]+\\.[0-9]+\\s-?[0-9]+\\" + ".[0-9]+\\s[0-9]+\\.[0-9]+$");
Matcher m = p.matcher(answer);
if (m.matches()) {
// get rid of everything after the second space
int firstSpace = answer.indexOf(" ");
int secondSpace = answer.indexOf(" ", firstSpace + 1);
answer = answer.substring(0, secondSpace);
answer = answer.replace(' ', ',');
}
row.getCustomElements().setValueLocal(TextUtils.htmlEncode(path), answer);
}
// Send the new row to the API for insertion.
try {
URL listFeedUrl = we.getListFeedUrl();
row = service.insert(listFeedUrl, row);
} catch (IOException e) {
e.printStackTrace();
mResults.put(id, form_fail + e.getMessage());
return false;
} catch (ServiceException e) {
e.printStackTrace();
if (e.getLocalizedMessage().equalsIgnoreCase("Forbidden")) {
mResults.put(
id, form_fail + Collect.getInstance().getString(R.string.google_sheets_access_denied));
} else {
mResults.put(id, form_fail + Html.fromHtml(e.getResponseBody()));
}
return false;
}
mResults.put(id, Collect.getInstance().getString(R.string.success));
return true;
}
/*
* Loads the column ids from Google.
*/
protected void getGoogleColumnIds() throws EolModelLoadingException {
try {
System.out.print("Loading Google ids for '" + this.getName() + "'... ");
// Ensure the worksheet exists in the spreadsheet
if (!this.existsInSpreadsheet) {
throw new RuntimeException(this.nonexistentWorksheetMessage());
}
// Reset the flag early to avoid cycles
this.hasGoogleIdsSet = true;
// Position of the last header column
int maxColIndex = this.getHeader().last().getIndex();
// Determine the first blank row
int rowIndex = this.getRows().size() + 2;
// If the worksheet dimensions are smaller than the first blank row, then expand the worksheet
if (this.worksheetEntry.getRowCount() < rowIndex) {
this.worksheetEntry.setRowCount(rowIndex);
this.worksheetEntry = this.worksheetEntry.update();
}
// Fill the first spare row with temporary values
URL cellFeedUrl =
new URI(
this.worksheetEntry.getCellFeedUrl().toString()
+ "?min-row="
+ Integer.toString(rowIndex)
+ "&max-row="
+ Integer.toString(rowIndex)
+ "&min-col=1"
+ "&max-col="
+ Integer.toString(maxColIndex)
+ "&return-empty=true")
.toURL();
CellFeed cellFeed =
((GSModel) this.model).spreadsheetService.getFeed(cellFeedUrl, CellFeed.class);
for (CellEntry cell : cellFeed.getEntries()) {
cell.changeInputValueLocal("TEMP");
cell.update();
}
GSRow row = null;
// Get the row from the worksheet to populate the Google Unique Id set
URL listFeedUrl =
new URL(
this.worksheetEntry.getListFeedUrl().toString()
+ "?start-index="
+ Integer.toString(rowIndex - 1)
+ "&max-results=1");
ListFeed lf = ((GSModel) model).spreadsheetService.getFeed(listFeedUrl, ListFeed.class);
List<ListEntry> list = lf.getEntries();
if (list.size() == 1) {
ListEntry le = list.get(0);
int colIndex = 1;
// Iterate over all returned tags - Google preserves column ordering in the set
Iterator<String> it = le.getCustomElements().getTags().iterator();
while (it.hasNext()) {
GSColumn c = (GSColumn) this.getColumn(colIndex);
// If the column is known, then associate the id with it
if (c != null) {
c.setGoogleColumnId(it.next());
} else {
it.next();
}
// Stop once the final known column is reached
if (colIndex == maxColIndex) break;
colIndex++;
}
row = new GSRow(this, le);
} else {
throw new Exception(
"List feed failed to return the row of temporary values for learning the Unique Column Ids.");
}
// Delete the temporary row
this.removeRow(row);
} catch (Exception e) {
throw new EolModelLoadingException(e, this.model);
}
System.out.println("<< done");
System.out.println(this);
}
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);
}