Displaying a URL from a Google Spreadsheet.

[MYNASADATA]

Hello,

I've got a simple task where I'd like to store links to .pdf files in
a google spreadsheet and provide a searchable "database" for the
inputs. The issue I'm having is I can't figure out how to display the
URL as a clickable link.

Any advice?

Thanks!

Dan O.





Here's where the page is: https://nice.larc.nasa.gov/posters/eposter.php

Code:
<?php
$search= $_REQUEST['search'];
if ($search > ''){ $search = $search;} else { $search = '';}
?>

<script type="text/javascript" src="http://www.google.com/jsapi"></
script>
<script type="text/javascript">
google.load('visualization', '1', {packages: ['table']});
</script>
<script type="text/javascript">
var visualization;

function drawVisualization() {

var query = new google.visualization.Query(
'https://docs.google.com/spreadsheet/pub?
key=0AljmjaO5b6RKdFM2T29nbFNnajFuZ0RFRllMWllkcEE&output=html');

query.setQuery('SELECT A, B, C, D, E where upper(A) like upper("%<?php
echo $search; ?>%") or upper(D) like upper("%<?php echo $search; ?>%")
order by A asc label A "Author Name", B "Contact Email", C "Poster
Title", D "Abstract", E "Poster Link(PDF)"');

query.send(handleQueryResponse);
}

function handleQueryResponse(response) {
if (response.isError()) {
alert('Error in query: ' + response.getMessage() + ' ' +
response.getDetailedMessage());
return;
}

var data = response.getDataTable();

visualization = new
google.visualization.Table(document.getElementById('table'));
visualization.draw(data, {legend: 'bottom'});

}

google.setOnLoadCallback(drawVisualization);
</script>

<div id="table"></div>

</div>
</p>

[asgallant]

You need to format the DataTable column containing the URL with a PatternFormatter (https://developers.google.com/chart/interactive/docs/reference#patternformatter), and then draw the Table with the 'allowHtml' option set to true:

function handleQueryResponse(response) {
    if (response.isError()) {
        alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
        return;
    }
    
    var data = response.getDataTable();

    var formatter = new google.visualization.PatternFormat('<a href="{0}">{0}</a>');
    // format the 5th column (URL) and place the output in the 5th column
    formatter.format(data, [4], 4);

    visualization = new google.visualization.Table(document.getElementById('table'));
    visualization.draw(data, {

        allowHtml: true,
        legend: 'bottom'
    });