@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()); }
public BatchUpdateSpreadsheetResponse conditionalFormat(String spreadsheetId) throws IOException { List<GridRange> ranges = Collections.singletonList(new GridRange() .setSheetId(0) .setStartRowIndex(1) .setEndRowIndex(11) .setStartColumnIndex(0) .setEndColumnIndex(4) ); List<Request> requests = Arrays.asList( new Request().setAddConditionalFormatRule(new AddConditionalFormatRuleRequest() .setRule(new ConditionalFormatRule() .setRanges(ranges) .setBooleanRule(new BooleanRule() .setCondition(new BooleanCondition() .setType("CUSTOM_FORMULA") .setValues(Collections.singletonList( new ConditionValue().setUserEnteredValue( "=GT($D2,median($D$2:$D$11))") )) ) .setFormat(new CellFormat().setTextFormat( new TextFormat().setForegroundColor( new Color().setRed(0.8f)) )) ) ) .setIndex(0) ), new Request().setAddConditionalFormatRule(new AddConditionalFormatRuleRequest() .setRule(new ConditionalFormatRule() .setRanges(ranges) .setBooleanRule(new BooleanRule() .setCondition(new BooleanCondition() .setType("CUSTOM_FORMULA") .setValues(Collections.singletonList( new ConditionValue().setUserEnteredValue( "=LT($D2,median($D$2:$D$11))") )) ) .setFormat(new CellFormat().setBackgroundColor( new Color().setRed(1f).setGreen(0.4f).setBlue(0.4f) )) ) ) .setIndex(0) ) ); BatchUpdateSpreadsheetRequest body = new BatchUpdateSpreadsheetRequest() .setRequests(requests); BatchUpdateSpreadsheetResponse result = service.spreadsheets() .batchUpdate(spreadsheetId, body) .execute(); System.out.printf("%d cells updated.", result.getReplies().size()); return result; }
SpreadsheetSnippets { public BatchUpdateSpreadsheetResponse conditionalFormat(String spreadsheetId) throws IOException { List<GridRange> ranges = Collections.singletonList(new GridRange() .setSheetId(0) .setStartRowIndex(1) .setEndRowIndex(11) .setStartColumnIndex(0) .setEndColumnIndex(4) ); List<Request> requests = Arrays.asList( new Request().setAddConditionalFormatRule(new AddConditionalFormatRuleRequest() .setRule(new ConditionalFormatRule() .setRanges(ranges) .setBooleanRule(new BooleanRule() .setCondition(new BooleanCondition() .setType("CUSTOM_FORMULA") .setValues(Collections.singletonList( new ConditionValue().setUserEnteredValue( "=GT($D2,median($D$2:$D$11))") )) ) .setFormat(new CellFormat().setTextFormat( new TextFormat().setForegroundColor( new Color().setRed(0.8f)) )) ) ) .setIndex(0) ), new Request().setAddConditionalFormatRule(new AddConditionalFormatRuleRequest() .setRule(new ConditionalFormatRule() .setRanges(ranges) .setBooleanRule(new BooleanRule() .setCondition(new BooleanCondition() .setType("CUSTOM_FORMULA") .setValues(Collections.singletonList( new ConditionValue().setUserEnteredValue( "=LT($D2,median($D$2:$D$11))") )) ) .setFormat(new CellFormat().setBackgroundColor( new Color().setRed(1f).setGreen(0.4f).setBlue(0.4f) )) ) ) .setIndex(0) ) ); BatchUpdateSpreadsheetRequest body = new BatchUpdateSpreadsheetRequest() .setRequests(requests); BatchUpdateSpreadsheetResponse result = service.spreadsheets() .batchUpdate(spreadsheetId, body) .execute(); System.out.printf("%d cells updated.", result.getReplies().size()); return result; } }
SpreadsheetSnippets { public BatchUpdateSpreadsheetResponse conditionalFormat(String spreadsheetId) throws IOException { List<GridRange> ranges = Collections.singletonList(new GridRange() .setSheetId(0) .setStartRowIndex(1) .setEndRowIndex(11) .setStartColumnIndex(0) .setEndColumnIndex(4) ); List<Request> requests = Arrays.asList( new Request().setAddConditionalFormatRule(new AddConditionalFormatRuleRequest() .setRule(new ConditionalFormatRule() .setRanges(ranges) .setBooleanRule(new BooleanRule() .setCondition(new BooleanCondition() .setType("CUSTOM_FORMULA") .setValues(Collections.singletonList( new ConditionValue().setUserEnteredValue( "=GT($D2,median($D$2:$D$11))") )) ) .setFormat(new CellFormat().setTextFormat( new TextFormat().setForegroundColor( new Color().setRed(0.8f)) )) ) ) .setIndex(0) ), new Request().setAddConditionalFormatRule(new AddConditionalFormatRuleRequest() .setRule(new ConditionalFormatRule() .setRanges(ranges) .setBooleanRule(new BooleanRule() .setCondition(new BooleanCondition() .setType("CUSTOM_FORMULA") .setValues(Collections.singletonList( new ConditionValue().setUserEnteredValue( "=LT($D2,median($D$2:$D$11))") )) ) .setFormat(new CellFormat().setBackgroundColor( new Color().setRed(1f).setGreen(0.4f).setBlue(0.4f) )) ) ) .setIndex(0) ) ); BatchUpdateSpreadsheetRequest body = new BatchUpdateSpreadsheetRequest() .setRequests(requests); BatchUpdateSpreadsheetResponse result = service.spreadsheets() .batchUpdate(spreadsheetId, body) .execute(); System.out.printf("%d cells updated.", result.getReplies().size()); return result; } SpreadsheetSnippets(Sheets service); }
SpreadsheetSnippets { public BatchUpdateSpreadsheetResponse conditionalFormat(String spreadsheetId) throws IOException { List<GridRange> ranges = Collections.singletonList(new GridRange() .setSheetId(0) .setStartRowIndex(1) .setEndRowIndex(11) .setStartColumnIndex(0) .setEndColumnIndex(4) ); List<Request> requests = Arrays.asList( new Request().setAddConditionalFormatRule(new AddConditionalFormatRuleRequest() .setRule(new ConditionalFormatRule() .setRanges(ranges) .setBooleanRule(new BooleanRule() .setCondition(new BooleanCondition() .setType("CUSTOM_FORMULA") .setValues(Collections.singletonList( new ConditionValue().setUserEnteredValue( "=GT($D2,median($D$2:$D$11))") )) ) .setFormat(new CellFormat().setTextFormat( new TextFormat().setForegroundColor( new Color().setRed(0.8f)) )) ) ) .setIndex(0) ), new Request().setAddConditionalFormatRule(new AddConditionalFormatRuleRequest() .setRule(new ConditionalFormatRule() .setRanges(ranges) .setBooleanRule(new BooleanRule() .setCondition(new BooleanCondition() .setType("CUSTOM_FORMULA") .setValues(Collections.singletonList( new ConditionValue().setUserEnteredValue( "=LT($D2,median($D$2:$D$11))") )) ) .setFormat(new CellFormat().setBackgroundColor( new Color().setRed(1f).setGreen(0.4f).setBlue(0.4f) )) ) ) .setIndex(0) ) ); BatchUpdateSpreadsheetRequest body = new BatchUpdateSpreadsheetRequest() .setRequests(requests); BatchUpdateSpreadsheetResponse result = service.spreadsheets() .batchUpdate(spreadsheetId, body) .execute(); System.out.printf("%d cells updated.", result.getReplies().size()); return result; } SpreadsheetSnippets(Sheets service); String create(String title); BatchUpdateSpreadsheetResponse batchUpdate(String spreadsheetId, String title,
                                                      String find, String replacement); ValueRange getValues(String spreadsheetId, String range); BatchGetValuesResponse batchGetValues(String spreadsheetId, List<String> _ranges); UpdateValuesResponse updateValues(String spreadsheetId, String range,
                                             String valueInputOption, List<List<Object>> _values); BatchUpdateValuesResponse batchUpdateValues(String spreadsheetId, String range,
                                                       String valueInputOption,
                                                       List<List<Object>> _values); AppendValuesResponse appendValues(String spreadsheetId, String range,
                                             String valueInputOption, List<List<Object>> _values); BatchUpdateSpreadsheetResponse pivotTables(String spreadsheetId); BatchUpdateSpreadsheetResponse conditionalFormat(String spreadsheetId); }
