Esempio n. 1
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.");
    }
  }
Esempio n. 2
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);
    }
  }
  public void copyRowsFromCellFeed(CellFeed _cellFeed, WorksheetEntry _targetWorksheet)
      throws IOException, ServiceException {

    HashMap m = new HashMap();
    List<Map<Integer, Object>> rows = new ArrayList<Map<Integer, Object>>();

    int currentRow = 1;

    for (CellEntry cell : _cellFeed.getEntries()) {
      if (currentRow != cell.getCell().getRow()) {
        rows.add(m);
        currentRow = cell.getCell().getRow();
        m = new HashMap();
      }
      m.put(cell.getCell().getCol(), cell.getCell().getInputValue());

      //			 logger.info(cell.getTitle().getPlainText());
      //			  String shortId = cell.getId().substring(cell.getId().lastIndexOf('/') + 1);
      //			  logger.info(" -- A Cell(" + shortId + "/" + cell.getTitle().getPlainText()
      //			      + ") formula(" + cell.getCell().getInputValue() + ") numeric("
      //			      + cell.getCell().getNumericValue() + ") value("
      //			      + cell.getCell().getValue() + ")");
    }

    // add last row
    rows.add(m);
    logger.info("\n\nRows to add: " + rows.size());

    writeBatchRows(_targetWorksheet, rows, 1);
  }
Esempio n. 4
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);
    }
  }
Esempio n. 5
0
  @Override
  protected void loadHeader() throws EolModelLoadingException {
    try {
      // Ensure the header is loaded for a worksheet that exists in the spreadsheet
      if (!this.existsInSpreadsheet) {
        throw new Exception(this.nonexistentWorksheetMessage());
      }

      // First row is the header row
      CellFeed cellFeed = this.fetchCellFeed(1, true);
      if (cellFeed != null) {
        // Determine the size of the header row
        int indexOfLast = 0;
        for (CellEntry cell : cellFeed.getEntries()) {
          if (cell.getCell().getValue() != null) {
            indexOfLast = cell.getCell().getCol();
          }
        }
        // Get columns
        for (CellEntry entry : cellFeed.getEntries()) {
          if (entry.getCell().getCol() > indexOfLast) break;
          // Get column name
          String name = entry.getCell().getValue();
          if (name != null && !name.isEmpty()) {
            // Get column position
            int index = entry.getCell().getCol();
            // Add the column to the worksheet
            SpreadsheetColumnMetadata c = new SpreadsheetColumnMetadata();
            c.index = Integer.toString(index);
            c.name = name;
            this.addColumn(c);
          }
        }
      } else {
        throw new Exception("Google cell feed failed to load the header row.");
      }

    } catch (Exception e) {
      throw new EolModelLoadingException(e, this.model);
    }
  }
Esempio n. 6
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);
    }
  }
  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;
  }
Esempio n. 8
0
    /** Presents the given cell feed as a map from row, column pair to CellEntry. */
    private void refreshCachedData() throws IOException, ServiceException {

      CellQuery cellQuery = new CellQuery(backingEntry.getCellFeedUrl());
      cellQuery.setReturnEmpty(true);
      this.cellFeed = spreadsheetService.getFeed(cellQuery, CellFeed.class);

      //            A subtlety: Spreadsheets row,col numbers are 1-based whereas the
      //            cellEntries array is 0-based. Rather than wasting an extra row and
      //            column worth of cells in memory, we adjust accesses by subtracting
      //            1 from each row or column number.
      cellEntries = new CellEntry[rows][columns];
      for (CellEntry cellEntry : cellFeed.getEntries()) {
        Cell cell = cellEntry.getCell();
        cellEntries[cell.getRow() - 1][cell.getCol() - 1] = cellEntry;
      }
    }
