I searched everything already on the whole internet :) and did not find a solution for this.
I can add new rows, update old rows and do a lot of things in google sheets via C#, but I can't delete a row in a google sheet... can someone, please help me with this?
[EDITED]
OK, I finally found how to delete the rows...
So, what I had to do was first to build list of all indexes that are to be deleted. After doing that I had to build list of key pairs values with start and end of the index to delete, BUT for the end I had to add +1, as it seems like starts and ends are not deleted, only things that are between..
Finally I had to loop the list of key pairs from the end till the start and this deleted the rows...
The code to delete is here. Maybe this will help someone else who is looking how to delete rows in google sheets:
List<KeyValuePair<int, int>> _listStartEndIndexToDelete = new List<KeyValuePair<int, int>>();
List<int> _tempListOfAllIndex = new List<int>();
for (int i = 1; i <= ValuesInternal.Values.Count() - 1; i++)
{
if (ValuesInternal.Values[i][1] != null && ValuesInternal.Values[i][1].ToString().ToUpper() == "TASK COMPLETE")
{
_tempListOfAllIndex.Add(i);
}
}
for (int rowNumber = 0; rowNumber <= _tempListOfAllIndex.Count() - 1; rowNumber++)
{
int tempStart = _tempListOfAllIndex[rowNumber];
if(rowNumber != _tempListOfAllIndex.Count() - 1)
{
while (_tempListOfAllIndex[rowNumber] + 1 == _tempListOfAllIndex[rowNumber + 1])
{
rowNumber++;
if (rowNumber == _tempListOfAllIndex.Count() - 1) { break; }
}
}
int tempEnd = _tempListOfAllIndex[rowNumber] + 1;
KeyValuePair<int, int> tempPair = new KeyValuePair<int, int>(tempStart, tempEnd);
_listStartEndIndexToDelete.Add(tempPair);
}
for(int keyValuePair = _listStartEndIndexToDelete.Count()-1; keyValuePair >= 0; keyValuePair--)
{
List<Request> deleteRequestsList = new List<Request>();
BatchUpdateSpreadsheetRequest _batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest();
Request _deleteRequest = new Request();
_deleteRequest.DeleteDimension = new DeleteDimensionRequest();
_deleteRequest.DeleteDimension.Range = new DimensionRange();
_deleteRequest.DeleteDimension.Range.SheetId = SheetIDnumberWhereDeleteShouldBeDone;
_deleteRequest.DeleteDimension.Range.Dimension = "ROWS";
_deleteRequest.DeleteDimension.Range.StartIndex = _listStartEndIndexToDelete[keyValuePair].Key;
_deleteRequest.DeleteDimension.Range.EndIndex = _listStartEndIndexToDelete[keyValuePair].Value;
deleteRequestsList.Add(_deleteRequest);
_batchUpdateSpreadsheetRequest.Requests = deleteRequestsList;
sheetsService.Spreadsheets.BatchUpdate(_batchUpdateSpreadsheetRequest, SheetIDInternal).Execute();
}
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…