com.google.api.services.sheets.v4.model.ValueRange Java Exaples

Source File: GoogleSpreadsheetWriter.java From SPDS with Eclipse Public License 2.0

7 votes

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

Source File: GoogleSheetsWorkitemHandlerTest.java From jbpm-work-items with Apache License 2.0

6 votes

@Before
public void setUp() {
    try {
        ValueRange valueRange = new ValueRange();
        List<List<Object>> testValues = new ArrayList<>();
        List<Object> testRowValues = new ArrayList<>();
        testRowValues.add("testValueOne");
        testRowValues.add("testValueTwo");
        testValues.add(testRowValues);
        valueRange.setValues(testValues);

        when(auth.getSheetsService(anyString(),
                                   anyString())).thenReturn(sheetsClient);
        when(sheetsClient.spreadsheets()).thenReturn(spreadsheets);
        when(spreadsheets.values()).thenReturn(spreasheetsValues);
        when(spreasheetsValues.get(anyString(),
                                   anyString())).thenReturn(spreasheetsValuesGet);
        when(spreasheetsValuesGet.execute()).thenReturn(valueRange);
    } catch (Exception e) {
        fail(e.getMessage());
    }
}

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

6 votes

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

    customizer.customize(getComponent(), options);

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

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

    Assert.assertEquals(getSpreadsheetId(), inbound.getIn().getHeader(GoogleSheetsStreamConstants.SPREADSHEET_ID));
    Assert.assertEquals("A1", inbound.getIn().getHeader(GoogleSheetsStreamConstants.RANGE));
    Assert.assertEquals(RangeCoordinate.DIMENSION_ROWS, inbound.getIn().getHeader(GoogleSheetsStreamConstants.MAJOR_DIMENSION));
    Assert.assertEquals("RAW", inbound.getIn().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX + "valueInputOption"));

    ValueRange valueRange = (ValueRange) inbound.getIn().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX + "values");
    Assert.assertEquals(0L, valueRange.getValues().size());
}

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

6 votes

@Test
public void testBeforeProducerWithJsonObject() throws Exception {
    Map<String, Object> options = new HashMap<>();
    options.put("range", "A1:B2");

    customizer.customize(getComponent(), options);

    Exchange inbound = new DefaultExchange(createCamelContext());

    String body = "{\"spreadsheetId\": \"" + getSpreadsheetId() + "\", \"A\": \"a1\", \"B\": \"b1\" }";
    inbound.getIn().setBody(body);

    getComponent().getBeforeProducer().process(inbound);

    Assert.assertEquals("A1:B2", inbound.getIn().getHeader(GoogleSheetsStreamConstants.RANGE));
    Assert.assertEquals(RangeCoordinate.DIMENSION_ROWS, inbound.getIn().getHeader(GoogleSheetsStreamConstants.MAJOR_DIMENSION));
    Assert.assertEquals("USER_ENTERED", inbound.getIn().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX + "valueInputOption"));

    ValueRange valueRange = (ValueRange) inbound.getIn().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX + "values");
    Assert.assertEquals(1L, valueRange.getValues().size());
    Assert.assertEquals(2L, valueRange.getValues().get(0).size());
    Assert.assertEquals("a1", valueRange.getValues().get(0).get(0));
    Assert.assertEquals("b1", valueRange.getValues().get(0).get(1));
}

Source File: SheetSynchronizerTest.java From nomulus with Apache License 2.0

6 votes

@Before
public void before() throws Exception {
  sheetSynchronizer.sheetsService = sheetsService;
  when(sheetsService.spreadsheets()).thenReturn(spreadsheets);
  when(spreadsheets.values()).thenReturn(values);

  when(values.get(any(String.class), any(String.class))).thenReturn(getReq);
  when(values.append(any(String.class), any(String.class), any(ValueRange.class)))
      .thenReturn(appendReq);
  when(values.clear(any(String.class), any(String.class), any(ClearValuesRequest.class)))
      .thenReturn(clearReq);
  when(values.batchUpdate(any(String.class), any(BatchUpdateValuesRequest.class)))
      .thenReturn(updateReq);

  when(appendReq.execute()).thenReturn(new AppendValuesResponse());
  when(appendReq.setValueInputOption(any(String.class))).thenReturn(appendReq);
  when(appendReq.setInsertDataOption(any(String.class))).thenReturn(appendReq);
  when(clearReq.execute()).thenReturn(new ClearValuesResponse());
  when(updateReq.execute()).thenReturn(new BatchUpdateValuesResponse());

  existingSheet = newArrayList();
  data = ImmutableList.of();
  ValueRange valueRange = new ValueRange().setValues(existingSheet);
  when(getReq.execute()).thenReturn(valueRange);
}

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

6 votes

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

    customizer.customize(getComponent(), options);

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

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

    Assert.assertEquals(getSpreadsheetId(), inbound.getIn().getHeader(GoogleSheetsStreamConstants.SPREADSHEET_ID));
    Assert.assertEquals("A1", inbound.getIn().getHeader(GoogleSheetsStreamConstants.RANGE));
    Assert.assertEquals(RangeCoordinate.DIMENSION_ROWS, inbound.getIn().getHeader(GoogleSheetsStreamConstants.MAJOR_DIMENSION));
    Assert.assertEquals("RAW", inbound.getIn().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX + "valueInputOption"));

    ValueRange valueRange = (ValueRange) inbound.getIn().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX + "values");
    Assert.assertEquals(0L, valueRange.getValues().size());
}

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

