com.google.gdata.data.spreadsheet.SpreadsheetEntry 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);
}
 

private String getLatestWorksheetId(SpreadsheetEntry spreadsheet) throws Throwable
{
    String worksheetId = null;
    List<WorksheetEntry> worksheets = spreadsheet.getWorksheets();
    Date reportCurrentDate = new Date(Long.MIN_VALUE);
    for (WorksheetEntry worksheet : worksheets) {
        String title = worksheet.getTitle().getPlainText();
        if(title.equals("instructions") || title.equals("Sheet1") || title.startsWith("diff_") || title.startsWith("ignore_")) continue;
        Date date = dateUtil.getDateFromString(title);
        if (date.after(reportCurrentDate)){
            reportCurrentDate = date;
            worksheetId = title;
        }
    }
    return worksheetId;
}
 

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

private SpreadsheetEntry extractSpreadSheetEntryFromUrl() throws SQLException {
    try {
        URL spreadSheetFeedUrl = this.feedProcessor.getSpreadSheetFeedUrl();
        SpreadsheetFeed feed =
                this.feedProcessor.getFeed(spreadSheetFeedUrl, SpreadsheetFeed.class);
        List<SpreadsheetEntry> entries = feed.getEntries();
        return (entries != null && entries.size() > 0) ? entries.get(0) : null;
    } catch (Exception e) {
        throw new SQLException("Error occurred while extracting spread sheet entry", e);
    }
}
 

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

public Map<Integer, TitleWithQueries> extractTitlesWithQueries(String worksheetId) throws Throwable, IOException, ServiceException
{
    Map<Integer, TitleWithQueries> titlesWithQueries = null;
    SpreadsheetEntry spreadsheet = getSpreadsheet(Properties.inputQueriesSheet.get());
    WorksheetEntry worksheet = getWorksheet(spreadsheet, worksheetId);
    if (worksheet != null) {
        List<CellEntry> cellEntries = getCellsForWorksheet(worksheet).getEntries();
        Map<String, String> header = getHeader(cellEntries);
        titlesWithQueries = getTitlesWithQueries(cellEntries, header, worksheetId);
    }
    return titlesWithQueries;
}
 

public List<String> getLatestSummaryReportAsTsv() throws Throwable
{
    SpreadsheetEntry spreadsheet = getSpreadsheet(summaryReportName);
    String worksheetId = getLatestWorksheetId(spreadsheet);
    return extractWorksheetData(spreadsheet, worksheetId, HeaderUtils.getHeader(ReportType.summary));

}
 

public void extractReport(Report report, boolean isDetailReport) throws Throwable
{
    List<ReportItem> reportItems = null;
    String spreadsheetName = getReportName(isDetailReport);
    SpreadsheetEntry spreadsheet = getSpreadsheet(spreadsheetName);
    String worksheetId = getLatestWorksheetId(spreadsheet);
    WorksheetEntry worksheet = getWorksheet(spreadsheet, worksheetId);
    if (worksheet != null) {
        List<CellEntry> cellEntries = getCellsForWorksheet(worksheet).getEntries();
        Map<String, String> header = getHeader(cellEntries);
        reportItems = getReport(cellEntries, header, isDetailReport);
    }
    report.setItems(reportItems);
    report.setDate(worksheetId);
}
 

private List<String> extractWorksheetData(SpreadsheetEntry spreadsheet, String worksheetId, String[] header) throws Throwable, IOException, ServiceException
{
    List<String> returnValue = Lists.newArrayList();
    WorksheetEntry worksheet = getWorksheet(spreadsheet, worksheetId);
    if (worksheet != null) {
        ListFeed listFeed = getListFeedForWorksheet(worksheet);
        returnValue = getAllEntries(listFeed, header);
    }
    return returnValue;
}
 

public void updateReport(String worksheetId, String[] reportHeader, List<ReportItem> reportItems, boolean isDetailReport) throws Throwable, IOException, ServiceException
{
    String reportSpreadsheetName = getReportName(isDetailReport);
    SpreadsheetEntry spreadsheet = getSpreadsheet(reportSpreadsheetName );

    int numberOfRows = reportItems.size() + 1;
    int numberOfColumns = reportHeader.length;

    addNewWorksheet(spreadsheet, worksheetId, numberOfRows, numberOfColumns);

    WorksheetEntry worksheet = getWorksheet(spreadsheet, worksheetId);
    if (worksheet != null) {
        updateWorksheetWithAllItems(worksheet, reportHeader, reportItems, numberOfRows, numberOfColumns, reportSpreadsheetName);
    }
}
 

private SpreadsheetEntry getSpreadsheet(String reportSpreadsheetName) throws Throwable
{
    SpreadsheetFeed feed = spreadsheetService.getFeed(spreadsheetsFeedUrl, SpreadsheetFeed.class);
    for (SpreadsheetEntry spreadsheet : feed.getEntries()) {
        if (spreadsheet.getTitle().getPlainText().equalsIgnoreCase(reportSpreadsheetName))
            return spreadsheet;
    }
    throw new RuntimeException(String.format("Either user '%s' has no access to the specified spreadsheet, or there is no spreadsheet named '%s'", Properties.serviceAccountEmail.get(), reportSpreadsheetName));
}
 

private void addNewWorksheet(SpreadsheetEntry spreadsheet, String worksheetId, int numberOfRows, int numberOfColumns) throws Throwable
{
    if (spreadsheet != null) {
        WorksheetEntry worksheet = new WorksheetEntry();
        worksheet.setTitle(new PlainTextConstruct(worksheetId));
        worksheet.setRowCount(numberOfRows);
        worksheet.setColCount(numberOfColumns);

        URL worksheetFeedUrl = spreadsheet.getWorksheetFeedUrl();
        spreadsheetService.insert(worksheetFeedUrl, worksheet);
    }
}
 

private WorksheetEntry getWorksheet(SpreadsheetEntry spreadsheet, String worksheetId) throws Throwable
{
    List<WorksheetEntry> worksheets = spreadsheet.getWorksheets();
    for (WorksheetEntry worksheet : worksheets) {
        String title = worksheet.getTitle().getPlainText();
        if (title.equalsIgnoreCase(worksheetId))
            return worksheet;
    }
    return null;
}
 

public List<String> getTitlesWithQueriesAsTsv(String worksheetId) throws Throwable
{
    SpreadsheetEntry spreadsheet = getSpreadsheet(Properties.inputQueriesSheet.get());
    return extractWorksheetData(spreadsheet, worksheetId, null);
}
 

public List<String> getLatestDetailReportAsTsv() throws Throwable
{
    SpreadsheetEntry spreadsheet = getSpreadsheet(detailReportName);
    String worksheetId = getLatestWorksheetId(spreadsheet);
    return extractWorksheetData(spreadsheet, worksheetId, HeaderUtils.getHeader(ReportType.details));
}
 

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();
}