/**
* Searches a track title in a spreadsheet.
*
* @param trackName the track name to search
* @param activity to get context
* @param spreadsheetTitle the title of spreadsheet
* @param isDelete whether delete the information of this track in the document
* @param accountName the name of Google account
* @return true means find the track name in the spreadsheet
*/
private static boolean searchTrackTitleInSpreadsheet(
String trackName,
Activity activity,
String spreadsheetTitle,
boolean isDelete,
String accountName) {
try {
// Get spreadsheet Id.
String spreadsheetId =
searchAllSpreadsheetByTitle(spreadsheetTitle, activity, accountName).get(0).getId();
// Get spreadsheet service.
SpreadsheetService spreadsheetService = new SpreadsheetService(spreadsheetTitle);
Credential credential = new Credential(BearerToken.authorizationHeaderAccessMethod());
credential.setAccessToken(
SendToGoogleUtils.getToken(
activity.getApplicationContext(), accountName, SendToGoogleUtils.SPREADSHEETS_SCOPE));
spreadsheetService.setOAuth2Credentials(credential);
// Get work sheet.
WorksheetFeed feed =
spreadsheetService.getFeed(
new URL(
String.format(
Locale.US, SendSpreadsheetsAsyncTask.GET_WORKSHEETS_URI, spreadsheetId)),
WorksheetFeed.class);
List<WorksheetEntry> data = feed.getEntries();
for (Iterator<WorksheetEntry> iterator = data.iterator(); iterator.hasNext(); ) {
WorksheetEntry worksheetEntry = (WorksheetEntry) iterator.next();
String title = worksheetEntry.getTitle().getPlainText();
if (title.equals(WORK_SHEET_NAME)) {
URL listFeedUrl = worksheetEntry.getListFeedUrl();
List<ListEntry> listFeed =
spreadsheetService.getFeed(listFeedUrl, ListFeed.class).getEntries();
for (Iterator<ListEntry> iterator2 = listFeed.iterator(); iterator2.hasNext(); ) {
ListEntry listEntry = (ListEntry) iterator2.next();
String name = listEntry.getCustomElements().getValue(TRANCK_NAME_COLUMN);
if (name.equals(trackName)) {
if (isDelete) {
listEntry.delete();
}
return true;
}
}
}
}
} catch (Exception e) {
Log.e(EndToEndTestUtils.LOG_TAG, "Search spreadsheet failed.");
}
return false;
}
public static void main(String[] args) {
SpreadsheetService service = new SpreadsheetService("Sheet1");
try {
String sheetUrl =
"https://spreadsheets.google.com/feeds/list/1BuEN5wHRrQJFgTj9bXN6lXEAcb1lcJ-QYg4Lya-3lCo/default/public/values";
URL url = new URL(sheetUrl);
// Get Feed of Spreadsheet url
ListFeed lf = service.getFeed(url, ListFeed.class);
// Iterate over feed to get cell value
for (ListEntry le : lf.getEntries()) {
CustomElementCollection cec = le.getCustomElements();
// Pass column name to access it's cell values
String val = cec.getValue("CustomerName");
System.out.println(val);
String val2 = cec.getValue("Customeremailid");
System.out.println(val2);
}
} catch (IOException e) {
e.printStackTrace();
} catch (ServiceException e) {
e.printStackTrace();
}
}
@SuppressWarnings("unchecked")
@Test
public void getEntityClass() throws IOException, ServiceException {
when(spreadsheetService.getFeed(any(URL.class), (Class<CellFeed>) any(Class.class)))
.thenReturn(cellFeed);
assertEquals(MapEntity.class, spreadsheetRepository.getEntityMetaData().getEntityClass());
}
/**
* 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);
}
}
/**
* 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.");
}
}
public CSV(String email, String password, String spreadsheetTitle, String worksheetTitle)
throws ServiceException, IOException {
SpreadsheetService service = new SpreadsheetService("gsimple-gspreadsheet-1");
service.setUserCredentials(email, password);
URL metafeedUrl = new URL("https://spreadsheets.google.com/feeds/spreadsheets/private/full");
SpreadsheetFeed feed = service.getFeed(metafeedUrl, SpreadsheetFeed.class);
List<SpreadsheetEntry> spreadsheets = feed.getEntries();
for (SpreadsheetEntry entry : spreadsheets) {
if (entry.getTitle().getPlainText().equals(spreadsheetTitle)) {
List<WorksheetEntry> worksheets = entry.getWorksheets();
for (WorksheetEntry worksheet : worksheets) {
if (worksheet.getTitle().getPlainText().equals(worksheetTitle)) {
URL listFeedUrl = worksheet.getListFeedUrl();
entries = service.getFeed(listFeedUrl, ListFeed.class).getEntries().iterator();
}
}
}
}
}
@SuppressWarnings("unchecked")
@Test
public void iterator() throws IOException, ServiceException {
when(spreadsheetService.getFeed(any(URL.class), (Class<IFeed>) any(Class.class)))
.thenReturn(cellFeed)
.thenReturn(listFeed);
Iterator<Entity> it = spreadsheetRepository.iterator();
assertTrue(it.hasNext());
Entity entity = it.next();
assertEquals(entity.getString("col1"), "val1");
assertEquals(entity.getString("col2"), "val2");
assertEquals(entity.getString("col3"), "val3");
assertFalse(it.hasNext());
}
public URL getWorksheetURL(String spreadsheetName) throws IOException, ServiceException {
URL worksheetURLtoFind = null;
URL metafeedUrl = new URL("https://spreadsheets.google.com/feeds/spreadsheets/private/full");
SpreadsheetFeed feed = spreadsheetService.getFeed(metafeedUrl, SpreadsheetFeed.class);
List<SpreadsheetEntry> spreadsheets = feed.getEntries();
for (SpreadsheetEntry entry : spreadsheets) {
logger.fine("\t" + entry.getTitle().getPlainText());
if (spreadsheetName.equalsIgnoreCase(entry.getTitle().getPlainText())) {
worksheetURLtoFind = entry.getWorksheetFeedUrl();
break;
}
}
return worksheetURLtoFind;
}
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;
}
/** 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;
}
}
@SuppressWarnings("unchecked")
@Test
public void getEntityMetaData() throws IOException, ServiceException {
when(spreadsheetService.getFeed(any(URL.class), (Class<CellFeed>) any(Class.class)))
.thenReturn(cellFeed);
EntityMetaData entityMetaData = spreadsheetRepository.getEntityMetaData();
assertEquals(entityMetaData.getName(), "name");
Iterator<AttributeMetaData> it = entityMetaData.getAttributes().iterator();
assertTrue(it.hasNext());
assertEquals(it.next().getName(), "col1");
assertTrue(it.hasNext());
assertEquals(it.next().getName(), "col2");
assertTrue(it.hasNext());
assertEquals(it.next().getName(), "col3");
assertFalse(it.hasNext());
}
/**
* Uses the user's credentials to get a list of spreadsheets. Then asks the user which spreadsheet
* to load. If the selected spreadsheet has multiple worksheets then the user will also be
* prompted to select what sheet to use.
*
* @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 loadSheet(BufferedReader reader) throws IOException, ServiceException {
// Get the spreadsheet to load
SpreadsheetFeed feed = service.getFeed(factory.getSpreadsheetsFeedUrl(), SpreadsheetFeed.class);
List spreadsheets = feed.getEntries();
int spreadsheetIndex = getIndexFromUser(reader, spreadsheets, "spreadsheet");
SpreadsheetEntry spreadsheet = feed.getEntries().get(spreadsheetIndex);
// Get the worksheet to load
if (spreadsheet.getWorksheets().size() == 1) {
cellFeedUrl = spreadsheet.getWorksheets().get(0).getCellFeedUrl();
} else {
List worksheets = spreadsheet.getWorksheets();
int worksheetIndex = getIndexFromUser(reader, worksheets, "worksheet");
WorksheetEntry worksheet = (WorksheetEntry) worksheets.get(worksheetIndex);
cellFeedUrl = worksheet.getCellFeedUrl();
}
System.out.println("Sheet loaded.");
}
public ImportClient(String username, String password, int itemsPerBatch, String spreadsheetName)
throws Exception {
ITEMS_PER_BATCH = itemsPerBatch;
factory = FeedURLFactory.getDefault();
service = new SpreadsheetService("Props-2-Xls");
service.setUserCredentials(username, password);
service.setProtocolVersion(
SpreadsheetService.Versions
.V1); // bug workaround! http://code.google.com/p/gdata-java-client/issues/detail?id=103
spreadsheet = getSpreadsheet(spreadsheetName);
backingEntry = spreadsheet.getDefaultWorksheet();
CellQuery cellQuery = new CellQuery(backingEntry.getCellFeedUrl());
cellQuery.setReturnEmpty(true);
CellFeed cellFeed = service.getFeed(cellQuery, CellFeed.class);
}
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);
}
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();
}
public WorksheetEntry getWorksheet(String spreadsheetName, String worksheetName)
throws IOException, ServiceException {
WorksheetEntry worksheetToFind = null;
URL metafeedUrl = new URL("https://spreadsheets.google.com/feeds/spreadsheets/private/full");
SpreadsheetFeed feed = spreadsheetService.getFeed(metafeedUrl, SpreadsheetFeed.class);
List<SpreadsheetEntry> spreadsheets = feed.getEntries();
for (SpreadsheetEntry entry : spreadsheets) {
logger.info("\t" + entry.getTitle().getPlainText());
if (spreadsheetName.equalsIgnoreCase(entry.getTitle().getPlainText())) {
List<WorksheetEntry> worksheets = entry.getWorksheets();
for (WorksheetEntry currentWorksheet : worksheets) {
logger.info("\t" + currentWorksheet.getTitle().getPlainText());
if (worksheetName.equalsIgnoreCase(currentWorksheet.getTitle().getPlainText())) {
worksheetToFind = currentWorksheet;
break;
}
}
}
}
return worksheetToFind;
}
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;
}
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);
}
/**
* Taken directly from Google Spreadsheet API because of Authorization Issue
*
* <p>Gets all worksheet entries that are part of this spreadsheet.
*
* <p>You must be online for this to work.
*
* @param spreadsheetEntry
* @return the list of worksheet entries
* @throws java.io.IOException
* @throws com.google.gdata.util.ServiceException
*/
public List<WorksheetEntry> getWorksheets(SpreadsheetEntry spreadsheetEntry)
throws IOException, ServiceException {
WorksheetFeed feed =
spreadsheetService.getFeed(spreadsheetEntry.getWorksheetFeedUrl(), WorksheetFeed.class);
return feed.getEntries();
}
