fetch URL of the hyperlink from the cell using spreadsheets API

[parashutist84]

PLEASE READ: If you have a support contract with Google, please create an issue in the support console instead of filing on GitHub. This will ensure a timely response.

Hello, I need te fetch the data from the Google spreadsheets via API. But my cells contain hyperlinks. When I fetch the data in the response I receive only labels of the hyperlink. How to get URIs too?

[yash30201]

Hi @parashutist84

You can read everything about reading data from sheets api here: https://developers.google.com/sheets/api/samples/reading

Hyperlinks require extra metadata than just the formatted value / formula of a cell. Hence to fetch that, we would need to get the complete metadata of cell values which can be done using spreadsheets.get api as follows:

Code example:

$service = new Sheets(GOOGLEAPI_CLIENT);
$spreadsheetId = 'SPREADSHEET_ID';
$spreadsheets = $service->spreadsheets;
$values = $spreadsheets->get(
    $spreadsheetId,
    [
        'ranges' => 'Sheet1!A1:B3',
        'includeGridData' => true // If false then data is not returned
    ]
);
$sheet = $values->getSheets()[0];
$grids = $sheet->getData()[0];
$rows = $grids->getRowData();
foreach ($rows as $row) {
    $values = $row->getValues();
    foreach ($values as $cellData) {
        echo $cellData['formattedValue'] . PHP_EOL;
        echo $cellData['hyperlink'] . PHP_EOL;
        // hyperlink is null for non hyperlink values
    }
    echo PHP_EOL;
}