//TODO this metod only adds conditional formatting, there si also needed to implement delete conditional formatting method
private void SetConditionalFormatting()
{
Spreadsheet spr = Service.Spreadsheets.Get(SheetId).Execute();
Sheet sh = spr.Sheets.FirstOrDefault(s => s.Properties.Title == "attendance");
int sheetId = (int)sh.Properties.SheetId;
var conditionalFormatRule = new ConditionalFormatRule
{
GradientRule = new GradientRule
{
Maxpoint = new InterpolationPoint {
Color = _redColor, Type = InterpolationTypeNumber, Value = "12"
},
Midpoint = new InterpolationPoint {
Color = _greenColor, Type = InterpolationTypeNumber, Value = "8"
},
Minpoint = new InterpolationPoint {
Color = _redColor, Type = InterpolationTypeNumber, Value = "6"
}
},
Ranges = new List <GridRange>
{
new GridRange
{
SheetId = sheetId,
StartColumnIndex = 1,
StartRowIndex = 2
}
}
};
var batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest();
var updateCellsRequest = new Request
{
AddConditionalFormatRule = new AddConditionalFormatRuleRequest
{
Rule = conditionalFormatRule
}
};
batchUpdateSpreadsheetRequest.Requests = new List <Request> {
updateCellsRequest
};
var bur = Service.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequest, SheetId);
bur.Execute();
}
public void SetGradientConditionalFormat()
{
SheetsService service = ConnectToGoogle();
ConditionalFormatRule conditionalFormatRule = new ConditionalFormatRule();
GradientRule gradientRule = new GradientRule
{
Maxpoint = new InterpolationPoint
{
Color = SelectColor(ColorEnum.Green),
Type = "MAX",
},
Minpoint = new InterpolationPoint
{
Color = SelectColor(ColorEnum.Red),
Type = "MIN",
},
};
conditionalFormatRule.GradientRule = gradientRule;
int sheetGId = SelectGIdOfSheetToFormat("wPDI", service);
GridRange gridRange = new GridRange
{
SheetId = sheetGId,
StartRowIndex = 5,
EndRowIndex = 50,
StartColumnIndex = 8,
EndColumnIndex = 9,
};
C.WriteLine($"gridRange: {gridRange.SheetId}");
IList <GridRange> listOfRanges = conditionalFormatRule.Ranges;
foreach (GridRange range in listOfRanges)
{
C.WriteLine($"range: {range}");
}
conditionalFormatRule.Ranges.Add(gridRange);
foreach (GridRange range in listOfRanges)
{
C.WriteLine($"range: {range}");
}
}
