com.google.api.services.sheets.v4.model.GridRange Java Examples

protected void populateValuesWithStrings(String spreadsheetId) throws IOException {
  List<Request> requests = new ArrayList<>();
  requests.add(new Request().setRepeatCell(new RepeatCellRequest()
      .setRange(new GridRange()
          .setSheetId(0)
          .setStartRowIndex(0)
          .setEndRowIndex(10)
          .setStartColumnIndex(0)
          .setEndColumnIndex(10))
      .setCell(new CellData()
          .setUserEnteredValue(new ExtendedValue()
              .setStringValue("Hello")))
      .setFields("userEnteredValue")));
  BatchUpdateSpreadsheetRequest body = new BatchUpdateSpreadsheetRequest()
      .setRequests(requests);
  service.spreadsheets().batchUpdate(spreadsheetId, body).execute();
}
 

protected void populateValuesWithNumbers(String spreadsheetId) throws IOException {
  List<Request> requests = new ArrayList<>();
  requests.add(new Request().setRepeatCell(new RepeatCellRequest()
      .setRange(new GridRange()
          .setSheetId(0)
          .setStartRowIndex(0)
          .setEndRowIndex(10)
          .setStartColumnIndex(0)
          .setEndColumnIndex(10))
      .setCell(new CellData()
          .setUserEnteredValue(new ExtendedValue()
              .setNumberValue(1337D)))
      .setFields("userEnteredValue")));
  BatchUpdateSpreadsheetRequest body = new BatchUpdateSpreadsheetRequest()
      .setRequests(requests);
  service.spreadsheets().batchUpdate(spreadsheetId, body).execute();
}
 

private static void addPieChart(ChartSpec chartSpec, GoogleChart model) {
    Integer sheetId = Optional.ofNullable(model.getSheetId())
                              .orElse(0);

    Integer sourceSheetId = Optional.ofNullable(model.getSourceSheetId())
                                    .orElse(sheetId);

    GoogleChart.PieChart pieChart = model.getPieChart();

    PieChartSpec pieChartSpec = new PieChartSpec();
    pieChartSpec.setLegendPosition(pieChart.getLegendPosition());
    pieChartSpec.setDomain(new ChartData().setSourceRange(getDomainSourceRange(sourceSheetId, pieChart.getDomainRange())));

    ChartSourceRange sourceRange = new ChartSourceRange();
    GridRange gridRange = new GridRange();
    gridRange.setSheetId(sourceSheetId);
    RangeCoordinate coordinates = RangeCoordinate.fromRange(pieChart.getDataRange());
    gridRange.setStartRowIndex(coordinates.getRowStartIndex());
    gridRange.setEndRowIndex(coordinates.getRowEndIndex());
    gridRange.setStartColumnIndex(coordinates.getColumnStartIndex());
    gridRange.setEndColumnIndex(coordinates.getColumnEndIndex());
    sourceRange.setSources(Collections.singletonList(gridRange));

    pieChartSpec.setSeries(new ChartData().setSourceRange(sourceRange));

    chartSpec.setPieChart(pieChartSpec);
}
 

private static ChartSourceRange getDomainSourceRange(int sourceSheetId, String domainRange) {
    ChartSourceRange domainSourceRange = new ChartSourceRange();
    GridRange domainGridRange = new GridRange();
    domainGridRange.setSheetId(sourceSheetId);
    RangeCoordinate coordinate = RangeCoordinate.fromRange(domainRange);
    domainGridRange.setStartRowIndex(coordinate.getRowStartIndex());
    domainGridRange.setEndRowIndex(coordinate.getRowEndIndex());
    domainGridRange.setStartColumnIndex(coordinate.getRowStartIndex());
    domainGridRange.setEndColumnIndex(coordinate.getColumnEndIndex());
    domainSourceRange.setSources(Collections.singletonList(domainGridRange));
    return domainSourceRange;
}
 

private static void addBasicChart(ChartSpec chartSpec, GoogleChart model) {
    Integer sheetId = Optional.ofNullable(model.getSheetId())
            .orElse(0);

    Integer sourceSheetId = Optional.ofNullable(model.getSourceSheetId())
            .orElse(sheetId);

    GoogleChart.BasicChart basicChart = model.getBasicChart();

    BasicChartSpec basicChartSpec = new BasicChartSpec();
    basicChartSpec.setHeaderCount(1);

    BasicChartAxis bottomAxis = new BasicChartAxis();
    bottomAxis.setPosition("BOTTOM_AXIS");
    bottomAxis.setTitle(Optional.ofNullable(basicChart.getAxisTitleBottom())
            .orElse("X-Axis"));

    BasicChartAxis leftAxis = new BasicChartAxis();
    leftAxis.setPosition("LEFT_AXIS");
    leftAxis.setTitle(Optional.ofNullable(basicChart.getAxisTitleLeft())
            .orElse("Y-Axis"));
    basicChartSpec.setAxis(Arrays.asList(bottomAxis, leftAxis));

    BasicChartDomain chartDomain = new BasicChartDomain();

    chartDomain.setDomain(new ChartData().setSourceRange(getDomainSourceRange(sourceSheetId, basicChart.getDomainRange())));
    basicChartSpec.setDomains(Collections.singletonList(chartDomain));
    basicChartSpec.setChartType(basicChart.getType());

    List<BasicChartSeries> series = new ArrayList<>();
    String dataRange = Optional.ofNullable(basicChart.getDataRange()).orElse("");
    Stream.of(dataRange.split(",", -1)).forEach(range -> {
        ChartSourceRange sourceRange = new ChartSourceRange();
        GridRange gridRange = new GridRange();
        gridRange.setSheetId(sourceSheetId);
        RangeCoordinate coordinate = RangeCoordinate.fromRange(range);
        gridRange.setStartRowIndex(coordinate.getRowStartIndex());
        gridRange.setEndRowIndex(coordinate.getRowEndIndex());
        gridRange.setStartColumnIndex(coordinate.getColumnStartIndex());
        gridRange.setEndColumnIndex(coordinate.getColumnEndIndex());
        sourceRange.setSources(Collections.singletonList(gridRange));

        BasicChartSeries basicChartSeries = new BasicChartSeries();
        basicChartSeries.setTargetAxis("LEFT_AXIS");
        basicChartSeries.setSeries(new ChartData().setSourceRange(sourceRange));
        series.add(basicChartSeries);
    });

    basicChartSpec.setSeries(series);
    chartSpec.setBasicChart(basicChartSpec);
}
 

private void beforeProducer(Exchange exchange) {
    final Message in = exchange.getIn();
    final GooglePivotTable model = exchange.getIn().getBody(GooglePivotTable.class);

    if (model != null && ObjectHelper.isNotEmpty(model.getSpreadsheetId())) {
        spreadsheetId = model.getSpreadsheetId();
    }

    BatchUpdateSpreadsheetRequest batchUpdateRequest = new BatchUpdateSpreadsheetRequest();
    batchUpdateRequest.setIncludeSpreadsheetInResponse(true);
    batchUpdateRequest.setRequests(new ArrayList<>());

    UpdateCellsRequest updateCellsRequest = new UpdateCellsRequest();
    updateCellsRequest.setFields("pivotTable");
    batchUpdateRequest.getRequests().add(new Request().setUpdateCells(updateCellsRequest));

    RowData rowData = new RowData();
    PivotTable pivotTable = new PivotTable();

    if (model != null) {
        Integer defaultSheetId = Optional.ofNullable(model.getSheetId()).orElse(0);

        pivotTable.setValueLayout(model.getValueLayout());

        GridRange sourceRange = new GridRange();
        sourceRange.setSheetId(Optional.ofNullable(model.getSourceSheetId()).orElse(defaultSheetId));
        RangeCoordinate coordinate = RangeCoordinate.fromRange(model.getSourceRange());
        sourceRange.setStartRowIndex(coordinate.getRowStartIndex());
        sourceRange.setEndRowIndex(coordinate.getRowEndIndex());
        sourceRange.setStartColumnIndex(coordinate.getColumnStartIndex());
        sourceRange.setEndColumnIndex(coordinate.getColumnEndIndex());
        pivotTable.setSource(sourceRange);


        addRowGroups(pivotTable, model);
        addColumnGroups(pivotTable, model);
        addValueGroups(pivotTable, model);
    }

    updateCellsRequest.setStart(getStartCoordinate(pivotTable, model));

    rowData.setValues(Collections.singletonList(new CellData().setPivotTable(pivotTable)));
    updateCellsRequest.setRows(Collections.singletonList(rowData));

    in.setHeader(GoogleSheetsStreamConstants.SPREADSHEET_ID, spreadsheetId);
    in.setHeader(GoogleSheetsConstants.PROPERTY_PREFIX + "batchUpdateSpreadsheetRequest", batchUpdateRequest);
}
 

