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); }