// Appends existing sheet with specified rows.
internal static SpreadsheetsResource.ValuesResource.AppendRequest GetAppendRowsRequest(SheetsService sheetsService, string spreadsheetId, string sheetTitleId, int columnCount, int rowCount, int rowsToAdd, IList <IList <object> > newRowsData)
{
// Construct string representing range(rows) appending the sheet
string appendedSheetRange = RangeTranslator.GetRangeString(sheetTitleId, LiveSheetLeftIndex, LiveSheetTopIndex, columnCount, rowCount + rowsToAdd);
// Construct requestBody value range.
ValueRange requestBody = new ValueRange();
// Assign request body range string.
requestBody.Range = appendedSheetRange;
// Assign request body values.
requestBody.Values = newRowsData;
// Constructs append rows request.
SpreadsheetsResource.ValuesResource.AppendRequest request = sheetsService.Spreadsheets.Values.Append(requestBody, spreadsheetId, appendedSheetRange);
// Specify the way in which input data should be interpreted.
request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW;
// Specify the way in which input data should be inserted.
request.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
// Return constructed append rows request.
return(request);
}
// sheet title id, sheet grid data
public static IDictionary <string, IList <IList <string> > > GetDataAsStringsGridsIndex(this Spreadsheet spreadsheet, IDictionary <string, string> requiredRangesBySheetTitleId)
{
IDictionary <string, IList <IList <string> > > stringsGridsIndex = new Dictionary <string, IList <IList <string> > >();
HashSet <string> requiredSheetTitleIds = new HashSet <string>(requiredRangesBySheetTitleId.Keys);
// Foreach sheet
foreach (string requiredSheetTitleId in requiredSheetTitleIds)
{
string sheetRequiredRange = requiredRangesBySheetTitleId[requiredSheetTitleId];
// Declare range translator out variables
// Translate string range back in to separate range parameters
RangeTranslator.RangeStringToParameters(sheetRequiredRange, out string dummyString, out int sheetRequiredLeftIndex, out int sheetRequiredTopIndex, out int sheetRequiredColumnCount, out int sheetRequiredRowCount);
// Add sheet strings grids index placeholder dictionary entry
stringsGridsIndex.Add(requiredSheetTitleId, null);
// TODO confirm spreadsheet.Sheets is never null
// Obtain sheet data if any available
Sheet sheet = spreadsheet.Sheets.Where(s => s.Properties.Title == sheetRequiredRange).FirstOrDefault();
if (!(sheet is null))
{
// Fill that sheet with a list representing required amount of rows.
stringsGridsIndex[requiredSheetTitleId] = sheet.GetDataAsStringsGrid(sheetRequiredColumnCount, sheetRequiredRowCount);
}
// 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);
}
}
