public async Task ClearArea(string range, string spreadsheetId) { Data.ClearValuesRequest requestBody = new Data.ClearValuesRequest(); SpreadsheetsResource.ValuesResource.ClearRequest request = _service.Spreadsheets.Values.Clear(requestBody, spreadsheetId, range); Data.ClearValuesResponse response = await request.ExecuteAsync(); }
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); } } }