private static Spreadsheet copySheet(final Sheets sheetsService, final Spreadsheet stonky, final File export, final InternalSheet sheet) throws IOException { final String name = sheet.getId(); LOGGER.debug("Requested to copy sheet '{}' to Stonky '{}' from imported '{}'.", name, stonky.getSpreadsheetId(), export.getId()); final Optional<Sheet> targetSheet = stonky.getSheets().stream() .filter(s -> Objects.equals(s.getProperties().getTitle(), name)) .findFirst(); final List<Request> requests = new ArrayList<>(0); targetSheet.ifPresent(s -> { final int sheetId = s.getProperties().getSheetId(); LOGGER.debug("Will delete existing '{}' sheet #{}.", name, sheetId); final DeleteSheetRequest delete = new DeleteSheetRequest().setSheetId(sheetId); requests.add(new Request().setDeleteSheet(delete)); }); LOGGER.debug("Copying sheet."); final SheetProperties newSheet = copySheet(sheetsService, stonky, export) .setIndex(sheet.getOrder()) .setTitle(name); final UpdateSheetPropertiesRequest update = new UpdateSheetPropertiesRequest() .setFields("title,index") .setProperties(newSheet); requests.add(new Request().setUpdateSheetProperties(update)); final BatchUpdateSpreadsheetRequest batch = new BatchUpdateSpreadsheetRequest() .setRequests(requests); LOGGER.debug("Renaming sheet and changing position."); sheetsService.spreadsheets().batchUpdate(stonky.getSpreadsheetId(), batch).execute(); LOGGER.debug("Stonky '{}' sheet processed.", name); return stonky; }
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 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("*")); }
sheetsRequests.add(new Request().setAddSheet(new AddSheetRequest())); sheetsRequests.add(new Request().setAddSheet(new AddSheetRequest())); )); List<Request> requests = Lists.newArrayList(); Request updateCellsRequest = new Request().setUpdateCells(new UpdateCellsRequest() .setFields("*") .setRows(Collections.singletonList(
public BatchUpdateSpreadsheetResponse batchUpdate(String spreadsheetId, String title, String find, String replacement) throws IOException { Sheets service = this.service; // [START sheets_batch_update] List<Request> requests = new ArrayList<>(); // Change the spreadsheet's title. requests.add(new Request() .setUpdateSpreadsheetProperties(new UpdateSpreadsheetPropertiesRequest() .setProperties(new SpreadsheetProperties() .setTitle(title)) .setFields("title"))); // Find and replace text. requests.add(new Request() .setFindReplace(new FindReplaceRequest() .setFind(find) .setReplacement(replacement) .setAllSheets(true))); // Add additional requests (operations) ... BatchUpdateSpreadsheetRequest body = new BatchUpdateSpreadsheetRequest().setRequests(requests); BatchUpdateSpreadsheetResponse response = service.spreadsheets().batchUpdate(spreadsheetId, body).execute(); FindReplaceResponse findReplaceResponse = response.getReplies().get(1).getFindReplace(); System.out.printf("%d replacements made.", findReplaceResponse.getOccurrencesChanged()); // [END sheets_batch_update] return response; }
); List<Request> requests = Arrays.asList( new Request().setAddConditionalFormatRule(new AddConditionalFormatRuleRequest() .setRule(new ConditionalFormatRule() .setRanges(ranges) new Request().setAddConditionalFormatRule(new AddConditionalFormatRuleRequest() .setRule(new ConditionalFormatRule() .setRanges(ranges)
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 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 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 static Spreadsheet copySheet(final Sheets sheetsService, final Spreadsheet stonky, final File export, final InternalSheet sheet) throws IOException { final String name = sheet.getId(); LOGGER.debug("Requested to copy sheet '{}' to Stonky '{}' from imported '{}'.", name, stonky.getSpreadsheetId(), export.getId()); final Optional<Sheet> targetSheet = stonky.getSheets().stream() .filter(s -> Objects.equals(s.getProperties().getTitle(), name)) .findFirst(); final List<Request> requests = new ArrayList<>(0); targetSheet.ifPresent(s -> { final int sheetId = s.getProperties().getSheetId(); LOGGER.debug("Will delete existing '{}' sheet #{}.", name, sheetId); final DeleteSheetRequest delete = new DeleteSheetRequest().setSheetId(sheetId); requests.add(new Request().setDeleteSheet(delete)); }); LOGGER.debug("Copying sheet."); final SheetProperties newSheet = copySheet(sheetsService, stonky, export) .setIndex(sheet.getOrder()) .setTitle(name); final UpdateSheetPropertiesRequest update = new UpdateSheetPropertiesRequest() .setFields("title,index") .setProperties(newSheet); requests.add(new Request().setUpdateSheetProperties(update)); final BatchUpdateSpreadsheetRequest batch = new BatchUpdateSpreadsheetRequest() .setRequests(requests); LOGGER.debug("Renaming sheet and changing position."); sheetsService.spreadsheets().batchUpdate(stonky.getSpreadsheetId(), batch).execute(); LOGGER.debug("Stonky '{}' sheet processed.", name); return stonky; }