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

public static void createSheet(List<Object> headers) throws IOException, GeneralSecurityException{
	if(onlyOnce)
		return;
	onlyOnce = true;	
	Sheets service = getService();
	String sheetID = getGitRepositoryState().commitId;
	List<Request> requests = new ArrayList<>(); 
	AddSheetRequest addSheet = new AddSheetRequest();
	addSheet.setProperties(new SheetProperties().setTitle(sheetID));
	requests.add(new Request().setAddSheet(addSheet));
	BatchUpdateSpreadsheetRequest requestBody = new BatchUpdateSpreadsheetRequest();
	requestBody.setRequests(requests);
	service.spreadsheets().batchUpdate(SPREADSHEET_ID, requestBody).execute();
	
	ArrayList<List<Object>> rows = Lists.newArrayList();
	rows.add(headers);
	ValueRange body = new ValueRange().setValues(Arrays.asList(headers));
	service.spreadsheets().values().append(SPREADSHEET_ID, sheetID, body).setValueInputOption("USER_ENTERED")
			.execute();
}
 

@Path("/update")
@PATCH
@Consumes(MediaType.TEXT_PLAIN)
public Response updateSheet(@QueryParam("spreadsheetId") String spreadsheetId, String title) {
    BatchUpdateSpreadsheetRequest request = new BatchUpdateSpreadsheetRequest()
            .setIncludeSpreadsheetInResponse(true)
            .setRequests(Collections
                    .singletonList(new Request().setUpdateSpreadsheetProperties(new UpdateSpreadsheetPropertiesRequest()
                            .setProperties(new SpreadsheetProperties().setTitle(title))
                            .setFields("title"))));

    final Map<String, Object> headers = new HashMap<>();
    headers.put("CamelGoogleSheets.spreadsheetId", spreadsheetId);
    headers.put("CamelGoogleSheets.batchUpdateSpreadsheetRequest", request);
    producerTemplate.requestBodyAndHeaders("google-sheets://spreadsheets/batchUpdate", null, headers);
    return Response.ok().build();
}
 

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

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

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

    if (model != null) {
        if (ObjectHelper.isNotEmpty(model.getSpreadsheetId())) {
            spreadsheetId = model.getSpreadsheetId();
        }
        if (ObjectHelper.isNotEmpty(model.getTitle())) {
            title = model.getTitle();
        }
        if (ObjectHelper.isNotEmpty(model.getSubtitle())) {
            subtitle = model.getSubtitle();
        }
    }

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

    AddChartRequest addChartRequest = new AddChartRequest();
    batchUpdateRequest.getRequests().add(new Request().setAddChart(addChartRequest));

    ChartSpec chartSpec = createChartSpec(title, subtitle);
    if (model != null) {
        addChartRequest.setChart(createEmbeddedChart(model, chartSpec));
        if (model.getBasicChart() != null) {
            addBasicChart(chartSpec, model);
        } else if (model.getPieChart() != null) {
            addPieChart(chartSpec, model);
        }
    } else {
        addChartRequest.setChart(createEmptyChart(chartSpec));
    }

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

@Test
public void testBeforeProducerFromOptions() throws Exception {
    Map<String, Object> options = new HashMap<>();
    options.put("spreadsheetId", getSpreadsheetId());
    options.put("title", "SyndesisTest");
    options.put("timeZone", "America/New_York");
    options.put("locale", "en");

    customizer.customize(getComponent(), options);

    Exchange inbound = new DefaultExchange(createCamelContext());
    getComponent().getBeforeProducer().process(inbound);

    Assert.assertEquals(GoogleSheetsApiCollection.getCollection().getApiName(SheetsSpreadsheetsApiMethod.class).getName(), ConnectorOptions.extractOption(options, "apiName"));
    Assert.assertEquals("batchUpdate", ConnectorOptions.extractOption(options, "methodName"));

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

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

    UpdateSpreadsheetPropertiesRequest updateSpreadsheetPropertiesRequest = batchUpdateRequest.getRequests().get(0).getUpdateSpreadsheetProperties();
    Assert.assertEquals("title,timeZone,locale", updateSpreadsheetPropertiesRequest.getFields());
    Assert.assertEquals("SyndesisTest", updateSpreadsheetPropertiesRequest.getProperties().getTitle());
    Assert.assertEquals("America/New_York", updateSpreadsheetPropertiesRequest.getProperties().getTimeZone());
    Assert.assertEquals("en", updateSpreadsheetPropertiesRequest.getProperties().getLocale());
}
 

