How to get text color formatting from Google Sheets API?

Question

I've set up Google Sheets as a simple backend using the api for a php website. It is for a foreign language learning resource, so people can add / edit sentences.

Screenshot of the google sheet

Screenshot of Site generated from sheet

At the top of the template.php that generates each language's page I have this script, then I loop over $sheetsValues to make the table on the site.

<?php 

require __DIR__ . '/vendor/autoload.php';
  // connect to API
  $client = new \Google_Client();
  $client->setApplicationName('12Sentences');
  $client->setScopes([\Google_Service_Sheets::SPREADSHEETS]);
  $client->setAccessType('offline');
  $client->setAuthConfig(__DIR__ . '/credentials.json');
  $service = new Google_Service_Sheets($client);
  $spreadsheetId = 'MY_SPREADSHEET_ID';

  $range = "$language!B3:E15";
  $response = $service->spreadsheets_values->get($spreadsheetId, $range );
  $sheetsValues = $response->getValues();
  
?>

Though it doesn't get the color and formatting information from the sheet. I'd like to get the text color from the google sheet to show on the site (like the red of 'La Pomme') - as it can be used to indicate elements of the sentences (subject, verb, object order etc.) Is this possible with google sheets api?

Thanks,

Thomas

Updated code: Used Tanaike's solution below in function getRes() to get color data from Google Sheets API. Then getFormattedHtml() is called to get colored text in HTML and CSS. Not elegant but it works well for my use.

<?php

function getFormattedHtml($row, $column, $res) {
  $formattedHtml = "";
  // get plain text
  $plain_text = $res[$row][$column]['formattedValue'];

  // get textFormatRuns
  $textFormatRuns = $res[$row][$column]['textFormatRuns'];

  // loop over the textFormatRuns
  $len = count($textFormatRuns);
  for ($i=0; $i < $len; $i++) { 
    $currentRunStart = $textFormatRuns[$i]['startIndex'];
    $currentRunEnd = $textFormatRuns[$i + 1]['startIndex'];
    $substring = "";
    if ($i == $len - 1) {
      $substring = substr($plain_text, $currentRunStart);
    } else {
      $substring = substr($plain_text, $currentRunStart, $currentRunEnd - $currentRunStart);
    }
    
    $span = "";
    if (isset($textFormatRuns[$i]['format']['foregroundColor'])) {
      $red = $textFormatRuns[$i]['format']['foregroundColor']['red'] * 255;
      $green = $textFormatRuns[$i]['format']['foregroundColor']['green'] * 255;
      $blue = $textFormatRuns[$i]['format']['foregroundColor']['blue'] * 255;
      $span = "<span style=\"color:rgb($red, $green, $blue)\">$substring</span>";
    } else {
      $span = "<span>$substring</span>";
    }
    
    $formattedHtml .= $span;
  }

  return($formattedHtml);
}

function getRes() {
  require __DIR__ . '/vendor/autoload.php';
// connect to API
$client = new \Google_Client();
$client->setApplicationName('12Sentences');
$client->setScopes([\Google_Service_Sheets::SPREADSHEETS]);
$client->setAccessType('offline');
$client->setAuthConfig(__DIR__ . '/credentials.json');
$service = new Google_Service_Sheets($client);
$spreadsheetId = 'MY_SPREADSHEET_ID';
$range = "$language!B3:E15";

// This script uses the method of "spreadsheets.get".
$sheets = $service->spreadsheets->get($spreadsheetId, ["ranges" => [$range], "fields" => "sheets"])->getSheets();

// Following script is a sample script for retrieving "textFormat" and "textFormatRuns".
$data = $sheets[0] -> getData();
$startRow = $data[0] -> getStartRow();
$startColumn = $data[0] -> getStartColumn();
$rowData = $data[0] -> getRowData();
$res = array();
foreach ($rowData as $i => $row) {
    $temp = array();
    foreach ($row -> getValues() as $j => $value) {
        $tempObj = [
            "row" => $i + 1 + $startRow,
            "column" => $j + 1 + $startColumn
        ];
        if (isset($value['formattedValue'])) {
            $tempObj['formattedValue'] = $value -> getFormattedValue();
        } else {
            $tempObj['formattedValue'] = "";
        }
        $userEnteredFormat = $value -> getUserEnteredFormat();
        if (isset($userEnteredFormat['textFormat'])) {
            $tempObj['textFormat'] = $userEnteredFormat -> getTextFormat();
        } else {
            $tempObj['textFormat'] = null;
        }
        if (isset($value['textFormatRuns'])) {
            $tempObj['textFormatRuns'] = $value -> getTextFormatRuns();
        } else {
            $tempObj['textFormatRuns'] = null;
        }
        array_push($temp, $tempObj);
    }
    array_push($res, $temp);
}

  return($res);
}
?>

