CODEWITHSUNDEEP
c#google-sheets-api
Vinod Verma
Vinod Verma

Reputation: 165

I want to delete row in googlesheet using googlesheet api v4

I want to delete a row using rowno in googlesheet using googlesheetv4 api. Can anybody provide me a sample code for this? I have created a method for delete row:

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

Upvotes: 8

Views: 16954

Answers (5)

Saad Siddiqui
Saad Siddiqui

Reputation: 1

The code is correct but the few mistakes are:

  1. In my case the sheetId is 0 and it works.
  2. You must have google permissions, to update the sheet. Go to Google OAuth playground. Search for google sheets v4 and select all the options and then authorise for the access token and copy the access token, the code example is:
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();
    }
  };

This is not a production code, we will have to save the token while we login, this was an example for the spreadsheet to work.

Upvotes: 0

Abhinav Saxena
Abhinav Saxena

Reputation: 3904

I'm not sure if my approach is best one or not but it is serving the purpose, so I'll explain what I did.

  1. Keep one extra column(say ROWNUM) in the sheet where you are saving data.

  2. Write same string "=ROW()" in this column each time when you're posting data, it will generate the row id for that row

  3. Then utilize the following method, hope it helps!

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

Upvotes: 0

Albert Liu
Albert Liu

Reputation: 1

Your code is correct, but you have to make sure that you execute the request. My code basically combined both of your results together. 

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

Upvotes: 0

Eric Koleda
Eric Koleda

Reputation: 12673

Your code looks close, but you aren't setting up the DimensionRange correctly. Try the following:

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

Upvotes: 6

Teyam
Teyam

Reputation: 8092

As mentioned in Migrate to the Google Sheets API, to delete a row using Google Sheets API V4, is handled by a spreadsheet.batchUpdate method call, using a DeleteDimension request. This request can also be used to remove columns, and developers can choose to only remove part of a row or column.

Example Code which removes the 6th row of a sheet with the given ID:

POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId:batchUpdate

{
  "requests": [
    {
      "deleteDimension": {
        "range": {
          "sheetId": sheetId,
          "dimension": "ROWS",
          "startIndex": 5,
          "endIndex": 6
        }
      }
    }
  ],
}

More information in row and column operations might help.

Upvotes: 6

Related Questions