@Test
public void testBeforeProducerFromOptions() throws Exception {
    Map<String, Object> options = new HashMap<>();
    options.put("spreadsheetId", getSpreadsheetId());
    options.put("title", "SyndesisChart");
    options.put("subtitle", "Some subtitle");

    customizer.customize(getComponent(), options);

    Exchange inbound = new DefaultExchange(createCamelContext());
    getComponent().getBeforeProducer().process(inbound);

    Assert.assertEquals(GoogleSheetsApiCollection.getCollection().getApiName(SheetsSpreadsheetsApiMethod.class).getName(), ConnectorOptions.extractOption(options, "apiName"));
    Assert.assertEquals("batchUpdate", ConnectorOptions.extractOption(options, "methodName"));

    Assert.assertNotNull(inbound.getIn().getHeader(GoogleSheetsStreamConstants.SPREADSHEET_ID));
    Assert.assertEquals(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();

    Assert.assertEquals("SyndesisChart", addChartRequest.getChart().getSpec().getTitle());
    Assert.assertEquals("Some subtitle", addChartRequest.getChart().getSpec().getSubtitle());
    Assert.assertTrue(addChartRequest.getChart().getPosition().getNewSheet());

}
 

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

    Exchange inbound = new DefaultExchange(createCamelContext());

    GooglePivotTable model = new GooglePivotTable();
    model.setSpreadsheetId(getSpreadsheetId());

    model.setSourceRange("A1:D10");

    GooglePivotTable.ValueGroup valueGroup = new GooglePivotTable.ValueGroup();
    valueGroup.setSourceColumn("A");
    valueGroup.setFormula("=Cost*SUM(Quantity)");
    model.setValueGroups(Collections.singletonList(valueGroup));

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

    UpdateCellsRequest updateCellsRequest = batchUpdateRequest.getRequests().get(0).getUpdateCells();

    Assert.assertEquals(Integer.valueOf(0), updateCellsRequest.getStart().getSheetId());
    Assert.assertEquals(Integer.valueOf(5), updateCellsRequest.getStart().getColumnIndex());
    Assert.assertEquals(Integer.valueOf(0), updateCellsRequest.getStart().getRowIndex());

    PivotTable pivotTable = updateCellsRequest.getRows().get(0).getValues().get(0).getPivotTable();

    Assert.assertEquals(1, pivotTable.getValues().size());
    Assert.assertEquals("CUSTOM", pivotTable.getValues().get(0).getSummarizeFunction());
    Assert.assertEquals("=Cost*SUM(Quantity)", pivotTable.getValues().get(0).getFormula());
    Assert.assertNull(pivotTable.getValues().get(0).getSourceColumnOffset());
}
 

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

    Exchange inbound = new DefaultExchange(createCamelContext());

    GooglePivotTable model = new GooglePivotTable();
    model.setStart("C12");
    model.setSpreadsheetId(getSpreadsheetId());

    model.setSourceRange("A1:D10");

    GooglePivotTable.ValueGroup valueGroup = new GooglePivotTable.ValueGroup();
    valueGroup.setSourceColumn("E");
    valueGroup.setFunction("AVERAGE");
    model.setValueGroups(Collections.singletonList(valueGroup));

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

    UpdateCellsRequest updateCellsRequest = batchUpdateRequest.getRequests().get(0).getUpdateCells();

    Assert.assertEquals(Integer.valueOf(0), updateCellsRequest.getStart().getSheetId());
    Assert.assertEquals(Integer.valueOf(2), updateCellsRequest.getStart().getColumnIndex());
    Assert.assertEquals(Integer.valueOf(11), updateCellsRequest.getStart().getRowIndex());

    PivotTable pivotTable = updateCellsRequest.getRows().get(0).getValues().get(0).getPivotTable();

    Assert.assertEquals(1, pivotTable.getValues().size());
    Assert.assertEquals("AVERAGE", pivotTable.getValues().get(0).getSummarizeFunction());
    Assert.assertEquals(Integer.valueOf(4), pivotTable.getValues().get(0).getSourceColumnOffset());
}
 

