public Sheet getSheetById(Spreadsheet spreadsheet, Integer sheetId) { return spreadsheet.getSheets().stream().filter(sheet -> sheet.getProperties().getSheetId().equals(sheetId)) .findFirst().orElse(null); }
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; }
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; }
Spreadsheet spreadsheet = spreadsheetsService.createSpreadsheet(testRun.getTestSuite().getName(), TEST_RUN_INFO_SHEET_NAME); result = spreadsheet.getSpreadsheetUrl(); Sheet infoSheet = spreadsheet.getSheets().get(0); String spreadsheetId = spreadsheet.getSpreadsheetId(); Request infoHeaderCellsStyleRequest = spreadsheetsService.setCellsStyle(infoSheet.getProperties().getSheetId(), 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);
/** * 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(); }
/** * 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(); }
final Spreadsheet parent = GoogleUtil.toSpreadsheet(f); transport.addReponseHandler(new GetSpreadsheetResponseHandler(parent)); parent.getSheets() .forEach(sheet -> transport.addReponseHandler(new CopySheetResponseHandler(parent, sheet))); };