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

Answer 1

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.

Answer 2

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:

• batchUpdate
• BatchUpdateValuesRequest