public Request setColumnAndRowsCounts(SheetProperties sheetProperties, Integer columnCount, Integer rowCount) { sheetProperties.setGridProperties(new GridProperties() .setColumnCount(columnCount) .setRowCount(rowCount)); return new Request().setUpdateSheetProperties(new UpdateSheetPropertiesRequest().setProperties(sheetProperties).setFields("*")); }
public Request setCellsStyle(int sheetId, int startRowIndex, int endRowIndex, int startColumnIndex, int endColumnIndex, boolean center, int fontSize, String fontFamily, boolean bold) { return new Request().setRepeatCell(new RepeatCellRequest() .setCell(new CellData().setUserEnteredFormat(new CellFormat().setHorizontalAlignment(center ? "CENTER" : null).setTextFormat( new TextFormat().setFontSize(fontSize).setFontFamily(fontFamily).setBold(bold)))) .setRange(new GridRange().setSheetId(sheetId) .setStartRowIndex(startRowIndex).setEndRowIndex(endRowIndex) .setStartColumnIndex(startColumnIndex).setEndColumnIndex(endColumnIndex)).setFields("*")); }
public Request setTabColor(SheetProperties sheetProperties, float red, float green, float blue) { sheetProperties.setTabColor(new Color() .setRed(red) .setGreen(green) .setBlue(blue)); return new Request().setUpdateSheetProperties(new UpdateSheetPropertiesRequest().setProperties(sheetProperties).setFields("*")); }
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(); } }
public String create(String title) throws IOException { Sheets service = this.service; // [START sheets_create] Spreadsheet spreadsheet = new Spreadsheet() .setProperties(new SpreadsheetProperties() .setTitle(title)); spreadsheet = service.spreadsheets().create(spreadsheet) .setFields("spreadsheetId") .execute(); System.out.println("Spreadsheet ID: " + spreadsheet.getSpreadsheetId()); // [END sheets_create] return spreadsheet.getSpreadsheetId(); }
public BatchUpdateSpreadsheetResponse createGrid(String spreadsheetId, String sheetTitle) throws IOException { List<Request> requests = new ArrayList<>(); requests.add(new Request().setAddSheet(new AddSheetRequest().setProperties(new SheetProperties() .setTitle(sheetTitle)))); BatchUpdateSpreadsheetRequest body = new BatchUpdateSpreadsheetRequest().setRequests(requests); return sheetsService.spreadsheets().batchUpdate(spreadsheetId, body).execute(); }
public Request setAutoResizeDimensionToGrid(Spreadsheet spreadsheet, Integer sheetId) throws IOException { return new Request().setAutoResizeDimensions(new AutoResizeDimensionsRequest().setDimensions(new DimensionRange() .setDimension(AutoDimensionType.COLUMNS.name()).setSheetId(getSheetById(spreadsheet, sheetId).getProperties().getSheetId()))); }
public Spreadsheet createSpreadsheet(String title, Sheet... sheets) throws IOException { return sheetsService.spreadsheets().create(new Spreadsheet().setSheets(Arrays.asList(sheets)) .setProperties(new SpreadsheetProperties().setTitle(title))).execute(); }
@Override protected boolean appliesTo(final String method, final String url) { final int id = source.getProperties().getSheetId(); return Objects.equals(method, "POST") && Objects.equals(url, "https://sheets.googleapis.com/v4/spreadsheets/" + parent.getSpreadsheetId() + "/sheets/" + id + ":copyTo"); }
public Sheet getSheetById(Spreadsheet spreadsheet, Integer sheetId) { return spreadsheet.getSheets().stream().filter(sheet -> sheet.getProperties().getSheetId().equals(sheetId)) .findFirst().orElse(null); }
@Test public void testBatchGetValues() throws IOException { String spreadsheetId = this.createTestSpreadsheet(); this.populateValuesWithStrings(spreadsheetId); List<String> ranges = Arrays.asList("A1:A3", "B1:C1"); BatchGetValuesResponse result = this.snippets.batchGetValues(spreadsheetId, ranges); List<ValueRange> valueRanges = result.getValueRanges(); assertEquals(2, valueRanges.size()); List<List<Object>> values = valueRanges.get(0).getValues(); assertEquals(3, values.size()); }
@Test public void testAppendValues() throws IOException { String spreadsheetId = this.createTestSpreadsheet(); this.populateValuesWithStrings(spreadsheetId); List<List<Object>> values = Arrays.asList( Arrays.asList("A", "B"), Arrays.asList("C", "D")); AppendValuesResponse result = this.snippets.appendValues(spreadsheetId, "A1:B2", "USER_ENTERED", values); assertEquals("Sheet1!A1:J10", result.getTableRange()); UpdateValuesResponse updates = result.getUpdates(); assertEquals(2, updates.getUpdatedRows().intValue()); assertEquals(2, updates.getUpdatedColumns().intValue()); assertEquals(4, updates.getUpdatedCells().intValue()); }
@Override protected boolean appliesTo(final String method, final String url) { return Objects.equals(method, "GET") && Objects.equals(url, "https://sheets.googleapis.com/v4/spreadsheets/" + file.getSpreadsheetId()); }
@Test public void testBatchUpdate() throws IOException { String spreadsheetId = this.createTestSpreadsheet(); this.populateValuesWithStrings(spreadsheetId); BatchUpdateSpreadsheetResponse response = this.snippets.batchUpdate(spreadsheetId, "New Title", "Hello", "Goodbye"); List<Response> replies = response.getReplies(); assertEquals(2, replies.size()); FindReplaceResponse findReplaceResponse = replies.get(1).getFindReplace(); assertEquals(100, findReplaceResponse.getOccurrencesChanged().intValue()); }
public Spreadsheet createSpreadsheet(String title, String sheetTitle) throws IOException { return createSpreadsheet(title, new Sheet().setProperties(new SheetProperties().setTitle(sheetTitle))); }
@Test public void testGetValues() throws IOException { String spreadsheetId = this.createTestSpreadsheet(); this.populateValuesWithStrings(spreadsheetId); ValueRange result = this.snippets.getValues(spreadsheetId, "A1:C2"); List<List<Object>> values = result.getValues(); assertEquals(2, values.size()); assertEquals(3, values.get(0).size()); }
@Override protected MockLowLevelHttpResponse respond(final String method, final String url) { final SheetProperties cloned = source.getProperties().clone(); return new MockLowLevelHttpResponse().setContent(toJson(cloned)); } }
@Test public void testConditionalFormat() throws IOException { String spreadsheetId = this.createTestSpreadsheet(); this.populateValuesWithNumbers(spreadsheetId); BatchUpdateSpreadsheetResponse response = this.snippets.conditionalFormat(spreadsheetId); assertEquals(spreadsheetId, response.getSpreadsheetId()); assertEquals(2, response.getReplies().size()); }
protected void populateValuesWithStrings(String spreadsheetId) throws IOException { List<Request> requests = new ArrayList<>(); requests.add(new Request().setRepeatCell(new RepeatCellRequest() .setRange(new GridRange() .setSheetId(0) .setStartRowIndex(0) .setEndRowIndex(10) .setStartColumnIndex(0) .setEndColumnIndex(10)) .setCell(new CellData() .setUserEnteredValue(new ExtendedValue() .setStringValue("Hello"))) .setFields("userEnteredValue"))); BatchUpdateSpreadsheetRequest body = new BatchUpdateSpreadsheetRequest() .setRequests(requests); service.spreadsheets().batchUpdate(spreadsheetId, body).execute(); }
protected String createTestSpreadsheet() throws IOException { Spreadsheet spreadsheet = new Spreadsheet() .setProperties(new SpreadsheetProperties() .setTitle("Test Spreadsheet")); spreadsheet = service.spreadsheets().create(spreadsheet) .setFields("spreadsheetId") .execute(); return spreadsheet.getSpreadsheetId(); }