6 votes

@Test
public void testUpdateValuesCustomizer() throws Exception {
    Map<String, Object> options = new HashMap<>();
    options.put("columnNames", columnNames);
    options.put("range", range);

    GoogleSheetsUpdateValuesCustomizer customizer = new GoogleSheetsUpdateValuesCustomizer();
    customizer.customize(getComponent(), options);

    Exchange inbound = new DefaultExchange(createCamelContext());
    inbound.getIn().setBody(model);

    getComponent().getBeforeProducer().process(inbound);

    Assertions.assertThat(inbound.getIn().getHeader(GoogleSheetsStreamConstants.RANGE)).isEqualTo(range);
    Assertions.assertThat(inbound.getIn().getHeader(GoogleSheetsStreamConstants.MAJOR_DIMENSION)).isEqualTo(RangeCoordinate.DIMENSION_ROWS);

    ValueRange valueRange = (ValueRange) inbound.getIn().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX + "values");
    Assertions.assertThat(valueRange.getValues()).hasSize(values.size());

    for (List<Object> rowValues : values) {
        Assertions.assertThat(valueRange.getValues()).contains(rowValues);
    }
}

Source File: SheetSynchronizerTest.java From nomulus with Apache License 2.0

6 votes

@Test
public void testSynchronize_differentValues_updatesValues() throws Exception {
  existingSheet.add(createRow("a", "b"));
  existingSheet.add(createRow("diffVal1l", "diffVal2"));
  data = ImmutableList.of(ImmutableMap.of("a", "val1", "b", "val2"));
  sheetSynchronizer.synchronize("aSheetId", data);

  verifyZeroInteractions(appendReq);
  verifyZeroInteractions(clearReq);

  BatchUpdateValuesRequest expectedRequest = new BatchUpdateValuesRequest();
  List<List<Object>> expectedVals = newArrayList();
  expectedVals.add(createRow("val1", "val2"));
  expectedRequest.setData(
      newArrayList(new ValueRange().setRange("Registrars!A2").setValues(expectedVals)));
  expectedRequest.setValueInputOption("RAW");
  verify(values).batchUpdate("aSheetId", expectedRequest);
}

Source File: SheetSynchronizerTest.java From nomulus with Apache License 2.0

6 votes

@Test
public void testSynchronize_unknownFields_doesntUpdate() throws Exception {
  existingSheet.add(createRow("a", "c", "b"));
  existingSheet.add(createRow("diffVal1", "sameVal", "diffVal2"));
  data = ImmutableList.of(ImmutableMap.of("a", "val1", "b", "val2", "d", "val3"));
  sheetSynchronizer.synchronize("aSheetId", data);

  verifyZeroInteractions(appendReq);
  verifyZeroInteractions(clearReq);

  BatchUpdateValuesRequest expectedRequest = new BatchUpdateValuesRequest();
  List<List<Object>> expectedVals = newArrayList();
  expectedVals.add(createRow("val1", "sameVal", "val2"));
  expectedRequest.setData(
      newArrayList(new ValueRange().setRange("Registrars!A2").setValues(expectedVals)));
  expectedRequest.setValueInputOption("RAW");
  verify(values).batchUpdate("aSheetId", expectedRequest);
}

Source File: SheetSynchronizerTest.java From nomulus with Apache License 2.0

6 votes

@Test
public void testSynchronize_notFullRow_getsPadded() throws Exception {
  existingSheet.add(createRow("a", "c", "b"));
  existingSheet.add(createRow("diffVal1", "diffVal2"));
  data = ImmutableList.of(ImmutableMap.of("a", "val1", "b", "paddedVal", "d", "val3"));
  sheetSynchronizer.synchronize("aSheetId", data);

  verifyZeroInteractions(appendReq);
  verifyZeroInteractions(clearReq);

  BatchUpdateValuesRequest expectedRequest = new BatchUpdateValuesRequest();
  List<List<Object>> expectedVals = newArrayList();
  expectedVals.add(createRow("val1", "diffVal2", "paddedVal"));
  expectedRequest.setData(
      newArrayList(new ValueRange().setRange("Registrars!A2").setValues(expectedVals)));
  expectedRequest.setValueInputOption("RAW");
  verify(values).batchUpdate("aSheetId", expectedRequest);
}

Source File: SheetSynchronizerTest.java From nomulus with Apache License 2.0

6 votes

@Test
public void testSynchronize_moreData_appendsValues() throws Exception {
  existingSheet.add(createRow("a", "b"));
  existingSheet.add(createRow("diffVal1", "diffVal2"));
  data = ImmutableList.of(
      ImmutableMap.of("a", "val1", "b", "val2"),
      ImmutableMap.of("a", "val3", "b", "val4"));
  sheetSynchronizer.synchronize("aSheetId", data);

  verifyZeroInteractions(clearReq);

  BatchUpdateValuesRequest expectedRequest = new BatchUpdateValuesRequest();
  List<List<Object>> updatedVals = newArrayList();
  updatedVals.add(createRow("val1", "val2"));
  expectedRequest.setData(
      newArrayList(
          new ValueRange().setRange("Registrars!A2").setValues(updatedVals)));
  expectedRequest.setValueInputOption("RAW");
  verify(values).batchUpdate("aSheetId", expectedRequest);

  List<List<Object>> appendedVals = newArrayList();
  appendedVals.add(createRow("val3", "val4"));
  ValueRange appendRequest = new ValueRange().setValues(appendedVals);
  verify(values).append("aSheetId", "Registrars!A3", appendRequest);
}

