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();
    }
  }
Exemplo n.º 2
0
  /**
   * 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);
  }
Exemplo n.º 4
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());
 }
Exemplo n.º 6
0
  /**
   * 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);
    }
  }
Exemplo n.º 7
0
  /**
   * 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++;
  }
Exemplo n.º 14
0
  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);
  }
Exemplo n.º 15
0
  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");
    }
  }
Exemplo n.º 18
0
 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();
         }
       }
     }
   }
 }
Exemplo n.º 19
0
  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);

  }
Exemplo n.º 21
0
  /**
   * 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;
  }
Exemplo n.º 22
0
  /**
   * 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);
    }
  }
Exemplo n.º 23
0
  /**
   * 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);
  }
Exemplo n.º 24
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;
  }
Exemplo n.º 30
0
  /**
   * 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);
  }