public static Request GetFormatRequest(int sheetId, int fgColor, int bgColor, int startRowIndex, int endRowIndex, string startColumn, string endColumn)
{
int startColumnIndex = GoogleSheetsRequests.ColumnNumber(startColumn) - 1;
int endColumnIndex = GoogleSheetsRequests.ColumnNumber(endColumn);
return(GetFormatRequest(sheetId, fgColor, bgColor, startRowIndex, endRowIndex, startColumnIndex, endColumnIndex));
}
public static Request GetFormulaAndNumberFormatRequest(int sheetId, string formulaValue, int startRowIndex, int endRowIndex, string startColumn, string endColumn)
{
int startColumnIndex = GoogleSheetsRequests.ColumnNumber(startColumn) - 1;
int endColumnIndex = GoogleSheetsRequests.ColumnNumber(endColumn);
return(GetFormulaAndNumberFormatRequest(sheetId, formulaValue, startRowIndex, endRowIndex, startColumnIndex, endColumnIndex));
}
public static Request GetBasicFilterRequest(int sheetId, int startRowIndex, int endRowIndex, string startColumn, string endColumn)
{
int startColumnIndex = GoogleSheetsRequests.ColumnNumber(startColumn) - 1;
int endColumnIndex = GoogleSheetsRequests.ColumnNumber(endColumn);
return(GetBasicFilterRequest(sheetId, startRowIndex, endRowIndex, startColumnIndex, endColumnIndex));
}
public static Request HideColumnsRequest(int sheetId, string startColumn, string endColumn)
{
int startColumnIndex = GoogleSheetsRequests.ColumnNumber(startColumn) - 1;
int endColumnIndex = GoogleSheetsRequests.ColumnNumber(endColumn);
return(HideColumnsRequest(sheetId, startColumnIndex, endColumnIndex));
}
public int AddSheet(string sheetName, int columnCount = 26)
{
var batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest();
batchUpdateSpreadsheetRequest.Requests = new List <Request>();
// add the add sheet request
batchUpdateSpreadsheetRequest.Requests.Add(GoogleSheetsRequests.GetAddSheetRequest(sheetName, columnCount));
var batchUpdateRequest = Service.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequest, SPREADSHEET_ID);
var response = batchUpdateRequest.Execute();
if (response.Replies.Count() > 0)
{
AddSheetResponse addSheetResponse = (AddSheetResponse)response.Replies.FirstOrDefault().AddSheet;
return(addSheetResponse.Properties.SheetId.Value);
}
return(-1);
}
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);
}