Tanaike · Accepted Answer · 2021-05-17 01:42:24Z

I believe your goal as follows.

You want to retrieve the font color of the part of text in a cell.
You want to achieve this using googleapis for php.
You have already been able to get values from Google Spreadsheet using Sheets API.

Modification points:

In your script, the method of "spreadsheets.values.get" in Sheets API is used. In this case, the values of "textFormat" and "textFormatRuns" are not included. In order to retrieve those values of the part of text in a cell, it is required to use the method of "spreadsheets.get".

When above points are reflected to your script, it becomes as follows.

Modified script:

$service = new Google_Service_Sheets($client);
$spreadsheetId = 'MY_SPREADSHEET_ID';
$range = "$language!B3:E15";

// This script uses the method of "spreadsheets.get".
$sheets = $service->spreadsheets->get($spreadSheetId, ["ranges" => [$range], "fields" => "sheets"])->getSheets();

// Following script is a sample script for retrieving "textFormat" and "textFormatRuns".
$data = $sheets[0] -> getData();
$startRow = $data[0] -> getStartRow();
$startColumn = $data[0] -> getStartColumn();
$rowData = $data[0] -> getRowData();
$res = array();
foreach ($rowData as $i => $row) {
    $temp = array();
    foreach ($row -> getValues() as $j => $value) {
        $tempObj = [
            "row" => $i + 1 + $startRow,
            "column" => $j + 1 + $startColumn
        ];
        if (isset($value['formattedValue'])) {
            $tempObj['formattedValue'] = $value -> getFormattedValue();
        } else {
            $tempObj['formattedValue'] = "";
        }
        $userEnteredFormat = $value -> getUserEnteredFormat();
        if (isset($userEnteredFormat['textFormat'])) {
            $tempObj['textFormat'] = $userEnteredFormat -> getTextFormat();
        } else {
            $tempObj['textFormat'] = null;
        }
        if (isset($value['textFormatRuns'])) {
            $tempObj['textFormatRuns'] = $value -> getTextFormatRuns();
        } else {
            $tempObj['textFormatRuns'] = null;
        }
        array_push($temp, $tempObj);
    }
    array_push($res, $temp);
}
print($res);

Result:

When above script is used for the following situation,

the following result is obtained.

[
  [
    {
      "row":1,
      "column":1,
      "formattedValue":"sample1 sample2 sample3 sample4 sample5",
      "textFormat":{"bold":true},
      "textFormatRuns":[
        {"startIndex":8,"format":{"foregroundColor":{"red":1},"foregroundColorStyle":{"rgbColor":{"red":1}}}},
        {"startIndex":15,},
        {"startIndex":16,"format":{"foregroundColor":{"green":1},"foregroundColorStyle":{"rgbColor":{"green":1,}}}},
        {"startIndex":23,},
        {"startIndex":24,"format":{"foregroundColor":{"blue":1},"foregroundColorStyle":{"rgbColor":{"blue":1,}}}},
        {"startIndex":31,}]
    }
  ]
]

From above result, for example, it is found that sample2 of the index from 8 to 15 has the red color.
About the color of rgbColor in above result, you can see the detail information at the official document. Ref

Note:

This is a simple sample script. So please modify above for your actual situation.

Reference:

Method: spreadsheets.get

Collectives™ on Stack Overflow

How to get text color formatting from Google Sheets API?

1 Answer 1

Modification points:

Modified script:

Result:

Note:

Reference:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modification points:

Modified script:

Result:

Note:

Reference:

Comments

Your Answer

Sign up or log in

Post as a guest

Related