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();
}
}
/**
* 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.");
}
}
/** * Log in to Google, under the Google Spreadsheets and Docs account. * * @param username name of user to authenticate (e.g. [email protected]) * @param password password to use for authentication * @throws AuthenticationException if the service is unable to validate the username and password. */ public void login(String username, String password) throws AuthenticationException { spreadsheetService = new SpreadsheetService(APPLICATION_NAME); spreadsheetService.setUserCredentials(username, password); spreadsheetService.setConnectTimeout(0); spreadsheetService.setReadTimeout(0); docsService = new DocsService(APPLICATION_NAME); docsService.setUserCredentials(username, password); docsService.setConnectTimeout(0); docsService.setReadTimeout(0); }
/**
* 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;
}
@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);
}
}
/**
* Sets the particular cell at row, col to the specified formula or value.
*
* @param row the row number, starting with 1
* @param col the column number, starting with 1
* @param formulaOrValue the value if it doesn't start with an '=' sign; if it is a formula, be
* careful that cells are specified in R1C1 format instead of A1 format.
* @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 setCell(int row, int col, String formulaOrValue)
throws IOException, ServiceException {
CellEntry newEntry = new CellEntry(row, col, formulaOrValue);
service.insert(cellFeedUrl, newEntry);
out.println("Added!");
}
public void LoadDonationWorksheets() throws Exception {
if (MyService == null) ConnectToSpreadsheets();
String sheetName = Settings.FinancialsGoogleSpreadsheetName;
URL metafeedUrl = new URL("https://spreadsheets.google.com/feeds/spreadsheets/private/full");
System.out.println("Opening \"" + sheetName + "\" Google spreadsheet...");
SpreadsheetQuery query2 = new SpreadsheetQuery(metafeedUrl);
query2.setTitleQuery(sheetName);
query2.setTitleExact(true);
SpreadsheetFeed feed2 = MyService.query(query2, SpreadsheetFeed.class);
List<SpreadsheetEntry> entries2 = feed2.getEntries();
if (entries2.size() < 1)
throw new Exception("Cannot access spreadsheet:" + Settings.FinancialsGoogleSpreadsheetName);
MyFinancialsSheet = entries2.get(0);
MyDonationSheetMap = new HashMap<String, DonationSheet>();
MyDonationSheets = new Vector<DonationSheet>();
MyCellFeedURLs = new Vector<URL>();
List<WorksheetEntry> worksheets = MyFinancialsSheet.getWorksheets();
for (int i = 0; i < worksheets.size(); i++) {
String title = worksheets.get(i).getTitle().getPlainText();
if (title.contains("Donations")) {
DonationSheet sheet = new DonationSheet();
sheet.worksheetEntry = worksheets.get(i);
sheet.title = title;
MyDonationSheetMap.put(title, sheet);
MyDonationSheets.add(sheet);
} else {
MyDonationSheets.add(null);
}
MyCellFeedURLs.add(worksheets.get(i).getCellFeedUrl());
}
}
public WorksheetEntry createWorksheet(URL _worksheetUrl, WorksheetEntry _worksheetEntry)
throws IOException, ServiceException {
WorksheetEntry worksheet = new WorksheetEntry(_worksheetEntry);
return spreadsheetService.insert(_worksheetUrl, worksheet);
}
public List<WorksheetEntry> copyDocument(
DocumentListEntry documentListEntry,
String newDocumentTitle,
DocumentListEntry destinationFolderEntry)
throws IOException, ServiceException {
documentListEntry.setTitle(new PlainTextConstruct(newDocumentTitle));
URL url = new URL("https://docs.google.com/feeds/default/private/full/");
DocumentListEntry newDoc = docsService.insert(url, documentListEntry);
String destFolder = ((MediaContent) destinationFolderEntry.getContent()).getUri();
URL newUrl = new URL(destFolder);
// Convert DocumentListEntry to SpreadsheetEntry
DocumentListEntry newDocumentListEntry = docsService.insert(newUrl, newDoc);
String spreadsheetURL =
"https://spreadsheets.google.com/feeds/spreadsheets/" + newDocumentListEntry.getDocId();
SpreadsheetEntry spreadsheetEntry =
spreadsheetService.getEntry(new URL(spreadsheetURL), SpreadsheetEntry.class);
return spreadsheetEntry.getWorksheets();
}
public SpreadsheetEntry getSpreadsheetEntryFromDocumentListEntry(DocumentListEntry docListEntry)
throws IOException, ServiceException {
String spreadsheetURL =
"https://spreadsheets.google.com/feeds/spreadsheets/" + docListEntry.getDocId();
return spreadsheetService.getEntry(new URL(spreadsheetURL), SpreadsheetEntry.class);
}
public void ConnectToSpreadsheets() throws Exception {
try {
System.out.println("Logging in to Google Spreadsheets...");
MyService = new SpreadsheetService("HurumaHouse-DonationManager-1.0");
MyService.setUserCredentials(GoogleUserName, GooglePassword);
} catch (AuthenticationException E) {
throw new Exception("Google docs username or password are invalid.");
}
}
public void AppendDonation(int sheetHandle, int code, String date, double amount, double fee)
throws Exception {
if (MyFinancialsSheet == null) LoadDonationWorksheets();
String donationCodeStr = Integer.toString(code);
String amountStr = Double.toString(amount - fee);
URL url = MyCellFeedURLs.get(sheetHandle);
DonationSheet sheet = MyDonationSheets.get(sheetHandle);
if (sheet == null) return;
try {
MyService.insert(url, new CellEntry(sheet.nextDonationRow, 1, donationCodeStr));
MyService.insert(url, new CellEntry(sheet.nextDonationRow, 2, date));
MyService.insert(url, new CellEntry(sheet.nextDonationRow, 3, amountStr));
} catch (ServiceException E) {
throw new Exception("Cannot update " + sheet.title + " worksheet");
}
sheet.nextDonationRow++;
}
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);
}
private SpreadsheetService getService() throws AuthenticationException {
if (this.service == null) {
service = new SpreadsheetService("KATTS-Evaluation-v1");
service.setUserCredentials(getUsername(), getPassword());
}
return service;
}
public WorksheetEntry createWorksheet(URL url, String worksheetTitle, int rows, int columns)
throws IOException, ServiceException {
WorksheetEntry worksheet = new WorksheetEntry();
worksheet.setTitle(new PlainTextConstruct(worksheetTitle));
worksheet.setRowCount(rows);
worksheet.setColCount(columns);
return spreadsheetService.insert(url, worksheet);
}
public void UpdateCell(int sheetHandle, int row, int col, String value) throws Exception {
if (MyFinancialsSheet == null) LoadDonationWorksheets();
try {
MyService.insert(MyCellFeedURLs.get(sheetHandle), new CellEntry(row, col, value));
} catch (ServiceException E) {
throw new Exception(
"Cannot update " + MyDonationSheets.get(sheetHandle).title + " worksheet");
}
}
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();
}
}
}
}
}
public static void parseOneWorkSheet(
SpreadsheetService service,
Project project,
ProjectMetadata metadata,
final ImportingJob job,
URL docURL,
URL worksheetURL,
int limit,
JSONObject options,
List<Exception> exceptions) {
try {
WorksheetEntry worksheetEntry = service.getEntry(worksheetURL, WorksheetEntry.class);
String spreadsheetName = docURL.toExternalForm();
try {
SpreadsheetEntry spreadsheetEntry = service.getEntry(docURL, SpreadsheetEntry.class);
spreadsheetName = spreadsheetEntry.getTitle().getPlainText();
} catch (ServiceException e) { // RedirectRequiredException among others
// fall back to just using the URL (better for traceability anyway?)
}
String fileSource = spreadsheetName + " # " + worksheetEntry.getTitle().getPlainText();
setProgress(job, fileSource, 0);
TabularImportingParserBase.readTable(
project,
metadata,
job,
new WorksheetBatchRowReader(job, fileSource, service, worksheetEntry, 20),
fileSource,
limit,
options,
exceptions);
setProgress(job, fileSource, 100);
} catch (IOException e) {
e.printStackTrace();
exceptions.add(e);
} catch (ServiceException e) {
e.printStackTrace();
exceptions.add(e);
}
}
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);
}
/**
* 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;
}
/**
* 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);
}
}
/**
* 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);
}
/**
* 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);
}
}
@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 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 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 void LoadDonorWorksheet() throws Exception {
if (MyService == null) ConnectToSpreadsheets();
String sheetName = Settings.DonorsGoogleSpreadsheetName;
URL metafeedUrl = new URL("https://spreadsheets.google.com/feeds/spreadsheets/private/full");
System.out.println("Opening \"" + sheetName + "\" Google spreadsheet...");
SpreadsheetQuery query1 = new SpreadsheetQuery(metafeedUrl);
query1.setTitleQuery("Huruma House Donors");
query1.setTitleExact(true);
SpreadsheetFeed feed1 = MyService.query(query1, SpreadsheetFeed.class);
List<SpreadsheetEntry> entries1 = feed1.getEntries();
if (entries1.size() < 1)
throw new Exception("Cannot access spreadsheet:" + Settings.DonorsGoogleSpreadsheetName);
MyDonorsSheet = entries1.get(0);
}
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;
}
/**
* 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);
}