Java Examples for com.google.gdata.client.spreadsheet.SpreadsheetService

The following java examples will help you to understand the usage of com.google.gdata.client.spreadsheet.SpreadsheetService. These source code samples are taken from different open source projects.

/** * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) */ protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException < // TODO Auto-generated method stub PrintWriter out = response.getWriter(); HttpSession session = request.getSession(); String teacherPartnerExternalRef = (String) session.getAttribute("email"); //upload from google drive if (request.getParameter("from").toString().equals("Google")) < List> studentListFromGoogle = new ArrayList>(); SpreadsheetService service = new SpreadsheetService("ASSISTmentsDirect"); String accessToken = session.getAttribute("Google_access_token").toString(); service.setOAuth2Credentials(new Credential(BearerToken.authorizationHeaderAccessMethod()).setFromTokenResponse(new TokenResponse().setAccessToken(accessToken))); String worksheetId = request.getParameter("worksheetId"); List worksheets = (List) session.getAttribute("worksheets"); for (WorksheetEntry worksheet : worksheets) < if (worksheet.getId().equals(worksheetId)) < URL listFeedUrl = worksheet.getListFeedUrl(); try < ListFeed listFeed = service.getFeed(listFeedUrl, ListFeed.class); for (ListEntry row : listFeed.getEntries()) < MapstudentMap = new HashMap(); for (String tag : row.getCustomElements().getTags()) < if (tag.equalsIgnoreCase("firstname") || tag.equalsIgnoreCase("first_name") || tag.equalsIgnoreCase("first name")) < studentMap.put("first_name", row.getCustomElements().getValue(tag)); >else if (tag.equalsIgnoreCase("lastname") || tag.equalsIgnoreCase("last_name") || tag.equalsIgnoreCase("last name")) < studentMap.put("last_name", row.getCustomElements().getValue(tag)); >> //need to organize this block of codes later String firstName = studentMap.get("first_name"); String lastName = studentMap.get("last_name"); String teacherRef = new String(); String studentClassRef = new String(); ExternalStudentClassDAO classDAO = new ExternalStudentClassDAO(LiteUtility.PARTNER_REF); ExternalStudentClass esc = classDAO.findByPartnerExternalRef(teacherPartnerExternalRef); studentClassRef = esc.getAssistmentsExternalRefernce(); ExternalUserDAO userDAO = new ExternalUserDAO(LiteUtility.PARTNER_REF); ExternalUser user = userDAO.findByPartnerExternalRef(teacherPartnerExternalRef); teacherRef = user.getAssistmentsExternalRefernce(); User student = LiteUtility.populateStudentInfo(firstName.substring(0, 1).toUpperCase() + firstName.substring(1).toLowerCase(), lastName.substring(0, 1).toUpperCase() + lastName.substring(1).toLowerCase()); List studentRefAccessToken = null; try < String partnerExternalRef = student.getDisplayName() + "_" + teacherRef; studentRefAccessToken = LiteUtility.transferStudent(student, partnerExternalRef); >catch (TransferUserException e) < String errorMessage = e.getMessage(); String instruction = "The server seems to be unstable at this moment. Please take a break and try it again later."; LiteUtility.directToErrorPage(errorMessage, instruction, request, response); return; >if (studentRefAccessToken != null) < String studentRef = studentRefAccessToken.get(0); String onBehalf = studentRefAccessToken.get(1); int type = Integer.parseInt(studentRefAccessToken.get(2)); if (type == Constants.BRAND_NEW_USER) < int studentId = StudentClassController.getStudentId(studentRef); studentMap.put("student_id", Integer.toString(studentId)); studentListFromGoogle.add(studentMap); StudentClassController.enrollStudent(studentClassRef, studentRef, LiteUtility.PARTNER_REF, onBehalf); >> else < System.err.println("error occurred"); >> > catch (ServiceException e) < e.printStackTrace(); >break; > > request.getRequestDispatcher("/Teacher").forward(request, response); > else if (request.getParameter("from").toString().equals("file")) < // JsonArray studentList = new JsonArray(); String studentListFileName = request.getParameter("student_list_file_name"); String fileName = studentListFileName.substring(studentListFileName.lastIndexOf('\\') + 1); String filePath = LiteUtility.UPLOAD_DIRECTORY + "/" + fileName; FileReader fileReader = new FileReader(filePath); BufferedReader bufferedReader = new BufferedReader(fileReader); String line = null; while ((line = bufferedReader.readLine()) != null) < JsonObject jsonStudent = new JsonObject(); jsonStudent.addProperty("first_name", line.substring(0, line.indexOf(','))); jsonStudent.addProperty("last_name", line.substring(line.indexOf(',') + 2)); String firstName = jsonStudent.get("first_name").getAsString(); String lastName = jsonStudent.get("last_name").getAsString(); String teacherRef = new String(); String studentClassRef = new String(); ExternalStudentClassDAO classDAO = new ExternalStudentClassDAO(LiteUtility.PARTNER_REF); ExternalStudentClass esc = classDAO.findByPartnerExternalRef(teacherPartnerExternalRef); studentClassRef = esc.getAssistmentsExternalRefernce(); ExternalUserDAO userDAO = new ExternalUserDAO(LiteUtility.PARTNER_REF); ExternalUser user = userDAO.findByPartnerExternalRef(teacherPartnerExternalRef); teacherRef = user.getAssistmentsExternalRefernce(); User student = LiteUtility.populateStudentInfo(firstName.substring(0, 1).toUpperCase() + firstName.substring(1).toLowerCase(), lastName.substring(0, 1).toUpperCase() + lastName.substring(1).toLowerCase()); ListstudentRefAccessToken = null; try < String partnerExternalRef = student.getDisplayName() + "_" + teacherRef; studentRefAccessToken = LiteUtility.transferStudent(student, partnerExternalRef); >catch (TransferUserException e) < String errorMessage = e.getMessage(); String instruction = "The server seems to be unstable at this moment. Please take a break and try it again later."; LiteUtility.directToErrorPage(errorMessage, instruction, request, response); return; >if (studentRefAccessToken != null) < String studentRef = studentRefAccessToken.get(0); String onBehalf = studentRefAccessToken.get(1); int type = Integer.parseInt(studentRefAccessToken.get(2)); if (type == Constants.BRAND_NEW_USER) < int studentId = StudentClassController.getStudentId(studentRef); StudentClassController.enrollStudent(studentClassRef, studentRef, LiteUtility.PARTNER_REF, onBehalf); >> else < System.err.println("error occurred"); >> bufferedReader.close(); request.getRequestDispatcher("/Teacher").forward(request, response); > else if (request.getParameter("from").toString().equals("excel")) < String studentListFileName = request.getParameter("student_list_file_name"); String fileName = studentListFileName.substring(studentListFileName.lastIndexOf('\\') + 1); String filePath = LiteUtility.UPLOAD_DIRECTORY + "/" + fileName; File file = new File(filePath); FileInputStream fis = new FileInputStream(file); POIFSFileSystem fs = new POIFSFileSystem(fis); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) < HSSFRow row = sheet.getRow(i); if (row != null) < HSSFCell cell = row.getCell(0); String firstName = cell.getStringCellValue(); cell = row.getCell(1); String lastName = cell.getStringCellValue(); String teacherRef = new String(); String studentClassRef = new String(); ExternalStudentClassDAO classDAO = new ExternalStudentClassDAO(LiteUtility.PARTNER_REF); ExternalStudentClass esc = classDAO.findByPartnerExternalRef(teacherPartnerExternalRef); studentClassRef = esc.getAssistmentsExternalRefernce(); ExternalUserDAO userDAO = new ExternalUserDAO(LiteUtility.PARTNER_REF); ExternalUser user = userDAO.findByPartnerExternalRef(teacherPartnerExternalRef); teacherRef = user.getAssistmentsExternalRefernce(); User student = LiteUtility.populateStudentInfo(firstName.substring(0, 1).toUpperCase() + firstName.substring(1).toLowerCase(), lastName.substring(0, 1).toUpperCase() + lastName.substring(1).toLowerCase()); ListstudentRefAccessToken = null; try < String partnerExternalRef = student.getDisplayName() + "_" + teacherRef; studentRefAccessToken = LiteUtility.transferStudent(student, partnerExternalRef); >catch (TransferUserException e) < String errorMessage = e.getMessage(); String instruction = "The server seems to be unstable at this moment. Please take a break and try it again later."; LiteUtility.directToErrorPage(errorMessage, instruction, request, response); return; >if (studentRefAccessToken != null) < String studentRef = studentRefAccessToken.get(0); String onBehalf = studentRefAccessToken.get(1); int type = Integer.parseInt(studentRefAccessToken.get(2)); if (type == Constants.BRAND_NEW_USER) < int studentId = StudentClassController.getStudentId(studentRef); StudentClassController.enrollStudent(studentClassRef, studentRef, LiteUtility.PARTNER_REF, onBehalf); >> else < System.err.println("error occurred"); >> > request.getRequestDispatcher("/Teacher").forward(request, response); > >