Esempio n. 9
0
 /**
  * Writes (to stdout) a list of the entries in the batch request in a human readable format.
  *
  * @param batchRequest the CellFeed containing entries to display.
  */
 private void printBatchRequest(CellFeed batchRequest) {
   System.out.println("Current operations in batch");
   for (CellEntry entry : batchRequest.getEntries()) {
     String msg =
         "\tID: "
             + BatchUtils.getBatchId(entry)
             + " - "
             + BatchUtils.getBatchOperationType(entry)
             + " row: "
             + entry.getCell().getRow()
             + " col: "
             + entry.getCell().getCol()
             + " value: "
             + entry.getCell().getInputValue();
     System.out.println(msg);
   }
 }
Esempio n. 10
0
    List<List<Object>> getRowsOfCells(
        SpreadsheetService service,
        WorksheetEntry worksheet,
        int startRow, // 1-based
        int rowCount)
        throws IOException, ServiceException {
      URL cellFeedUrl = worksheet.getCellFeedUrl();

      int minRow = startRow;
      int maxRow = Math.min(worksheet.getRowCount(), startRow + rowCount - 1);
      int cols = worksheet.getColCount();
      int rows = worksheet.getRowCount();

      CellQuery cellQuery = new CellQuery(cellFeedUrl);
      cellQuery.setMinimumRow(minRow);
      cellQuery.setMaximumRow(maxRow);
      cellQuery.setMaximumCol(cols);
      cellQuery.setMaxResults(rows * cols);
      cellQuery.setReturnEmpty(false);

      CellFeed cellFeed = service.query(cellQuery, CellFeed.class);
      List<CellEntry> cellEntries = cellFeed.getEntries();

      List<List<Object>> rowsOfCells = new ArrayList<List<Object>>(rowCount);
      for (CellEntry cellEntry : cellEntries) {
        Cell cell = cellEntry.getCell();
        if (cell != null) {
          int row = cell.getRow() - startRow;
          int col = cell.getCol() - 1;

          while (row >= rowsOfCells.size()) {
            rowsOfCells.add(new ArrayList<Object>());
          }
          List<Object> rowOfCells = rowsOfCells.get(row);

          while (col >= rowOfCells.size()) {
            rowOfCells.add(null);
          }
          rowOfCells.set(col, cell.getValue());
        }
      }
      return rowsOfCells;
    }
