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!

Answer 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);
}