com.google.gdata.data.spreadsheet.WorksheetFeed Java Examples

private WorksheetFeed extractWorkSheetFeed() throws SQLException {
    if (this.getSpreadSheetFeed() == null) {
        throw new SQLException("Spread Sheet Feed is null");
    }
    List<SpreadsheetEntry> entries = this.getSpreadSheetFeed().getEntries();
    /* If no SpreadSheetEntry is available in the spreadsheet feed inferred using a
     * SpreadSheetQuery, try getting it directly via a SpreadSheetFeed retrieved via the 
     * SpreadSheetService */
    SpreadsheetEntry spreadsheetEntry =
            (entries != null && entries.size() > 0) ? entries.get(0) :
                    this.extractSpreadSheetEntryFromUrl();
    if (spreadsheetEntry == null) {
        throw new SQLException("No SpreadSheetEntry is available, matching provided " +
                "connection information");
    }
    WorksheetQuery worksheetQuery =
            TDriverUtil.createWorkSheetQuery(spreadsheetEntry.getWorksheetFeedUrl());
    return this.feedProcessor.getFeed(worksheetQuery, WorksheetFeed.class);
}
 

public GSpreadResultSet retrieveData() throws Exception {
	URL worksheetUrl = this.getConfig().generateWorksheetFeedURL();
	WorksheetFeed feedw = this.getConfig().getFeed(worksheetUrl, WorksheetFeed.class);
	WorksheetEntry worksheetEntry = feedw.getEntries().get(this.getWorksheetNumber() - 1);			
	CellFeed feedc = this.getConfig().getFeed(worksheetEntry.getCellFeedUrl(), CellFeed.class);			
	List<CellEntry> entries = feedc.getEntries();			
	GSpreadResultSet grs = new GSpreadResultSet();
	
	/* store the data */
	for (CellEntry entry : entries) {
		grs.addCell(this.getPosStringFromId(entry.getId()), 
				entry.getTextContent().getContent().getPlainText());				
	}
	
	return grs;
}
 

private boolean isWorkSheetExists(TGSpreadConnection conn) {
    WorksheetFeed worksheetFeed = conn.getWorksheetFeed();
    for (WorksheetEntry worksheet : worksheetFeed.getEntries()) {
        if (this.getTableName().equals(worksheet.getTitle().getPlainText())) {
            return true;
        }
    }
    return false;
}
 

private WorksheetEntry getCurrentWorksheetEntry(TGSpreadConnection conn) throws SQLException {
    WorksheetEntry currentWorksheetEntry = null;
    SpreadsheetEntry currentSpreadsheetEntry =
            conn.getSpreadSheetFeed().getEntries().get(0);
    WorksheetFeed worksheetFeed =
            conn.getFeedProcessor().getFeed(
                    currentSpreadsheetEntry.getWorksheetFeedUrl(), WorksheetFeed.class);
    for (WorksheetEntry worksheetEntry : worksheetFeed.getEntries()) {
        if (this.getTableName().equals(worksheetEntry.getTitle().getPlainText())) {
            currentWorksheetEntry = worksheetEntry;
            break;
        }
    }
    return currentWorksheetEntry;
}
 

public static WorksheetEntry getCurrentWorkSheetEntry(TGSpreadConnection connection,
                                                      String sheetName) throws SQLException {
    SpreadsheetEntry spreadsheetEntry = connection.getSpreadSheetFeed().getEntries().get(0);
    WorksheetQuery worksheetQuery =
            TDriverUtil.createWorkSheetQuery(spreadsheetEntry.getWorksheetFeedUrl());
    WorksheetFeed worksheetFeed = connection.getFeedProcessor().getFeed(worksheetQuery,
                                                                        WorksheetFeed.class);
    for (WorksheetEntry entry : worksheetFeed.getEntries()) {
        if (sheetName.equals(entry.getTitle().getPlainText())) {
            return entry;
        }
    }
    return null;
}
 

