// Returns spreadsheet with only listed sheets filled with data, and the non listed ones being obtained only as a sheet dataless shells.
internal static async Task <Spreadsheet> GetSpreadsheetPartiallyAsync(this SheetsService sheetsService, string spreadsheetId, IList <string> sheetTitleIds)
{
// Wait for google apis request quota availability.
await SessionRequestsLimiter.Instance.WaitAsync();
// Obtain get spreadsheet shell request
SpreadsheetsResource.GetRequest getSpreadsheetShellRequest = GoogleServicesExtensionsRequestsFactory.GetGetSpreadsheetShellRequest(sheetsService, spreadsheetId);
// Execute google request in a safe synchronous manner.
Spreadsheet spreadsheet = await RequestsExecutor.SafeExecuteAsync <Spreadsheet>(getSpreadsheetShellRequest);
// Wait for google apis request quota availability.
await SessionRequestsLimiter.Instance.WaitAsync();
// Obtain get spreadsheet listed sheets only request
SpreadsheetsResource.GetRequest getSpreadsheetListedSheetsOnlyRequest = GoogleServicesExtensionsRequestsFactory.GetGetSpreadsheetListedSheetsOnlyRequest(sheetsService, spreadsheetId, sheetTitleIds);
// Execute google request in a safe synchronous manner.
Spreadsheet spreadsheetSheetsData = await RequestsExecutor.SafeExecuteAsync <Spreadsheet>(getSpreadsheetListedSheetsOnlyRequest);
// Loops through sheets with data and replaces they empty representations in the spreadsheetDataShell with them.
foreach (Sheet sheetData in spreadsheetSheetsData.Sheets)
{
// Find spreadsheet shell
Sheet sheetSheetToBeReplaced = spreadsheet.Sheets.Where((sheetShell) => { return(sheetShell.Properties.Title == sheetData.Properties.Title); }).First();
// Replace sheet shell, with instance of a sheet containing data.s
spreadsheet.Sheets.Remove(sheetSheetToBeReplaced);
spreadsheet.Sheets.Add(sheetData);
}
// Return spreadsheet shell filled with data of the listed sheets.
return(spreadsheet);
}
// Removes existing spreadsheet.
internal static void RemoveSpreadsheetSync(this DriveService driveService, string spreadsheetId)
{
// Wait for google apis request quota availability.
SessionRequestsLimiter.Instance.Wait();
// Get appropriate request instance
FilesResource.DeleteRequest removeSpreadsheetRequest = GoogleServicesExtensionsRequestsFactory.GetRemoveSpreadsheetRequest(driveService, spreadsheetId);
// Execute removeSpreadsheetRequest in safe, synchronous manner.
RequestsExecutor.SafeExecuteSync(removeSpreadsheetRequest);
}
// Assures that existing spreadsheet edit rights are available for the specified email address.
internal static async Task AssureSpreadsheetEditIsEmailPermittedAsync(this DriveService driveService, string spreadsheetId, string email)
{
// Wait for google apis request quota availability.
await SessionRequestsLimiter.Instance.WaitAsync();
// Construct new instance of batch request
BatchRequest emailAddressCanEditRequest = GoogleServicesExtensionsRequestsFactory.GetAssureEditIsEmailPermittedRequest(driveService, spreadsheetId, email);
// Execute batch request in safe synchronous way.
await RequestsExecutor.SafeExecuteAsync(emailAddressCanEditRequest);
}
// Assures that existing spreadsheet edit rights are available for the specified domain.
internal static void AssureSpreadsheetEditIsDomainPermittedSync(this DriveService driveService, string spreadsheetId, string domain)
{
// Wait for google apis request quota availability.
SessionRequestsLimiter.Instance.Wait();
// Construct new instance of batch request
BatchRequest domainCanEditRequest = GoogleServicesExtensionsRequestsFactory.GetAssureEditIsDomainPermittedRequest(driveService, spreadsheetId, domain);
// Execute batch request in safe synchronous way.
RequestsExecutor.SafeExecuteSync(domainCanEditRequest);
}
// Assures that existing spreadsheet edit rights are public.
internal static async Task AssureSpreadsheetIsEditPublicAsync(this DriveService driveService, string spreadsheetId)
{
// Wait for google apis request quota availability.
await SessionRequestsLimiter.Instance.WaitAsync();
// Construct new instance of batch request
BatchRequest anyoneCanEditRequest = GoogleServicesExtensionsRequestsFactory.GetAssureEditIsPublicRequest(driveService, spreadsheetId);
// Execute batch request in safe asynchronous way.
await RequestsExecutor.SafeExecuteAsync(anyoneCanEditRequest);
}
// Create new spreadsheet from dateless blueprint
internal static async Task <Spreadsheet> AddSpreadsheetAsync(this SheetsService sheetsService, string spreadsheetTitle, IList <Tuple <string, int, int> > spreadsheetSizeBlueprint)
{
// Wait for google apis request quota availability.
await SessionRequestsLimiter.Instance.WaitAsync();
// Obtain create spreadsheet request.
SpreadsheetsResource.CreateRequest createSpreadsheetRequest = GoogleServicesExtensionsRequestsFactory.GetAddSpreadsheetRequest(sheetsService, spreadsheetTitle, spreadsheetSizeBlueprint);
// Execute createSpreadsheetRequest in safe asynchronous manner, and return the result
return(await RequestsExecutor.SafeExecuteAsync <Spreadsheet>(createSpreadsheetRequest));
}
// Assures that existing spreadsheet view is public.
internal static void AssureSpreadsheetViewIsPublicSync(this DriveService driveService, string spreadsheetId)
{
// Wait for google apis request quota availability.
SessionRequestsLimiter.Instance.Wait();
// Construct new instance of batch request
BatchRequest anyoneCanViewRequest = GoogleServicesExtensionsRequestsFactory.GetAssureViewIsPublicRequest(driveService, spreadsheetId);
// Execute batch request in safe synchronous way.
RequestsExecutor.SafeExecuteSync(anyoneCanViewRequest);
}
// Adjust multiple rows ranges height dimensions.
internal static async Task AdjustMultipleRowsRangesHeightDimensionsAsync(this SheetsService sheetsService, string commonSpreadsheetId, IList <Tuple <int?, int, int, int> > rowsRangesDiemansionsAdjustmentBlueprint)
{
// Wait for google apis request quota availability.
await SessionRequestsLimiter.Instance.WaitAsync();
// Obtain appropriate adjust multiple rows ranges height dimension request
SpreadsheetsResource.BatchUpdateRequest adjustMultipleRowsRangesHeightDimensionsRequest = GoogleServicesExtensionsRequestsFactory.GetAdjustMultipleRowsRangesHeightDimensionsRequest(sheetsService, commonSpreadsheetId, rowsRangesDiemansionsAdjustmentBlueprint);
// Execute adjust multiple spreadsheet rows ranges height dimensions sheet request in safe asynchronous manner.
await RequestsExecutor.SafeExecuteAsync <BatchUpdateSpreadsheetResponse>(adjustMultipleRowsRangesHeightDimensionsRequest);
}
// Removes existing spreadsheet asynchronously.
internal static async Task RemoveSpreadsheetAsync(this DriveService driveService, string spreadsheetId)
{
// Await for google apis request quota availability.
await SessionRequestsLimiter.Instance.WaitAsync();
// Get appropriate request instance
FilesResource.DeleteRequest removeSpreadsheetRequest = GoogleServicesExtensionsRequestsFactory.GetRemoveSpreadsheetRequest(driveService, spreadsheetId);
// Await execution results of the removeSpreadsheetRequest in safe, asynchronous manner.
await RequestsExecutor.SafeExecuteAsync(removeSpreadsheetRequest);
}
// Returns existing spreadsheet dataless shell
internal static Spreadsheet GetSpreadsheetShellSync(this SheetsService sheetsService, string spreadsheetId)
{
// Wait for google apis request quota availability.
SessionRequestsLimiter.Instance.Wait();
// Obtain get spreadsheet request
SpreadsheetsResource.GetRequest getSpreadsheetShellRequest = GoogleServicesExtensionsRequestsFactory.GetGetSpreadsheetShellRequest(sheetsService, spreadsheetId);
// Execute google request in a safe synchronous manner.
return(RequestsExecutor.SafeExecuteSync <Spreadsheet>(getSpreadsheetShellRequest));
}
// Update data stored in the specified range.
internal static void UpdateRangeDataSync(this SheetsService sheetsService, string spreadsheetId, string rangeString, IList <IList <object> > rangeData)
{
// Wait for google apis request quota availability.
SessionRequestsLimiter.Instance.Wait();
// Construct appropriate update range data google apis request.
SpreadsheetsResource.ValuesResource.BatchUpdateRequest updateRangeDataRequest = GoogleServicesExtensionsRequestsFactory.GetUpdateRangeDataRequest(sheetsService, spreadsheetId, rangeString, rangeData);
// Execute update range data request in safe synchronous manner.
RequestsExecutor.SafeExecuteSync <BatchUpdateValuesResponse>(updateRangeDataRequest);
}
// Add new sheet into the existing spreadsheet specified with provided spreadsheetId.
internal static async Task AddSheetAsync(this SheetsService sheetsService, string spreadsheetId, string sheetTitleId, int columnCount, int rowCount)
{
// Wait for google apis request quota availability.
await SessionRequestsLimiter.Instance.WaitAsync();
// Obtain appropriate add sheet BatchUpdateRequest.
SpreadsheetsResource.BatchUpdateRequest addSheetRequest = GoogleServicesExtensionsRequestsFactory.GetAddSheetRequest(sheetsService, spreadsheetId, sheetTitleId, columnCount, rowCount);
// Execute add sheet request in safe synchronous manner.
await RequestsExecutor.SafeExecuteAsync <BatchUpdateSpreadsheetResponse>(addSheetRequest);
}
// Removes specified rows range from the existing sheet.
internal static async Task RemoveRowsAsync(this SheetsService sheetsService, string spreadsheetId, string sheetTitleId, int?sheetId, int toBeRemovedTopRowIndex, int toBeRemovedRowCount)
{
// Wait for google apis request quota availability.
await SessionRequestsLimiter.Instance.WaitAsync();
// Obtain appropriate remove rows batch request.
SpreadsheetsResource.BatchUpdateRequest removeRowsRequest = GoogleServicesExtensionsRequestsFactory.GetRemoveRowsRequest(sheetsService, spreadsheetId, sheetId, toBeRemovedTopRowIndex, toBeRemovedRowCount);
// Execute remove rows request in safe synchronous manner.
await RequestsExecutor.SafeExecuteAsync <BatchUpdateSpreadsheetResponse>(removeRowsRequest);
}
// Appends existing sheet with new range of rows
internal static async Task AppendRowsAsync(this SheetsService sheetsService, string spreadsheetId, string sheetTitleId, int columnCount, int rowCount, int rowsToAdd, IList <IList <object> > newRowsData)
{
// Wait for google apis request quota availability.
await SessionRequestsLimiter.Instance.WaitAsync();
// Obtain appropriate append rows request.
SpreadsheetsResource.ValuesResource.AppendRequest appendRowsRequest = GoogleServicesExtensionsRequestsFactory.GetAppendRowsRequest(sheetsService, spreadsheetId, sheetTitleId, columnCount, rowCount, rowsToAdd, newRowsData);
// Execute append sheet rows request in safe synchronous manner.
await RequestsExecutor.SafeExecuteAsync <AppendValuesResponse>(appendRowsRequest);
}
// Adjust multiple columns ranges width dimensions.
internal static void AdjustMultipleColumnsRangesWidthDimensionsSync(this SheetsService sheetsService, string commonSpreadsheetId, IList <Tuple <int?, int, int, int> > columnsRangesDiemansionsAdjustmentBlueprint)
{
// Wait for google apis request quota availability.
SessionRequestsLimiter.Instance.Wait();
// Obtain appropriate adjust multiple columns ranges width dimension request
SpreadsheetsResource.BatchUpdateRequest adjustMultipleColumnsRangesWidthDimansionsRequest = GoogleServicesExtensionsRequestsFactory.GetAdjustMultipleColumnsRangesWidthDimensionsRequest(sheetsService, commonSpreadsheetId, columnsRangesDiemansionsAdjustmentBlueprint);
// Execute adjust multiple spreadsheet columns ranges with dimensions sheet request in safe synchronous manner.
RequestsExecutor.SafeExecuteSync <BatchUpdateSpreadsheetResponse>(adjustMultipleColumnsRangesWidthDimansionsRequest);
}
// Retrieves sheet int? Id based on the provided spreadsheetId and sheetTitleId.
internal static async Task <int?> GetSheetIdFromSheetTitleIdIdAsync(this SheetsService sheetsService, string spreadsheetId, string sheetTitleId)
{
// Wait for google apis request quota availability.
await SessionRequestsLimiter.Instance.WaitAsync();
// Obtain get spreadsheet shell request.
SpreadsheetsResource.GetRequest request = GoogleServicesExtensionsRequestsFactory.GetGetSpreadsheetShellRequest(sheetsService, spreadsheetId);
// Get spreadsheet shell data in safe synchronous way.
Spreadsheet spreadsheetShellData = await RequestsExecutor.SafeExecuteAsync <Spreadsheet>(request);
// Loop through all the sheet shells and return the int? sheetId of the one with Title equal to provided sheetTitleId.
return(spreadsheetShellData.Sheets.Where((sheetShell) => { return sheetShell.Properties.Title == sheetTitleId; }).First().Properties.SheetId);
}
// Return range data as grid of objects, where each object represents content of the cell in the grid.
internal static async Task <IList <object> > GetObjectsRowDataAsync(this SheetsService sheetsService, string spreadsheetId, string rowRangeString)
{
// Wait for google apis request quota availability.
await SessionRequestsLimiter.Instance.WaitAsync();
// Obtain appropriate get row data request.
SpreadsheetsResource.ValuesResource.GetRequest getRowDataRequest = GoogleServicesExtensionsRequestsFactory.GetGetRangeDataRequest(sheetsService, spreadsheetId, rowRangeString);
// Execute get row data request in safe synchronous manner.
ValueRange getRangeDataResponse = await RequestsExecutor.SafeExecuteAsync <ValueRange>(getRowDataRequest);
// Return response objects data grid
return(getRangeDataResponse.Values?[0]);
}
internal static object GetObjectCellDataSync(this SheetsService sheetsService, string spreadsheetId, string cellRangeString)
{
// Wait for google apis request quota availability.
SessionRequestsLimiter.Instance.Wait();
// Obtain appropriate get cell data request.
SpreadsheetsResource.ValuesResource.GetRequest getCellDataRequest = GoogleServicesExtensionsRequestsFactory.GetGetRangeDataRequest(sheetsService, spreadsheetId, cellRangeString);
// Execute get cell data request in safe synchronous manner.
ValueRange getRangeDataResponse = RequestsExecutor.SafeExecuteSync <ValueRange>(getCellDataRequest);
// Return response objects data grid
return(getRangeDataResponse.Values?[0]?[0]);
}
// Adjust columns range width dimensions.
internal static void AdjustColumnsWidthDimensionSync(this SheetsService sheetsService, string spreadsheetId, int?sheetId, int leftColumnIndex, int columnsToAdjustWidthCount, int columnWidthPixelsCount)
{
// If provided sheetId is null, throw appropriate exception.
if (sheetId is null)
{
throw new ArgumentNullException(nameof(sheetId));
}
// Wait for google apis request quota availability.
SessionRequestsLimiter.Instance.Wait();
// Obtain adjust columns width dimension request.
SpreadsheetsResource.BatchUpdateRequest adjustColumnsWidthDimensionRequest = GoogleServicesExtensionsRequestsFactory.GetAdjustColumnsWidthDimensionRequest(sheetsService, spreadsheetId, sheetId, leftColumnIndex, columnsToAdjustWidthCount, columnWidthPixelsCount);
// Execute add sheet request in safe synchronous manner.
RequestsExecutor.SafeExecuteSync <BatchUpdateSpreadsheetResponse>(adjustColumnsWidthDimensionRequest);
}
// Remove existing, (not-last in a spreadsheet) sheet from the specified existing spreadsheet.
internal static async Task RemoveSheetAsync(this SheetsService sheetsService, string spreadsheetId, int?sheetId)
{
// If provided sheetId is null, throw appropriate exception.
if (sheetId is null)
{
throw new ArgumentNullException(nameof(sheetId));
}
// Wait for google apis request quota availability.
await SessionRequestsLimiter.Instance.WaitAsync();
// Obtain appropriate remove sheet BatchUpdateRequest.
SpreadsheetsResource.BatchUpdateRequest removeSheetRequest = GoogleServicesExtensionsRequestsFactory.GetRemoveSheetRequest(sheetsService, spreadsheetId, sheetId);
// Execute remove sheet request in safe asynchronous manner.
await RequestsExecutor.SafeExecuteAsync <BatchUpdateSpreadsheetResponse>(removeSheetRequest);
}
// Adjust rows range height dimensions.
internal static async Task AdjustRowsHeightDimensionAsync(this SheetsService sheetsService, string spreadsheetId, int?sheetId, int topRowIndex, int rowsToAdjustHeightCount, int rowHeightPixelsCount)
{
// If provided sheetId is null, throw appropriate exception.
if (sheetId is null)
{
throw new ArgumentNullException(nameof(sheetId));
}
// Wait for google apis request quota availability.
await SessionRequestsLimiter.Instance.WaitAsync();
// Obtain adjust columns width dimension request.
SpreadsheetsResource.BatchUpdateRequest adjustRowsHeightDimensionRequest = GoogleServicesExtensionsRequestsFactory.GetAdjustRowsHeightDimensionRequest(sheetsService, spreadsheetId, sheetId, topRowIndex, rowsToAdjustHeightCount, rowHeightPixelsCount);
// Execute add sheet request in safe synchronous manner.
await RequestsExecutor.SafeExecuteAsync <BatchUpdateSpreadsheetResponse>(adjustRowsHeightDimensionRequest);
}
// Create new spreadsheet from data containing spreadsheet blueprint
internal static Spreadsheet AddSpreadsheetSync(this SheetsService sheetsService, string spreadsheetTitle, IList <Tuple <string, int, int, IList <IList <string> > > > spreadsheetBlueprint)
{
// Wait for google apis request quota availability.
SessionRequestsLimiter.Instance.Wait();
// Obtain create spreadsheet request.
SpreadsheetsResource.CreateRequest createSpreadsheetRequest = GoogleServicesExtensionsRequestsFactory.GetAddSpreadsheetRequest(sheetsService, spreadsheetTitle, spreadsheetBlueprint);
// Execute createSpreadsheetRequest in safe synchronous manner, and store revived dateless spreadsheet instance
Spreadsheet spreadsheet = RequestsExecutor.SafeExecuteSync <Spreadsheet>(createSpreadsheetRequest);
// Obtain get spreadsheet request.
SpreadsheetsResource.GetRequest getSpreadsheetRequest = GoogleServicesExtensionsRequestsFactory.GetGetSpreadsheetRequest(sheetsService, spreadsheet.SpreadsheetId);
// Execute getSpreadsheetRequest in safe synchronous manner.
return(RequestsExecutor.SafeExecuteSync <Spreadsheet>(getSpreadsheetRequest));
}
// Inserts rows range into the existing sheet
internal static async Task InsertRowsAsync(this SheetsService sheetsService, string spreadsheetId, string sheetTitleId, int?sheetId, int columnCount, int toBeInsertedTopRowIndex, int toBeInsertedRowCount, IList <IList <object> > newRowsData)
{
// Wait for google apis request quota availability.
await SessionRequestsLimiter.Instance.WaitAsync();
// Obtain appropriate insert row batch request.
SpreadsheetsResource.BatchUpdateRequest insertRowsRequest = GoogleServicesExtensionsRequestsFactory.GetInsertRowsRequest(sheetsService, spreadsheetId, sheetId, toBeInsertedTopRowIndex, toBeInsertedRowCount);
// Execute insert sheet rows request in safe synchronous manner.
await RequestsExecutor.SafeExecuteAsync <BatchUpdateSpreadsheetResponse>(insertRowsRequest);
// If new rows data haven't been provided
if (!(newRowsData is null))
{
// Obtain new rows range string.
string newRowsRangeString = RangeTranslator.GetRangeString(sheetTitleId, 0, toBeInsertedTopRowIndex, columnCount, toBeInsertedRowCount);
// Fills inserted rows with provided data.
await sheetsService.UpdateRangeDataAsync(spreadsheetId, newRowsRangeString, newRowsData);
}
}
