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

public GSpreadConfig(DataService dataService, String configId, Map<String, String> properties, boolean odataEnable)
           throws DataServiceFault {
	super(dataService, configId, DataSourceTypes.GDATA_SPREADSHEET, properties, odataEnable);

       this.key = extractKey(this.getProperty(GSpread.DATASOURCE));

       String clientId = DBUtils.resolvePasswordValue(this.getDataService(), this.getProperty(GSpread.CLIENT_ID));
       String clientSecret = DBUtils.resolvePasswordValue(this.getDataService(), this.getProperty(GSpread.CLIENT_SECRET));
       String refreshToken = DBUtils.resolvePasswordValue(this.getDataService(), this.getProperty(GSpread.REFRESH_TOKEN));
       String visibility = this.getProperty(GSpread.VISIBILITY);

       try {
           this.feedProcessor = new GSpreadFeedProcessor(clientId, clientSecret, refreshToken,
                                                         visibility, BASE_REGISTRY_AUTH_TOKEN_PATH);
       } catch (SQLException e) {
           throw new DataServiceFault(e, "Error initialising GSpread feed Processor, " + e.getMessage());
       }
	if (!dataService.isServiceInactive()) {
           this.feedProcessor.setService(new SpreadsheetService(this.getDataService().getName() +
                                                                ":" + this.getConfigId()));
	}
}
 

public TGSpreadConnection(Properties props) throws SQLException {
    super(props);
    this.spreadSheetName = props.getProperty(Constants.DRIVER_PROPERTIES.SHEET_NAME);

    String visibility = props.getProperty(Constants.DRIVER_PROPERTIES.VISIBILITY);
    visibility = (visibility != null) ? visibility : Constants.ACCESS_MODE_PRIVATE;
    String clientId = props.getProperty(Constants.GSPREAD_PROPERTIES.CLIENT_ID);
    String clientSecret = props.getProperty(Constants.GSPREAD_PROPERTIES.CLIENT_SECRET);
    String refreshToken = props.getProperty(Constants.GSPREAD_PROPERTIES.REFRESH_TOKEN);


    feedProcessor = new GSpreadFeedProcessor(clientId, clientSecret, refreshToken, visibility,
                                             Constants.SPREADSHEET_FEED_BASE_URL);
    if (feedProcessor.requiresAuth()) {
        try {
            this.feedProcessor.setClientId(URLDecoder.decode(this.feedProcessor.getClientId(), "UTF-8"));
            this.feedProcessor.setClientSecret(URLDecoder.decode(this.feedProcessor.getClientSecret(), "UTF-8"));
            this.feedProcessor.setRefreshToken(URLDecoder.decode(this.feedProcessor.getRefreshToken(), "UTF-8"));
        } catch (UnsupportedEncodingException e) {
            throw new SQLException("Error in retrieving Authentication information " + e.getMessage(), e);
        }
    }
    if (spreadSheetName == null) {
        throw new SQLException("Spread Sheet name is not provided");
    }

    SpreadsheetService service = new SpreadsheetService(Constants.SPREADSHEET_SERVICE_NAME);
    service.setCookieManager(null);
    this.feedProcessor.setService(service);

    this.spreadSheetFeed = this.extractSpreadSheetFeed();
    this.worksheetFeed = this.extractWorkSheetFeed();
}
 

private void initSpreadsheetService() throws Throwable, IOException
{
    HTTP_TRANSPORT = GoogleNetHttpTransport.newTrustedTransport();
    spreadsheetService = new SpreadsheetService(Properties.googleAppName.get());
    GoogleCredential googleCredential = buildGoogleCredential();
    spreadsheetService.setOAuth2Credentials(googleCredential);
    spreadsheetsFeedUrl = FEED_URL_FACTORY.getSpreadsheetsFeedUrl();
}
 

/**
 * Gets the worksheet url.
 * 
 * @param spreadsheetService the spreadsheet service
 * @param spreadsheetId the spreadsheet id
 */
