C# Google Sheets API - Delete Row

Question

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();
    }

Answer 1

I checked the links that You provided here, but non of them solved the problem. For example this one:

Request request = new Request()
  .setDeleteDimension(new DeleteDimensionRequest()
    .setRange(new DimensionRange()
      .setSheetId(0)
      .setDimension("ROWS")
      .setStartIndex(30)
      .setEndIndex(32)
    )
  );

The problem is that, there is no such a thing like .setDeleteDimension under Request. This shouldn't be such a problem, but it is....

Below You can find my code. What does it do, is to take data from one sheet (internal) and put it to another sheet (internal archive). When this is done (and this works well), I want to delete data from internal as it is already archived... and that part is not working. I just don't know how to delete the rows.. if anyone could have a look on this, it would be great. Thanks for your help...

public void RunArchiveInternal2(bool testRun)
{
    //internal
    string SheetIDInternal = "googlesheetid_internal";
    string RangeInternal = testRun ? "test_task tracking" : "Task Tracking - INTERNAL";
    SpreadsheetsResource.ValuesResource.GetRequest getRequestInternal = sheetsService.Spreadsheets.Values.Get(SheetIDInternal, RangeInternal);
    ValueRange ValuesInternal = getRequestInternal.Execute();

    //internal archive
    string SheetIDInternalArchive = "googlesheetid_internal_archive";
    string RangeInternalArchive = testRun ? "test_archive_internal" : "Sheet1";
    SpreadsheetsResource.ValuesResource.GetRequest getRequestInternalArchive = sheetsService.Spreadsheets.Values.Get(SheetIDInternalArchive, RangeInternalArchive);
    ValueRange ValuesInternalArchive = getRequestInternalArchive.Execute();


    //Get data from internal and put to internal archive
    List<IList<object>> listOfValuesToInsert = new List<IList<object>>();            

    for (int i = 1; i <= ValuesInternal.Values.Count() - 1; i++)
    {
        List<object> rowToUpdate = new List<object>();

        if (ValuesInternal.Values[i][1] != null && ValuesInternal.Values[i][1].ToString().ToUpper() == "TASK COMPLETE")
        {
            rowToUpdate = (List<object>)ValuesInternal.Values[i];
            listOfValuesToInsert.Add(rowToUpdate);
        }
    } 
    SpreadsheetsResource.ValuesResource.AppendRequest insertRequest = sheetsService.Spreadsheets.Values.Append(new ValueRange { Values = listOfValuesToInsert }, SheetIDInternalArchive, RangeInternalArchive + "!A1");
    insertRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
    insertRequest.Execute();


    //delete things from internal
    BatchUpdateSpreadsheetRequest batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest();
    List<DeleteDimensionRequest> requests = new List<DeleteDimensionRequest>();

    for (int i = ValuesInternal.Values.Count() - 1; i >= 1; i--)
    {
        DeleteDimensionRequest request = new DeleteDimensionRequest();
        //Request request = new Request();

        if (ValuesInternal.Values[i][1] != null && ValuesInternal.Values[i][1].ToString().ToUpper() == "TASK COMPLETE")
        {
            request.Range = new DimensionRange
            {
                Dimension = "ROWS",
                StartIndex = i,
                EndIndex = i
            };
            requests.Add(request);
        }
    }
    batchUpdateSpreadsheetRequest.Requests = requests;//this is wrong
    SpreadsheetsResource.BatchUpdateRequest Deletion = sheetsService.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequest, SheetIDInternal);
    Deletion.Execute();
}