Source File: GoogleSpreadsheetWriter.java From SPDS with Eclipse Public License 2.0

5 votes

public static void computeMetrics() throws IOException, GeneralSecurityException {
	Sheets service = getService();
	String sheetID = getGitRepositoryState().commitId;
	ArrayList<List<Object>> rows = Lists.newArrayList();
	ArrayList<Object> content = Lists.newArrayList();
	content.add(getGitRepositoryState().buildHost);
	content.add(getGitRepositoryState().buildTime);
	content.add(getGitRepositoryState().branch);
	content.add(sheetID);
	content.add("=GEOMITTEL('"+sheetID+"'!J2:J1004)");
	rows.add(content);
	ValueRange body = new ValueRange().setValues(rows);
	service.spreadsheets().values().append(SPREADSHEET_ID, "history", body).setValueInputOption("USER_ENTERED")
			.execute();
}

Source File: GoogleSpreadsheetWriter.java From SPDS with Eclipse Public License 2.0

5 votes

/**
 * Prints the names and majors of students in a sample spreadsheet:
 * https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
 * @throws GeneralSecurityException 
 * @throws IOException 
 */
public static void write(List<Object> data) throws IOException, GeneralSecurityException  {
	Sheets service = getService();
	String sheetID = getGitRepositoryState().commitId;
	ArrayList<List<Object>> rows = Lists.newArrayList();
	rows.add(data);
	ValueRange body = new ValueRange().setValues(rows);
	service.spreadsheets().values().append(SPREADSHEET_ID, sheetID, body).setValueInputOption("USER_ENTERED")
			.execute();
}

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

5 votes

@Test
public void testBeforeProducerMultipleColumns() throws Exception {
    Map<String, Object> options = new HashMap<>();
    options.put("range", "A1:B2");
    options.put("majorDimension", RangeCoordinate.DIMENSION_COLUMNS);

    customizer.customize(getComponent(), options);

    Exchange inbound = new DefaultExchange(createCamelContext());

    List<String> model = Arrays.asList("{" +
                    "\"spreadsheetId\": \"" + getSpreadsheetId() + "\"," +
                    "\"#1\": \"a1\"," +
                    "\"#2\": \"a2\"" +
                "}",
                "{" +
                    "\"spreadsheetId\": \"" + getSpreadsheetId() + "\"," +
                    "\"#1\": \"b1\"," +
                    "\"#2\": \"b2\"" +
                "}");

    inbound.getIn().setBody(model);

    getComponent().getBeforeProducer().process(inbound);

    Assert.assertEquals("A1:B2", inbound.getIn().getHeader(GoogleSheetsStreamConstants.RANGE));
    Assert.assertEquals(RangeCoordinate.DIMENSION_COLUMNS, inbound.getIn().getHeader(GoogleSheetsStreamConstants.MAJOR_DIMENSION));
    Assert.assertEquals("USER_ENTERED", inbound.getIn().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX + "valueInputOption"));

    ValueRange valueRange = (ValueRange) inbound.getIn().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX + "values");
    Assert.assertEquals(2L, valueRange.getValues().size());
    Assert.assertEquals(2L, valueRange.getValues().get(0).size());
    Assert.assertEquals("a1", valueRange.getValues().get(0).get(0));
    Assert.assertEquals("a2", valueRange.getValues().get(0).get(1));
    Assert.assertEquals(2L, valueRange.getValues().get(1).size());
    Assert.assertEquals("b1", valueRange.getValues().get(1).get(0));
    Assert.assertEquals("b2", valueRange.getValues().get(1).get(1));
}

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

5 votes

@Test
public void testBeforeProducerMultipleRows() throws Exception {
    Map<String, Object> options = new HashMap<>();
    options.put("range", "A1:B2");

    customizer.customize(getComponent(), options);

    Exchange inbound = new DefaultExchange(createCamelContext());

    List<String> model = Arrays.asList("{" +
                    "\"spreadsheetId\": \"" + getSpreadsheetId() + "\"," +
                    "\"A\": \"a1\"," +
                    "\"B\": \"b1\"" +
                "}",
                "{" +
                    "\"spreadsheetId\": \"" + getSpreadsheetId() + "\"," +
                    "\"A\": \"a2\"," +
                    "\"B\": \"b2\"" +
                "}");
    inbound.getIn().setBody(model);

    getComponent().getBeforeProducer().process(inbound);

    Assert.assertEquals("A1:B2", inbound.getIn().getHeader(GoogleSheetsStreamConstants.RANGE));
    Assert.assertEquals(RangeCoordinate.DIMENSION_ROWS, inbound.getIn().getHeader(GoogleSheetsStreamConstants.MAJOR_DIMENSION));
    Assert.assertEquals("USER_ENTERED", inbound.getIn().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX + "valueInputOption"));

    ValueRange valueRange = (ValueRange) inbound.getIn().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX + "values");
    Assert.assertEquals(2L, valueRange.getValues().size());
    Assert.assertEquals(2L, valueRange.getValues().get(0).size());
    Assert.assertEquals("a1", valueRange.getValues().get(0).get(0));
    Assert.assertEquals("b1", valueRange.getValues().get(0).get(1));
    Assert.assertEquals(2L, valueRange.getValues().get(1).size());
    Assert.assertEquals("a2", valueRange.getValues().get(1).get(0));
    Assert.assertEquals("b2", valueRange.getValues().get(1).get(1));
}

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