private URL getWorksheetUrl(SpreadsheetService spreadsheetService, String spreadsheetId)
    throws IOException, ServiceException {
  if (isCancelled()) {
    return null;
  }
  URL url = new URL(String.format(Locale.US, GET_WORKSHEETS_URI, spreadsheetId));
  WorksheetFeed feed = spreadsheetService.getFeed(url, WorksheetFeed.class);
  List<WorksheetEntry> worksheets = feed.getEntries();

  if (worksheets.size() > 0) {
    return worksheets.get(0).getListFeedUrl();
  }
  return null;
}
 

private static void addNewRecord(String reportDataFileId, String reportName, String user, String date, String email, String folderId, String folderName) throws MalformedURLException, GeneralSecurityException, IOException, ServiceException {
    URL SPREADSHEET_FEED_URL = new URL("https://spreadsheets.google.com/feeds/spreadsheets/private/full/" + REPORTS_INFO_SHEET_ID);

    SpreadsheetService service = GoogleAuth.getSpreadSheetService();
    SpreadsheetEntry spreadSheetEntry = service.getEntry(SPREADSHEET_FEED_URL, SpreadsheetEntry.class);
    
    WorksheetFeed worksheetFeed = service.getFeed(
    spreadSheetEntry.getWorksheetFeedUrl(), WorksheetFeed.class);
    List<WorksheetEntry> worksheets = worksheetFeed.getEntries();
    WorksheetEntry worksheet = worksheets.get(0);
    
    // Fetch the list feed of the worksheet.
    URL listFeedUrl = worksheet.getListFeedUrl();
    
    // Create a local representation of the new row.
    ListEntry row = new ListEntry();
    row.getCustomElements().setValueLocal("reportdatafileid", reportDataFileId);
    row.getCustomElements().setValueLocal("reportname", reportName);
    row.getCustomElements().setValueLocal("user", user);
    row.getCustomElements().setValueLocal("requestdate", date);
    row.getCustomElements().setValueLocal("email", email);
    row.getCustomElements().setValueLocal("sendreminder", "No");
    if(folderId != null) {
        row.getCustomElements().setValueLocal("folderid", folderId);
    }
    if(folderName != null) {
        row.getCustomElements().setValueLocal("foldername", folderName);
    }

    // Send the new row to the API for insertion.
    service.insert(listFeedUrl, row);
}
 

public static SpreadsheetService getSpreadSheetService() throws GeneralSecurityException, IOException, ServiceException {
    if (SPREADSHEET_SERVICE != null) {
        return SPREADSHEET_SERVICE;
    }

    createGoogleCredential();
    createSpreadSheetService();

    return SPREADSHEET_SERVICE;
}
 

private static void createSpreadSheetService() throws GeneralSecurityException, IOException, ServiceException {
    HttpTransport httpTransport = new NetHttpTransport();
    JacksonFactory jsonFactory = new JacksonFactory();

    SPREADSHEET_SERVICE = new SpreadsheetService("data");
    SPREADSHEET_SERVICE.setOAuth2Credentials(CREDENTIAL);
}
 

@Override
public boolean init(StepMetaInterface smi, StepDataInterface sdi) {
    meta = (GoogleSpreadsheetOutputMeta) smi;
    data = (GoogleSpreadsheetOutputData) sdi;

    if (super.init(smi, sdi)) {
        try {
            data.accessToken = GoogleSpreadsheet.getAccessToken(meta.getServiceEmail(), meta.getPrivateKeyStore());
            if (data.accessToken == null) {
                logError("Unable to get access token.");
                setErrors(1L);
                stopAll();
                return false;
            }
            data.service = new SpreadsheetService("PentahoKettleTransformStep-v1");
            data.service.setHeader("Authorization", "Bearer " + data.accessToken);

            data.spreadsheetKey = meta.getSpreadsheetKey();
            data.worksheetId = meta.getWorksheetId();

            data.worksheetFeedURL = data.urlFactory.getWorksheetFeedUrl(data.spreadsheetKey, "private", "full");
            data.worksheetFeed = data.service.getFeed(data.worksheetFeedURL, WorksheetFeed.class);

            data.cellFeedURL = data.urlFactory.getCellFeedUrl(data.spreadsheetKey, data.worksheetId, "private",
                    "full");
            data.cellFeed = data.service.getFeed(data.cellFeedURL, CellFeed.class);
            data.cellBatchURL = new URL(data.cellFeed.getLink(Link.Rel.FEED_BATCH, Link.Type.ATOM).getHref());

            data.spreadsheet = new Spreadsheet();
        } catch (Exception e) {
            logError("Error: " + e.getMessage(), e);
            setErrors(1L);
            stopAll();
            return false;
        }

        return true;
    }
    return false;
}
 

