Google Spreadsheet API : Empty all cells in sheet

Question

We use Google Sheet API v4. We want to clear entire sheet with empty data. We don't want to delete rows/columns.

1. Not working UpdateCells Call(Delete Columns by API) : developers.google.com
2. Working UpdateCells Call(All Cells) : developers.google.com

Answer 1

I was able to do it by using the clear method documented here looking at rev607.

ClearValuesRequest clearValuesRequest = new ClearValuesRequest();
// assumes you have a sheetservice initialized 
sheetsService.spreadsheets().values().clear(spreadsheetId, "Sheet1", clearValuesRequest);

The key insight that wasn't well documented is you can pass in just the sheet name for the range and that clears ALL cells in the sheet.

Answer 2

Here's the Java Quickstart for Spreadsheet API.

You will then be using spreadsheets.batchUpdate to clear the sheets.Leave fields blank and place an asterisk to instruct Sheets API that all cells should be empty/cleared. The following body request looks like:

{
  "requests": [
    {
      "updateCells": {
        "range": {
          "sheetId": 0
        },
        "fields": "*"
      }
    }
  ]
}

Give this a quick-try in the oauth playground.

UPDATE: This is working now. It cleared my spreadsheet.

Answer 3

I don't know if this will help anyone else, but this is how I got it working for me: This should clear your entire sheet. I do not recall why 'sheetId' is 0, but perhaps it is an index into the sheets within the sheet itself. (like how you have tabs?) I honestly do not remember though. The function expects you to pass in your $service object and the spreadsheetID.

function clearSheet($service, $spreadsheetID = 0){

$request = new \Google_Service_Sheets_UpdateCellsRequest([
    'updateCells' => [ 
        'range' => [
            'sheetId' => 0 
        ],
        'fields' => "*" //clears everything
    ]
  ]);
$requests[] = $request;

$requestBody = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest();
$requestBody->setRequests($requests);
$response = $service->spreadsheets->batchUpdate($spreadsheetID, $requestBody);
return $response;
}

Answer 4

Java example:

        UpdateCellsRequest clearAllDataRequest = new UpdateCellsRequest();
        int allSheetsId = 0;
        String clearAllFieldsSpell = "*";
        GridRange gridRange = new GridRange();
        gridRange.setSheetId(allSheetsId);
        clearAllDataRequest.setRange(gridRange);
        clearAllDataRequest.setFields(clearAllFieldsSpell);
        BatchUpdateSpreadsheetRequest request = new BatchUpdateSpreadsheetRequest();
        Request clearAllDataRequest = new Request().setUpdateCells(clearAllDataRequest);
        request.setRequests(List.of(clearAllDataRequest));
        return request;