5 votes

@Test
public void testBeforeProducerColumnDimension() throws Exception {
    Map<String, Object> options = new HashMap<>();
    options.put("range", "A1:A2");
    options.put("majorDimension", RangeCoordinate.DIMENSION_COLUMNS);

    customizer.customize(getComponent(), options);

    Exchange inbound = new DefaultExchange(createCamelContext());

    String model = "{" +
                        "\"spreadsheetId\": \"" + getSpreadsheetId() + "\"," +
                        "\"#1\": \"a1\"," +
                        "\"#2\": \"a2\"" +
                    "}";
    inbound.getIn().setBody(model);

    getComponent().getBeforeProducer().process(inbound);

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

    Assert.assertEquals(getSpreadsheetId(), inbound.getIn().getHeader(GoogleSheetsStreamConstants.SPREADSHEET_ID));
    Assert.assertEquals("A1:A2", inbound.getIn().getHeader(GoogleSheetsStreamConstants.RANGE));
    Assert.assertEquals(RangeCoordinate.DIMENSION_COLUMNS, inbound.getIn().getHeader(GoogleSheetsStreamConstants.MAJOR_DIMENSION));
    Assert.assertEquals("USER_ENTERED", inbound.getIn().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX + "valueInputOption"));

    ValueRange valueRange = (ValueRange) inbound.getIn().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX + "values");
    Assert.assertEquals(1L, valueRange.getValues().size());
    Assert.assertEquals("a1", valueRange.getValues().get(0).get(0));
    Assert.assertEquals("a2", valueRange.getValues().get(0).get(1));
}

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

5 votes

@Test
public void testBeforeProducerRowDimension() throws Exception {
    Map<String, Object> options = new HashMap<>();
    options.put("range", "A1:B1");

    customizer.customize(getComponent(), options);

    Exchange inbound = new DefaultExchange(createCamelContext());

    String model = "{" +
                        "\"spreadsheetId\": \"" + getSpreadsheetId() + "\"," +
                        "\"A\": \"a1\"," +
                        "\"B\": \"b1\"" +
                    "}";
    inbound.getIn().setBody(model);

    getComponent().getBeforeProducer().process(inbound);

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

    Assert.assertEquals(getSpreadsheetId(), inbound.getIn().getHeader(GoogleSheetsStreamConstants.SPREADSHEET_ID));
    Assert.assertEquals("A1:B1", inbound.getIn().getHeader(GoogleSheetsStreamConstants.RANGE));
    Assert.assertEquals(RangeCoordinate.DIMENSION_ROWS, inbound.getIn().getHeader(GoogleSheetsStreamConstants.MAJOR_DIMENSION));
    Assert.assertEquals("USER_ENTERED", inbound.getIn().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX + "valueInputOption"));

    ValueRange valueRange = (ValueRange) inbound.getIn().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX + "values");
    Assert.assertEquals(1L, valueRange.getValues().size());
    Assert.assertEquals("a1", valueRange.getValues().get(0).get(0));
    Assert.assertEquals("b1", valueRange.getValues().get(0).get(1));
}

Source File: GoogleSheetsLiveTest.java From tutorials with MIT License

5 votes

@Test
public void whenWriteSheet_thenReadSheetOk() throws IOException {
    ValueRange body = new ValueRange().setValues(Arrays.asList(Arrays.asList("Expenses January"), Arrays.asList("books", "30"), Arrays.asList("pens", "10"), Arrays.asList("Expenses February"), Arrays.asList("clothes", "20"), Arrays.asList("shoes", "5")));
    UpdateValuesResponse result = sheetsService.spreadsheets().values().update(SPREADSHEET_ID, "A1", body).setValueInputOption("RAW").execute();

    List<ValueRange> data = new ArrayList<>();
    data.add(new ValueRange().setRange("D1").setValues(Arrays.asList(Arrays.asList("January Total", "=B2+B3"))));
    data.add(new ValueRange().setRange("D4").setValues(Arrays.asList(Arrays.asList("February Total", "=B5+B6"))));

    BatchUpdateValuesRequest batchBody = new BatchUpdateValuesRequest().setValueInputOption("USER_ENTERED").setData(data);
    BatchUpdateValuesResponse batchResult = sheetsService.spreadsheets().values().batchUpdate(SPREADSHEET_ID, batchBody).execute();

    List<String> ranges = Arrays.asList("E1", "E4");
    BatchGetValuesResponse readResult = sheetsService.spreadsheets().values().batchGet(SPREADSHEET_ID).setRanges(ranges).execute();

    ValueRange januaryTotal = readResult.getValueRanges().get(0);
    assertThat(januaryTotal.getValues().get(0).get(0)).isEqualTo("40");

    ValueRange febTotal = readResult.getValueRanges().get(1);
    assertThat(febTotal.getValues().get(0).get(0)).isEqualTo("25");

    ValueRange appendBody = new ValueRange().setValues(Arrays.asList(Arrays.asList("Total", "=E1+E4")));
    AppendValuesResponse appendResult = sheetsService.spreadsheets().values().append(SPREADSHEET_ID, "A1", appendBody).setValueInputOption("USER_ENTERED").setInsertDataOption("INSERT_ROWS").setIncludeValuesInResponse(true).execute();

    ValueRange total = appendResult.getUpdates().getUpdatedData();
    assertThat(total.getValues().get(0).get(1)).isEqualTo("65");
}

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