@Test
public void testBeforeProducerFromOptions() throws Exception {
    Map<String, Object> options = new HashMap<>();
    options.put("spreadsheetId", getSpreadsheetId());

    customizer.customize(getComponent(), options);

    Exchange inbound = new DefaultExchange(createCamelContext());
    getComponent().getBeforeProducer().process(inbound);

    Assert.assertEquals(GoogleSheetsApiCollection.getCollection().getApiName(SheetsSpreadsheetsApiMethod.class).getName(), ConnectorOptions.extractOption(options, "apiName"));
    Assert.assertEquals("batchUpdate", ConnectorOptions.extractOption(options, "methodName"));

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

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

    UpdateCellsRequest updateCellsRequest = batchUpdateRequest.getRequests().get(0).getUpdateCells();

    Assert.assertEquals("pivotTable", updateCellsRequest.getFields());
    Assert.assertNotNull(updateCellsRequest.getStart());
    Assert.assertEquals(Integer.valueOf(0), updateCellsRequest.getStart().getSheetId());
    Assert.assertEquals(Integer.valueOf(0), updateCellsRequest.getStart().getColumnIndex());
    Assert.assertEquals(Integer.valueOf(0), updateCellsRequest.getStart().getRowIndex());

    PivotTable pivotTable = updateCellsRequest.getRows().get(0).getValues().get(0).getPivotTable();
    Assert.assertNotNull(pivotTable);
    Assert.assertNull(pivotTable.getSource());
    Assert.assertNull(pivotTable.getRows());
    Assert.assertNull(pivotTable.getColumns());
    Assert.assertNull(pivotTable.getValues());
}
 

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

    Exchange inbound = new DefaultExchange(createCamelContext());

    GooglePivotTable model = new GooglePivotTable();
    model.setStart("C12");
    model.setSpreadsheetId(getSpreadsheetId());

    model.setSourceRange("A1:D10");
    model.setValueGroups(Collections.singletonList(sampleValueGroup()));

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

    UpdateCellsRequest updateCellsRequest = batchUpdateRequest.getRequests().get(0).getUpdateCells();

    Assert.assertEquals(Integer.valueOf(0), updateCellsRequest.getStart().getSheetId());
    Assert.assertEquals(Integer.valueOf(2), updateCellsRequest.getStart().getColumnIndex());
    Assert.assertEquals(Integer.valueOf(11), updateCellsRequest.getStart().getRowIndex());

    PivotTable pivotTable = updateCellsRequest.getRows().get(0).getValues().get(0).getPivotTable();
    assertSampleValueGroup(pivotTable);
}
 

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

    Exchange inbound = new DefaultExchange(createCamelContext());

    GooglePivotTable model = new GooglePivotTable();
    model.setSpreadsheetId(getSpreadsheetId());
    model.setSourceSheetId(0);
    model.setSheetId(1);

    model.setSourceRange("A1:D10");
    model.setValueGroups(Collections.singletonList(sampleValueGroup()));

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

    UpdateCellsRequest updateCellsRequest = batchUpdateRequest.getRequests().get(0).getUpdateCells();

    Assert.assertEquals(Integer.valueOf(1), updateCellsRequest.getStart().getSheetId());
    Assert.assertEquals(Integer.valueOf(0), updateCellsRequest.getStart().getColumnIndex());
    Assert.assertEquals(Integer.valueOf(0), updateCellsRequest.getStart().getRowIndex());

    PivotTable pivotTable = updateCellsRequest.getRows().get(0).getValues().get(0).getPivotTable();
    assertSampleValueGroup(pivotTable);
}
 

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

    Exchange inbound = new DefaultExchange(createCamelContext());

    GooglePivotTable model = new GooglePivotTable();
    model.setStart("E10");
    model.setSpreadsheetId(getSpreadsheetId());
    model.setSourceSheetId(1);
    model.setSheetId(0);

    model.setSourceRange("A1:D10");
    model.setValueGroups(Collections.singletonList(sampleValueGroup()));

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

    UpdateCellsRequest updateCellsRequest = batchUpdateRequest.getRequests().get(0).getUpdateCells();

    Assert.assertEquals(Integer.valueOf(0), updateCellsRequest.getStart().getSheetId());
    Assert.assertEquals(Integer.valueOf(4), updateCellsRequest.getStart().getColumnIndex());
    Assert.assertEquals(Integer.valueOf(9), updateCellsRequest.getStart().getRowIndex());

    PivotTable pivotTable = updateCellsRequest.getRows().get(0).getValues().get(0).getPivotTable();
    assertSampleValueGroup(pivotTable);
}
 

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

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

    Exchange inbound = new DefaultExchange(createCamelContext());

    GooglePivotTable model = new GooglePivotTable();
    model.setSpreadsheetId(getSpreadsheetId());
    model.setSourceSheetId(0);
    model.setSheetId(0);

    model.setSourceRange("A1:G10");

    GooglePivotTable.PivotGroup rowGroup = new GooglePivotTable.PivotGroup();
    rowGroup.setLabel("RowGROUP");
    rowGroup.setSourceColumn("A");
    rowGroup.setValueBucket("Bucket");
    rowGroup.setShowTotals(false);
    rowGroup.setSortOrder("DESCENDING");

    model.setRowGroups(Collections.singletonList(rowGroup));

    GooglePivotTable.PivotGroup columnGroup = new GooglePivotTable.PivotGroup();
    columnGroup.setLabel("ColumnGROUP");
    columnGroup.setSourceColumn("E");

    model.setColumnGroups(Collections.singletonList(columnGroup));

    GooglePivotTable.ValueGroup valueGroup = new GooglePivotTable.ValueGroup();
    valueGroup.setSourceColumn("D");
    model.setValueGroups(Collections.singletonList(valueGroup));

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

    UpdateCellsRequest updateCellsRequest = batchUpdateRequest.getRequests().get(0).getUpdateCells();

    Assert.assertEquals(Integer.valueOf(0), updateCellsRequest.getStart().getSheetId());
    Assert.assertEquals(Integer.valueOf(8), updateCellsRequest.getStart().getColumnIndex());
    Assert.assertEquals(Integer.valueOf(0), updateCellsRequest.getStart().getRowIndex());

    PivotTable pivotTable = updateCellsRequest.getRows().get(0).getValues().get(0).getPivotTable();
    Assert.assertEquals("HORIZONTAL", pivotTable.getValueLayout());

    Assert.assertEquals(Integer.valueOf(0), pivotTable.getSource().getStartRowIndex());
    Assert.assertEquals(Integer.valueOf(10), pivotTable.getSource().getEndRowIndex());
    Assert.assertEquals(Integer.valueOf(0), pivotTable.getSource().getStartColumnIndex());
    Assert.assertEquals(Integer.valueOf(7), pivotTable.getSource().getEndColumnIndex());

    Assert.assertEquals(1, pivotTable.getRows().size());
    Assert.assertEquals("RowGROUP", pivotTable.getRows().get(0).getLabel());
    Assert.assertEquals(Integer.valueOf(0), pivotTable.getRows().get(0).getSourceColumnOffset());
    Assert.assertEquals("DESCENDING", pivotTable.getRows().get(0).getSortOrder());
    Assert.assertFalse(pivotTable.getRows().get(0).getShowTotals());
    Assert.assertNotNull(pivotTable.getRows().get(0).getValueBucket());
    Assert.assertEquals("Bucket", pivotTable.getRows().get(0).getValueBucket().getBuckets().get(0).getStringValue());


    Assert.assertEquals(1, pivotTable.getColumns().size());
    Assert.assertEquals("ColumnGROUP", pivotTable.getColumns().get(0).getLabel());
    Assert.assertEquals(Integer.valueOf(4), pivotTable.getColumns().get(0).getSourceColumnOffset());
    Assert.assertEquals("ASCENDING", pivotTable.getColumns().get(0).getSortOrder());
    Assert.assertTrue(pivotTable.getColumns().get(0).getShowTotals());
    Assert.assertNull(pivotTable.getColumns().get(0).getValueBucket());

    Assert.assertEquals(1, pivotTable.getValues().size());
    Assert.assertEquals("SUM", pivotTable.getValues().get(0).getSummarizeFunction());
    Assert.assertEquals(Integer.valueOf(3), pivotTable.getValues().get(0).getSourceColumnOffset());
}
 

