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