@Test
public void testBeforeProducerFromModel() throws Exception {
    customizer.customize(getComponent(), new HashMap<>());

    Exchange inbound = new DefaultExchange(createCamelContext());

    GoogleChart model = new GoogleChart();
    model.setSpreadsheetId(getSpreadsheetId());
    model.setTitle("SyndesisChart");
    model.setSubtitle("Some subtitle");
    model.setSourceSheetId(0);
    model.setSheetId(1);
    model.setOverlayPosition("D2");

    GoogleChart.BasicChart basicChart = new GoogleChart.BasicChart();
    basicChart.setAxisTitleBottom("Product Names");
    basicChart.setAxisTitleLeft("Sales Numbers");

    basicChart.setDomainRange("A1:A10");
    basicChart.setDataRange("B1:B10");

    model.setBasicChart(basicChart);

    inbound.getIn().setBody(model);
    getComponent().getBeforeProducer().process(inbound);

    Assert.assertNotNull(inbound.getIn().getHeader(GoogleSheetsStreamConstants.SPREADSHEET_ID));
    Assert.assertEquals(model.getSpreadsheetId(), inbound.getIn().getHeader(GoogleSheetsStreamConstants.SPREADSHEET_ID));

    BatchUpdateSpreadsheetRequest batchUpdateRequest = (BatchUpdateSpreadsheetRequest) inbound.getIn().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX + "batchUpdateSpreadsheetRequest");
    Assert.assertEquals(1, batchUpdateRequest.getRequests().size());

    AddChartRequest addChartRequest = batchUpdateRequest.getRequests().get(0).getAddChart();
    EmbeddedChart chart = addChartRequest.getChart();
    Assert.assertEquals("SyndesisChart", chart.getSpec().getTitle());
    Assert.assertEquals("Some subtitle", chart.getSpec().getSubtitle());
    Assert.assertEquals(Integer.valueOf(1), chart.getPosition().getOverlayPosition().getAnchorCell().getSheetId());
    Assert.assertEquals(Integer.valueOf(1), chart.getPosition().getOverlayPosition().getAnchorCell().getRowIndex());
    Assert.assertEquals(Integer.valueOf(3), chart.getPosition().getOverlayPosition().getAnchorCell().getColumnIndex());

    BasicChartSpec chartSpec = chart.getSpec().getBasicChart();
    Assert.assertNotNull(chartSpec);
    Assert.assertEquals(2, chartSpec.getAxis().size());
    Assert.assertEquals("Product Names", chartSpec.getAxis().get(0).getTitle());
    Assert.assertEquals("BOTTOM_AXIS", chartSpec.getAxis().get(0).getPosition());
    Assert.assertEquals("Sales Numbers", chartSpec.getAxis().get(1).getTitle());
    Assert.assertEquals("LEFT_AXIS", chartSpec.getAxis().get(1).getPosition());

    Assert.assertEquals(1, chartSpec.getDomains().size());
    Assert.assertEquals(1, chartSpec.getDomains().get(0).getDomain().getSourceRange().getSources().size());
    Assert.assertEquals(Integer.valueOf(0), chartSpec.getDomains().get(0).getDomain().getSourceRange().getSources().get(0).getStartRowIndex());
    Assert.assertEquals(Integer.valueOf(10), chartSpec.getDomains().get(0).getDomain().getSourceRange().getSources().get(0).getEndRowIndex());
    Assert.assertEquals(Integer.valueOf(0), chartSpec.getDomains().get(0).getDomain().getSourceRange().getSources().get(0).getStartColumnIndex());
    Assert.assertEquals(Integer.valueOf(1), chartSpec.getDomains().get(0).getDomain().getSourceRange().getSources().get(0).getEndColumnIndex());

    Assert.assertEquals(1, chartSpec.getSeries().size());

    Assert.assertEquals("LEFT_AXIS", chartSpec.getSeries().get(0).getTargetAxis());
    BasicChartSeries basicChartSeries = chartSpec.getSeries().get(0);
    Assert.assertEquals(1, basicChartSeries.getSeries().getSourceRange().getSources().size());
    GridRange gridRange = basicChartSeries.getSeries().getSourceRange().getSources().get(0);
    Assert.assertEquals(Integer.valueOf(0), gridRange.getStartRowIndex());
    Assert.assertEquals(Integer.valueOf(10), gridRange.getEndRowIndex());
    Assert.assertEquals(Integer.valueOf(1), gridRange.getStartColumnIndex());
    Assert.assertEquals(Integer.valueOf(2), gridRange.getEndColumnIndex());
}
 

