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

Solution 1

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.

Solution 2

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.

Solution 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;
}

Comments

[noogui]

Check my updated answer. The error was due to a wrong 'sheetId' data. sheetId is the value of the gid parameter. In my spreadsheet's case, it's 0. Also, the 'asterisk' helped. Hope that helps

[Gaurav Ashara]

Spreadsheet : docs.google.com/spreadsheets/d/…

[jpaugh]

@Gaurav Ashara Welcome to Stack Overflow! If you found noogui's answer helpful, please accept it. Not only does it give noogui credit, but it also helps future readers who may need something similar.

[Gaurav Ashara]

Actually we was not used app script. We used Google Sheet API v4 for java.

[Gaurav Ashara]

we got following error "code": 400, "errors": [ { "domain": "global", "message": "Invalid requests[0].updateCells: At least one field must be listed in 'fields'. (Use '' to indicate all fields.)", "reason": "badRequest" } ], "message": "Invalid requests[0].updateCells: At least one field must be listed in 'fields'. (Use '' to indicate all fields.)", "status": "INVALID_ARGUMENT" }

[Sam Berlin]

I have a pending update to the reply to fix the field parameter. I suggest you take a look at the Clear a sheet of all values while preserving formats example in the Sheets v4 API documentation. It is answering exactly your question (and this answer is basically a copy/paste of that).

[Sam Berlin]

looks like my edits to the answer weren't accepted -- the answer is incorrect because it says to leave the "fields" param blank (which as you noted isn't allowed by the API). the correct solution is to write "userEnteredValue" as the fields param, which tells the API to update only that field. if you truly want to clear every field (which based on your question, you don't want to), then you'd use an "*" as the error message tells you.

[noogui]

@GauravAshara I've updated my answer. It should be working now ; )

[Gaurav Ashara]

Ok We tried above solution with Java API but we got following error "code": 503, "errors": [ { "domain": "global", "message": "The service is currently unavailable.", "reason": "backendError" } ], "message": "The service is currently unavailable.", "status": "UNAVAILABLE" }

[noogui]

@GauravAshara That seems to be outside the scope of this thread as it is referring to your 'backend error'. Try to start another thread. Don't forget to accept the answer if it helped. Thanks :) With regard to '503 backend error', it seems other users are experiencing it too. Check this SO thread.

[Sam Berlin]

@noogui, I see you changed your answer to include the asterisk in fields. Previously you didn't have that, and it resulted in the error message the OP listed in his followup comment. I did provide a working answer in my proposed edits to your answer, but it seems the edits weren't accepted. (I didn't feel a need to provide a wholly separate answer, since yours was good enough with the tweaks .) I feel confident in my answers because I designed, implemented, and launched this API.

[Gaurav Ashara]

@noogui , Thank you for helping , Actually our entire java code of Google Sheet API V4 work perfectly but when add above code then we got : "The service is currently unavailable." error. If possible please check at your end also.

[noogui]

@GauravAshara Make sure your access token has not expired.

[Gaurav Ashara]

@noogui , We uses newly created access token before sending request.

[noogui]

@GauravAshara, I did another test run on oauth playground and everything is ok. My spreadsheet is being cleared. Can you try it in oauth playground?

[Gaurav Ashara]

@noogui : We checked with Google Sheet API Explorer but we same error. You can see screenshot i.stack.imgur.com/NL5pV.png

[Sam Berlin]

@GauravAshara, that exception indicates the server is having trouble handling the request. There are two probable reasons for this -- 1: The number of cells in the sheet is too large, or 2: The amount of data being cleared is too large. You can workaround (1) by listing an explicit range (if it works if you just specify a start/end row/col index, then it's probably because of too many cells). You can workaround (2) by saying to clear only the values, not everything else, by listing "userEnteredValue" in "fields" instead of "*".

[Gaurav Ashara]

@SamBerlin :Thank you for suggestion we performed above suggestion. We analyze bit more about issue and found that When sheet columns are not deleted with Google Sheet API V4 update cells work perfectly otherwise it will throw 503 Error. For this issue I am sharing my spread sheet link(Public), not working update cells call and working updatecells call (Find links in URL). 1)Spreadsheet : docs.google.com/spreadsheets/d/… Please guide how to resolve this issue..

[Sam Berlin]

@GauravAshara, thanks reporting this. I can reproduce the problem and am looking into what's going wrong.

[noogui]

In your given public spreadsheet- Sample Sheet, "clear-all-sheet" is working for Sheet3 where columns go from A-Z, I was able to delete all dummy data. I'm getting "service unavailable" for Sheet1 where columns go from A-E. Try moving your Sheet1 data to a Sheet that has A-Z columns, complete columns

[Gaurav Ashara]

@noogui : Actually I want to uses more number cells in sheet(20 mil cells). So I have delete empty columns.

[Gaurav Ashara]

@SamBerlin : Please share update and let me know if you require any other information

[Sam Berlin]

We found a problem with using UpdateCellsRequest if performing only a clear if the clear impacts under 10k cells. You could potentially workaround using RepeatCellRequest for now, and I'll post back here when we deploy the fix.

[Priya]

@GauravAshara.... how to Perform this operation through postman...can u post sample with some screenshots...it will help full to all ...

[Priya]

@noogui....pls share ur answer with screenshots to perform same operation through postman

[Logan Haser]

Oh, yeah this was before I figured out how to do requests to clear the whole sheet. Thank you for pointing that out! I will edit it with the code used to clear the whole sheet.

[bstovall]

Per stackoverflow.com/a/62568335/7020955, the first sheet in a newly created spreadsheet has ID 0, but additional ones have a random value. Note that copying a sheet maintains all of the sheet ids though.

[davey]

thx, this works if execute is called: sheetsService.spreadsheets().values().clear(spreadsheetId, "Sheet1", clearValuesRequest).execute();