@Override public void write(Writable w) throws IOException < MapWritable map = (MapWritable) w; final GDataService supplier = new GDataService(credentials); final SpreadsheetService service = supplier.getSpreadsheetService(); final URL worksheetURL = supplier.getWorksheetURL(spreadsheetName, worksheetName); if (worksheetURL == null) < throw new IOException(String.format("Could not find worksheet [%s] in spreadsheet [%s].", worksheetName, spreadsheetName)); >final ListEntry newEntry = new ListEntry(); final CustomElementCollection customElements = newEntry.getCustomElements(); for (final Map.Entry entry : map.entrySet()) < customElements.setValueLocal(entry.getKey().toString(), entry.getValue().toString()); >try < service.insert(worksheetURL, newEntry); >catch (final ServiceException e) < throw new RuntimeException(e); >>

public StringMatrix downloadSpreadsheet(String username, String password, String spreadsheet, String sheet, IProgressMonitor monitor) throws BioclipseException < StringMatrix strMatrix = new StringMatrix(); try < if (username == null) throw new NullPointerException("undefined username"); if (password == null) throw new NullPointerException("undefined password"); SpreadsheetService service = new SpreadsheetService("net.bioclipse.google.client"); System.out.println("Google user name: " + username); System.out.println("Google user name: " + password); service.setUserCredentials(username, password); URL metafeedUrl = new URL("http://spreadsheets.google.com/feeds/spreadsheets/private/full"); SpreadsheetFeed feed = service.getFeed(metafeedUrl, SpreadsheetFeed.class); List spreadsheets = feed.getEntries(); SpreadsheetEntry spreadsheetEntry = null; for (int i = 0; i < spreadsheets.size(); i++) < SpreadsheetEntry entry = spreadsheets.get(i); if (spreadsheet.equals(entry.getTitle().getPlainText())) < spreadsheetEntry = entry; >> if (spreadsheet == null) < throw new Exception("No spreadsheets with the name: " + spreadsheet); >WorksheetQuery worksheetQuery = new WorksheetQuery(spreadsheetEntry.getWorksheetFeedUrl()); worksheetQuery.setTitleQuery(sheet); WorksheetFeed worksheetFeed = service.query(worksheetQuery, WorksheetFeed.class); List worksheets = worksheetFeed.getEntries(); if (worksheets.isEmpty()) < throw new Exception("No worksheets with that name in spreadhsheet " + spreadsheetEntry.getTitle().getPlainText()); >WorksheetEntry worksheet = worksheets.get(0); CellFeed cellFeed = service.getFeed(worksheet.getCellFeedUrl(), CellFeed.class); List cells = cellFeed.getEntries(); System.out.println("#Cells: " + cells.size()); for (CellEntry cellEntry : cells) < Cell cell = cellEntry.getCell(); System.out.println("Cell: " + cell.getRow() + ", " + cell.getCol() + ", " + cell.getValue()); strMatrix.set(cell.getRow(), cell.getCol(), cell.getValue()); >> catch (Exception exception) < throw new BioclipseException("Error while creating the StringMatrix.", exception); >return strMatrix; >

