我想删除行在谷歌表使用谷歌表api v4

本文关键字：谷歌 api v4 删除行 | 更新日期: 2023-09-27 18:04:18

我想在使用googlesheetv4 api的googlesheet中使用rowno删除一行。谁能给我提供一个示例代码吗?我已经创建了一个方法来删除行:

public void deleteRow() {
    BatchUpdateSpreadsheetRequest content = new BatchUpdateSpreadsheetRequest();
    Request request = new Request();
    request.setDeleteDimension(new DeleteDimensionRequest().setRange(new DimensionRange().setDimension("D30:D31")));
    List<Request> requests = new ArrayList<Request>();
    requests.add(request);
    content.setRequests(requests);
    System.out.println(content.getRequests());
    try {
        service.spreadsheets().batchUpdate(IConstant.SPREADSHEET_ID, content);
    } catch (IOException e) {
        e.printStackTrace();
    }
}

我想删除行在谷歌表使用谷歌表api v4

您的代码看起来很接近，但是您没有正确设置DimensionRange。试试以下命令:

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

正如迁移到Google Sheets API中所提到的，使用Google Sheets API V4删除一行，由spreadsheet.batchUpdate方法调用处理，使用DeleteDimension请求。这个请求也可以用来删除列，开发人员可以选择只删除一行或列的一部分。

删除给定ID的工作表的第6行代码示例: 

POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId:batchUpdate
{
  "requests": [
    {
      "deleteDimension": {
        "range": {
          "sheetId": sheetId,
          "dimension": "ROWS",
          "startIndex": 5,
          "endIndex": 6
        }
      }
    }
  ],
}

关于行和列操作的更多信息可能会有所帮助。

您的代码是正确的，但是您必须确保执行了请求。我的代码基本上把你的结果结合在一起。

    BatchUpdateSpreadsheetRequest content = new BatchUpdateSpreadsheetRequest();
    Request request = new Request()
        .setDeleteDimension(new DeleteDimensionRequest()
          .setRange(new DimensionRange()
            .setSheetId(Integer.parseInt(Defines.GRID_ID))
            .setDimension("ROWS")
            .setStartIndex(30)
            .setEndIndex(30 + numofrows)
          )
        );
    // request.setDeleteDimension(new DeleteDimensionRequest().setRange(new DimensionRange().setDimension("D30:D31")));
    List<Request> requests = new ArrayList<Request>();
    requests.add(request);
    content.setRequests(requests);
    try {
        sheetsService.spreadsheets().batchUpdate(Defines.GOOGLE_SPREADSHEET_ID, content).execute();
    } catch (IOException e) {
        e.printStackTrace();
    }

我不确定我的方法是不是最好的，但它达到了目的，所以我将解释我所做的。

  1. 在您要保存的工作表中保留一个额外的列(例如ROWNUM)数据。

  2. 写相同的字符串"=ROW()"在这一栏每次当当你发布数据时，它会生成行id

  3. 那就用下面的方法吧，希望对你有帮助!

    public void deleteRows (String range, String textToBeDeleted) {//textToBeDeleted is cell value which will be find in the column and that row will be deleted
try {
    Sheets sheetsService = createSheetsService();
    ValueRange response = sheetsService.spreadsheets().values()
            .get(spreadsheetId, range)
            .execute(); //Getting all the data of the sheet
    List<Request> requests = new ArrayList<>();
    List<List<Object>> allData = response.getValues();
    List<Integer> rowsToBeDeleted = new ArrayList<>();
    for (List<Object> row : allData) {
        if (row.get(0).toString().equalsIgnoreCase(textToBeDeleted)) { //0 is the column value based on which I'll be deleting the row
            rowsToBeDeleted.add(Integer.parseInt(row.get(23).toString())); //23 is the RowNumber column in my case
        }
    }
    BatchUpdateSpreadsheetRequest requestBody = new BatchUpdateSpreadsheetRequest();
    for (int i = rowsToBeDeleted.size() - 1; i >= 0; i--) { //Reverse loop while deleting so that wrong index doesn't get deleted
        Request request = new Request();
        request.setDeleteDimension(new DeleteDimensionRequest()
                .setRange(new DimensionRange()
                        .setSheetId(sheetid)
                        .setDimension("ROWS")
                        .setStartIndex(rowsToBeDeleted.get(i) - 1)
                        .setEndIndex(rowsToBeDeleted.get(i))
                )
        );
        requests.add(request);
    }
    requestBody.setRequests(requests);
    Sheets.Spreadsheets.BatchUpdate deleteRequest =
            sheetsService.spreadsheets().batchUpdate(spreadsheetId, requestBody);
    BatchUpdateSpreadsheetResponse deleteResponse = deleteRequest.execute();
    System.out.println(deleteResponse);
} catch (Exception e) {
    System.out.println(e.getStackTrace());
}}

代码是正确的，但有几个错误:

  1. 在我的情况下，sheetId是0，它的工作。
  2. 你必须有谷歌的权限，以更新表。去Google OAuth游乐场。搜索google sheets v4并选择所有选项，然后授权访问令牌并复制访问令牌，代码示例为:
const deleteRow = async (rowIndex: number) => {
    setBusy(true);
    try {
      const response = await fetch(
        `https://sheets.googleapis.com/v4/spreadsheets/${sheet_id}:batchUpdate`,
        {
          method: 'POST',
          headers: {
            'Content-Type': 'application/json',
            Authorization: 'Bearer' + YOUR_TOKEN,
          },
          body: JSON.stringify({
            requests: [
              {
                deleteDimension: {
                  range: {
                    sheetId: 0, // number after gid=0 in your url
                    dimension: 'ROWS',
                    startIndex: rowIndex,
                    endIndex: rowIndex + 1,
                  },
                },
              },
            ],
          }),
        },
      );
      console.log(await response.json());
    } catch (err: any) {
      console.log(err.message);
    } finally {
      // refetch the data
      setBusy(false);
      handleFetch();
    }
  };

这不是生产代码，我们必须在登录时保存令牌，这是电子表格工作的一个示例。

相关文章：