Trying to make first 2 columns of imported Datatable from Google Sheet clickable URls
Trying to make first 2 columns of imported Datatable from Google Sheet clickable URls
![Voyager_2](https://secure.gravatar.com/avatar/a16d57ad111c1c2a9627695a245590ff/?default=https%3A%2F%2Fvanillicon.com%2Fa16d57ad111c1c2a9627695a245590ff_200.png&rating=g&size=120)
Link to test case:
Been working in Javascript.HTML
Here's the fiddle version of that file:
https://jsfiddle.net/6qhumdn0/
Description of problem:
I'm using a Google Apps Script to convert a spreadsheet into a searchable, sortable datatable. I've been able to get it to work and display as I want except for 1 thing: I'd like to make the first two columns below the header formatted as hyperlinks. I formatted the desired spreadsheet cells as:
="<a href='https://URL'>Link Destination</a>"
That created hyperlinks on my table, but they were not clickable, which is so close to what I want..but not quite there. I'm thinking that something in my JavaScript needs to be modified, but my feeble attempts to modify ColumnDefs were unsuccessful.
The original source behind this Sheets-to-Datatable conversion is here:
https://www.bpwebs.com/pull-data-from-google-sheets-to-html-table/ and all credit to those authors.
Thanks for any help!
This question has an accepted answers - jump to answer
Answers
If you want to create a link automatically, use a rendering function - e.g. something like:
Allan
Okay, thank you! I was trying to use a render function but apparently had it formatted incorrectly. I am still a little confused as to where to place the render function? Does it go under the {"title":"Column name"} for the columns I want to link?
Also if there's a better way of posting code snippets, please let me know; I'm new and didn't see in the FAQ how to post like what you did.
function showData(dataArray){
$(document).ready(function(){
var table = $('#data-table').DataTable({
data: dataArray,
//CHANGE THE TABLE HEADINGS BELOW TO MATCH WITH YOUR SELECTED DATA RANGE
columns: [
{"title":"Ship"},
{
data: 'linkName',
render: function (data, type, row) {
return '<a href="'+row.link+'">'+data+'</a>';
}
}
{"title":"Class"},
{"title":"Build Type"},
{"title":"Subtype"},
{"title":"Performance"},
{"title":"Author"}
]}
});
table.page.len(25).draw();
});
}
Don't use the backticks on each line. See the formatting tip in the text below the
Post Comment
button when you are entering a comment:Yes, see this example. Like you don't want to copy Allan's example verbatim into your code. It will need adjustments based on your data structure, etc.
Kevin
Got it.
The script I baselined from is using columns: [ {"title":"Name1"},{"title":"Name2"}]
Where do I fit in the render function so to still use that existing structure for the headers but add links to those comments? I tried the syntax below but was unsuccessful so I think I have it placed incorrectly. Does it need to be outside of the columns: [ ] array?
Based on your fiddle it looks like your row data is arrays not objects. You don't have
columns.data
so that indicates array structured data. See the data docs for more details.If my assumption is correct then
data: 'linkName',
is causing an error. Do you get any errors?This statement probably needs changed as well.
row.link
will look for an object with the keylink
in your data. This won't be there if using arrays. A better example might be from the Sheets to Datatable conversion technote you linked. Look towards the bottom for the sectionDisplay URLs as hyperlinks
.No,
columns.render
is defined where you have it.If you still have questions then post a couple rows of your data so we can see exactly what you have.
Kevin
That's a good point about the code snippet! I went back and implemented that using a separate column, but it's still not doing exactly what I wanted. Whether I leave a URL directly in the table or pipe it using this format in sheets:
="<a href='https://URL'>Link Destination</a>"
It doesn't actually make the link clickable. I can get URLs to show up either way and I can right-click a URL to open it in a new tab. I'd prefer to use the first two columns with piped links rather than having a separate URL column that posts the raw URL but ideally they'd be clickable, especially for mobile formats. This is what I've currently got in there right now:
Data:
https://docs.google.com/spreadsheets/d/1JFSJ-8wVRCY3u1-XhxchNxoXf5-iunT_Mj75oMinTLw/edit?usp=sharing
Live (but only accessible via direct url) page where the table is being deployed (at the bottom)
https://sites.google.com/view/stobetter/copy-of-intro-builds
Inspecting the link in the first column they look like this:
Looks correct. In fact you can right click and open in a new window.
Take a look at your browser's console to see this error:
Not sure how ti fix it but this SO thread may help you to understand the issue. Doesn't appear to be a Datatables issue.
Kevin
Thank you for explaining that! I will go down that road; thank you again for all of your help!
How do you format the DataTable properly so that it will show date/time format. I think you have to render it, but I am not sure. Currently my spreadsheet doesn't load in the DataTable if it contains a date. I appreciate the help as I am new using DataTables..
@Oralpath This is off topic from the thread which is asking about URLs. Since its a new subject its best to start a new thread.
Assuming you are using Datatables 1.12+ see this blog about date rendering.
Do you get alert messages or errors in the browser's console?
Kevin