/**
* 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();
}
}
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);
}
