// Private Helper - provide partial request for adjusting rows height dimension using BatchUpdateRequest.
private static UpdateDimensionPropertiesRequest GetAdjustRowsHeightDimensionPartialRequest(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));
}
// Create appropriate partial request of type UpdateDimensionPropertiesRequest, where rows are considered as the dimension to adjust size.
UpdateDimensionPropertiesRequest updateDimensionPropertiesRequest = new UpdateDimensionPropertiesRequest();
updateDimensionPropertiesRequest.Range = new DimensionRange();
updateDimensionPropertiesRequest.Range.SheetId = sheetId ?? throw new ArgumentNullException(nameof(sheetId));
updateDimensionPropertiesRequest.Range.Dimension = "ROWS";
updateDimensionPropertiesRequest.Range.StartIndex = topRowIndex;
updateDimensionPropertiesRequest.Range.EndIndex = topRowIndex + rowsToAdjustHeightCount;
// Create new instance of DiemansionProperties, and set its new pixel size according the value of the provided rowHeightPixelsCount parameter.
updateDimensionPropertiesRequest.Properties = new DimensionProperties();
updateDimensionPropertiesRequest.Properties.PixelSize = rowHeightPixelsCount;
// Set fields to be update to "pixelSize" string.
updateDimensionPropertiesRequest.Fields = "pixelSize";
// Return update rows height dimension partial request.
return(updateDimensionPropertiesRequest);
}
// Adjust the pixelSize rows height.
internal static SpreadsheetsResource.BatchUpdateRequest GetAdjustRowsHeightDimensionRequest(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));
}
// Create appropriate partial request of type UpdateDimensionPropertiesRequest, where rows are considered as the dimension to adjust size.
UpdateDimensionPropertiesRequest updateDimensionPropertiesRequest = GoogleServicesExtensionsRequestsFactory.GetAdjustRowsHeightDimensionPartialRequest(spreadsheetId, sheetId, topRowIndex, rowsToAdjustHeightCount, rowHeightPixelsCount);
// Construct new batch update request body..
BatchUpdateSpreadsheetRequest batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest();
// .. initialize new list of partial requests ..
batchUpdateSpreadsheetRequest.Requests = new List <Request>();
// .. and add appropriate partial request.
batchUpdateSpreadsheetRequest.Requests.Add(new Request {
UpdateDimensionProperties = updateDimensionPropertiesRequest
});
// Set ResponseIncludeGridData flag to false to increase performance.
batchUpdateSpreadsheetRequest.ResponseIncludeGridData = false;
// Construct and return appropriate batch update request based on the provided request body and spreadsheet id.
return(sheetsService.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequest, spreadsheetId));
}
private static async System.Threading.Tasks.Task HideColumnAsync(SheetsService service, string spreadsheetId, string name, int index, SheetConfig config)
{
try
{
var updateDimensionRequest = new UpdateDimensionPropertiesRequest();
updateDimensionRequest.Properties = new DimensionProperties();
updateDimensionRequest.Properties.HiddenByUser = true;
updateDimensionRequest.Fields = "hiddenByUser";
updateDimensionRequest.Range = new DimensionRange()
{
Dimension = "COLUMNS",
SheetId = await GetSheetIdFromSheetNameAsync(service, spreadsheetId, name).ConfigureAwait(false),
StartIndex = index,
EndIndex = index + 1
};
BatchUpdateSpreadsheetRequest batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest();
batchUpdateSpreadsheetRequest.Requests = new List <Request>();
batchUpdateSpreadsheetRequest.Requests.Add(new Request
{
UpdateDimensionProperties = updateDimensionRequest
});
var batchUpdateRequest = service.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequest, spreadsheetId);
await batchUpdateRequest.ExecuteAsync().ConfigureAwait(false);
}
finally
{
await Throttler.ThrottleCheck().ConfigureAwait(false);
}
}
// Adjust the pixelSize width of the multiple specified rows ranges.
internal static SpreadsheetsResource.BatchUpdateRequest GetAdjustMultipleRowsRangesHeightDimensionsRequest(SheetsService sheetsService, string commonSpreadsheetId, IList <Tuple <int?, int, int, int> > rowsRangesDimensionsAdjustmentBlueprint)
{
// Declare partial requests list container.
IList <Request> requests = new List <Request>();
// Loop through all the columns ranges dimensions adjustment blueprints.
foreach (Tuple <int?, int, int, int> rowRangeDiemansionsAdjustmentBlueprint in rowsRangesDimensionsAdjustmentBlueprint)
{
// Obtain sheet id from currently looped through Tuple blueprint
int?sheetId = rowRangeDiemansionsAdjustmentBlueprint.Item1 ?? throw new ArgumentNullException(nameof(sheetId));
// Obtain index of the most top row which height will be affected.
int topRowIndex = rowRangeDiemansionsAdjustmentBlueprint.Item2;
// Obtain number of rows to adjust start counting from the one specified with topRowIndex to the bottom.
int rowsToAdjustHeightCount = rowRangeDiemansionsAdjustmentBlueprint.Item3;
// Obtain height in pixels of the rows in the range after adjustment.
int rowHeightPixelsCount = rowRangeDiemansionsAdjustmentBlueprint.Item4;
// Get appropriate partial update columns width dimensions request ...
UpdateDimensionPropertiesRequest updateDimensionPropertiesRequest = GoogleServicesExtensionsRequestsFactory.GetAdjustRowsHeightDimensionPartialRequest(commonSpreadsheetId, sheetId, topRowIndex, rowsToAdjustHeightCount, rowHeightPixelsCount);
// .. and add it into the collection contain all the partial requests.
requests.Add(new Request {
UpdateDimensionProperties = updateDimensionPropertiesRequest
});
}
// Construct new batch update request body..
BatchUpdateSpreadsheetRequest batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest();
// .. initialize and assign gathered collection of partial requests.
batchUpdateSpreadsheetRequest.Requests = requests;
// Set ResponseIncludeGridData flag to false to increase performance.
batchUpdateSpreadsheetRequest.ResponseIncludeGridData = false;
// Construct and return appropriate batch update request based on the provided request body and spreadsheet id.
return(sheetsService.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequest, commonSpreadsheetId));
}
// Private Helper - provide partial request for adjusting columns width dimension using BatchUpdateRequest.
private static UpdateDimensionPropertiesRequest GetAdjustColumnsWidthDimensionPartialRequest(string spreadsheetId, int?sheetId, int leftColumnIndex, int columnsToAdjustWidthCount, int columnWidthPixelsCount)
{
// Create appropriate partial request of type UpdateDimensionPropertiesRequest, where columns are considered as the dimension to adjust size.
UpdateDimensionPropertiesRequest updateDimensionPropertiesRequest = new UpdateDimensionPropertiesRequest();
updateDimensionPropertiesRequest.Range = new DimensionRange();
updateDimensionPropertiesRequest.Range.SheetId = sheetId ?? throw new ArgumentNullException(nameof(sheetId));
updateDimensionPropertiesRequest.Range.Dimension = "COLUMNS";
updateDimensionPropertiesRequest.Range.StartIndex = leftColumnIndex;
updateDimensionPropertiesRequest.Range.EndIndex = leftColumnIndex + columnsToAdjustWidthCount;
// Create new instance of DiemansionProperties, and set its new pixel size according the value of the provided columnWidthPixelsCount parameter.
updateDimensionPropertiesRequest.Properties = new DimensionProperties();
updateDimensionPropertiesRequest.Properties.PixelSize = columnWidthPixelsCount;
// Set fields to be update to "pixelSize" string.
updateDimensionPropertiesRequest.Fields = "pixelSize";
// Return update columns width dimension partial request.
return(updateDimensionPropertiesRequest);
}
