C#Google Sheets API-删除行 [英] C# Google Sheets API - Delete Row

问题描述

我已经搜索了整个互联网上的所有内容:)，但没有找到解决方案.我可以通过C#在Google工作表中添加新行，更新旧行并做很多事情，但是我无法删除Google工作表中的行...有人可以帮忙吗?

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?

好的，我终于找到了删除行的方法...因此，我要做的是首先构建要删除的所有索引的列表.完成此操作后，我必须构建要删除的索引对值列表以及索引的开始和结束，但最后我必须添加+1，因为似乎开始和结束并没有删除，只有两者之间的内容才删除.

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...

要删除的代码在这里.也许这会帮助正在寻找如何删除Google表格中行的其他人:

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

推荐答案

我检查了您在此处提供的链接，但没有一个解决了问题.例如，这个:

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

问题在于，在Request下没有.setDeleteDimension之类的东西.这不应该是这样的问题，但是....

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

这篇关于C#Google Sheets API-删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！