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

The following examples show how to use com.google.api.services.sheets.v4.model.GridRange. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.

Example #1

Source File: BaseTest.java From java-samples with Apache License 2.0

6 votes

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

Example #2

Source File: BaseTest.java From java-samples with Apache License 2.0

6 votes

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

Example #3

Source File: GoogleSheetsAddChartCustomizer.java From syndesis with Apache License 2.0

5 votes

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

Example #4

Source File: GoogleSheetsAddChartCustomizer.java From syndesis with Apache License 2.0

5 votes

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

Example #5

Source File: GoogleSheetsAddChartCustomizer.java From syndesis with Apache License 2.0

4 votes

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

Example #6

Source File: GoogleSheetsAddPivotTableCustomizer.java From syndesis with Apache License 2.0

4 votes

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

Example #7

Source File: GoogleSheetsAddChartCustomizerTest.java From syndesis with Apache License 2.0

4 votes

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

Example #8

Source File: GoogleSheetsAddChartCustomizerTest.java From syndesis with Apache License 2.0

4 votes

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

Example #9

Source File: GoogleSheetsAddChartCustomizerTest.java From syndesis with Apache License 2.0

4 votes

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

Example #10

Source File: GoogleSheetsLiveTest.java From tutorials with MIT License

4 votes

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