How to update multiple cells via the Google Sheets API?

Question

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

What's the issue you're having? `values.batchUpdate` exists: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchUpdate — tehhowch, Jul 05 '18 at 18:16
Yeah, I have this but I don't understand how can I use it with my arrays :( — Malasuerte94, Jul 06 '18 at 12:29
There is an example at the bottom of the page for the PHP client. While it is not a very good example, it at least demonstrates how you can create a `BatchUpdateValuesRequest` and then use that request with `batchUpdate`. Your posted code doesn't even attempt to do that, which makes me doubt that you read the appropriate documentation for the [PHP Client Sheets API](https://developers.google.com/resources/api-libraries/documentation/sheets/v4/php/latest/), or read the guides and samples on the Sheets REST API pages https://developers.google.com/sheets/api/guides/values#writing_multiple_ranges — tehhowch, Jul 06 '18 at 13:19

score 18 · Accepted Answer · edited Aug 29 '19 at 07:00

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.

score 3 · Answer 2 · answered Aug 25 '21 at 19:05

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:

How to update multiple cells via the Google Sheets API?

2 Answers2

Linked