public APIResponse readSpreadsheetContents(@Named("spreadSheetKey") String gsKey, HttpServletRequest req, User user) < try < SpreadsheetService service = new SpreadsheetService("MySpreadsheetIntegration-v1"); URL metafeedUrl = new URL("https://spreadsheets.google.com/feeds/spreadsheets/1ce55J6l5IjXCOVfFklL9i6c0kYOHu-p9rabM9YdpBTg"); String authString = req.getHeader("Authorization"); String strBearer = "Bearer "; String accessToken = authString.substring(strBearer.length()); System.out.println(accessToken); GoogleCredential credential = new GoogleCredential().setAccessToken(accessToken); service.setOAuth2Credentials(credential); SpreadsheetEntry spreadsheet = service.getEntry(metafeedUrl, SpreadsheetEntry.class); URL listFeedUrl = ((WorksheetEntry) spreadsheet.getWorksheets().get(0)).getListFeedUrl(); // Print entries ListFeed feed = (ListFeed) service.getFeed(listFeedUrl, ListFeed.class); for (ListEntry entry : feed.getEntries()) < System.out.println("new row"); for (String tag : entry.getCustomElements().getTags()) < System.out.println(" " + tag + ": " + entry.getCustomElements().getValue(tag)); >> return new APIResponse().status(Status.SUCCESS); > catch (Exception ex) < return APIUtils.toAPIResponse(ex, true, new RequestInfo().req(req)); >>

public static String onSupport2() throws AuthenticationException, MalformedURLException, IOException, ServiceException, URISyntaxException < SpreadsheetService service = new SpreadsheetService("MySpreadsheetIntegration-foo"); URL url = new URL("https://spreadsheets.google.com/feeds/spreadsheets/private/full?title=GSS-Dev-Schedule"); SpreadsheetFeed feed = service.getFeed(url, SpreadsheetFeed.class); List spreadsheets = feed.getEntries(); SpreadsheetEntry spreadsheet = spreadsheets.get(0); WorksheetFeed worksheetFeed = service.getFeed(spreadsheet.getWorksheetFeedUrl(), WorksheetFeed.class); List worksheets = worksheetFeed.getEntries(); WorksheetEntry worksheet = worksheets.get(0); // fetch A1 only URL cellFeedUrl = new URI(worksheet.getCellFeedUrl().toString() + "?min-row=1&max-row=1&min-col=1&max-col=1").toURL(); CellFeed cellFeed = service.getFeed(cellFeedUrl, CellFeed.class); // Iterate through each cell, printing its value. String onSupport = cellFeed.getEntries().get(0).getCell().getValue(); return onSupport; >

private String getFeedUrlString(String linkRelKind) < Version spreadsheetVersion = state.service.getProtocolVersion(); if (spreadsheetVersion.isCompatible(SpreadsheetService.Versions.V1)) < Link feedLink = this.getLink(linkRelKind, Link.Type.ATOM); return feedLink.getHref(); >else < // List or Cells feed Url? if (linkRelKind.equals(Namespaces.LIST_LINK_REL)) < // the list feed is stored as a tag return ((OutOfLineContent) (this.getContent())).getUri(); > else < // it must be Namespaces.CELLS_LINK_REL // the cells feed is stored in the tag Link feedLink = this.getLink(linkRelKind, Link.Type.ATOM); return feedLink.getHref(); > > >

/** * Runs the demo. * * @param args the command-line arguments * @throws AuthenticationException if the service is unable to validate the * username and password. */ public static void main(String[] args) throws AuthenticationException < SimpleCommandLineParser parser = new SimpleCommandLineParser(args); String username = parser.getValue("username", "user", "u"); String password = parser.getValue("password", "pass", "p"); boolean help = parser.containsKey("help", "h"); if (help || username == null || password == null) < usage(); System.exit(1); >TableDemo demo = new TableDemo(new SpreadsheetService("Table Demo"), System.out); demo.run(username, password); >

