public com.google.gdata.data.docs.SpreadsheetEntry createSpreadsheet(
      String _title,
      DocumentListEntry _destinationFolderEntry,
      String _defaultWorksheetName,
      int _rows,
      int _columns)
      throws IOException, ServiceException {
    com.google.gdata.data.docs.SpreadsheetEntry newEntry =
        new com.google.gdata.data.docs.SpreadsheetEntry();
    newEntry.setTitle(new PlainTextConstruct(_title));

    String destFolder = ((MediaContent) _destinationFolderEntry.getContent()).getUri();
    URL destinationURL = new URL(destFolder);

    com.google.gdata.data.docs.SpreadsheetEntry newSpreadsheetEntry =
        docsService.insert(destinationURL, newEntry);
    // convert from Docs API Spreadsheet to Spreadsheet API Spreadsheet
    WorksheetEntry worksheet =
        getSpreadsheetEntryFromDocumentListEntry(newSpreadsheetEntry).getDefaultWorksheet();
    worksheet.setTitle(new PlainTextConstruct(_defaultWorksheetName));
    worksheet.setRowCount(_rows);
    worksheet.setColCount(_columns);
    worksheet.update();

    return newSpreadsheetEntry;
  }
Example #2
0
 /**
  * Sets this worksheets's row count.
  *
  * @param newRowCount
  * @throws com.google.gdata.util.ServiceException
  */
 void setRowCount(int newRowCount) throws IOException, ServiceException {
   rows = newRowCount;
   backingEntry.setRowCount(newRowCount);
   backingEntry = backingEntry.update();
   refreshCachedData();
 }
  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 addEmptyRows(WorksheetEntry worksheetEntry, int newRows)
     throws IOException, ServiceException {
   int rows = worksheetEntry.getRowCount();
   worksheetEntry.setRowCount(rows + newRows);
   worksheetEntry.update();
 }