Google Sheets - Setting background color

Question

I'm writing an app previously held by another developer. After a certain treatment, he wanted to fill a Google Sheets file with values. Before he could start developing, he was gone, leaving me with the task to understand the google-api-client-php library.

I manage to insert values (that was a big step for me) but I would like to add a background color to certain cells. I didn't find any way to acheive this...

For now, that's how I insert values :

class Sheet {
    public function __construct($client) {
        $this->service = new \Google_Service_Sheets($client);
    }
    public function write($line, $newValues, $startColumn)
    {
        $values = new \Google_Service_Sheets_ValueRange();
        $values->setValues([    $newValues  ]); 

        $this->service->spreadsheets_values->update($this->id, $range, $values, ['valueInputOption' => 'USER_ENTERED']);
    }
}

And I'd like to create a colorLine() function.

Here my first try :

 public function colorLine($line, $r, $g, $b, $a = 1) {
   $myRange = [
        'sheetId' => 1,
        'startRowIndex' => $line,
        'endRowIndex' => $line,
        'startColumnIndex' => 0,
        'endColumnIndex' => 1000,
    ];

    $requests = [
        new \Google_Service_Sheets_Request([
            'addConditionalFormatRule' => [
                'rule' => [
                    'ranges' => [ $myRange ],
                    'booleanRule' => [
                        'condition' => [
                            'type' => 'CUSTOM_FORMULA',
                            'values' => [ [ 'userEnteredValue' => '=1' ] ]
                        ],
                        'format' => [
                            'backgroundColor' => [ 'red' => $r, 'green' => $g, 'blue' => $b ]
                        ]
                    ]
                ],
                'index' => 1
            ]
        ])
    ];

    $batchUpdateRequest = new \Google_Service_Sheets_BatchUpdateSpreadsheetRequest([
        'requests' => $requests
    ]);
    $response = $this->service->spreadsheets->batchUpdate($this->id,
        $batchUpdateRequest);
}

First, I don't even really understand what I've written... Plus, it's saying "Invalid requests[0].addConditionalFormatRule: No grid with id: 1", but it's not that bad, I don't think it would have done what I'm looking for.

I think it would create a "conditional format", but I just want a background... This API looks very complicated for simple applications...

Anyway! If someone could help me, I would be very thankful!

Solution 1

If you don't really care about explainations, just go to the final section :)

That's maybe not the best solution, but at least it worked.

The solution' explaination (TL, NR) :

What do we need ?