5 votes

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

    customizer.customize(getComponent(), options);

    Exchange inbound = new DefaultExchange(createCamelContext());

    ValueRange valueRange = new ValueRange();
    valueRange.setRange(sheetName + "!" + range);
    valueRange.setMajorDimension(majorDimension);
    valueRange.setValues(values);

    inbound.getIn().setBody(valueRange);
    getComponent().getBeforeConsumer().process(inbound);

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

    @SuppressWarnings("unchecked")
    List<String> model = inbound.getIn().getBody(List.class);
    Assert.assertEquals(expectedValueModel.size(), model.size());
    Iterator<String> modelIterator = model.iterator();
    for (String expected : expectedValueModel) {
        assertThatJson(modelIterator.next()).isEqualTo(String.format(expected, getSpreadsheetId()));
    }
}

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

5 votes

@Test
public void testBeforeProducerWithJsonArray() throws Exception {
    Map<String, Object> options = new HashMap<>();
    options.put("range", "A1:B2");

    customizer.customize(getComponent(), options);

    Exchange inbound = new DefaultExchange(createCamelContext());

    String body = "[{" +
                        "\"spreadsheetId\": \"" + getSpreadsheetId() + "\"," +
                        "\"A\": \"a1\"," +
                        "\"B\": \"b1\"" +
                    "}," +
                    "{" +
                        "\"spreadsheetId\": \"" + getSpreadsheetId() + "\"," +
                        "\"A\": \"a2\"," +
                        "\"B\": \"b2\"" +
                    "}]";
    inbound.getIn().setBody(body);

    getComponent().getBeforeProducer().process(inbound);

    Assert.assertEquals("A1:B2", inbound.getIn().getHeader(GoogleSheetsStreamConstants.RANGE));
    Assert.assertEquals(RangeCoordinate.DIMENSION_ROWS, inbound.getIn().getHeader(GoogleSheetsStreamConstants.MAJOR_DIMENSION));
    Assert.assertEquals("USER_ENTERED", inbound.getIn().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX + "valueInputOption"));

    ValueRange valueRange = (ValueRange) inbound.getIn().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX + "values");
    Assert.assertEquals(2L, valueRange.getValues().size());
    Assert.assertEquals(2L, valueRange.getValues().get(0).size());
    Assert.assertEquals("a1", valueRange.getValues().get(0).get(0));
    Assert.assertEquals("b1", valueRange.getValues().get(0).get(1));
    Assert.assertEquals(2L, valueRange.getValues().get(1).size());
    Assert.assertEquals("a2", valueRange.getValues().get(1).get(0));
    Assert.assertEquals("b2", valueRange.getValues().get(1).get(1));
}

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

5 votes

@Test
public void testBeforeProducerAutoFillRowValues() throws Exception {
    Map<String, Object> options = new HashMap<>();
    options.put("range", "A1:C3");
    options.put("majorDimension", RangeCoordinate.DIMENSION_COLUMNS);

    customizer.customize(getComponent(), options);

    Exchange inbound = new DefaultExchange(createCamelContext());

    List<String> model = Arrays.asList("{" +
                "\"spreadsheetId\": \"" + getSpreadsheetId() + "\"," +
                "\"#1\": \"a1\"," +
                "\"#3\": \"c1\"" +
            "}",
            "{" +
                "\"spreadsheetId\": \"" + getSpreadsheetId() + "\"," +
                "\"#1\": \"a2\"," +
                "\"#2\": \"b2\"" +
            "}");

    inbound.getIn().setBody(model);

    getComponent().getBeforeProducer().process(inbound);

    Assert.assertEquals("A1:C3", inbound.getIn().getHeader(GoogleSheetsStreamConstants.RANGE));
    Assert.assertEquals(RangeCoordinate.DIMENSION_COLUMNS, inbound.getIn().getHeader(GoogleSheetsStreamConstants.MAJOR_DIMENSION));
    Assert.assertEquals("USER_ENTERED", inbound.getIn().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX + "valueInputOption"));

    ValueRange valueRange = (ValueRange) inbound.getIn().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX + "values");
    Assert.assertEquals(2L, valueRange.getValues().size());
    Assert.assertEquals(3L, valueRange.getValues().get(0).size());
    Assert.assertEquals("a1", valueRange.getValues().get(0).get(0));
    Assert.assertEquals("", valueRange.getValues().get(0).get(1));
    Assert.assertEquals("c1", valueRange.getValues().get(0).get(2));
    Assert.assertEquals(3L, valueRange.getValues().get(1).size());
    Assert.assertEquals("a2", valueRange.getValues().get(1).get(0));
    Assert.assertEquals("b2", valueRange.getValues().get(1).get(1));
    Assert.assertEquals("", valueRange.getValues().get(1).get(2));
}

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

