CODEWITHSUNDEEP
google-api-php-clientgoogle-sheets-apibatch-updates
Malasuerte94
Malasuerte94

Reputation: 1474

How to update multiple cells via the Google Sheets API?

I'm trying to update and get some results from google sheets, it's working but slow. I need to batchUpdate and get the data.

This is my script

foreach ($import_cels as $celu => $valoare) {
    $range_ins = $celu;
    $valueRange->setValues(["values" => [$valoare]]);
    $service->spreadsheets_values->update($spreadsheetId, $range_ins, $valueRange, $conf);
}

foreach ($cells_to_get as $celu => $valoare) {
    $response = $service->spreadsheets_values->get($spreadsheetId, $celu);
    $values = $response->getValues()[0][0];
    echo "each cell :" . $values;
}

The problem: I have too many requests because I update the cells one by one and extract them the same.

I need to batch update the cells and get them like

B12 => 3
BB1 => 1
CC3 => 4

Upvotes: 6

Views: 12717

Answers (2)

Yannickv
Yannickv

Reputation: 572

This is an old question, but for completeness (and because the docs aren't very clear) this is how you would do it using PHP classes (instead of the API request used in the approved answer)

$data = [];
array_push(
    $data,
    new \Google\Service\Sheets\ValueRange([
        'range' => 'B20',
        'values' =>  [["Hello"]]
    ])
);
array_push(
    $data,
    new \Google\Service\Sheets\ValueRange([
        'range' => 'C20',
        'values' =>  [["World"]]
    ])
);
array_push(
    $data,
    new \Google\Service\Sheets\ValueRange([
        'range' => 'E20',
        'values' =>  [["Test"]]
    ])
        );
$body = new \Google\Service\Sheets\BatchUpdateValuesRequest([
    'valueInputOption' => 'RAW',
    'data' => $data
]);


$result = $service->spreadsheets_values->batchUpdate($sheetId, $body);
printf("%d cells updated.", $result->getTotalUpdatedCells());

References:

Upvotes: 3

Onkar Chopade
Onkar Chopade

Reputation: 314

Please create your request in below format

var resources = {
  auth: "auth key",
  spreadsheetId: "spread sheet id",
  resource:{
    valueInputOption: "RAW",
    data:[
      {
        range: "Sheet1!A5", // Update single cell
        values: [["A5"]]
      }, {
        range: "Sheet1!B4:B6", // Update a column
        values: [["B4"], ["B5"], ["B6"]]
      }, {
        range: "Sheet1!C4:E4", // Update a row
         values: [["C4", "D4", "E4"]]
      }, {
         range: "Sheet1!F5:H6", // Update a 2d range
        values: [["F5", "F5"], ["H6", "H6"]]
      }
    ]
  }
};

and use sheets.spreadsheets.values.batchUpdate(resources) function for batch update.

Upvotes: 19

Related Questions