/**
 * 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;
}
 

@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;
}
 

public void populateData() throws SQLException {
    int tmp = -1;

    TGSpreadConnection gsConnection = (TGSpreadConnection) getConnection();
    WorksheetFeed workSheetFeed = gsConnection.getWorksheetFeed();
    if (workSheetFeed == null) {
        throw new SQLException("Work sheet feed it not initialized properly and is null");
    }
    List<WorksheetEntry> workSheets = workSheetFeed.getEntries();
    for (WorksheetEntry workSheet : workSheets) {
        DataRow dataRow = null;
        CellFeed cellFeed = TDriverUtil.getGSpreadCellFeed((TGSpreadConnection) getConnection(), workSheet);

        ColumnInfo[] headers = this.extractHeaders(workSheet);
        DataTable result = new FixedDataTable(workSheet.getTitle().getPlainText(), headers);
        for (CellEntry cell : cellFeed.getEntries()) {
            int rowId = TDriverUtil.getRowIndex(cell.getId());
            if (tmp != rowId && rowId != 1) {
                if (dataRow != null) {
                    result.addRow(this.fillUpEmptyCells(dataRow, headers));
                }
                dataRow = new DataRow(rowId - 1);
                tmp = rowId;
            }
            int columnId = TDriverUtil.getColumnIndex(cell.getId());
            if (columnId > headers.length) {
                continue;
            }
            if (rowId != 1 && dataRow != null) {
                DataCell dataCell =
                        new DataCell(TDriverUtil.getColumnIndex(cell.getId()),
                                cell.getContent().getType(),
                                cell.getTextContent().getContent().getPlainText());

                dataRow.addCell(dataCell.getColumnId(), dataCell);
            }
        }
        /* adding the last row of the sheet */
        if (dataRow != null) {
            result.addRow(this.fillUpEmptyCells(dataRow, headers));
        }
        this.getData().put(result.getTableName(), result);
    }
}
 

public WorksheetFeed getWorksheetFeed() {
    return worksheetFeed;
}
 

/**
 * 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 getWorksheets() throws IOException, ServiceException {
    String propFileName = "properties/config.properties";
    Properties prop = new Properties();
    ValidationConfig config = new ValidationConfig();
    InputStream inputStream = config.getStram(propFileName);

    if (inputStream != null) {
        try {
            prop.load(inputStream);
        } catch (IOException ex) {
            Logger.getLogger(validation.class.getName()).log(Level.SEVERE, null, ex);
        }
    } else {
        throw new FileNotFoundException("property file '" + propFileName + "' not found in the classpath");
    }

    String REPORT_PARENT_FOLDER = prop.getProperty("google.report_parent_folder");
    String REPORT_DATA_TEMPLATE = prop.getProperty("google.report_data_template");

    System.out.println("Got drive service");

    DateFormat dateFormat = new SimpleDateFormat("MM/dd/yyyy");
    Date date = new Date();

    String fileName = "Data run " + dateFormat.format(date);
    File file = new File();
    file.setTitle(fileName);
    file.setParents(Arrays.asList(new ParentReference().setId(REPORT_PARENT_FOLDER)));
    file.setDescription("New File created from server");

    System.out.println("Copying file");

    file = driveService.files().copy(REPORT_DATA_TEMPLATE, file).execute();

    System.out.println("Successfully copied file. Start to change file content");

    String fileId = file.getId();
    URL SPREADSHEET_FEED_URL = new URL("https://spreadsheets.google.com/feeds/spreadsheets/private/full/" + fileId);

    SpreadsheetEntry spreadSheetEntry = spreadsheetService.getEntry(SPREADSHEET_FEED_URL, SpreadsheetEntry.class);

    WorksheetFeed worksheetFeed = spreadsheetService.getFeed(
        spreadSheetEntry.getWorksheetFeedUrl(), WorksheetFeed.class);
    worksheets = worksheetFeed.getEntries();
}