5 votes

@Test
public void testBeforeProducerAutoFillColumnValues() throws Exception {
    Map<String, Object> options = new HashMap<>();
    options.put("range", "A1:C2");

    customizer.customize(getComponent(), options);

    Exchange inbound = new DefaultExchange(createCamelContext());

    List<String> model = Arrays.asList("{" +
                "\"spreadsheetId\": \"" + getSpreadsheetId() + "\"," +
                "\"A\": \"a1\"," +
                "\"C\": \"c1\"" +
            "}",
            "{" +
                "\"spreadsheetId\": \"" + getSpreadsheetId() + "\"," +
                "\"A\": \"a2\"," +
                "\"B\": \"b2\"" +
            "}");

    inbound.getIn().setBody(model);

    getComponent().getBeforeProducer().process(inbound);

    Assert.assertEquals("A1:C2", inbound.getIn().getHeader(GoogleSheetsStreamConstants.RANGE));
    Assert.assertEquals(RangeCoordinate.DIMENSION_ROWS, inbound.getIn().getHeader(GoogleSheetsStreamConstants.MAJOR_DIMENSION));
    Assert.assertEquals("USER_ENTERED", inbound.getIn().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX + "valueInputOption"));

    ValueRange valueRange = (ValueRange) inbound.getIn().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX + "values");
    Assert.assertEquals(2L, valueRange.getValues().size());
    Assert.assertEquals(3L, valueRange.getValues().get(0).size());
    Assert.assertEquals("a1", valueRange.getValues().get(0).get(0));
    Assert.assertEquals("", valueRange.getValues().get(0).get(1));
    Assert.assertEquals("c1", valueRange.getValues().get(0).get(2));
    Assert.assertEquals(3L, valueRange.getValues().get(1).size());
    Assert.assertEquals("a2", valueRange.getValues().get(1).get(0));
    Assert.assertEquals("b2", valueRange.getValues().get(1).get(1));
    Assert.assertEquals("", valueRange.getValues().get(1).get(2));
}

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

5 votes

@Test
public void testBeforeProducerMultipleColumns() throws Exception {
    Map<String, Object> options = new HashMap<>();
    options.put("range", "A1:B2");
    options.put("majorDimension", RangeCoordinate.DIMENSION_COLUMNS);

    customizer.customize(getComponent(), options);

    Exchange inbound = new DefaultExchange(createCamelContext());

    List<String> model = Arrays.asList("{" +
                    "\"spreadsheetId\": \"" + getSpreadsheetId() + "\"," +
                    "\"#1\": \"a1\"," +
                    "\"#2\": \"a2\"" +
                "}",
                "{" +
                    "\"spreadsheetId\": \"" + getSpreadsheetId() + "\"," +
                    "\"#1\": \"b1\"," +
                    "\"#2\": \"b2\"" +
                "}");

    inbound.getIn().setBody(model);

    getComponent().getBeforeProducer().process(inbound);

    Assert.assertEquals("A1:B2", inbound.getIn().getHeader(GoogleSheetsStreamConstants.RANGE));
    Assert.assertEquals(RangeCoordinate.DIMENSION_COLUMNS, inbound.getIn().getHeader(GoogleSheetsStreamConstants.MAJOR_DIMENSION));
    Assert.assertEquals("USER_ENTERED", inbound.getIn().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX + "valueInputOption"));

    ValueRange valueRange = (ValueRange) inbound.getIn().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX + "values");
    Assert.assertEquals(2L, valueRange.getValues().size());
    Assert.assertEquals(2L, valueRange.getValues().get(0).size());
    Assert.assertEquals("a1", valueRange.getValues().get(0).get(0));
    Assert.assertEquals("a2", valueRange.getValues().get(0).get(1));
    Assert.assertEquals(2L, valueRange.getValues().get(1).size());
    Assert.assertEquals("b1", valueRange.getValues().get(1).get(0));
    Assert.assertEquals("b2", valueRange.getValues().get(1).get(1));
}

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

5 votes