@BeforeMethod public void setUp() throws IOException, ServiceException < spreadsheetService = mock(SpreadsheetService.class); listFeed = mock(ListFeed.class); TextConstruct textConstruct = when(mock(TextConstruct.class).getPlainText()).thenReturn("name").getMock(); when(listFeed.getTitle()).thenReturn(textConstruct); List entries = new ArrayList<>(); ListEntry entry = mock(ListEntry.class); CustomElementCollection customElements = mock(CustomElementCollection.class); when(customElements.getTags()).thenReturn(new LinkedHashSet<>(Arrays.asList("col1", "col2", "col3"))); when(customElements.getValue("col1")).thenReturn("val1"); when(customElements.getValue("col2")).thenReturn("val2"); when(customElements.getValue("col3")).thenReturn("val3"); when(entry.getCustomElements()).thenReturn(customElements); entries.add(entry); when(listFeed.getEntries()).thenReturn(entries); cellFeed = mock(CellFeed.class); List cells = new ArrayList<>(); Cell cell1 = mock(Cell.class); when(cell1.getRow()).thenReturn(1); when(cell1.getValue()).thenReturn("col1"); Cell cell2 = mock(Cell.class); when(cell2.getRow()).thenReturn(1); when(cell2.getValue()).thenReturn("col2"); Cell cell3 = mock(Cell.class); when(cell3.getRow()).thenReturn(1); when(cell3.getValue()).thenReturn("col3"); CellEntry entry1 = when(mock(CellEntry.class).getCell()).thenReturn(cell1).getMock(); CellEntry entry2 = when(mock(CellEntry.class).getCell()).thenReturn(cell2).getMock(); CellEntry entry3 = when(mock(CellEntry.class).getCell()).thenReturn(cell3).getMock(); cells.add(entry1); cells.add(entry2); cells.add(entry3); when(cellFeed.getEntries()).thenReturn(cells); when(cellFeed.getTitle()).thenReturn(textConstruct); spreadsheetRepository = new GoogleSpreadsheetRepository(spreadsheetService, "key", "id", entityTypeFactory, attrMetaFactory); >

public static void initializeFeedback(String user, String task_code, String filename) < SpreadsheetService sp_service = null; SpreadsheetEntry spreadsheet = null; try < String system_gmail_user = TheConfig.getInstance().getString(TheConfig.SYSTEMEMAIL_ADDRESS); String system_gmail_password = TheConfig.getInstance().getString(TheConfig.SYSTEMEMAIL_PASSWORD); Utils utils = new Utils(system_gmail_user, system_gmail_password); sp_service = utils.authenticateSpreadsheetService("Test-Doc-Service"); spreadsheet = utils.getOneSpreadSheet(sp_service, filename); System.out.println(spreadsheet.getTitle().getPlainText()); WorksheetFeed worksheetFeed = sp_service.getFeed(spreadsheet.getWorksheetFeedUrl(), WorksheetFeed.class); Listworksheets = worksheetFeed.getEntries(); WorksheetEntry worksheet = worksheets.get(0); URL listFeedUrl = worksheet.getCellFeedUrl(); List cells = new ArrayList(); cells.add(new CellEntry(1, 1, "Gate Data Extractor feedback")); cells.add(new CellEntry(5, 2, "Filename")); cells.add(new CellEntry(6, 2, "")); cells.add(new CellEntry(6, 3, "Expected expression")); cells.add(new CellEntry(6, 4, "Detected expression")); cells.add(new CellEntry(6, 5, "User comment")); cells.add(new CellEntry(7, 3, "Professor of Chemistry, 2010, University of Málaga")); cells.add(new CellEntry(7, 4, "Professor of Chemistry, 2010")); cells.add(new CellEntry(7, 5, "Miss the university!")); for (CellEntry cell : cells) sp_service.insert(listFeedUrl, cell); > catch (javax.naming.AuthenticationException ex) < ProjectLogger.LOGGER.error("Error build feedback template: " + ex.toString()); >catch (MalformedURLException ex) < ProjectLogger.LOGGER.error("Error build feedback template: " + ex.toString()); >catch (IOException ex) < ProjectLogger.LOGGER.error("Error build feedback template: " + ex.toString()); >catch (ServiceException ex) < ProjectLogger.LOGGER.error("Error build feedback template: " + ex.toString()); >>

Example 10

private List getWorksheetEntriesForDoc(URL docUrl, String token) throws IOException, ServiceException < if (token != null) < try < SpreadsheetService spreadsheetService = GDataExtension.getSpreadsheetService(token); SpreadsheetEntry spreadsheetEntry = spreadsheetService.getEntry(docUrl, SpreadsheetEntry.class); return spreadsheetEntry.getWorksheets(); > catch (ServiceException e) < >> return getWorksheetEntriesForDoc(docUrl); >

Example 11

