private async Task ResetSorting(SpreadsheetsResource spreadsheets) { ValueRange response = await spreadsheets.Values.Get(this.spreadsheetId, this.headerRange).ExecuteAsync(); this.LoadHeaderIndexes(response.Values.FirstOrDefault()); List <Request> list = new List <Request>(); ClearBasicFilterRequest clearFilter = new ClearBasicFilterRequest() { SheetId = this.dataSheetId }; list.Add(new Request() { ClearBasicFilter = clearFilter }); SortRangeRequest sortRequest = new SortRangeRequest() { Range = new GridRange() { SheetId = this.dataSheetId, StartColumnIndex = 0 }, SortSpecs = new List <SortSpec>() { new SortSpec() { DimensionIndex = this.dateRow, SortOrder = "DESCENDING" } } }; list.Add(new Request() { SortRange = sortRequest }); SetBasicFilterRequest setFilter = new SetBasicFilterRequest() { Filter = new BasicFilter() { Range = new GridRange() { SheetId = this.dataSheetId, StartColumnIndex = 0 }, } }; list.Add(new Request() { SetBasicFilter = setFilter }); BatchUpdateSpreadsheetRequest updateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest() { Requests = list }; BatchUpdateSpreadsheetResponse updateResponse = await spreadsheets.BatchUpdate(updateSpreadsheetRequest, this.spreadsheetId).ExecuteAsync(); this.logger.Debug($"Updated filters and sorting."); }
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; } }