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!
Advertisement
Answer
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.
- From your question of
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' ] ]) ];