public void run() < try < System.out.println("inside"); String valueToSendToForm = null; SpreadsheetService service = new SpreadsheetService("MySpreadsheetIntegration-v1"); String accessToken = "1/gC9y-YwAli-YuYHhvybt2gMhCO2O3OJDCwhHrGYTRIs"; String accessTokenSecret = "h8-A2d7QTJno_Sd-QTVWQeBY"; String appKey = "343113720444.apps.googleusercontent.com"; String appSecret = "_9AlBI6Wuqzaa8Js75Fox6Bb"; GoogleOAuthParameters oauthParameters = new GoogleOAuthParameters(); oauthParameters.setOAuthToken(accessToken); oauthParameters.setOAuthTokenSecret(accessTokenSecret); oauthParameters.setOAuthConsumerKey(appKey); OAuthSigner signer; oauthParameters.setOAuthConsumerSecret(appSecret); signer = new OAuthHmacSha1Signer(); service.setOAuthCredentials(oauthParameters, signer); // Define the URL to request. URL SPREADSHEET_FEED_URL; SPREADSHEET_FEED_URL = new URL("https://spreadsheets.google.com/feeds/spreadsheets/private/full"); // < Make a request to the API and get all spreadsheets. SpreadsheetFeed feed = service.getFeed(SPREADSHEET_FEED_URL, SpreadsheetFeed.class); Listspreadsheets = feed.getEntries(); // spreadsheet. for (SpreadsheetEntry spreadsheet : spreadsheets) < System.out.println(spreadsheet.getTitle().getPlainText()); if (spreadsheet.getTitle().getPlainText().equals("test")) < // < Get the first worksheet of the first // spreadsheet. WorksheetFeed worksheetFeed = service.getFeed(spreadsheet.getWorksheetFeedUrl(), WorksheetFeed.class); Listworksheets = worksheetFeed.getEntries(); WorksheetEntry worksheet = worksheets.get(0); // > // Fetch first row. URL cellFeedUrl = new URI(worksheet.getCellFeedUrl().toString() + "?min-row=1&max-row=1").toURL(); CellFeed cellFeed = service.getFeed(cellFeedUrl, CellFeed.class); // < Iterate through each cell, printing its value. for (CellEntry cell : cellFeed.getEntries()) < // Print the cell's address in A1 notation System.out.print(cell.getTitle().getPlainText() + "\t"); // Print the cell's address in R1C1 notation System.out.print(cell.getId().substring(cell.getId().lastIndexOf('/') + 1) + "\t"); // Print the cell's formula or text value System.out.print(cell.getCell().getInputValue() + "\t"); // Print the cell's calculated value if the // cell's value // is numeric // Prints empty string if cell's value is not // numeric System.out.print(cell.getCell().getNumericValue() + "\t"); // Print the cell's displayed value (useful if // the cell // has a formula) System.out.println(cell.getCell().getValue() + "\t"); // set value to send to user form valueToSendToForm = cell.getCell().getValue(); >break; > > // > Map data = new HashMap(); data.put("cellValue", valueToSendToForm); data.put("body", "Do zaakceptowania wartosc: " + valueToSendToForm); System.out.println("end"); // Notify manager that work item has been completed, pass // data ksession.getWorkItemManager().completeWorkItem(workItem.getId(), data); > catch (MalformedURLException e) < e.printStackTrace(); >catch (IOException e) < e.printStackTrace(); >catch (ServiceException e) < e.printStackTrace(); >catch (OAuthException e) < e.printStackTrace(); >catch (URISyntaxException e) < e.printStackTrace(); >>

Example 12

/** * Deletes Google Spreadsheets row. * * @param context the context * @param accountName the account name * @param trackName the track name * @return true if deletion is success. */ public static boolean deleteSpreadsheetsRow(Context context, String accountName, String trackName) < try < // Get spreadsheet Id Listfiles = searchSpreadsheets(context, accountName); if (files == null || files.size() == 0) < return false; >String spreadsheetId = files.get(0).getId(); // Get spreadsheet service SpreadsheetService spreadsheetService = new SpreadsheetService("MyTracks-" + SystemUtils.getMyTracksVersion(context)); Credential credential = new Credential(BearerToken.authorizationHeaderAccessMethod()); credential.setAccessToken(SendToGoogleUtils.getToken(context, accountName, SendToGoogleUtils.SPREADSHEETS_SCOPE)); spreadsheetService.setOAuth2Credentials(credential); // Get work sheet WorksheetFeed worksheetFeed = spreadsheetService.getFeed(new URL(String.format(Locale.US, SendSpreadsheetsAsyncTask.GET_WORKSHEETS_URI, spreadsheetId)), WorksheetFeed.class); Iterator worksheetEntryIterator = worksheetFeed.getEntries().iterator(); while (worksheetEntryIterator.hasNext()) < WorksheetEntry worksheetEntry = (WorksheetEntry) worksheetEntryIterator.next(); String worksheetTitle = worksheetEntry.getTitle().getPlainText(); if (worksheetTitle.equals(SPREADSHEETS_WORKSHEET_NAME)) < URL url = worksheetEntry.getListFeedUrl(); IteratorlistEntryIterator = spreadsheetService.getFeed(url, ListFeed.class).getEntries().iterator(); while (listEntryIterator.hasNext()) < ListEntry listEntry = (ListEntry) listEntryIterator.next(); String name = listEntry.getCustomElements().getValue(SPREADSHEETS_TRANCK_NAME_COLUMN); if (name.equals(trackName)) < listEntry.delete(); return true; >> > > > catch (Exception e) < Log.e(TAG, "Unable to delete spreadsheets row.", e); >return false; >

Example 13

private SpreadsheetEntry connect() < service = new SpreadsheetService("MySpreadsheetIntegration-v1"); try < service.setUserCredentials(googleUserName, googlePasswd); spreadSheetFeedUrl = new URL("https://spreadsheets.google.com/feeds/spreadsheets/private/full"); SpreadsheetFeed feed = service.getFeed(spreadSheetFeedUrl, SpreadsheetFeed.class); Listspreadsheets = feed.getEntries(); for (SpreadsheetEntry sheet : spreadsheets) < LOGGER.debug("Sheet title plain text: " + sheet.getTitle().getPlainText()); if (sheet.getTitle().getPlainText().equalsIgnoreCase(sheetName)) < return sheet; >> > catch (AuthenticationException e) < LOGGER.error(e); >catch (MalformedURLException e) < LOGGER.error(e); >catch (IOException e) < LOGGER.error(e); >catch (ServiceException e) < LOGGER.error(e); >return null; >

Example 14

