Trying to make first 2 columns of imported Datatable from Google Sheet clickable URls

Voyager_2

Link to test case:

https://script.google.com/d/1OxqXesglmhXLeCpc9hEsnFY-xMslLwMJvNYelfrmZVqEh_pH3kaY14Ty/edit?usp=sharing

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!

allan

If you want to create a link automatically, use a rendering function - e.g. something like:

{
  data: 'linkName',
  render: function (data, type, row) {
    return '<a href="'+row.link+'">'+data+'</a>';
  }
}

Allan

Voyager_2

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

kthorngren

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.

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:

Posts are formatted using Markdown. To highlight code, please use triple back ticks (```) on new lines before and after the code block.

Does it go under the {"title":"Column name"} for the columns I want to link?

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

Voyager_2

Don't use the backticks on each line.

Got it.

Title vs. the Render function

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?

   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>';
                 }
            }

kthorngren

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?

return '<a href="'+row.link+'">'+data+'</a>';

This statement probably needs changed as well. row.link will look for an object with the key link 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 section Display URLs as hyperlinks.

Does it need to be outside of the columns: [ ] array?

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

Voyager_2

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:

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"},
          {"title":"Class"},
          {"title":"Build Type"},
          {"title":"Subtype"},
          {"title":"Performance"},
          {"title":"Author"},
          {"title":"Build Link",
              "render": function(data, type, row, meta){
              if(type === 'display'){
              data = '<a href="' + data + '">' + data + '</a>';
              }
              return data;
            }
          }
        ]
      });
    table.page.len(25).draw();
    });
  }

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

kthorngren

Inspecting the link in the first column they look like this:

<a href="https://sites.google.com/view/stobetter/intro-builds/tilor/bring-the-rainbow-tzentar-torps">Bring the Rainbow</a>

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:

Unsafe attempt to initiate navigation for frame with origin 'https://sites.google.com' from frame with URL 'https://n-oor2drlw5fl5rfj72yoo6tujbdd3yzl3hlpwkgq-0lu-script.googleusercontent.com/userCodeAppPanel'. The frame attempting navigation of the top-level window is sandboxed, but the flag of 'allow-top-navigation' or 'allow-top-navigation-by-user-activation' is not set.

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

Voyager_2

Thank you for explaining that! I will go down that road; thank you again for all of your help!

Oralpath

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..

kthorngren

@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.

Currently my spreadsheet doesn't load in the DataTable if it contains a date

Do you get alert messages or errors in the browser's console?

Kevin