public static Request GetFormulaAndTextFormatRequest(int sheetId, string formulaValue, int fgColor, int bgColor, int startRowIndex, int endRowIndex, int startColumnIndex, int endColumnIndex)
{
var userEnteredFormat = new CellFormat()
{
BackgroundColor = GoogleSheetsRequests.GetColor(bgColor),
TextFormat = new TextFormat()
{
ForegroundColor = GoogleSheetsRequests.GetColor(fgColor),
FontSize = 11,
Bold = true
}
};
var formulaRequest = new Request()
{
RepeatCell = new RepeatCellRequest()
{
Range = new GridRange()
{
SheetId = sheetId,
StartColumnIndex = startColumnIndex,
EndColumnIndex = endColumnIndex,
StartRowIndex = startRowIndex,
EndRowIndex = endRowIndex
},
Cell = new CellData()
{
UserEnteredValue = new ExtendedValue()
{
FormulaValue = formulaValue,
},
UserEnteredFormat = userEnteredFormat
},
Fields = "UserEnteredValue,UserEnteredFormat"
}
};
return(formulaRequest);
}
public static Request GetFormatRequest(int sheetId, int fgColor, int bgColor, int startRowIndex, int endRowIndex, int startColumnIndex, int endColumnIndex)
{
// define format
var userEnteredFormat = new CellFormat()
{
BackgroundColor = GoogleSheetsRequests.GetColor(bgColor),
//HorizontalAlignment = "LEFT",
TextFormat = new TextFormat()
{
ForegroundColor = GoogleSheetsRequests.GetColor(fgColor),
FontSize = 11,
Bold = false
}
};
// create the request
var formatRequest = new Request()
{
RepeatCell = new RepeatCellRequest()
{
Range = new GridRange()
{
SheetId = sheetId,
StartColumnIndex = startColumnIndex,
EndColumnIndex = endColumnIndex,
StartRowIndex = startRowIndex,
EndRowIndex = endRowIndex
},
Cell = new CellData()
{
UserEnteredFormat = userEnteredFormat
},
Fields = "UserEnteredFormat(BackgroundColor,TextFormat,HorizontalAlignment,Borders)"
}
};
return(formatRequest);
}
public static Request GetNumberFormatRequest(int sheetId, string numberFormatPattern, int fgColor, int bgColor, int startRowIndex, int endRowIndex, int startColumnIndex, int endColumnIndex)
{
var numberFormat = new CellFormat()
{
NumberFormat = new NumberFormat()
{
Type = "NUMBER",
Pattern = numberFormatPattern // e.g. "#,##0.00;[Red]-#,##0.00;"
},
BackgroundColor = GoogleSheetsRequests.GetColor(bgColor),
TextFormat = new TextFormat()
{
ForegroundColor = GoogleSheetsRequests.GetColor(fgColor)
}
};
var numberFormatRequest = new Request()
{
RepeatCell = new RepeatCellRequest()
{
Range = new GridRange()
{
SheetId = sheetId,
StartColumnIndex = startColumnIndex,
EndColumnIndex = endColumnIndex,
StartRowIndex = startRowIndex,
EndRowIndex = endRowIndex
},
Cell = new CellData()
{
UserEnteredFormat = numberFormat
},
Fields = "UserEnteredFormat"
}
};
return(numberFormatRequest);
}