Changing Cell Colour With Google Sheets API (PHP)

Question

I'm trying to change a ranges colour via the Google Sheets API in PHP.

I have done around an hour of researchig. The code below is as far as I've got.

$requests = [
      // Change the spreadsheet's title.
      new Google_Service_Sheets_Request([
          'updateSpreadsheetProperties' => [
              'properties' => [
                  'title' => "The Title"
              ],
              'fields' => 'title'
          ],
          'UpdateCellsRequest' => [
              'properties' => [
                  'range' => "Sheet1!A1",
                  'backgroundColor' => "#000"
              ],
              'fields' => ''
          ]
      ])
    ];

    // Add additional requests (operations) ...
    $batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest([
        'requests' => $requests
    ]);

    $response = $GoogleSheetsAPIHandler->sheets->spreadsheets->batchUpdate("SHEETID", $batchUpdateRequest);

    print_r($response);

If I take out this:

'UpdateCellsRequest' => [
  'properties' => [
      'range' => "Sheet1!A1",
      'backgroundColor' => "#000"
  ],
  'fields' => ''
]

Then the code works to update the sheets title. However, I can't seem to update a ranges colour.

Any advice would be greatly appreciated!

Answer 1

I believe your goal and situation as follows.

• You want to change the background color of cells using googleapis for php.
• You have already been able to get and put values for Google Spreadsheet using Sheets API.

Modification points:

• When you want to use the batchUpdate method of Sheets API, please put each request to each element of the array of requests.
• I think that the request body of UpdateCellsRequest in your script is not correct.
  • From your question of I'm trying to change a ranges colour via the Google Sheets API in PHP., when you want to change the background color of several cells with one color, I think that RepeatCellRequest might be suitable.

In this answer, I would like to propose a modified script for changing the several cells using one color. When your script is modified, it becomes as follows.

Modified script:

Before you use this, please set the sheet ID.

$requests = [
    new Google_Service_Sheets_Request([
        'updateSpreadsheetProperties' => [
            'properties' => [
                'title' => "The Title"
            ],
            'fields' => 'title'
        ]
    ]),
    new Google_Service_Sheets_Request([
        'repeatCell' => [
            'cell' => [
                'userEnteredFormat' => [
                    'backgroundColor' => [
                        'red' => 1,
                        'green' => 0,
                        'blue' => 0
                    ]
                ]
            ],
            'range' => [
                'sheetId' => $sheetId,  // <--- Please set the sheet ID.
                'startRowIndex' => 0,
                'endRowIndex' => 3,
                'startColumnIndex' => 0,
                'endColumnIndex' => 2
            ],
            'fields' => 'userEnteredFormat'
        ]
    ])
];

• When above request body is used for the batchUpdate method of Sheets API, the title of Spreadsheet is changed and the background color of the cells "A1:B3" changed to the red color.

• Wne you want to use UpdateCellsRequest, you can use the following request body. At the following request body, the background colors of cells "A1:B1" are changed to red and green colors, respectively. When UpdateCellsRequest is used, each cell can be updated. About the detail information of UpdateCellsRequest, please check the official document. Ref

    $requests = [
        new Google_Service_Sheets_Request([
            'updateCells' => [
                'rows' => array([
                    'values' => array(
                        ['userEnteredFormat' => [
                            'backgroundColor' => [
                                'red' => 1,
                                'green' => 0,
                                'blue' => 0
                            ]
                        ]],
                        ['userEnteredFormat' => [
                            'backgroundColor' => [
                                'red' => 0,
                                'green' => 1,
                                'blue' => 0
                            ]
                        ]]
                    )
                ]),
                'range' => [
                    'sheetId' => $sheetId,  // <--- Please set the sheet ID.
                    'startRowIndex' => 0,
                    'startColumnIndex' => 0,
                ],
                'fields' => 'userEnteredFormat'
            ]
        ])
    ];
  

References:

• UpdateCellsRequest
• RepeatCellRequest