/** * Recording a copy of the information somebody used the Streetlight Seattle Reporter app to send to Seattle City Light. * Google Docs approach inspired by: https://developers.google.com/google-apps/spreadsheets/#adding_a_list_row * Spreadsheet viewable at: https://docs.google.com/spreadsheet/ccc?key=0AvDYc0olGEwRdE56dTlKMVVZdGYxQkc5eGJEQzJLNkE&usp=drive_web#gid=0 * * @param parameters names and values from the activity; names are column headers in the spreadsheet. * @throws AuthenticationException * @throws MalformedURLException * @throws IOException * @throws ServiceException */ private void createRecordInSpreadsheet(List parameters) throws AuthenticationException, MalformedURLException, IOException, ServiceException < String timeStamp = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss").format(Calendar.getInstance().getTime()); parameters.add(new BasicNameValuePair("Date", timeStamp)); //parameters.remove(object) SpreadsheetService service = new SpreadsheetService("CodeForSeattle-StreetlightSeattleReporter-v1"); // Authorize the service object for a specific user (see other sections) final String user = "streetlightseattlereporter@gmail.com"; final String pword = StringUtil.repeat(getString(R.string.app_initials), 3) + "333"; service.setUserCredentials(user, pword); // Define the URL to request. This should never change. URL SPREADSHEET_FEED_URL = new URL("https://spreadsheets.google.com/feeds/spreadsheets/private/full"); // Make a request to the API and get all spreadsheets. SpreadsheetFeed feed = service.getFeed(SPREADSHEET_FEED_URL, SpreadsheetFeed.class); List spreadsheets = feed.getEntries(); if (spreadsheets.size() == 0) < // There were no spreadsheets, act accordingly. return; >SpreadsheetEntry spreadsheet = null; for (SpreadsheetEntry s : spreadsheets) < String title = s.getTitle().getPlainText(); if (title.equals("StreetlightSeattleReporter")) < spreadsheet = s; break; >if (// Backup plan. spreadsheet == null) spreadsheet = spreadsheets.get(0); > // Get the first worksheet of the first spreadsheet. WorksheetFeed worksheetFeed = service.getFeed(spreadsheet.getWorksheetFeedUrl(), WorksheetFeed.class); List worksheets = worksheetFeed.getEntries(); WorksheetEntry worksheet = worksheets.get(0); // Fetch the list feed of the worksheet. URL listFeedUrl = worksheet.getListFeedUrl(); ListFeed listFeed = service.getFeed(listFeedUrl, ListFeed.class); // Create a local representation of the new row. ListEntry row = new ListEntry(); // Set the values of the new row. for (NameValuePair param : parameters) < String key = param.getName(); // Refrain from storing these 3 fields in the Google Docs spreadsheet. if (key.equals("LastName") || key.equals("Phone") || key.equals("Email")) continue; row.getCustomElements().setValueLocal(key, param.getValue()); >// Send the new row to the API for insertion. row = service.insert(listFeedUrl, row); >

Example 15

/** * Function for writing an entry to the data store. * * @param entry * containing information about * * This function returns immediately. Writing to the data store is * done asynchronously. The Storage implementation should maintain a * queue of not yet written entries and try writing them from time to * time. * * Status string should be reported through getStatusString() * function, number of pending writes should be reported through * getPendingWritesCount() function. */ public void write(StudentGradesEntry entry) < try < SpreadsheetService service = new SpreadsheetService("Tigus Project Storage"); service.setUserCredentials(this.username, this.password); URL metafeedUrl = new URL("http://spreadsheets.google.com/feeds/spreadsheets/private/full"); SpreadsheetFeed feed = service.getFeed(metafeedUrl, SpreadsheetFeed.class); List spreadsheets = feed.getEntries(); for (int i = 0; i < spreadsheets.size(); i++) < SpreadsheetEntry en = spreadsheets.get(i); Listworksheets = en.getWorksheets(); for (int j = 0; j < worksheets.size(); j++) < WorksheetEntry worksheet = worksheets.get(j); //String title = worksheet.getTitle().getPlainText(); URL listFeedUrl = worksheet.getListFeedUrl(); service.getFeed(listFeedUrl, ListFeed.class); ListEntry newEntry = new ListEntry(); /* String nameValuePairs = new String("EntryID=4,TestID=456789,Grupa=334CA,Nume=Ene Andreea,PunctajTotal=45,Punctaj1=5,Punctaj2=10,Punctaj3=10,Punctaj4=10,Punctaj5=10"); for (String nameValuePair : nameValuePairs.split(",")) < String[] parts = nameValuePair.split("=", 2); String tag = parts[0]; String value = parts[1]; newEntry.getCustomElements().setValueLocal(tag, value); >*/ newEntry.getCustomElements().setValueLocal("EntryID", entry.id); newEntry.getCustomElements().setValueLocal("TestID", entry.testSerialNumber); newEntry.getCustomElements().setValueLocal("Grupa", entry.studentGroup); newEntry.getCustomElements().setValueLocal("Nume", entry.studentName); newEntry.getCustomElements().setValueLocal("PunctajTotal", entry.total + ""); Iterator it = entry.mapQuestionPosition.keySet().iterator(); while (it.hasNext()) < Integer key = (Integer) it.next(); switch(entry.mapQuestionPosition.get(key)) < case 1: newEntry.getCustomElements().setValueLocal("Punctaj1", entry.mapQuestionGrade.get(key) + ""); break; case 2: newEntry.getCustomElements().setValueLocal("Punctaj2", entry.mapQuestionGrade.get(key) + ""); break; case 3: newEntry.getCustomElements().setValueLocal("Punctaj3", entry.mapQuestionGrade.get(key) + ""); break; case 4: newEntry.getCustomElements().setValueLocal("Punctaj4", entry.mapQuestionGrade.get(key) + ""); break; case 5: newEntry.getCustomElements().setValueLocal("Punctaj5", entry.mapQuestionGrade.get(key) + ""); break; >> try < service.insert(listFeedUrl, newEntry); >catch (ServiceException se) < se.printStackTrace(); >> > > catch (Exception e) < e.printStackTrace(); >>

Example 16

