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 Spreadsheet createSpreadsheet(String title, String sheetTitle) throws IOException { return createSpreadsheet(title, new Sheet().setProperties(new SheetProperties().setTitle(sheetTitle))); }
/** * This is synchronized because if it weren't and two copies were happening at the same time, Google API would * have thrown an undescribed HTTP 500 error when trying to execute the actual copying operation. A working theory * is that all the old sheet IDs are invalidated when a new sheet is added - but this is not verified. * @param sheetsService * @param stonky * @param export * @return * @throws IOException */ private static synchronized SheetProperties copySheet(final Sheets sheetsService, final Spreadsheet stonky, final File export) throws IOException { final int sheetId = sheetsService.spreadsheets().get(export.getId()) .execute() .getSheets() .get(0) // first and only sheet .getProperties() .getSheetId(); final CopySheetToAnotherSpreadsheetRequest r = new CopySheetToAnotherSpreadsheetRequest() .setDestinationSpreadsheetId(stonky.getSpreadsheetId()); LOGGER.debug("Will copy sheet {} from spreadsheet '{}' to spreadsheet '{}'", sheetId, export.getId(), stonky.getSpreadsheetId()); return sheetsService.spreadsheets().sheets() .copyTo(export.getId(), sheetId, r) .execute() .clone(); }
Sheet infoSheet = spreadsheet.getSheets().get(0); String spreadsheetId = spreadsheet.getSpreadsheetId(); Request infoHeaderCellsStyleRequest = spreadsheetsService.setCellsStyle(infoSheet.getProperties().getSheetId(), 0, testRunInfo.size(), 0, 1, true, 10, "Arial", true); Request infoCellsStyleRequest = spreadsheetsService.setCellsStyle(infoSheet.getProperties().getSheetId(), 1, testRunInfo.size(), 1, testRunInfo.get(0).size(), true, 10, "Arial", false); spreadsheetsService.batchUpdate(spreadsheet.getSpreadsheetId(), Arrays.asList(infoHeaderCellsStyleRequest, infoCellsStyleRequest)); spreadsheetsService.writeValuesIntoSpreadsheet(spreadsheet.getSpreadsheetId(), testRunInfo, TEST_RUN_INFO_SHEET_NAME); Request infoColumnsAndRowsCountRequest = spreadsheetsService.setColumnAndRowsCounts(infoSheetProperties, testRunInfo.get(0).size(), testRunInfo.size()); Request infoAutoResizeRequest = spreadsheetsService.setAutoResizeDimensionToGrid(spreadsheet, infoSheetProperties.getSheetId()); Request infoTabColorRequest = spreadsheetsService.setTabColor(infoSheetProperties, 1F, 0, 0); spreadsheetsService.batchUpdate(spreadsheet.getSpreadsheetId(), Arrays.asList(infoColumnsAndRowsCountRequest, infoTabColorRequest, infoAutoResizeRequest)); spreadsheetsService.createGrid(spreadsheet.getSpreadsheetId(), TEST_RUN_RESULTS_SHEET_NAME); spreadsheet = spreadsheetsService.getSpreadsheetById(spreadsheet.getSpreadsheetId()); Sheet sheet = spreadsheet.getSheets().stream().filter(s -> s.getProperties().getTitle().equalsIgnoreCase(TEST_RUN_RESULTS_SHEET_NAME)).findFirst().orElse(new Sheet()); Request cellsStyleRequest = spreadsheetsService.setCellsStyle(sheet.getProperties().getSheetId(), 0, 1, 0, testRunResults.get(0).size(), true, 10, "Arial", true); spreadsheetsService.batchUpdate(spreadsheet.getSpreadsheetId(), Collections.singletonList(cellsStyleRequest)); spreadsheetsService.writeValuesIntoSpreadsheet(spreadsheet.getSpreadsheetId(), testRunResults, TEST_RUN_RESULTS_SHEET_NAME); Request columnsAndRowsCountRequest = spreadsheetsService.setColumnAndRowsCounts(sheetProperties, testRunResults.get(0).size(), testRunResults.size()); Request autoResizeRequest = spreadsheetsService.setAutoResizeDimensionToGrid(spreadsheet, sheetProperties.getSheetId()); Request tabColorRequest = spreadsheetsService.setTabColor(sheetProperties, 1F, 0, 0); spreadsheetsService.batchUpdate(spreadsheet.getSpreadsheetId(), Arrays.asList(columnsAndRowsCountRequest, tabColorRequest, autoResizeRequest));
public Sheet getSheetById(Spreadsheet spreadsheet, Integer sheetId) { return spreadsheet.getSheets().stream().filter(sheet -> sheet.getProperties().getSheetId().equals(sheetId)) .findFirst().orElse(null); }
@Override protected MockLowLevelHttpResponse respond(final String method, final String url) { final SheetProperties cloned = source.getProperties().clone(); return new MockLowLevelHttpResponse().setContent(toJson(cloned)); } }
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("*")); }
/** * This is synchronized because if it weren't and two copies were happening at the same time, Google API would * have thrown an undescribed HTTP 500 error when trying to execute the actual copying operation. A working theory * is that all the old sheet IDs are invalidated when a new sheet is added - but this is not verified. * @param sheetsService * @param stonky * @param export * @return * @throws IOException */ private static synchronized SheetProperties copySheet(final Sheets sheetsService, final Spreadsheet stonky, final File export) throws IOException { final int sheetId = sheetsService.spreadsheets().get(export.getId()) .execute() .getSheets() .get(0) // first and only sheet .getProperties() .getSheetId(); final CopySheetToAnotherSpreadsheetRequest r = new CopySheetToAnotherSpreadsheetRequest() .setDestinationSpreadsheetId(stonky.getSpreadsheetId()); LOGGER.debug("Will copy sheet {} from spreadsheet '{}' to spreadsheet '{}'", sheetId, export.getId(), stonky.getSpreadsheetId()); return sheetsService.spreadsheets().sheets() .copyTo(export.getId(), sheetId, r) .execute() .clone(); }
@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"); }
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 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 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(); }
.batchUpdate(spreadsheetId, createSheetsBody).execute(); int sourceSheetId = createSheetsResponse.getReplies().get(0).getAddSheet().getProperties() .getSheetId(); int targetSheetId = createSheetsResponse.getReplies().get(1).getAddSheet().getProperties() .getSheetId();