@Override
public boolean init(StepMetaInterface smi, StepDataInterface sdi) {
    meta = (GoogleSpreadsheetInputMeta) smi;
    data = (GoogleSpreadsheetInputData) sdi;

    if (super.init(smi, sdi)) {
        try {
            data.accessToken = GoogleSpreadsheet.getAccessToken(meta.getServiceEmail(), meta.getPrivateKeyStore());
            if (data.accessToken == null) {
                logError("Unable to get access token.");
                setErrors(1L);
                stopAll();
                return false;
            }
            data.service = new SpreadsheetService("PentahoKettleTransformStep-v1");
            data.service.setHeader("Authorization", "Bearer " + data.accessToken);

            URL listFeedURL = FeedURLFactory.getDefault().getListFeedUrl(meta.getSpreadsheetKey(), meta.getWorksheetId(), "private", "full");
            ListFeed listFeed = data.service.getFeed(listFeedURL, ListFeed.class);
            data.rows = listFeed.getEntries();
        } catch (Exception e) {
            logError("Error: " + e.getMessage(), e);
            setErrors(1L);
            stopAll();
            return false;
        }

        return true;
    }
    return false;
}
 

private void getSpreadsheetFields() {
    try {
        GoogleSpreadsheetInputMeta meta = new GoogleSpreadsheetInputMeta();
        setData(meta);

        wFields.table.removeAll();

        String accessToken = GoogleSpreadsheet.getAccessToken(meta.getServiceEmail(), meta.getPrivateKeyStore());
        if (accessToken == null || accessToken.equals("")) {
            throw new Exception("Unable to get access token.");
        }

        SpreadsheetService service = new SpreadsheetService("PentahoKettleTransformStep-v1");
        service.setHeader("Authorization", String.format("Bearer %s", accessToken));

        Query feedQuery = new Query(FeedURLFactory.getDefault().getListFeedUrl(meta.getSpreadsheetKey(), meta.getWorksheetId(), "private", "full"));
        feedQuery.setMaxResults(1);
        ListFeed feed = service.getFeed(feedQuery, ListFeed.class);
        List<ListEntry> rows = feed.getEntries();
        ListEntry row = rows.get(0);

        for (String tag : row.getCustomElements().getTags()) {
            TableItem item = new TableItem(wFields.table, SWT.NONE);
            item.setText(1, Const.trim(tag));
            item.setText(2, ValueMeta.getTypeDesc(ValueMetaInterface.TYPE_STRING));
        }
        wFields.removeEmptyRows();
        wFields.setRowNums();
        wFields.optWidth(true);
    } catch (Exception e) {
        new ErrorDialog(shell, BaseMessages.getString(PKG, "System.Dialog.Error.Title"), "Error getting Fields", e);
    }
}
 

public void setService(SpreadsheetService service) {
    this.service = service;
}
 

public SpreadsheetService getService() {
    return service;
}
 

/**
 * Adds track info to a worksheet.
 * 
 * @param spreadsheetService the spreadsheet service
 * @param worksheetUrl the worksheet url
 * @param track the track
 * @return true if completes.
 */
