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.