1. The RGBa values we want
2. The range (sheetId, row index, columns index's)
3. The spreadsheet Id.

Now, how should we proceed? Well the previous source code wasn't that bad actually... Just need to change it a little bit :

• We don't want to create ConditionalFormats, but to update cells, so we should use the "repeatCell" request (I will explain why the "repeatCell" instead of "updateCell")
• In this request, we can have 3 parameters :
  • the mask field (that restrict updates),
  • the range
  • the cell. That's a CellData object that can contain a "userEnteredFormat" (CellFormat) AND NOW you have access to the backgroundColor property!!

Start coding :

Ok, so let's define the range :

You cannot have the "start" and "end" at the same position (so have -1 at the start, it will change only one line)

    $myRange = [
        'sheetId' => $sheetId,
        'startRowIndex' => $line-1,
        'endRowIndex' => $line,
        'startColumnIndex' => 0,
        'endColumnIndex' => 17,
    ];

Now let's define the color (each component must be between 0 and 1) :

    $format = [
        "backgroundColor" => [
            "red" => $r,
            "green" => $g,
            "blue" => $b,
            "alpha" => $a,
        ],
    ];

And that's it, we're almost ready!

We just have to tell the service that we want a "repeatCell" request. Do not forget the "fields" parameter. If you don't restrict the update, all the data of the cell will change, including the text! In this case, the path for the "fields" starts at "cell", so we just type 'userEnteredFormat.backgroundColor'. Then use the $format variable previously created.

    $requests = [
        new \Google_Service_Sheets_Request([
            'repeatCell' => [
                'fields' => 'userEnteredFormat.backgroundColor',
                'range' => $myRange,
                'cell' => [
                    'userEnteredFormat' => $format,
                ],
            ],
        ])
    ];

OK! Done. Now include this (or these) request in the batch :

    $batchUpdateRequest = new \Google_Service_Sheets_BatchUpdateSpreadsheetRequest([
        'requests' => $requests
    ]);

And at last, send the request with the service, include the speadsheet ID ($this->id in my case).

    $response = $this->service->spreadsheets->batchUpdate($this->id,
        $batchUpdateRequest);

Complete solution :

Thanks for reading, there's your solution :

public function colorLine($line, $r, $g, $b, $a = 1.0, $worksheetName = null)
{
    if($r > 1) $r = Tools::rescale($r, 0, 255, 0, 1);
    if($g > 1) $g = Tools::rescale($g, 0, 255, 0, 1);
    if($b > 1) $b = Tools::rescale($b, 0, 255, 0, 1);
    if($a > 1) $a = Tools::rescale($a, 0, 255, 0, 1);

    $worksheetName = ($worksheetName ? : $this->defaultWorksheet);
    $sheetId = $this->getWorksheetId($worksheetName);

    $myRange = [
        'sheetId' => $sheetId,
        'startRowIndex' => $line-1,
        'endRowIndex' => $line,
        'startColumnIndex' => 0,
        'endColumnIndex' => 17,
    ];
    $format = [
        "backgroundColor" => [
            "red" => $r,
            "green" => $g,
            "blue" => $b,
            "alpha" => $a,
        ],
    ];

    $requests = [
        new \Google_Service_Sheets_Request([
            'repeatCell' => [
                'fields' => 'userEnteredFormat.backgroundColor',
                'range' => $myRange,
                'cell' => [
                    'userEnteredFormat' => $format,
                ],
            ],
        ])
    ];

    $batchUpdateRequest = new \Google_Service_Sheets_BatchUpdateSpreadsheetRequest([
        'requests' => $requests
    ]);
    $response = $this->service->spreadsheets->batchUpdate($this->id,
        $batchUpdateRequest);
}

Solution 2

Finally, I have found a working solution for this.

$sheetId = $service->spreadsheets->get($spreadsheetId, ['ranges' => 'worksheetname']);

//For range, end rows and end columns are not considered for updating. Index start from 0
//Here we are setting range for A3:E5 grid
$range = new Google_Service_Sheets_GridRange();
$range->setSheetId($sheetId->sheets[0]->properties->sheetId);
$range->setEndRowIndex(2);
$range->setEndRowIndex(5);
$range->setStartColumnIndex(0);
$range->setEndColumnIndex(5);

//set the color value in RGBA
$color = new Google_Service_Sheets_Color();
$color->setRed($red / 255);
$color->setGreen($green / 255);
$color->setBlue($blue / 255);
$color->setAlpha($alpha);

//cellFormat is used to set different properties of a cell
$cellFormat = new Google_Service_Sheets_CellFormat();

//textFormat is used to set different text formats like Bold
$textFormat = new Google_Service_Sheets_TextFormat();
$textFormat->setBold(true);
$cellFormat->setBackgroundColor($color);
$cellFormat->setTextFormat($textFormat);

//New cell class. Assign the cellFormat to it
$cell = new Google_Service_Sheets_CellData();
$cell->setUserEnteredFormat($cellFormat);

//repeatCell request is used to assign requests to range of cells
$repeatCell = new Google_Service_Sheets_RepeatCellRequest();
$repeatCell->setRange($range);

//Fields is used to specify which properties of a cell to update
//Here we are updating two properties. So both are specified and seperated by comma ,
$repeatCell->setFields('userEnteredFormat.textFormat.bold,userEnteredFormat.backgroundColor');

//Set repeatCellrequest to the requests class
$requests = new Google_Service_Sheets_Request();
$requests->setRepeatCell($repeatCell);

//requests are set to batchupdatespreadsheetrequest class
$batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest();
$batchUpdateRequest->setRequests($requests);

//Finally batchUpdate is called to update the format of cells
$response = $service->spreadsheets->batchUpdate($spreadsheetId, $batchUpdateRequest, []);

For more detailed video