private boolean addTrackInfo(SpreadsheetService spreadsheetService, URL worksheetUrl, Track track)
    throws IOException, ServiceException {
  if (isCancelled()) {
    return false;
  }
  TripStatistics tripStatistics = track.getTripStatistics();
  boolean metricUnits = PreferencesUtils.isMetricUnits(context);
  String distanceUnit = context.getString(
      metricUnits ? R.string.unit_kilometer : R.string.unit_mile);
  String speedUnit = context.getString(
      metricUnits ? R.string.unit_kilometer_per_hour : R.string.unit_mile_per_hour);
  String elevationUnit = context.getString(
      metricUnits ? R.string.unit_meter : R.string.unit_feet);
  ListEntry row = new ListEntry();

  row.getCustomElements().setValueLocal("name", track.getName());
  row.getCustomElements().setValueLocal("description", track.getDescription());
  row.getCustomElements()
      .setValueLocal("date", StringUtils.formatDateTime(context, tripStatistics.getStartTime()));
  row.getCustomElements().setValueLocal(
      "totaltime", StringUtils.formatElapsedTimeWithHour(tripStatistics.getTotalTime()));
  row.getCustomElements().setValueLocal(
      "movingtime", StringUtils.formatElapsedTimeWithHour(tripStatistics.getMovingTime()));
  row.getCustomElements().setValueLocal(
      "distance", SendSpreadsheetsUtils.getDistance(tripStatistics.getTotalDistance(), metricUnits));
  row.getCustomElements().setValueLocal("distanceunit", distanceUnit);
  row.getCustomElements().setValueLocal(
      "averagespeed", SendSpreadsheetsUtils.getSpeed(tripStatistics.getAverageSpeed(), metricUnits));
  row.getCustomElements().setValueLocal("averagemovingspeed",
      SendSpreadsheetsUtils.getSpeed(tripStatistics.getAverageMovingSpeed(), metricUnits));
  row.getCustomElements().setValueLocal(
      "maxspeed", SendSpreadsheetsUtils.getSpeed(tripStatistics.getMaxSpeed(), metricUnits));
  row.getCustomElements().setValueLocal("speedunit", speedUnit);
  row.getCustomElements().setValueLocal("elevationgain",
      SendSpreadsheetsUtils.getElevation(tripStatistics.getTotalElevationGain(), metricUnits));
  row.getCustomElements().setValueLocal(
      "minelevation", SendSpreadsheetsUtils.getElevation(tripStatistics.getMinElevation(), metricUnits));
  row.getCustomElements().setValueLocal(
      "maxelevation", SendSpreadsheetsUtils.getElevation(tripStatistics.getMaxElevation(), metricUnits));
  row.getCustomElements().setValueLocal("elevationunit", elevationUnit);
  
  ListEntry result = spreadsheetService.insert(worksheetUrl, row);
  return result != null;
}
 

/**
 * 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
    List<File> files = 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<WorksheetEntry> 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();
        Iterator<ListEntry> listEntryIterator = 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;
}
 

private static void changeFileContent(String fileId, String fileName, JSONObject content, JSONArray records) throws MalformedURLException, GeneralSecurityException, IOException, ServiceException, JSONException {
    URL SPREADSHEET_FEED_URL = new URL("https://spreadsheets.google.com/feeds/spreadsheets/private/full/" + fileId);

    SpreadsheetService service = GoogleAuth.getSpreadSheetService();
    SpreadsheetEntry spreadSheetEntry = service.getEntry(SPREADSHEET_FEED_URL, SpreadsheetEntry.class);

    WorksheetFeed worksheetFeed = service.getFeed(
    spreadSheetEntry.getWorksheetFeedUrl(), WorksheetFeed.class);
    List<WorksheetEntry> worksheets = worksheetFeed.getEntries();
    WorksheetEntry worksheet = worksheets.get(0);

    // Fetch the list feed of the worksheet.
    URL listFeedUrl = worksheet.getListFeedUrl();
    
    System.out.println("Successfully get all entries. Start to add record");
    
    for(int i = 0 ; i < records.length() ; i++) {
        ListEntry row = new ListEntry();
        JSONObject record = records.getJSONObject(i);
        Iterator<String> keys = content.keys();
        while(keys.hasNext()){
            String key = keys.next();
            if(!content.get(key).toString().equals("")) {
                row.getCustomElements().setValueLocal(key, content.get(key).toString());
            }
        }
        Iterator<String> recordKeys = record.keys();
        while(recordKeys.hasNext()){
            String recordKey = recordKeys.next();
            if(!record.get(recordKey).toString().equals("")) {
                row.getCustomElements().setValueLocal(recordKey, record.get(recordKey).toString());
            }else{
                System.out.println(recordKey);
                System.out.println(content);
            }
        }
        System.out.println("Added one record.");
        service.insert(listFeedUrl, row);
    }
    System.out.println("Done.");
}