private void AddCategoryHeader(string categoryName, int subcategoriesCount) { var cells = new List <CellData>(); cells.Add(new CellData() { UserEnteredValue = new ExtendedValue() { StringValue = categoryName }, UserEnteredFormat = SubHeaderFormat(), }); _budget.Categories.FirstOrDefault(x => x.Name.ToLower() == categoryName.ToLower()).RowIndex = _googleSheetService.CurrentRow; _googleSheetService.AddRow(cells); cells = new List <CellData>(); cells.Add(new CellData() { UserEnteredValue = new ExtendedValue() { StringValue = "Name" }, UserEnteredFormat = new CellFormat() { Borders = GoogleSheetService.GetTopBorder(), TextFormat = GoogleSheetService.GetDefaultTableHeaderFormatting(), WrapStrategy = "WRAP", VerticalAlignment = "MIDDLE", } }); cells.Add(new CellData() { UserEnteredValue = new ExtendedValue() { StringValue = "Planned spending" }, UserEnteredFormat = new CellFormat() { Borders = GoogleSheetService.GetTopBorder(), TextFormat = GoogleSheetService.GetDefaultTableHeaderFormatting(), WrapStrategy = "WRAP", HorizontalAlignment = "RIGHT", VerticalAlignment = "MIDDLE", } }); cells.Add(new CellData() { UserEnteredValue = new ExtendedValue() { StringValue = "Actual spending" }, UserEnteredFormat = new CellFormat() { Borders = GoogleSheetService.GetTopBorder(), TextFormat = GoogleSheetService.GetDefaultTableHeaderFormatting(), WrapStrategy = "WRAP", HorizontalAlignment = "RIGHT", VerticalAlignment = "MIDDLE", } }); cells.Add(new CellData() { UserEnteredValue = new ExtendedValue() { StringValue = "Difference" }, UserEnteredFormat = new CellFormat() { Borders = GoogleSheetService.GetTopBorder(), TextFormat = GoogleSheetService.GetDefaultTableHeaderFormatting(), WrapStrategy = "WRAP", HorizontalAlignment = "RIGHT", VerticalAlignment = "MIDDLE", } }); cells.Add(new CellData() { UserEnteredValue = new ExtendedValue() { StringValue = "Realization" }, UserEnteredFormat = new CellFormat() { Borders = GoogleSheetService.GetTopBorder(), TextFormat = GoogleSheetService.GetDefaultTableHeaderFormatting(), WrapStrategy = "WRAP", HorizontalAlignment = "CENTER", VerticalAlignment = "MIDDLE", } }); cells.Add(new CellData()); for (int i = 0; i < DateTime.DaysInMonth(_budget.Month.Year, _budget.Month.Month); i++) { cells.Add(new CellData() { UserEnteredValue = new ExtendedValue() { FormulaValue = $"=SUM({GoogleSheetService.GetCellAddress(6 + i, _googleSheetService.CurrentRow + 2)}:{GoogleSheetService.GetCellAddress(6 + i, _googleSheetService.CurrentRow + 2 + subcategoriesCount - 1)})" }, UserEnteredFormat = new CellFormat() { NumberFormat = new NumberFormat() { Type = "CURRENCY" }, Borders = GoogleSheetService.GetTopBorder(), TextFormat = GoogleSheetService.GetDefaultCellFormatting(), VerticalAlignment = "MIDDLE", } }); } _googleSheetService.AddRow(cells); cells = new List <CellData>(); cells.Add(new CellData() { UserEnteredValue = new ExtendedValue() { StringValue = "Sum:" }, UserEnteredFormat = new CellFormat() { Borders = GoogleSheetService.GetBottomBorder(), TextFormat = GoogleSheetService.GetDefaultTableHeaderFormatting(), VerticalAlignment = "MIDDLE", } }); cells.Add(new CellData() { UserEnteredValue = new ExtendedValue() { FormulaValue = $"=SUM({GoogleSheetService.GetCellAddress(1, _googleSheetService.CurrentRow + 1)}:{GoogleSheetService.GetCellAddress(1, _googleSheetService.CurrentRow + subcategoriesCount)})" }, UserEnteredFormat = new CellFormat() { NumberFormat = new NumberFormat() { Type = "CURRENCY" }, Borders = GoogleSheetService.GetBottomBorder(), TextFormat = GoogleSheetService.GetDefaultTableHeaderFormatting(), VerticalAlignment = "MIDDLE", } }); cells.Add(new CellData() { UserEnteredValue = new ExtendedValue() { FormulaValue = $"=SUM({GoogleSheetService.GetCellAddress(2, _googleSheetService.CurrentRow + 1)}:{GoogleSheetService.GetCellAddress(2, _googleSheetService.CurrentRow + subcategoriesCount)})" }, UserEnteredFormat = new CellFormat() { NumberFormat = new NumberFormat() { Type = "CURRENCY" }, Borders = GoogleSheetService.GetBottomBorder(), TextFormat = GoogleSheetService.GetDefaultTableHeaderFormatting(), VerticalAlignment = "MIDDLE", } }); cells.Add(new CellData() { UserEnteredValue = new ExtendedValue() { FormulaValue = $"={GoogleSheetService.GetCellAddress(2, _googleSheetService.CurrentRow)}-{GoogleSheetService.GetCellAddress(1, _googleSheetService.CurrentRow)}" }, UserEnteredFormat = new CellFormat() { NumberFormat = new NumberFormat() { Type = "CURRENCY" }, Borders = GoogleSheetService.GetBottomBorder(), TextFormat = GoogleSheetService.GetDefaultTableHeaderFormatting(), VerticalAlignment = "MIDDLE", } }); cells.Add(new CellData() { UserEnteredValue = new ExtendedValue() { FormulaValue = $"=IFERROR({GoogleSheetService.GetCellAddress(2, _googleSheetService.CurrentRow)}/{GoogleSheetService.GetCellAddress(1, _googleSheetService.CurrentRow)}; \"-\")" }, UserEnteredFormat = new CellFormat() { NumberFormat = new NumberFormat() { Type = "PERCENT", Pattern = "0.00%" }, Borders = GoogleSheetService.GetBottomBorder(), TextFormat = GoogleSheetService.GetDefaultTableHeaderFormatting(), HorizontalAlignment = "CENTER", VerticalAlignment = "MIDDLE", } }); cells.Add(new CellData()); DateTime columnDate = new DateTime(_budget.Month.Year, _budget.Month.Month, 1); for (int i = 0; i < DateTime.DaysInMonth(_budget.Month.Year, _budget.Month.Month); i++) { cells.Add(new CellData() { UserEnteredValue = new ExtendedValue() { StringValue = $"{columnDate.ToString("yyyy-MM-dd")}" }, UserEnteredFormat = new CellFormat() { Borders = GoogleSheetService.GetBottomBorder(), TextFormat = GoogleSheetService.GetDefaultTableHeaderFormatting(), HorizontalAlignment = "CENTER", VerticalAlignment = "MIDDLE", } }); columnDate = columnDate.AddDays(1); } _googleSheetService.AddRow(cells); }