public async Task <string> CreateSheet() { base.BeginBatch(); base.AddNewSheet(StarSymbol + "Voters", 1, "VoterId", "Timestamp", "Email"); var formatRequest = new RepeatCellRequest { Range = new GridRange { SheetId = 1, StartColumnIndex = 1, EndColumnIndex = 2, StartRowIndex = 0 }, Cell = new CellData { UserEnteredFormat = new CellFormat { NumberFormat = new NumberFormat { Type = "DATE", Pattern = "M/d/yy hh:mm" } } }, Fields = "userEnteredFormat.numberFormat" }; AddToBatch(new Request { RepeatCell = formatRequest }); var batch = EndBatch(); var response = await batch.ExecuteAsync().ConfigureAwait(false); return(JsonConvert.SerializeObject(response)); }
/// <summary> /// Creates a request to format cells /// <para></para> /// THROWS: InvalidOperationException /// </summary> /// <param name="spreadsheetId">The id of the sheets doc</param> /// <param name="tabName">The name of the tab to be formatted</param> /// <param name="format">The cell format to apply to the cells</param> /// <param name="range">The range to apply the format to</param> /// <returns></returns> private static Request requestFormatCells(string spreadsheetId, string tabName, CellFormat format, GridRange range) { int?sheetIndex = getTabIndex(spreadsheetId, tabName); // if the name was not found return if (sheetIndex == null) { throw new InvalidOperationException(m_errorTabDoesNotExist); } CellData cellData = new CellData(); cellData.UserEnteredFormat = format; GridRange grid = range; RepeatCellRequest repeatRequest = new RepeatCellRequest(); repeatRequest.Cell = cellData; repeatRequest.Fields = "UserEnteredFormat"; repeatRequest.Range = grid; Request request = new Request(); request.RepeatCell = repeatRequest; return(request); }
protected void AddCentering(GridRange range) { var formatRequest = new RepeatCellRequest { Range = range, Cell = new CellData { UserEnteredFormat = new CellFormat { HorizontalAlignment = "CENTER", WrapStrategy = "WRAP" } }, Fields = "userEnteredFormat" }; _requests.Add(new Request { RepeatCell = formatRequest }); }
protected void AddDateFormating(GridRange range) { var formatRequest = new RepeatCellRequest { Range = range, Cell = new CellData { UserEnteredFormat = new CellFormat { NumberFormat = new NumberFormat { Type = "DATE", Pattern = "M/d/yy hh:mm" } } }, Fields = "userEnteredFormat.numberFormat" }; _requests.Add(new Request { RepeatCell = formatRequest }); }
private static async System.Threading.Tasks.Task CreateSheetAsync(SheetsService service, string spreadsheetId, string name, SheetConfig config) { try { var addSheetRequest = new AddSheetRequest(); addSheetRequest.Properties = new SheetProperties(); addSheetRequest.Properties.Title = name; addSheetRequest.Properties.Hidden = config.IsSheetHidden(name); BatchUpdateSpreadsheetRequest batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest(); batchUpdateSpreadsheetRequest.Requests = new List <Request>(); batchUpdateSpreadsheetRequest.Requests.Add(new Request { AddSheet = addSheetRequest }); var batchUpdateRequest = service.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequest, spreadsheetId); await batchUpdateRequest.ExecuteAsync().ConfigureAwait(false); await Throttler.ThrottleCheck().ConfigureAwait(false); var repeatCellRequest = new RepeatCellRequest(); repeatCellRequest.Range = new GridRange() { SheetId = await GetSheetIdFromSheetNameAsync(service, spreadsheetId, name).ConfigureAwait(false), StartRowIndex = 0, StartColumnIndex = 0, EndColumnIndex = 1000 }; repeatCellRequest.Fields = "userEnteredFormat.numberFormat"; repeatCellRequest.Cell = new CellData() { UserEnteredFormat = new CellFormat() { NumberFormat = new NumberFormat() { Type = "TEXT" } } }; batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest(); batchUpdateSpreadsheetRequest.Requests = new List <Request>(); batchUpdateSpreadsheetRequest.Requests.Add(new Request { RepeatCell = repeatCellRequest }); batchUpdateRequest = service.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequest, spreadsheetId); await batchUpdateRequest.ExecuteAsync().ConfigureAwait(false); await Throttler.ThrottleCheck().ConfigureAwait(false); } catch { try { ClearValuesRequest clearValuesRequest = new ClearValuesRequest(); SpreadsheetsResource.ValuesResource.ClearRequest request = service.Spreadsheets.Values.Clear(clearValuesRequest, spreadsheetId, name + "!$A$1:$YY"); ClearValuesResponse response = await request.ExecuteAsync().ConfigureAwait(false); await Throttler.ThrottleCheck().ConfigureAwait(false); //remove validations BatchUpdateSpreadsheetRequest batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest(); batchUpdateSpreadsheetRequest.Requests = new List <Request>(); batchUpdateSpreadsheetRequest.Requests.Add(new Request() { SetDataValidation = new SetDataValidationRequest() { Range = new GridRange() { SheetId = await GetSheetIdFromSheetNameAsync(service, spreadsheetId, name).ConfigureAwait(false), StartRowIndex = 0, StartColumnIndex = 0, EndColumnIndex = 1000 } } }); var batchUpdateRequest = service.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequest, spreadsheetId); await batchUpdateRequest.ExecuteAsync().ConfigureAwait(false); await Throttler.ThrottleCheck().ConfigureAwait(false); } catch (Exception e) { //sheet doesn`t exist throw new ServiceDirectoryException("Unable to create spreadsheet", e); } } }