How to delete row from Google Sheets using api v4

2019-08-16 14:44发布

问题:

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'
      ]
    ]
])
]; 

回答1:

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