SpreadsheetSnippets { public BatchUpdateSpreadsheetResponse conditionalFormat(String spreadsheetId) throws IOException { List<GridRange> ranges = Collections.singletonList(new GridRange() .setSheetId(0) .setStartRowIndex(1) .setEndRowIndex(11) .setStartColumnIndex(0) .setEndColumnIndex(4) ); List<Request> requests = Arrays.asList( new Request().setAddConditionalFormatRule(new AddConditionalFormatRuleRequest() .setRule(new ConditionalFormatRule() .setRanges(ranges) .setBooleanRule(new BooleanRule() .setCondition(new BooleanCondition() .setType("CUSTOM_FORMULA") .setValues(Collections.singletonList( new ConditionValue().setUserEnteredValue( "=GT($D2,median($D$2:$D$11))") )) ) .setFormat(new CellFormat().setTextFormat( new TextFormat().setForegroundColor( new Color().setRed(0.8f)) )) ) ) .setIndex(0) ), new Request().setAddConditionalFormatRule(new AddConditionalFormatRuleRequest() .setRule(new ConditionalFormatRule() .setRanges(ranges) .setBooleanRule(new BooleanRule() .setCondition(new BooleanCondition() .setType("CUSTOM_FORMULA") .setValues(Collections.singletonList( new ConditionValue().setUserEnteredValue( "=LT($D2,median($D$2:$D$11))") )) ) .setFormat(new CellFormat().setBackgroundColor( new Color().setRed(1f).setGreen(0.4f).setBlue(0.4f) )) ) ) .setIndex(0) ) ); BatchUpdateSpreadsheetRequest body = new BatchUpdateSpreadsheetRequest() .setRequests(requests); BatchUpdateSpreadsheetResponse result = service.spreadsheets() .batchUpdate(spreadsheetId, body) .execute(); System.out.printf("%d cells updated.", result.getReplies().size()); return result; } SpreadsheetSnippets(Sheets service); String create(String title); BatchUpdateSpreadsheetResponse batchUpdate(String spreadsheetId, String title,
                                                      String find, String replacement); ValueRange getValues(String spreadsheetId, String range); BatchGetValuesResponse batchGetValues(String spreadsheetId, List<String> _ranges); UpdateValuesResponse updateValues(String spreadsheetId, String range,
                                             String valueInputOption, List<List<Object>> _values); BatchUpdateValuesResponse batchUpdateValues(String spreadsheetId, String range,
                                                       String valueInputOption,
                                                       List<List<Object>> _values); AppendValuesResponse appendValues(String spreadsheetId, String range,
                                             String valueInputOption, List<List<Object>> _values); BatchUpdateSpreadsheetResponse pivotTables(String spreadsheetId); BatchUpdateSpreadsheetResponse conditionalFormat(String spreadsheetId);  }