@Override protected Void doInBackground(String. strings) < // We do not write to the spreadsheet if none is setup as that would generate an error. if (spreadsheetSet) < // Retrieve arguments passed by GetAuthToken and store them in mValues for further use. String subCategory = strings[0]; String amount = strings[1]; String description = strings[2]; String category = subCategory.split(" : ")[1]; subCategory = subCategory.split(" : ")[0]; mValues.put("subCategory", subCategory); mValues.put("category", category); mValues.put("amount", amount); mValues.put("description", description); try < // Create a SpreadsheetService to access the Google Spreadsheets data API SpreadsheetService service = new SpreadsheetService(APPLICATION_NAME); service.setProtocolVersion(SpreadsheetService.Versions.V3); service.setAuthSubToken(strings[3]); // Fetch the spreadsheet SpreadsheetEntry spreadsheet = getSpreadsheet(service); // Fetch the cells from the spreadsheet List cells = getCells(spreadsheet, service); if (cells == null) cannotGetCells(); else writeToSpreadsheet(cells); > catch (ServiceException e) < e.printStackTrace(); >catch (URISyntaxException e) < e.printStackTrace(); >catch (IOException e) < e.printStackTrace(); >> return null; >

Example 17

@Override protected void download(BandwidthThrottle throttle, StreamListener listener, boolean check) < if (attributes().isFile()) < OutputStream out = null; InputStream in = null; try < if (check) < this.getSession().check(); >MediaContent mc = new MediaContent(); StringBuilder uri = new StringBuilder(this.getExportUri()); final String type = this.getDocumentType(); final GoogleAuthTokenFactory.UserToken token = (GoogleAuthTokenFactory.UserToken) this.getSession().getClient().getAuthTokenFactory().getAuthToken(); try < if (type.equals(SpreadsheetEntry.LABEL)) < // Authenticate against the Spreadsheets API to obtain an auth token SpreadsheetService spreadsheet = new SpreadsheetService(this.getSession().getUserAgent()); final Credentials credentials = this.getSession().getHost().getCredentials(); spreadsheet.setUserCredentials(credentials.getUsername(), credentials.getPassword()); // Substitute the spreadsheets token for the docs token this.getSession().getClient().setUserToken(((GoogleAuthTokenFactory.UserToken) spreadsheet.getAuthTokenFactory().getAuthToken()).getValue()); > if (StringUtils.isNotEmpty(getExportFormat(type))) < uri.append("&exportFormat=").append(getExportFormat(type)); >mc.setUri(uri.toString()); MediaSource ms = this.getSession().getClient().getMedia(mc); in = ms.getInputStream(); if (null == in) < throw new IOException("Unable opening data stream"); >out = this.getLocal().getOutputStream(this.status().isResume()); this.download(in, out, throttle, listener); > finally < // Restore docs token for our DocList client this.getSession().getClient().setUserToken(token.getValue()); >> catch (IOException e) < this.error("Download failed", e); >catch (ServiceException e) < this.error("Download failed", e); >finally < IOUtils.closeQuietly(in); IOUtils.closeQuietly(out); >> >

Example 18

