I am trying to delete a row in Google Sheets using the Google APIs PHP client library that Google provide for their APIs. Currently I am only able to clear the contents of the row but not delete it:
$requestBody = new Google_Service_Sheets_ClearValuesRequest();
$range = 'Form responses 1!C5:H5';
$response = $service->spreadsheets_values->clear($spreadsheetId, $range, $requestBody);
The documentation does not explain in any way at all how to delete apart from giving one line of HTTP that has nothing to do with delete, only batchUpdate.
Edit: This is the answer
$requests = [
// Change the spreadsheet's title.
new Google_Service_Sheets_Request([
'deleteDimension' => [
'range' => [
'sheetId' => 'XXXXXXX', (THIS IS NOT THE SPREADSHEET ID - IT IS THE BIT AFTER GID IN THE URL)
'dimension' => "ROWS",
'startIndex' => '16',
'endIndex' => '17'
]
]
])
];
You should be using batchUpdate as per the documentation.
Delete rows or columns
The following spreadsheets.batchUpdate request deletes the first three rows in the sheet. A second request deletes columns B:D.
The request protocol is shown below. The Updating Spreadsheets guide shows how to implement a batch update in different languages using the Google API client libraries.
POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId:batchUpdate
{
"requests": [
{
"deleteDimension": {
"range": {
"sheetId": sheetId,
"dimension": "ROWS",
"startIndex": 0,
"endIndex": 3
}
}
},
{
"deleteDimension": {
"range": {
"sheetId": sheetId,
"dimension": "COLUMNS",
"startIndex": 1,
"endIndex": 4
}
}
},
],
}
<?php
/*
* BEFORE RUNNING:
* ---------------
* 1. If not already done, enable the Google Sheets API
* and check the quota for your project at
* https://console.developers.google.com/apis/api/sheets
* 2. Install the PHP client library with Composer. Check installation
* instructions at https://github.com/google/google-api-php-client.
*/
// Autoload Composer.
require_once __DIR__ . '/vendor/autoload.php';
$client = getClient();
$service = new Google_Service_Sheets($client);
// The spreadsheet to apply the updates to.
$spreadsheetId = 'my-spreadsheet-id'; // TODO: Update placeholder value.
// TODO: Assign values to desired properties of `requestBody`:
$requestBody = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest();
$response = $service->spreadsheets->batchUpdate($spreadsheetId, $requestBody);
// TODO: Change code below to process the `response` object:
echo '<pre>', var_export($response, true), '</pre>', "\n";
function getClient() {
// TODO: Change placeholder below to generate authentication credentials. See
// https://developers.google.com/sheets/quickstart/php#step_3_set_up_the_sample
//
// Authorize using one of the following scopes:
// 'https://www.googleapis.com/auth/drive'
// 'https://www.googleapis.com/auth/drive.file'
// 'https://www.googleapis.com/auth/spreadsheets'
return null;
}
?>