@Test
public void testBeforeProducerMultipleRows() throws Exception {
    Map<String, Object> options = new HashMap<>();
    options.put("range", "A1:B2");

    customizer.customize(getComponent(), options);

    Exchange inbound = new DefaultExchange(createCamelContext());

    List<String> model = Arrays.asList("{" +
                    "\"spreadsheetId\": \"" + getSpreadsheetId() + "\"," +
                    "\"A\": \"a1\"," +
                    "\"B\": \"b1\"" +
                "}",
                "{" +
                    "\"spreadsheetId\": \"" + getSpreadsheetId() + "\"," +
                    "\"A\": \"a2\"," +
                    "\"B\": \"b2\"" +
                "}");
    inbound.getIn().setBody(model);

    getComponent().getBeforeProducer().process(inbound);

    Assert.assertEquals("A1:B2", inbound.getIn().getHeader(GoogleSheetsStreamConstants.RANGE));
    Assert.assertEquals(RangeCoordinate.DIMENSION_ROWS, inbound.getIn().getHeader(GoogleSheetsStreamConstants.MAJOR_DIMENSION));
    Assert.assertEquals("USER_ENTERED", inbound.getIn().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX + "valueInputOption"));

    ValueRange valueRange = (ValueRange) inbound.getIn().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX + "values");
    Assert.assertEquals(2L, valueRange.getValues().size());
    Assert.assertEquals(2L, valueRange.getValues().get(0).size());
    Assert.assertEquals("a1", valueRange.getValues().get(0).get(0));
    Assert.assertEquals("b1", valueRange.getValues().get(0).get(1));
    Assert.assertEquals(2L, valueRange.getValues().get(1).size());
    Assert.assertEquals("a2", valueRange.getValues().get(1).get(0));
    Assert.assertEquals("b2", valueRange.getValues().get(1).get(1));
}

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

5 votes

@Test
public void testBeforeProducerColumnDimension() throws Exception {
    Map<String, Object> options = new HashMap<>();
    options.put("range", "A1:A2");
    options.put("majorDimension", RangeCoordinate.DIMENSION_COLUMNS);

    customizer.customize(getComponent(), options);

    Exchange inbound = new DefaultExchange(createCamelContext());

    String model = "{" +
                        "\"spreadsheetId\": \"" + getSpreadsheetId() + "\"," +
                        "\"#1\": \"a1\"," +
                        "\"#2\": \"a2\"" +
                    "}";
    inbound.getIn().setBody(model);

    getComponent().getBeforeProducer().process(inbound);

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

    Assert.assertEquals(getSpreadsheetId(), inbound.getIn().getHeader(GoogleSheetsStreamConstants.SPREADSHEET_ID));
    Assert.assertEquals("A1:A2", inbound.getIn().getHeader(GoogleSheetsStreamConstants.RANGE));
    Assert.assertEquals(RangeCoordinate.DIMENSION_COLUMNS, inbound.getIn().getHeader(GoogleSheetsStreamConstants.MAJOR_DIMENSION));
    Assert.assertEquals("USER_ENTERED", inbound.getIn().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX + "valueInputOption"));

    ValueRange valueRange = (ValueRange) inbound.getIn().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX + "values");
    Assert.assertEquals(1L, valueRange.getValues().size());
    Assert.assertEquals("a1", valueRange.getValues().get(0).get(0));
    Assert.assertEquals("a2", valueRange.getValues().get(0).get(1));
}

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

5 votes

@Test
public void testBeforeProducerRowDimension() throws Exception {
    Map<String, Object> options = new HashMap<>();
    options.put("range", "A1:B1");

    customizer.customize(getComponent(), options);

    Exchange inbound = new DefaultExchange(createCamelContext());

    String model = "{" +
                        "\"spreadsheetId\": \"" + getSpreadsheetId() + "\"," +
                        "\"A\": \"a1\"," +
                        "\"B\": \"b1\"" +
                    "}";
    inbound.getIn().setBody(model);

    getComponent().getBeforeProducer().process(inbound);

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

    Assert.assertEquals(getSpreadsheetId(), inbound.getIn().getHeader(GoogleSheetsStreamConstants.SPREADSHEET_ID));
    Assert.assertEquals("A1:B1", inbound.getIn().getHeader(GoogleSheetsStreamConstants.RANGE));
    Assert.assertEquals(RangeCoordinate.DIMENSION_ROWS, inbound.getIn().getHeader(GoogleSheetsStreamConstants.MAJOR_DIMENSION));
    Assert.assertEquals("USER_ENTERED", inbound.getIn().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX + "valueInputOption"));

    ValueRange valueRange = (ValueRange) inbound.getIn().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX + "values");
    Assert.assertEquals(1L, valueRange.getValues().size());
    Assert.assertEquals("a1", valueRange.getValues().get(0).get(0));
    Assert.assertEquals("b1", valueRange.getValues().get(0).get(1));
}

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

5 votes

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

    GoogleSheetsGetValuesCustomizer customizer = new GoogleSheetsGetValuesCustomizer();
    customizer.customize(getComponent(), options);

    Exchange inbound = new DefaultExchange(createCamelContext());

    ValueRange valueRange = new ValueRange();
    valueRange.setRange(range);
    valueRange.setMajorDimension(RangeCoordinate.DIMENSION_ROWS);
    valueRange.setValues(values);

    inbound.getIn().setBody(valueRange);
    getComponent().getBeforeConsumer().process(inbound);

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

    @SuppressWarnings("unchecked")
    List<String> body = inbound.getIn().getBody(List.class);
    Assert.assertEquals(model.size(), body.size());
    Iterator<String> modelIterator = body.iterator();
    for (String expected : model) {
        assertThatJson(modelIterator.next()).isEqualTo(String.format(expected, getSpreadsheetId()));
    }
}

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

5 votes

