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