时间戳

首页 > 解决方案 > C# Google Sheets API - 删除行

c# - C# Google Sheets API - 删除行

问题描述

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

[编辑]
好的，我终于找到了如何删除行......所以，我要做的就是首先建立要删除的所有索引的列表。这样做之后，我必须构建键对值列表，其中包含要删除的索引的开始和结束，但最后我必须添加 +1，因为似乎开始和结束没有被删除，只有介于两者之间的东西。 .

最后，我不得不从最后到开始循环密钥对列表，这删除了行......

要删除的代码在这里。也许这会帮助其他正在寻找如何删除谷歌表格中的行的人：

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

标签： c#google-sheets-apidelete-row

解决方案

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

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

问题是，Request 下没有 .setDeleteDimension 这样的东西。这不应该是一个这样的问题，但它是......

您可以在下面找到我的代码。它的作用是从一张纸（内部）获取数据并将其放入另一张纸（内部存档）。完成此操作后（并且效果很好），我想从内部删除数据，因为它已经存档了……并且该部分不起作用。我只是不知道如何删除行..如果有人可以看看这个，那就太好了。谢谢你的帮助...

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

推荐阅读