// Inserts rows into the existing spreadsheet.
internal static SpreadsheetsResource.BatchUpdateRequest GetInsertRowsRequest(SheetsService sheetsService, string spreadsheetId, int?sheetId, int toBeInsertedTopRowIndex, int toBeInsertedRowCount)
{
// Build insert dimensions partial request...
InsertDimensionRequest request = new InsertDimensionRequest();
request.Range = new DimensionRange();
request.Range.SheetId = sheetId ?? throw new ArgumentNullException(nameof(sheetId));
request.Range.Dimension = "ROWS";
request.Range.StartIndex = toBeInsertedTopRowIndex;
request.Range.EndIndex = toBeInsertedTopRowIndex + toBeInsertedRowCount;
if (toBeInsertedTopRowIndex > 0)
{
request.InheritFromBefore = true;
}
// 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 {
InsertDimension = request
});
// 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));
}
public static void UpdateSheetAtParticularIndex(string newSheetName, string sheetName, int index, IUoW uow, int clientId, string sheetId, int apiKeySecretId = 20, int accountTypeId = 27)
{
try
{
var service = GetGoogleSpreadSheetService(uow, clientId, sheetId, apiKeySecretId, accountTypeId);
Spreadsheet spreadsheet = service.Spreadsheets.Get(sheetId).Execute();
Sheet sheet = spreadsheet.Sheets.Where(s => s.Properties.Title == sheetName).FirstOrDefault();
int curretSheetId = (int)sheet.Properties.SheetId;
var dd = sheet.Data;
//getting all data from sheet
var valTest = service.Spreadsheets.Values.Get(sheetId, $"{sheetName}!A2:D2").Execute();
var valvalues = valTest.Values;
//adding to specific row
InsertRangeRequest insRow = new InsertRangeRequest();
insRow.Range = new GridRange()
{
SheetId = curretSheetId,
StartRowIndex = 3,
EndRowIndex = 4,
};
insRow.ShiftDimension = "ROWS";
//sorting
SortRangeRequest sorting = new SortRangeRequest();
sorting.Range = new GridRange()
{
SheetId = curretSheetId,
StartColumnIndex = 0, // sorted by firstcolumn for all data after 1st row
StartRowIndex = 1
};
sorting.SortSpecs = new List <SortSpec>
{
new SortSpec
{
SortOrder = "DESCENDING"
}
};
//
InsertDimensionRequest insertRow = new InsertDimensionRequest();
insertRow.Range = new DimensionRange()
{
SheetId = curretSheetId,
Dimension = "ROWS",
StartIndex = 1, // 0 based
EndIndex = 2
};
var oblistt = new List <object>()
{
"Helloins", "This4ins", "was4ins", "insertd4ins"
};
PasteDataRequest data = new PasteDataRequest
{
Data = string.Join(",", oblistt),
Delimiter = ",",
// data gets inserted form this cordinate point( i.e column and row) index and to the right
Coordinate = new GridCoordinate
{
ColumnIndex = 0, // 0 based Col A is 0, col B is 1 and so on
RowIndex = 2, //
SheetId = curretSheetId
},
};
BatchUpdateSpreadsheetRequest r = new BatchUpdateSpreadsheetRequest()
{
Requests = new List <Request>
{
//new Request{ InsertDimension = insertRow },
//new Request{ PasteData = data },
//new Request { InsertRange = insRow},
new Request {
SortRange = sorting
}
}
};
BatchUpdateSpreadsheetResponse response1 = service.Spreadsheets.BatchUpdate(r, sheetId).Execute();
//adding data to sheet
var valueRange = new ValueRange();
var oblist = new List <object>()
{
"Hello466", "This466", "was466", "insertd466"
};
valueRange.Values = new List <IList <object> > {
oblist
};
var appendRequest = service.Spreadsheets.Values.Append(valueRange, sheetId, $"{sheetName}!A:D");
//appendRequest.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
appendRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
var appendReponse = appendRequest.Execute();
//updating data to sheet
var valueRangeUpdate = new ValueRange();
var oblistUpdate = new List <object>()
{
"Hello4uqq", "This4uqq", "was4uqq", "insertd4uqq"
};
valueRangeUpdate.Values = new List <IList <object> > {
oblistUpdate
};
var updateRequest = service.Spreadsheets.Values.Update(valueRangeUpdate, sheetId, $"{sheetName}!A10:D510");
updateRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
var updateReponse = updateRequest.Execute();
//deleting row from sheet
var range = $"{sheetName}!A7:G7";
var requestBody = new ClearValuesRequest();
var deleteRequest = service.Spreadsheets.Values.Clear(requestBody, sheetId, range);
var deleteReponse = deleteRequest.Execute();
// update new Sheet
var updateSheetRequest = new UpdateSheetPropertiesRequest();
updateSheetRequest.Properties = new SheetProperties();
updateSheetRequest.Properties.Title = sheetName; //Sheet1
updateSheetRequest.Properties.Index = index;
updateSheetRequest.Properties.SheetId = curretSheetId;
updateSheetRequest.Fields = "Index,Title";//fields that needs to be updated.* means all fields from that sheet
BatchUpdateSpreadsheetRequest batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest();
batchUpdateSpreadsheetRequest.Requests = new List <Request>();
batchUpdateSpreadsheetRequest.Requests.Add(new Request
{
UpdateSheetProperties = updateSheetRequest
});
var batchUpdateRequest = service.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequest, sheetId);
var response = batchUpdateRequest.Execute();
}
catch (Exception ex)
{
throw;
}
}