/////////////////////////////////////// // Google Spreadsheet related methods public List updateSheet(final SpreadsheetService service, SpreadsheetEntry spreadsheetEntry, String worksheetName, Map db2count) throws IOException, ServiceException < final ListupdatedServices = Lists.newArrayList(); final WorksheetFacade myWorksheet = WorksheetFacade.getWorksheetByName(service, spreadsheetEntry, worksheetName); if (myWorksheet == null) < final String msg = "Could not find the worksheet '" + worksheetName + "' in spreadsheet '" + spreadsheetEntry.getTitle().getPlainText() + "'"; sendEmail(msg, ""); throw new IllegalArgumentException(msg); >Map columnName2Index = myWorksheet.getColumnNameIndex(); final int nextEmptyRow = myWorksheet.getNextEmptyRow(); log.info("Next empty Row: " + nextEmptyRow); final List updatedCells = Lists.newArrayList(); // If a total column is present, then sum up all cells into it boolean hasTotalColumn = false; int totalColumnIndex = -1; int currentTotalSum = 0; for (Map.Entry e : columnName2Index.entrySet()) < final String db = e.getKey(); final Integer colIndex = e.getValue(); if (colIndex == null) < throw new IllegalArgumentException("You must give a non null colIndex"); >final Long dbCount = db2count.get(db); log.info("Updating worksheet for '" + db + "' having count of " + dbCount); String count; if (dbCount == null) < if (TOTAL_COLUMN.equalsIgnoreCase(db)) < totalColumnIndex = colIndex; hasTotalColumn = true; log.info("Skipping column: " + db); // go to next databse continue; >if (DATE_COLUMN.equalsIgnoreCase(db)) < log.info("Skipping column: " + db); // go to next databse continue; >> CellEntry previousCell = null; CellEntry previousDateCell = null; // TODO create WorksheetFacade.hasData() that returns true if there is at least one row of data in it. if (nextEmptyRow > 2) < previousCell = myWorksheet.getCell(nextEmptyRow - 1, colIndex); log.info("Collecting data for previous cell (value=" + previousCell.getCell().getValue() + ")"); previousDateCell = myWorksheet.getCell(nextEmptyRow - 1, 1); >CellEntry cell = null; if (dbCount != null) < count = dbCount.toString(); // we have data for that database cell = myWorksheet.getCell(nextEmptyRow, colIndex); if (previousCell != null) < final String previousValue = previousCell.getCell().getValue(); if (previousValue == null) < log.info("No previous cell available."); if (!"0".equals(count)) < if (log.isDebugEnabled()) log.info(worksheetName + ": statistics for " + db + " has changed since " + previousDateCell.getCell().getValue()); // the service has a positive count so we record it in the stats cell.changeInputValueLocal(count); updatedServices.add(db); >else < log.info("Skip update as the count is 0"); >> else < log.info("Previous cell available: " + previousValue); if (!previousValue.equals(count) && !"0".equals(count)) < // the service has a positive count so we record it in the stats cell.changeInputValueLocal(count); updatedServices.add(db); >else < log.info("Skip update as the count is 0 or the same as previous (" + previousValue + ")"); // we store the value anyways but do not register the service as updated. cell.changeInputValueLocal(count); >> > else < // we don't have data yet so all services have to be updated log.info("No previous cell available."); cell.changeInputValueLocal(count); updatedServices.add(db); >if (!StringUtils.isEmpty(cell.getCell().getInputValue())) < log.info("Adding new cell for service '" + db + "' with value: " + cell.getCell().getInputValue()); updatedCells.add(cell); >> else < log.info("++++ No data for " + db); log.info("++++ previousCell: " + previousCell.getCell().getValue()); if (previousCell != null) < final String previousValue = previousCell.getCell().getValue(); if (!"0".equals(previousValue)) < if (log.isDebugEnabled()) log.info(worksheetName + ": statistics for " + db + " has NOT changed since " + previousDateCell.getCell().getValue() + " copying previous one instead: " + previousValue); cell = myWorksheet.getCell(nextEmptyRow, colIndex.intValue()); cell.changeInputValueLocal(previousCell.getCell().getValue()); updatedCells.add(cell); >> else < log.info("Previous cell is null :("); >> // we may ommit part of the totalSum as we only count those that have new data. if (cell != null && !StringUtils.isEmpty(cell.getCell().getInputValue())) < log.info("Adding " + cell.getCell().getInputValue() + " to total sum"); // keep track of current total currentTotalSum += Integer.parseInt(cell.getCell().getInputValue()); >else < if (previousCell != null) < log.info("Adding " + previousCell.getCell().getInputValue() + " to total sum (previous cell)"); // keep track of current total if (previousCell.getCell().getValue() != null) < currentTotalSum += Integer.parseInt(previousCell.getCell().getValue()); >> > > if (updatedServices.isEmpty()) < // no services updated, flush the collection of updatedCells updatedCells.clear(); >final boolean hasNewData = !updatedCells.isEmpty(); if (hasTotalColumn) < // process all existing rows and update the total column log.info("Updating Total. "); log.info("getNextEmptyRow(): " + myWorksheet.getNextEmptyRow()); for (int row = 2; row < myWorksheet.getNextEmptyRow(); row++) < int totalSum = 0; boolean abortRow = false; log.info("getNextEmptyColumn(): " + myWorksheet.getNextEmptyColumn()); for (int col = 2; col < myWorksheet.getNextEmptyColumn(); col++) < if (col != totalColumnIndex) < final CellEntry cell = myWorksheet.getCell(row, col); final String value = cell.getCell().getValue(); if (value != null) < try < final int valueInt = Integer.parseInt(value); totalSum += valueInt; log.info(myWorksheet.getCell(row, 1).getCell().getValue() + " ->" + valueInt); > catch (NumberFormatException e) < sendEmail("Cell format error in worksheet: " + worksheetName, "Cell[" + row + "," + col + "]='" + value + "' when an integer was expected"); abortRow = true; continue; >> > else < log.info("Total contains: " + myWorksheet.getCell(row, col).getCell().getValue()); >> // columns log.info(myWorksheet.getCell(row, 1).getCell().getValue() + " -> " + totalSum + " (SUM)"); if (!abortRow) < final CellEntry cell = myWorksheet.getCell(row, totalColumnIndex); cell.changeInputValueLocal(String.valueOf(totalSum)); updatedCells.add(cell); >> // rows log.info("currentTotalSum = " + currentTotalSum); if (hasNewData && currentTotalSum != 0) < // given that the last row of this sheet is not available in the WorksheetFacade until we save the updatedCells, // we save the last total additionally log.info("Current total: " + currentTotalSum); final CellEntry cell = myWorksheet.getCell(myWorksheet.getNextEmptyRow(), totalColumnIndex); cell.changeInputValueLocal(String.valueOf(currentTotalSum)); updatedCells.add(cell); >> // Check if a database is not yet registered in the spreadsheet. for (Map.Entry e : db2count.entrySet()) < String db = e.getKey(); final Integer colIndex = columnName2Index.get(db); if (colIndex == null) < sendEmail("Missing DB name in header of worksheet '" + worksheetName + "'", "Please add " + db + " in the header of the spreadsheet - please visit " + spreadsheetEntry.getSpreadsheetLink().getHref()); >> if (log.isDebugEnabled()) < log.info("has new data ? " + hasNewData); log.info("About to update spreadsheet with " + updatedCells.size() + " cell(s)"); for (CellEntry c : updatedCells) < final Cell cc = c.getCell(); log.info("[" + cc.getCol() + "," + cc.getRow() + "]: " + cc.getInputValue() + (cc.getValue() != null ? " (was " + cc.getValue() + ")" : " (new)")); >> if (!updatedCells.isEmpty()) < if (hasNewData) < // add date to the row final CellEntry cell = myWorksheet.getCell(nextEmptyRow, 1); cell.changeInputValueLocal(today()); updatedCells.add(cell); >// upload update myWorksheet.batchUpdate(updatedCells); > return updatedServices; >

Example 19

@Provides static SpreadsheetService provideSpreadsheetService(GoogleCredential credential) < SpreadsheetService service = new SpreadsheetService(APPLICATION_NAME); service.setOAuth2Credentials(credential.createScoped(SCOPES)); return service; >

• Popular Classes
• Recent Additions