@Test
public void testAfterProducerColumnDimension() throws Exception {
    String range = "A1:A5";
    String sheetName = "Sheet1";
    String majorDimension = RangeCoordinate.DIMENSION_COLUMNS;

    List<List<Object>> values = Collections.singletonList(Arrays.asList("a1", "a2", "a3", "a4", "a5"));
    List<String> expectedValueModel = Collections.singletonList("{\"spreadsheetId\":\"%s\", \"#1\":\"a1\",\"#2\":\"a2\",\"#3\":\"a3\",\"#4\":\"a4\",\"#5\":\"a5\"}");

    Map<String, Object> options = new HashMap<>();
    options.put("spreadsheetId", getSpreadsheetId());
    options.put("range", range);
    options.put("splitResults", false);

    customizer.customize(getComponent(), options);

    Exchange inbound = new DefaultExchange(createCamelContext());

    ValueRange valueRange = new ValueRange();
    valueRange.setRange(sheetName + "!" + range);
    valueRange.setMajorDimension(majorDimension);
    valueRange.setValues(values);

    inbound.getIn().setBody(valueRange);
    getComponent().getAfterProducer().process(inbound);

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

    @SuppressWarnings("unchecked")
    List<String> model = inbound.getIn().getBody(List.class);
    Assert.assertEquals(expectedValueModel.size(), model.size());
    Iterator<String> modelIterator = model.iterator();
    for (String expected : expectedValueModel) {
        assertThatJson(modelIterator.next()).isEqualTo(String.format(expected, getSpreadsheetId()));
    }
}

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

5 votes

public static String fetchHeaderRow(String spreadsheetId, String range, String headerRow, Map<String, Object> properties) {
    RangeCoordinate rangeCoordinate = RangeCoordinate.fromRange(range);
    StringBuilder rangeBuilder = new StringBuilder();

    if (range.contains("!")) {
        rangeBuilder.append(range, 0, range.indexOf('!') + 1);
    }

    rangeBuilder.append(CellCoordinate.getColumnName(rangeCoordinate.getColumnStartIndex()))
                .append(headerRow)
                .append(':')
                .append(CellCoordinate.getColumnName(rangeCoordinate.getColumnEndIndex()))
                .append(headerRow);

    final String rootUrl = ConnectorOptions.extractOption(properties, "rootUrl", Sheets.DEFAULT_ROOT_URL);

    final boolean validateCertificates = ConnectorOptions.extractOptionAndMap(
        properties, "validateCertificates", Boolean::valueOf, false);

    final String serverCertificate = ConnectorOptions.extractOption(properties, "serverCertificate", "");

    try {
        final GoogleSheetsClientFactory clientFactory = GoogleSheetsConnectorHelper.createClientFactory(rootUrl, serverCertificate, validateCertificates);
        Sheets client = GoogleSheetsConnectorHelper.makeClient(clientFactory, properties);

        ValueRange valueRange = client.spreadsheets().values().get(spreadsheetId, rangeBuilder.toString()).execute();
        if (ObjectHelper.isNotEmpty(valueRange.getValues())) {
            StringJoiner joiner = new StringJoiner(",");
            valueRange.getValues().get(0).stream().map(Object::toString).forEach(joiner::add);
            return joiner.toString();
        }
    } catch (IOException | GeneralSecurityException e) {
        LOG.warn(String.format("Failed to fetch header row %s from spreadsheet %s", rangeBuilder.toString(), spreadsheetId), e);
    }

    return rangeCoordinate.getColumnNames();
}

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

4 votes

@Test
public void testAfterProducerRowDimension() throws Exception {
    String range = "A1:A5";
    String sheetName = "Sheet1";
    String majorDimension = RangeCoordinate.DIMENSION_ROWS;

    List<List<Object>> values = Arrays.asList(Collections.singletonList("a1"),
            Collections.singletonList("a2"),
            Collections.singletonList("a3"),
            Collections.singletonList("a4"),
            Collections.singletonList("a5"));

    List<String> expectedValueModel = Arrays.asList("{\"spreadsheetId\":\"%s\", \"A\":\"a1\"}",
            "{\"spreadsheetId\":\"%s\", \"A\":\"a2\"}",
            "{\"spreadsheetId\":\"%s\", \"A\":\"a3\"}",
            "{\"spreadsheetId\":\"%s\", \"A\":\"a4\"}",
            "{\"spreadsheetId\":\"%s\", \"A\":\"a5\"}");


    Map<String, Object> options = new HashMap<>();
    options.put("spreadsheetId", getSpreadsheetId());
    options.put("range", range);
    options.put("splitResults", false);

    customizer.customize(getComponent(), options);

    Exchange inbound = new DefaultExchange(createCamelContext());

    ValueRange valueRange = new ValueRange();
    valueRange.setRange(sheetName + "!" + range);
    valueRange.setMajorDimension(majorDimension);
    valueRange.setValues(values);

    inbound.getIn().setBody(valueRange);
    getComponent().getAfterProducer().process(inbound);

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

    @SuppressWarnings("unchecked")
    List<String> model = inbound.getIn().getBody(List.class);
    Assert.assertEquals(expectedValueModel.size(), model.size());
    Iterator<String> modelIterator = model.iterator();
    for (String expected : expectedValueModel) {
        assertThatJson(modelIterator.next()).isEqualTo(String.format(expected, getSpreadsheetId()));
    }
}

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