@Test
public void testBarChartWithMultipleSeries() throws Exception {
    customizer.customize(getComponent(), new HashMap<>());

    Exchange inbound = new DefaultExchange(createCamelContext());

    GoogleChart model = new GoogleChart();
    model.setSpreadsheetId(getSpreadsheetId());
    model.setTitle("SyndesisBars");
    model.setSheetId(0);

    GoogleChart.BasicChart basicChart = new GoogleChart.BasicChart();
    basicChart.setAxisTitleBottom("Product Names");
    basicChart.setAxisTitleLeft("Sales Numbers");

    basicChart.setDomainRange("A1:A10");
    basicChart.setDataRange("B1:B10,C1:C10");

    model.setBasicChart(basicChart);

    inbound.getIn().setBody(model);
    getComponent().getBeforeProducer().process(inbound);

    BatchUpdateSpreadsheetRequest batchUpdateRequest = (BatchUpdateSpreadsheetRequest) inbound.getIn().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX + "batchUpdateSpreadsheetRequest");
    Assert.assertEquals(1, batchUpdateRequest.getRequests().size());

    AddChartRequest addChartRequest = batchUpdateRequest.getRequests().get(0).getAddChart();
    Assert.assertTrue(addChartRequest.getChart().getPosition().getNewSheet());
    BasicChartSpec chartSpec = addChartRequest.getChart().getSpec().getBasicChart();
    Assert.assertNotNull(chartSpec);
    Assert.assertEquals(2, chartSpec.getSeries().size());

    Assert.assertEquals("LEFT_AXIS", chartSpec.getSeries().get(0).getTargetAxis());
    BasicChartSeries basicChartSeries = chartSpec.getSeries().get(0);
    Assert.assertEquals(1, basicChartSeries.getSeries().getSourceRange().getSources().size());
    GridRange gridRange = basicChartSeries.getSeries().getSourceRange().getSources().get(0);
    Assert.assertEquals(Integer.valueOf(0), gridRange.getStartRowIndex());
    Assert.assertEquals(Integer.valueOf(10), gridRange.getEndRowIndex());
    Assert.assertEquals(Integer.valueOf(1), gridRange.getStartColumnIndex());
    Assert.assertEquals(Integer.valueOf(2), gridRange.getEndColumnIndex());

    Assert.assertEquals("LEFT_AXIS", chartSpec.getSeries().get(1).getTargetAxis());
    basicChartSeries = chartSpec.getSeries().get(1);
    Assert.assertEquals(1, basicChartSeries.getSeries().getSourceRange().getSources().size());
    gridRange = basicChartSeries.getSeries().getSourceRange().getSources().get(0);
    Assert.assertEquals(Integer.valueOf(0), gridRange.getStartRowIndex());
    Assert.assertEquals(Integer.valueOf(10), gridRange.getEndRowIndex());
    Assert.assertEquals(Integer.valueOf(2), gridRange.getStartColumnIndex());
    Assert.assertEquals(Integer.valueOf(3), gridRange.getEndColumnIndex());
}
 