@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 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 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 testBeforeProducerFromModel() throws Exception {
    customizer.customize(getComponent(), new HashMap<>());

    Exchange inbound = new DefaultExchange(createCamelContext());

    GoogleSpreadsheet model = new GoogleSpreadsheet();
    model.setSpreadsheetId(getSpreadsheetId());
    model.setTitle("SyndesisTest");
    model.setTimeZone("America/New_York");
    model.setLocale("en");

    GoogleSheet sheet1 = new GoogleSheet();
    sheet1.setTitle("Sheet1");
    sheet1.setSheetId(1);
    sheet1.setIndex(1);

    GoogleSheet sheet2 = new GoogleSheet();
    sheet2.setTitle("Sheet2");
    sheet2.setSheetId(2);

    model.setSheets(Arrays.asList(sheet1, sheet2));

    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(3, batchUpdateRequest.getRequests().size());

    UpdateSpreadsheetPropertiesRequest updateSpreadsheetPropertiesRequest = batchUpdateRequest.getRequests().get(0).getUpdateSpreadsheetProperties();
    Assert.assertEquals("title,timeZone,locale", updateSpreadsheetPropertiesRequest.getFields());
    Assert.assertEquals("SyndesisTest", updateSpreadsheetPropertiesRequest.getProperties().getTitle());
    Assert.assertEquals("America/New_York", updateSpreadsheetPropertiesRequest.getProperties().getTimeZone());
    Assert.assertEquals("en", updateSpreadsheetPropertiesRequest.getProperties().getLocale());

    UpdateSheetPropertiesRequest updateSheetPropertiesRequest = batchUpdateRequest.getRequests().get(1).getUpdateSheetProperties();
    Assert.assertEquals("title", updateSheetPropertiesRequest.getFields());
    Assert.assertEquals(Integer.valueOf(1), updateSheetPropertiesRequest.getProperties().getIndex());
    Assert.assertEquals(Integer.valueOf(1), updateSheetPropertiesRequest.getProperties().getSheetId());
    Assert.assertEquals("Sheet1", updateSheetPropertiesRequest.getProperties().getTitle());

    updateSheetPropertiesRequest = batchUpdateRequest.getRequests().get(2).getUpdateSheetProperties();
    Assert.assertEquals("title", updateSheetPropertiesRequest.getFields());
    Assert.assertNull(updateSheetPropertiesRequest.getProperties().getIndex());
    Assert.assertEquals(Integer.valueOf(2), updateSheetPropertiesRequest.getProperties().getSheetId());
    Assert.assertEquals("Sheet2", updateSheetPropertiesRequest.getProperties().getTitle());
}
 

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

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

    if (model != null) {
        if (ObjectHelper.isNotEmpty(model.getSpreadsheetId())) {
            spreadsheetId = model.getSpreadsheetId();
        }
        if (ObjectHelper.isNotEmpty(model.getTitle())) {
            title = model.getTitle();
        }
        if (ObjectHelper.isNotEmpty(model.getTimeZone())) {
            timeZone = model.getTimeZone();
        }
        if (ObjectHelper.isNotEmpty(model.getLocale())) {
            locale = model.getLocale();
        }
    }

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

    List<String> fields = new ArrayList<>();
    SpreadsheetProperties spreadsheetProperties = getSpreadsheetProperties(fields);
    if (ObjectHelper.isNotEmpty(fields)) {
        UpdateSpreadsheetPropertiesRequest updatePropertiesRequest = new UpdateSpreadsheetPropertiesRequest();
        updatePropertiesRequest.setProperties(spreadsheetProperties);
        updatePropertiesRequest.setFields(String.join(",", fields));
        batchUpdateRequest.getRequests().add(new Request().setUpdateSpreadsheetProperties(updatePropertiesRequest));
    }

    if (model != null && ObjectHelper.isNotEmpty(model.getSheets())) {
        for (GoogleSheet sheet : model.getSheets()) {
            UpdateSheetPropertiesRequest updateSheetPropertiesRequest = new UpdateSheetPropertiesRequest();
            SheetProperties properties = new SheetProperties();
            properties.setSheetId(sheet.getSheetId());
            properties.setIndex(sheet.getIndex());
            properties.setTitle(sheet.getTitle());
            updateSheetPropertiesRequest.setProperties(properties);
            updateSheetPropertiesRequest.setFields("title");
            batchUpdateRequest.getRequests().add(new Request().setUpdateSheetProperties(updateSheetPropertiesRequest));
        }
    }

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