Esempio n. 11
0
 /*
  * Returns a cell feed of the row with the given index. If the flag is set to true, then
  * the cell feed will also include empty cells.
  */
 private CellFeed fetchCellFeed(int rowIndex, boolean returnEmpty) throws EolRuntimeException {
   try {
     URL cellFeedUrl =
         new URI(
                 this.worksheetEntry.getCellFeedUrl().toString()
                     + "?min-row="
                     + Integer.toString(rowIndex)
                     + "&max-row="
                     + Integer.toString(rowIndex)
                     + "&return-empty="
                     + Boolean.toString(returnEmpty))
             .toURL();
     CellFeed cellFeed =
         ((GSModel) this.model).spreadsheetService.getFeed(cellFeedUrl, CellFeed.class);
     return (cellFeed.getEntries().size() == 0) ? null : cellFeed;
   } catch (Exception e) {
     throw new EolRuntimeException(e.getMessage());
   }
 }
  @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<ListEntry> entries = new ArrayList<ListEntry>();
    ListEntry entry = mock(ListEntry.class);
    CustomElementCollection customElements = mock(CustomElementCollection.class);
    when(customElements.getTags())
        .thenReturn(new LinkedHashSet<String>(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<CellEntry> cells = new ArrayList<CellEntry>();

    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");
  }
Esempio n. 13
0
  public static void gogogo(
      String username,
      String password,
      int itemsPerBatch,
      String spreadsheetName,
      String worksheetName,
      String data)
      throws Exception {
    System.out.println("# Initializing upload to Google Spreadsheets...");
    System.out.print("# Logging in as: \"" + username + "\"... ");
    ImportClient client = new ImportClient(username, password, itemsPerBatch, spreadsheetName);
    System.out.println("Success!");

    Pattern delim = Pattern.compile(DELIM);
    try {
      int row = 0;
      String[] allLines = data.split("\n");

      int currentCell = 1;
      int allRow = allLines.length;
      System.out.println("# Preparing " + allRow + " rows to be updated... ");

      List<CellEntry> updatedCells = new LinkedList<CellEntry>();
      Worksheet workSheet = client.getWorksheet(spreadsheetName, worksheetName);

      ProgressBar.updateProgress(0, allRow);
      for (String line : allLines) {
        // Break up the line by the delimiter and insert the cells
        String[] cells = delim.split(line, -1);
        for (int col = 0; col < cells.length; col++) {
          // old way - send the change
          //                    client.insertCellEntry(spreadsheet, worksheet, row + 1, col + 1,
          // cells[col]);

          // prepare change
          CellEntry cellEntry = workSheet.getCell(row + 1, col + 1);
          String value = cells[col];
          cellEntry.changeInputValueLocal(value);
          updatedCells.add(cellEntry);
        }
        // Advance the loop
        ProgressBar.updateProgress(++row, allRow);
      }

      // send the batches
      int allBatches = updatedCells.size();
      int currentBatch = 0;

      List<List<CellEntry>> batches = chunkList(updatedCells, ITEMS_PER_BATCH);
      System.out.println("\n\n# Uploading changes in " + batches.size() + " chunks, ");
      System.out.println("# containing a total of " + allBatches + " operations... ");

      for (List<CellEntry> batch : batches) {
        CellFeed batchFeed = new CellFeed();
        for (CellEntry cellEntry : batch) {
          ProgressBar.updateProgress(++currentBatch, allBatches);
          Cell cell = cellEntry.getCell();
          BatchUtils.setBatchId(cellEntry, "R" + cell.getRow() + "C" + cell.getCol());
          BatchUtils.setBatchOperationType(cellEntry, BatchOperationType.UPDATE);
          batchFeed.getEntries().add(cellEntry);
        }

        Link batchLink = workSheet.getBatchUpdateLink();
        CellFeed batchResultFeed = client.service.batch(new URL(batchLink.getHref()), batchFeed);
        // Make sure all the operations were successful.
        for (CellEntry entry : batchResultFeed.getEntries()) {
          if (!BatchUtils.isSuccess(entry)) {
            String batchId = BatchUtils.getBatchId(entry);
            BatchStatus status = BatchUtils.getBatchStatus(entry);
            System.err.println("Failed entry");
            System.err.println("\t" + batchId + " failed (" + status.getReason() + ") ");
            return;
          }
        }
      }

    } catch (Exception e) {
      e.printStackTrace();
    }
  }
  private boolean uploadOneSubmission(
      String id, String instanceFilePath, String jrFormId, String token, String formFilePath) {
    // if the token is null fail immediately
    if (token == null) {
      mResults.put(id, oauth_fail + Collect.getInstance().getString(R.string.invalid_oauth));
      return false;
    }

    HashMap<String, String> answersToUpload = new HashMap<String, String>();
    HashMap<String, String> photosToUpload = new HashMap<String, String>();
    HashMap<String, PhotoEntry> uploadedPhotos = new HashMap<String, PhotoEntry>();

    HttpTransport h = AndroidHttp.newCompatibleTransport();
    GoogleCredential gc = new GoogleCredential();
    gc.setAccessToken(token);

    PicasaClient client = new PicasaClient(h.createRequestFactory(gc));

    // get instance file
    File instanceFile = new File(instanceFilePath);

    // first check to see how many columns we have:
    ArrayList<String> columnNames = new ArrayList<String>();
    try {
      getColumns(formFilePath, columnNames);
    } catch (FileNotFoundException e2) {
      e2.printStackTrace();
      mResults.put(id, e2.getMessage());
      return false;
    } catch (XmlPullParserException e2) {
      e2.printStackTrace();
      mResults.put(id, e2.getMessage());
      return false;
    } catch (IOException e2) {
      e2.printStackTrace();
      mResults.put(id, e2.getMessage());
      return false;
    } catch (FormException e2) {
      e2.printStackTrace();
      mResults.put(id, e2.getMessage());
      return false;
    }

    if (columnNames.size() > 255) {
      mResults.put(
          id, Collect.getInstance().getString(R.string.sheets_max_columns, columnNames.size()));
      return false;
    }

    // make sure column names are legal
    for (String n : columnNames) {
      if (!isValidGoogleSheetsString(n)) {
        mResults.put(
            id, Collect.getInstance().getString(R.string.google_sheets_invalid_column_form, n));
        return false;
      }
    }

    // parses the instance file and populates the answers and photos
    // hashmaps.
    try {
      processInstanceXML(instanceFile, answersToUpload, photosToUpload);
    } catch (XmlPullParserException e) {
      e.printStackTrace();
      mResults.put(id, form_fail + e.getMessage());
      return false;
    } catch (FormException e) {
      mResults.put(id, form_fail + Collect.getInstance().getString(R.string.google_repeat_error));
      return false;
    } catch (FileNotFoundException e) {
      e.printStackTrace();
      mResults.put(id, form_fail + e.getMessage());
      return false;
    } catch (IOException e) {
      e.printStackTrace();
      mResults.put(id, form_fail + e.getMessage());
      return false;
    }

    try {
      Thread.sleep(GOOGLE_SLEEP_TIME);
    } catch (InterruptedException e3) {
      e3.printStackTrace();
    }

    // make sure column names in submission are legal (may be different than form)
    for (String n : answersToUpload.keySet()) {
      if (!isValidGoogleSheetsString(n)) {
        mResults.put(
            id, Collect.getInstance().getString(R.string.google_sheets_invalid_column_instance, n));
        return false;
      }
    }

    // if we have any photos to upload,
    // get the picasa album or create a new one
    // then upload the photos
    if (photosToUpload.size() > 0) {
      // First set up a picasa album to upload to:
      // maybe we should move this, because if we don't have any
      // photos we don't care...
      AlbumEntry albumToUse;
      try {
        albumToUse = getOrCreatePicasaAlbum(client, jrFormId);
      } catch (IOException e) {
        e.printStackTrace();
        GoogleAuthUtil.invalidateToken(Collect.getInstance(), token);
        mResults.put(id, picasa_fail + e.getMessage());
        return false;
      }

      try {
        uploadPhotosToPicasa(photosToUpload, uploadedPhotos, client, albumToUse, instanceFile);
      } catch (IOException e1) {
        e1.printStackTrace();
        mResults.put(id, picasa_fail + e1.getMessage());
        return false;
      }
    }

    // All photos have been sent to picasa (if there were any)
    // now upload data to Google Sheet

    String selection = InstanceColumns._ID + "=?";
    String[] selectionArgs = {id};

    Cursor cursor = null;
    String urlString = null;
    try {
      // see if the submission element was defined in the form
      cursor =
          Collect.getInstance()
              .getContentResolver()
              .query(InstanceColumns.CONTENT_URI, null, selection, selectionArgs, null);

      if (cursor.getCount() > 0) {
        cursor.moveToPosition(-1);
        while (cursor.moveToNext()) {
          int subIdx = cursor.getColumnIndex(InstanceColumns.SUBMISSION_URI);
          urlString = cursor.isNull(subIdx) ? null : cursor.getString(subIdx);

          // if we didn't find one in the content provider,
          // try to get from settings
          if (urlString == null) {
            SharedPreferences settings =
                PreferenceManager.getDefaultSharedPreferences(Collect.getInstance());
            urlString =
                settings.getString(
                    PreferencesActivity.KEY_GOOGLE_SHEETS_URL,
                    Collect.getInstance().getString(R.string.default_google_sheets_url));
          }
        }
      }
    } finally {
      if (cursor != null) {
        cursor.close();
      }
    }

    // now parse the url string if we have one
    final String googleHeader = "docs.google.com/spreadsheets/d/";
    String sheetId;
    if (urlString == null || urlString.length() < googleHeader.length()) {
      mResults.put(
          id, form_fail + Collect.getInstance().getString(R.string.invalid_sheet_id, urlString));
      return false;
    } else {
      int start = urlString.indexOf(googleHeader) + googleHeader.length();
      int end = urlString.indexOf("/", start);
      if (end == -1) {
        // if there wasn't a "/", just try to get the end
        end = urlString.length();
      }
      if (start == -1 || end == -1) {
        mResults.put(
            id, form_fail + Collect.getInstance().getString(R.string.invalid_sheet_id, urlString));
        return false;
      }
      sheetId = urlString.substring(start, end);
    }

    SpreadsheetService service = new SpreadsheetService("ODK-Collect");
    service.setAuthSubToken(token);

    // Define the URL to request.
    URL spreadsheetFeedURL = null;
    try {
      spreadsheetFeedURL =
          new URL("https://spreadsheets.google.com/feeds/worksheets/" + sheetId + "/private/full");
    } catch (MalformedURLException e) {
      e.printStackTrace();
      mResults.put(id, form_fail + e.getMessage());
      return false;
    }

    WorksheetQuery query = new WorksheetQuery(spreadsheetFeedURL);
    WorksheetFeed feed = null;
    try {
      feed = service.query(query, WorksheetFeed.class);
    } catch (IOException e) {
      e.printStackTrace();
      mResults.put(id, form_fail + e.getMessage());
      return false;
    } catch (ServiceException e) {
      e.printStackTrace();
      if (e.getLocalizedMessage().equalsIgnoreCase("forbidden")) {
        mResults.put(
            id, form_fail + Collect.getInstance().getString(R.string.google_sheets_access_denied));
      } else {
        mResults.put(id, form_fail + Html.fromHtml(e.getResponseBody()));
      }
      return false;
    }

    List<WorksheetEntry> spreadsheets = feed.getEntries();
    // get the first worksheet
    WorksheetEntry we = spreadsheets.get(0);

    // check the headers....
    URL headerFeedUrl = null;
    try {
      headerFeedUrl =
          new URI(
                  we.getCellFeedUrl().toString()
                      + "?min-row=1&max-row=1&min-col=1&max-col="
                      + we.getColCount()
                      + "&return-empty=true")
              .toURL();
    } catch (MalformedURLException e1) {
      e1.printStackTrace();
      mResults.put(id, form_fail + e1.getMessage());
      return false;
    } catch (URISyntaxException e1) {
      e1.printStackTrace();
      mResults.put(id, form_fail + e1.getMessage());
      return false;
    }

    CellFeed headerFeed = null;
    try {
      headerFeed = service.getFeed(headerFeedUrl, CellFeed.class);
    } catch (IOException e) {
      e.printStackTrace();
      mResults.put(id, form_fail + e.getMessage());
      return false;
    } catch (ServiceException e) {
      e.printStackTrace();
      mResults.put(id, form_fail + e.getMessage());
      return false;
    }

    boolean emptyheaders = true;

    // go through headers
    // if they're empty, resize and add
    for (CellEntry c : headerFeed.getEntries()) {
      if (c.getCell().getValue() != null) {
        emptyheaders = false;
        break;
      }
    }

    if (emptyheaders) {
      // if the headers were empty, resize the spreadsheet
      // and add the headers
      we.setColCount(columnNames.size());
      try {
        we.update();
      } catch (IOException e2) {
        e2.printStackTrace();
        mResults.put(id, form_fail + e2.getMessage());
        return false;
      } catch (ServiceException e2) {
        e2.printStackTrace();
        mResults.put(id, form_fail + e2.getMessage());
        return false;
      } catch (UnsupportedOperationException e) {
        e.printStackTrace();
        mResults.put(
            id, form_fail + Collect.getInstance().getString(R.string.google_sheets_update_error));
        return false;
      }

      // get the cell feed url
      URL cellFeedUrl = null;
      try {
        cellFeedUrl =
            new URI(
                    we.getCellFeedUrl().toString()
                        + "?min-row=1&max-row=1&min-col=1&max-col="
                        + columnNames.size()
                        + "&return-empty=true")
                .toURL();
      } catch (MalformedURLException e1) {
        e1.printStackTrace();
        mResults.put(id, form_fail + e1.getMessage());
        return false;
      } catch (URISyntaxException e1) {
        e1.printStackTrace();
        mResults.put(id, form_fail + e1.getMessage());
        return false;
      }

      // and the cell feed
      CellFeed cellFeed = null;
      try {
        cellFeed = service.getFeed(cellFeedUrl, CellFeed.class);
      } catch (IOException e) {
        e.printStackTrace();
        mResults.put(id, form_fail + e.getMessage());
        return false;
      } catch (ServiceException e) {
        e.printStackTrace();
        mResults.put(id, form_fail + e.getMessage());
        return false;
      }

      // write the headers
      for (int i = 0; i < cellFeed.getEntries().size(); i++) {
        CellEntry cell = cellFeed.getEntries().get(i);
        String column = columnNames.get(i);
        cell.changeInputValueLocal(column);
        try {
          cell.update();
        } catch (IOException e) {
          e.printStackTrace();
          mResults.put(id, form_fail + e.getMessage());
          return false;
        } catch (ServiceException e) {
          e.printStackTrace();
          mResults.put(id, form_fail + e.getMessage());
          return false;
        }
      }
    }

    // we may have updated the feed, so get a new one
    // update the feed
    try {
      headerFeedUrl =
          new URI(
                  we.getCellFeedUrl().toString()
                      + "?min-row=1&max-row=1&min-col=1&max-col="
                      + we.getColCount()
                      + "&return-empty=true")
              .toURL();
    } catch (MalformedURLException e3) {
      e3.printStackTrace();
      mResults.put(id, form_fail + e3.getMessage());
      return false;
    } catch (URISyntaxException e3) {
      e3.printStackTrace();
      mResults.put(id, form_fail + e3.getMessage());
      return false;
    }
    try {
      headerFeed = service.getFeed(headerFeedUrl, CellFeed.class);
    } catch (IOException e2) {
      e2.printStackTrace();
      mResults.put(id, form_fail + e2.getMessage());
      return false;
    } catch (ServiceException e2) {
      e2.printStackTrace();
      mResults.put(id, form_fail + e2.getMessage());
      return false;
    }

    // see if our columns match, now
    URL cellFeedUrl = null;
    try {
      cellFeedUrl =
          new URI(
                  we.getCellFeedUrl().toString()
                      + "?min-row=1&max-row=1&min-col=1&max-col="
                      + headerFeed.getEntries().size()
                      + "&return-empty=true")
              .toURL();
    } catch (MalformedURLException e1) {
      e1.printStackTrace();
      mResults.put(id, form_fail + e1.getMessage());
      return false;
    } catch (URISyntaxException e1) {
      e1.printStackTrace();
      mResults.put(id, form_fail + e1.getMessage());
      return false;
    }
    CellFeed cellFeed = null;
    try {
      cellFeed = service.getFeed(cellFeedUrl, CellFeed.class);
    } catch (IOException e) {
      e.printStackTrace();
      mResults.put(id, form_fail + e.getMessage());
      return false;
    } catch (ServiceException e) {
      e.printStackTrace();
      mResults.put(id, form_fail + e.getMessage());
      return false;
    }

    // first, get all the columns in the spreadsheet
    ArrayList<String> sheetCols = new ArrayList<String>();
    for (int i = 0; i < cellFeed.getEntries().size(); i++) {
      CellEntry cell = cellFeed.getEntries().get(i);
      sheetCols.add(cell.getPlainTextContent());
    }

    ArrayList<String> missingColumns = new ArrayList<String>();
    for (String col : columnNames) {
      if (!sheetCols.contains(col)) {
        missingColumns.add(col);
      }
    }

    if (missingColumns.size() > 0) {
      // we had some missing columns, so error out
      String missingString = "";
      for (int i = 0; i < missingColumns.size(); i++) {
        missingString += missingColumns.get(i);
        if (i < missingColumns.size() - 1) {
          missingString += ", ";
        }
      }
      mResults.put(
          id,
          form_fail
              + Collect.getInstance()
                  .getString(R.string.google_sheets_missing_columns, missingString));
      return false;
    }

    // if we get here.. all has matched
    // so write the values
    ListEntry row = new ListEntry();

    // add photos to answer set
    Iterator<String> photoIterator = uploadedPhotos.keySet().iterator();
    while (photoIterator.hasNext()) {
      String key = photoIterator.next();
      String url = uploadedPhotos.get(key).getImageLink();
      answersToUpload.put(key, url);
    }

    Iterator<String> answerIterator = answersToUpload.keySet().iterator();
    while (answerIterator.hasNext()) {
      String path = answerIterator.next();
      String answer = answersToUpload.get(path);
      // Check to see if answer is a location, if so, get rid of accuracy
      // and altitude
      // try to match a fairly specific pattern to determine
      // if it's a location
      // [-]#.# [-]#.# #.# #.#
      Pattern p =
          Pattern.compile(
              "^-?[0-9]+\\.[0-9]+\\s-?[0-9]+\\.[0-9]+\\s-?[0-9]+\\" + ".[0-9]+\\s[0-9]+\\.[0-9]+$");
      Matcher m = p.matcher(answer);
      if (m.matches()) {
        // get rid of everything after the second space
        int firstSpace = answer.indexOf(" ");
        int secondSpace = answer.indexOf(" ", firstSpace + 1);
        answer = answer.substring(0, secondSpace);
        answer = answer.replace(' ', ',');
      }
      row.getCustomElements().setValueLocal(TextUtils.htmlEncode(path), answer);
    }

    // Send the new row to the API for insertion.
    try {
      URL listFeedUrl = we.getListFeedUrl();
      row = service.insert(listFeedUrl, row);
    } catch (IOException e) {
      e.printStackTrace();
      mResults.put(id, form_fail + e.getMessage());
      return false;
    } catch (ServiceException e) {
      e.printStackTrace();
      if (e.getLocalizedMessage().equalsIgnoreCase("Forbidden")) {
        mResults.put(
            id, form_fail + Collect.getInstance().getString(R.string.google_sheets_access_denied));
      } else {
        mResults.put(id, form_fail + Html.fromHtml(e.getResponseBody()));
      }
      return false;
    }

    mResults.put(id, Collect.getInstance().getString(R.string.success));
    return true;
  }
  public void writeBatchRows(
      WorksheetEntry worksheetEntry, List<Map<Integer, Object>> rowList, int rowOffset)
      throws IOException, ServiceException {

    long startTime = System.currentTimeMillis();

    URL cellFeedUrl = worksheetEntry.getCellFeedUrl();
    CellFeed cellFeed = spreadsheetService.getFeed(worksheetEntry.getCellFeedUrl(), CellFeed.class);
    CellFeed batchRequest = new CellFeed();
    logger.info("Get Row Count:  " + cellFeed.getRowCount());
    int rowToBegin = rowOffset;
    addEmptyRows(worksheetEntry, rowList.size());

    logger.info("Row To Begin:  " + rowToBegin);

    // Build list of cell addresses to be filled in
    List<CellAddress> cellAddrs = new ArrayList<CellAddress>();
    CellAddress cellAddress = new CellAddress();
    String formula;
    for (Map<Integer, Object> row : rowList) {

      for (Map.Entry<Integer, Object> entry : row.entrySet()) {
        int column = entry.getKey();
        if (!(entry.getValue() instanceof String)) {
          formula = entry.getValue().toString();
        } else {
          formula = (String) entry.getValue();
        }
        logger.info("********************Column: " + column + "Formula: " + formula);

        cellAddress.setCol(column);
        cellAddress.setRow(rowToBegin);
        cellAddress.setIdString(String.format("R%sC%s", rowToBegin, column));
        cellAddrs.add(cellAddress);
        for (CellAddress cellAddr : cellAddrs) {
          CellEntry batchEntry = new CellEntry(cellAddr.row, cellAddr.col, formula);
          batchEntry.setId(String.format("%s/%s", cellFeedUrl.toString(), cellAddr.idString));
          BatchUtils.setBatchId(batchEntry, cellAddr.idString);
          BatchUtils.setBatchOperationType(batchEntry, BatchOperationType.UPDATE);

          logger.fine("Batch Entry: " + batchEntry);

          batchRequest.getEntries().add(batchEntry);
          batchEntry = null;
        }
        cellAddrs.clear();
      }
      // batch per row
      if (rowToBegin % 100 == 0) {
        long startBatchTime = System.currentTimeMillis();

        logger.info("\n\n\nEvery 100 rows batch call: " + rowToBegin);
        performBatchUpdate(batchRequest, cellFeedUrl);
        batchRequest.getEntries().clear();
        logger.info("\n\n ms elapsed for batch:  " + (System.currentTimeMillis() - startBatchTime));
      }

      rowToBegin++;
    }

    logger.info(
        "\n\n\n\nms elapsed to create batch request: " + (System.currentTimeMillis() - startTime));
    // for the stragglers
    logger.info("\n\n\nLast rows batch call: " + rowToBegin);
    performBatchUpdate(batchRequest, cellFeedUrl);
  }
Esempio n. 16
0
  /*
   * Loads the column ids from Google.
   */
  protected void getGoogleColumnIds() throws EolModelLoadingException {
    try {
      System.out.print("Loading Google ids for '" + this.getName() + "'... ");

      // Ensure the worksheet exists in the spreadsheet
      if (!this.existsInSpreadsheet) {
        throw new RuntimeException(this.nonexistentWorksheetMessage());
      }

      // Reset the flag early to avoid cycles
      this.hasGoogleIdsSet = true;

      // Position of the last header column
      int maxColIndex = this.getHeader().last().getIndex();

      // Determine the first blank row
      int rowIndex = this.getRows().size() + 2;

      // If the worksheet dimensions are smaller than the first blank row, then expand the worksheet
      if (this.worksheetEntry.getRowCount() < rowIndex) {
        this.worksheetEntry.setRowCount(rowIndex);
        this.worksheetEntry = this.worksheetEntry.update();
      }

      // Fill the first spare row with temporary values
      URL cellFeedUrl =
          new URI(
                  this.worksheetEntry.getCellFeedUrl().toString()
                      + "?min-row="
                      + Integer.toString(rowIndex)
                      + "&max-row="
                      + Integer.toString(rowIndex)
                      + "&min-col=1"
                      + "&max-col="
                      + Integer.toString(maxColIndex)
                      + "&return-empty=true")
              .toURL();
      CellFeed cellFeed =
          ((GSModel) this.model).spreadsheetService.getFeed(cellFeedUrl, CellFeed.class);
      for (CellEntry cell : cellFeed.getEntries()) {
        cell.changeInputValueLocal("TEMP");
        cell.update();
      }

      GSRow row = null;

      // Get the row from the worksheet to populate the Google Unique Id set
      URL listFeedUrl =
          new URL(
              this.worksheetEntry.getListFeedUrl().toString()
                  + "?start-index="
                  + Integer.toString(rowIndex - 1)
                  + "&max-results=1");
      ListFeed lf = ((GSModel) model).spreadsheetService.getFeed(listFeedUrl, ListFeed.class);
      List<ListEntry> list = lf.getEntries();
      if (list.size() == 1) {
        ListEntry le = list.get(0);
        int colIndex = 1;
        // Iterate over all returned tags - Google preserves column ordering in the set
        Iterator<String> it = le.getCustomElements().getTags().iterator();
        while (it.hasNext()) {
          GSColumn c = (GSColumn) this.getColumn(colIndex);

          // If the column is known, then associate the id with it
          if (c != null) {
            c.setGoogleColumnId(it.next());
          } else {
            it.next();
          }

          // Stop once the final known column is reached
          if (colIndex == maxColIndex) break;

          colIndex++;
        }
        row = new GSRow(this, le);
      } else {
        throw new Exception(
            "List feed failed to return the row of temporary values for learning the Unique Column Ids.");
      }

      // Delete the temporary row
      this.removeRow(row);
    } catch (Exception e) {
      throw new EolModelLoadingException(e, this.model);
    }
    System.out.println("<< done");
    System.out.println(this);
  }