public (bool hasError, string message) InsertRowAtSpecifiedPlace(string sheetName, string sheetId, int startRowIndex, int endRowIndex) { try { Spreadsheet spreadsheet = _service.Spreadsheets.Get(sheetId).Execute(); Sheet sheet = spreadsheet.Sheets.Where(s => s.Properties.Title == sheetName).FirstOrDefault(); int curretSheetId = (int)sheet.Properties.SheetId; InsertRangeRequest insRow = new InsertRangeRequest(); insRow.Range = new GridRange() { SheetId = curretSheetId, StartRowIndex = startRowIndex, EndRowIndex = endRowIndex, }; insRow.ShiftDimension = "ROWS"; BatchUpdateSpreadsheetRequest request = new BatchUpdateSpreadsheetRequest() { Requests = new List <Request> { new Request { InsertRange = insRow } } }; BatchUpdateSpreadsheetResponse response1 = _service.Spreadsheets.BatchUpdate(request, sheetId).Execute(); return(hasError : false, message : "Row inserted successfully"); } catch (Exception ex) { return(hasError : true, message : ex.Message); } }
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; } }