@Test
public void testPieChart() throws Exception {
    customizer.customize(getComponent(), new HashMap<>());

    Exchange inbound = new DefaultExchange(createCamelContext());

    GoogleChart model = new GoogleChart();
    model.setSpreadsheetId(getSpreadsheetId());
    model.setTitle("SyndesisPie");
    model.setSheetId(0);

    GoogleChart.PieChart pieChart = new GoogleChart.PieChart();

    pieChart.setDomainRange("A1:A5");
    pieChart.setDataRange("B1:B5");

    model.setPieChart(pieChart);

    inbound.getIn().setBody(model);
    getComponent().getBeforeProducer().process(inbound);

    BatchUpdateSpreadsheetRequest batchUpdateRequest = (BatchUpdateSpreadsheetRequest) inbound.getIn().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX + "batchUpdateSpreadsheetRequest");
    Assert.assertEquals(1, batchUpdateRequest.getRequests().size());

    AddChartRequest addChartRequest = batchUpdateRequest.getRequests().get(0).getAddChart();
    PieChartSpec chartSpec = addChartRequest.getChart().getSpec().getPieChart();
    Assert.assertNotNull(chartSpec);
    Assert.assertEquals("RIGHT_LEGEND", chartSpec.getLegendPosition());
    Assert.assertEquals(1, chartSpec.getSeries().size());

    ChartData domain = chartSpec.getDomain();
    Assert.assertEquals(1, domain.getSourceRange().getSources().size());
    GridRange domainRange = domain.getSourceRange().getSources().get(0);
    Assert.assertEquals(Integer.valueOf(0), domainRange.getStartRowIndex());
    Assert.assertEquals(Integer.valueOf(5), domainRange.getEndRowIndex());
    Assert.assertEquals(Integer.valueOf(0), domainRange.getStartColumnIndex());
    Assert.assertEquals(Integer.valueOf(1), domainRange.getEndColumnIndex());

    ChartData data = chartSpec.getSeries();
    Assert.assertEquals(1, data.getSourceRange().getSources().size());
    GridRange dataRange = data.getSourceRange().getSources().get(0);
    Assert.assertEquals(Integer.valueOf(0), dataRange.getStartRowIndex());
    Assert.assertEquals(Integer.valueOf(5), dataRange.getEndRowIndex());
    Assert.assertEquals(Integer.valueOf(1), dataRange.getStartColumnIndex());
    Assert.assertEquals(Integer.valueOf(2), dataRange.getEndColumnIndex());
}
 

@Test
public void whenUpdateSpreadSheetTitle_thenOk() throws IOException {

    UpdateSpreadsheetPropertiesRequest updateRequest = new UpdateSpreadsheetPropertiesRequest().setFields("*").setProperties(new SpreadsheetProperties().setTitle("Expenses"));

    CopyPasteRequest copyRequest = new CopyPasteRequest().setSource(new GridRange().setSheetId(0).setStartColumnIndex(0).setEndColumnIndex(2).setStartRowIndex(0).setEndRowIndex(1))
            .setDestination(new GridRange().setSheetId(1).setStartColumnIndex(0).setEndColumnIndex(2).setStartRowIndex(0).setEndRowIndex(1)).setPasteType("PASTE_VALUES");

    List<Request> requests = new ArrayList<>();

    requests.add(new Request().setCopyPaste(copyRequest));
    requests.add(new Request().setUpdateSpreadsheetProperties(updateRequest));

    BatchUpdateSpreadsheetRequest body = new BatchUpdateSpreadsheetRequest().setRequests(requests);

    sheetsService.spreadsheets().batchUpdate(SPREADSHEET_ID, body).execute();
}