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();
}
private SpreadsheetEntry getSpreadSheet() throws IOException, ServiceException {
if (spreadsheet == null) {
URL spreadSheetFeedUrl;
SpreadsheetFeed spreadSheetFeed;
List<SpreadsheetEntry> spreadsheets;
spreadSheetFeedUrl = new URL(GoogleSpreadsheetHelper.SPREADSHEET_FEED_URL);
spreadSheetFeed = getService().getFeed(spreadSheetFeedUrl, SpreadsheetFeed.class);
spreadsheets = spreadSheetFeed.getEntries(); // get all of them
if (spreadsheets.size() == 0) {
throw new RuntimeException("No spreadsheet defined in this google account.");
}
for (SpreadsheetEntry s : spreadsheets) {
if (s.getTitle().getPlainText().equalsIgnoreCase(getSpreadsheetName())) {
spreadsheet = s;
return s;
}
}
throw new RuntimeException("No spreadsheet found with name '" + getSpreadsheetName() + "'.");
} else {
return spreadsheet;
}
}
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;
}
/**
* Get the WorksheetEntry for the worksheet in the spreadsheet with the specified name.
*
* @param spreadsheetName the name of the spreadsheet
* @param worksheetName the name of the worksheet in the spreadsheet
* @return worksheet with the specified name in the spreadsheet with the specified name
* @throws Exception if error is encountered, such as no spreadsheets with the name, or no
* worksheet wiht the name in the spreadsheet
*/
public Worksheet getWorksheet(String spreadsheetName, String worksheetName) throws Exception {
SpreadsheetEntry spreadsheetEntry = getSpreadsheet(spreadsheetName);
WorksheetQuery worksheetQuery = new WorksheetQuery(spreadsheetEntry.getWorksheetFeedUrl());
worksheetQuery.setTitleQuery(worksheetName);
WorksheetFeed worksheetFeed = service.query(worksheetQuery, WorksheetFeed.class);
List<WorksheetEntry> worksheets = worksheetFeed.getEntries();
if (worksheets.isEmpty()) {
throw new Exception(
"No worksheets with that name in spreadhsheet "
+ spreadsheetEntry.getTitle().getPlainText());
}
return new Worksheet(worksheets.get(0), service);
}
public 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());
}
}
@RequestMapping("home")
public ModelAndView welcomeHandler(HttpServletRequest req, HttpServletResponse res) {
ModelAndView mv = new ModelAndView("home");
HttpSession session = req.getSession();
UserInfo ui = (UserInfo) session.getAttribute("userInfo");
String accessToken = ui.getAccess_token();
DocsService service = new DocsService("mayRoro");
service.setHeader("Authorization", "OAuth " + accessToken);
try {
URL metafeedUrl =
new URL(
"https://spreadsheets.google.com/feeds/spreadsheets/private/full?title="
+ ConstFunc.SPREADSHEET_PREFIX);
SpreadsheetFeed feed = service.getFeed(metafeedUrl, SpreadsheetFeed.class);
List<SpreadsheetEntry> spreadsheets = feed.getEntries();
for (int i = 0; i < spreadsheets.size(); i++) {
SpreadsheetEntry entry = spreadsheets.get(i);
entry.setTitle(
new PlainTextConstruct(
entry.getTitle().getPlainText().substring(ConstFunc.SPREADSHEET_PREFIX.length())));
System.out.println("Self: " + entry.getSelfLink().getHref());
}
mv.addObject("spreadsheets", spreadsheets);
} catch (Exception e) {
e.printStackTrace();
}
// ************* Seznam vseh map *************
// try {
// URL metafeedUrl = new URL("https://docs.google.com/feeds/default/private/full/-/folder");
// Feed feed = service.getFeed(metafeedUrl, Feed.class);
// List<Entry> spreadsheets = feed.getEntries();
// for (int i = 0; i < spreadsheets.size(); i++) {
// Entry entry = spreadsheets.get(i);
// System.out.println("\t" + entry.getTitle().getPlainText());
// }
// mv.addObject("spreadsheets", spreadsheets);
// } catch (Exception e) {
// e.printStackTrace();
// }
return mv;
}
/**
* 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 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);
}
}
@RequestMapping("maut/{spreadsheetID}")
public ModelAndView maut(
HttpServletRequest req, HttpServletResponse res, @PathVariable String spreadsheetID) {
ModelAndView mv = new ModelAndView("maut");
HttpSession session = req.getSession();
String accessToken = ((UserInfo) session.getAttribute("userInfo")).getAccess_token();
DocsService service = new DocsService("mayRoro");
service.setHeader("Authorization", "OAuth " + accessToken);
try {
URL spreadsheetUrl =
new URL(
"https://spreadsheets.google.com/feeds/spreadsheets/private/full/" + spreadsheetID);
SpreadsheetEntry spreadsheet = service.getEntry(spreadsheetUrl, SpreadsheetEntry.class);
System.out.println("Title: " + spreadsheet.getTitle().getPlainText());
for (WorksheetEntry we : spreadsheet.getWorksheets()) {
System.out.println(we.getTitle().getPlainText() + ": " + we.getId());
}
spreadsheet.setTitle(
new PlainTextConstruct(
spreadsheet
.getTitle()
.getPlainText()
.substring(ConstFunc.SPREADSHEET_PREFIX.length())));
mv.addObject("spreadsheet", spreadsheet);
} catch (Exception e) {
e.printStackTrace();
}
return mv;
}
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;
}
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 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 connect(MessageContext messageContext) throws ConnectException {
try {
String spreadsheetName =
GoogleSpreadsheetUtils.lookupFunctionParam(messageContext, SPREADSHEET_NAME);
if (spreadsheetName == null || "".equals(spreadsheetName.trim())) {
log.error("Please make sure you have given a name for the spreadsheet");
ConnectException connectException =
new ConnectException("Please make sure you have given a name for the spreadsheet");
GoogleSpreadsheetUtils.storeErrorResponseStatus(messageContext, connectException);
return;
}
SpreadsheetService ssService =
new GoogleSpreadsheetClientLoader(messageContext).loadSpreadsheetService();
GoogleSpreadsheet gss = new GoogleSpreadsheet(ssService);
SpreadsheetEntry ssEntry = gss.getSpreadSheetsByTitle(spreadsheetName);
GoogleSpreadsheetWorksheet gssWorksheet =
new GoogleSpreadsheetWorksheet(ssService, ssEntry.getWorksheetFeedUrl());
List<String> resultData = gssWorksheet.getAllWorksheets();
int resultSize = resultData.size();
if (messageContext.getEnvelope().getBody().getFirstElement() != null) {
messageContext.getEnvelope().getBody().getFirstElement().detach();
}
OMFactory factory = OMAbstractFactory.getOMFactory();
OMNamespace ns =
factory.createOMNamespace("http://org.wso2.esbconnectors.googlespreadsheet", "ns");
OMElement searchResult = factory.createOMElement("getAllWorksheetsResult", ns);
OMElement result = factory.createOMElement("result", ns);
searchResult.addChild(result);
result.setText("true");
OMElement data = factory.createOMElement("data", ns);
searchResult.addChild(data);
for (int iterateCount = 0; iterateCount < resultSize; iterateCount++) {
if (resultData.get(iterateCount) != null) {
OMElement title = factory.createOMElement("title", ns);
data.addChild(title);
title.setText(resultData.get(iterateCount));
}
}
messageContext.getEnvelope().getBody().addChild(searchResult);
} catch (IOException te) {
log.error("Failed to show status: " + te.getMessage(), te);
GoogleSpreadsheetUtils.storeErrorResponseStatus(messageContext, te);
} catch (ServiceException te) {
log.error("Failed to show status: " + te.getMessage(), te);
GoogleSpreadsheetUtils.storeErrorResponseStatus(messageContext, te);
} catch (Exception te) {
log.error("Failed to show status: " + te.getMessage(), te);
GoogleSpreadsheetUtils.storeErrorResponseStatus(messageContext, te);
}
}
/**
* 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();
}
public void connect(MessageContext messageContext) throws ConnectException {
try {
String worksheetName =
GoogleSpreadsheetUtils.lookupFunctionParam(messageContext, WORKSHEET_NAME);
String spreadsheetName =
GoogleSpreadsheetUtils.lookupFunctionParam(messageContext, SPREADSHEET_NAME);
if ((worksheetName == null || "".equals(worksheetName.trim()))
&& (spreadsheetName == null || "".equals(spreadsheetName.trim()))) {
log.error(
"Please make sure you have given a valid input for the worksheet or spreadsheet name");
ConnectException connectException =
new ConnectException(
"Please make sure you have given a valid input for the worksheet or spreadsheet name");
GoogleSpreadsheetUtils.storeErrorResponseStatus(messageContext, connectException);
return;
}
SpreadsheetService ssService =
new GoogleSpreadsheetClientLoader(messageContext).loadSpreadsheetService();
GoogleSpreadsheet gss = new GoogleSpreadsheet(ssService);
SpreadsheetEntry ssEntry = gss.getSpreadSheetsByTitle(spreadsheetName);
if (ssEntry != null) {
List<Person> resultData = null;
resultData = gss.getAuthors(ssEntry);
if (worksheetName != null) {
GoogleSpreadsheetWorksheet gssWorksheet =
new GoogleSpreadsheetWorksheet(ssService, ssEntry.getWorksheetFeedUrl());
WorksheetEntry wsEntry = gssWorksheet.getWorksheetByTitle(worksheetName);
if (wsEntry != null) {
if (gssWorksheet.getAuthors(wsEntry).size() > 0) {
resultData.clear();
resultData = gssWorksheet.getAuthors(wsEntry);
}
} else {
ConnectException connectException =
new ConnectException(
"Cannot retrieve the authors. Worksheet with the given name is not available.");
log.error("Error occured: " + connectException.getMessage(), connectException);
GoogleSpreadsheetUtils.storeErrorResponseStatus(messageContext, connectException);
return;
}
}
int resultSize = resultData.size();
GoogleSpreadsheetUtils.removeTransportHeaders(messageContext);
if (messageContext.getEnvelope().getBody().getFirstElement() != null) {
messageContext.getEnvelope().getBody().getFirstElement().detach();
}
OMFactory factory = OMAbstractFactory.getOMFactory();
OMNamespace ns =
factory.createOMNamespace("http://org.wso2.esbconnectors.googlespreadsheet", "ns");
OMElement searchResult = factory.createOMElement("getAuthorsResult", ns);
OMElement result = factory.createOMElement("result", ns);
searchResult.addChild(result);
result.setText("true");
OMElement data = factory.createOMElement("data", ns);
searchResult.addChild(data);
for (int iterateCount = 0; iterateCount < resultSize; iterateCount++) {
if (resultData.get(iterateCount) != null) {
OMElement author = factory.createOMElement("author", ns);
data.addChild(author);
author.setText(resultData.get(iterateCount).getName());
}
}
messageContext.getEnvelope().getBody().addChild(searchResult);
} else {
ConnectException connectException =
new ConnectException(
"Cannot retrieve the authors. Spreadsheet with the given name is not available.");
log.error("Error occured: " + connectException.getMessage(), connectException);
GoogleSpreadsheetUtils.storeErrorResponseStatus(messageContext, connectException);
}
} catch (IOException te) {
log.error("Error occured " + te.getMessage(), te);
GoogleSpreadsheetUtils.storeErrorResponseStatus(messageContext, te);
} catch (ServiceException te) {
log.error("Error occured " + te.getMessage(), te);
GoogleSpreadsheetUtils.storeErrorResponseStatus(messageContext, te);
} catch (Exception te) {
log.error("Error occured " + te.getMessage(), te);
GoogleSpreadsheetUtils.storeErrorResponseStatus(messageContext, te);
}
}
