Skip to content
Advertisement

Changing Cell Colour With Google Sheets API (PHP)

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.

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:

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement