How to pull URL/hyperlink data from google sheets to Google Script

Question

I'm new to google scripts...

I have a reading list in a google sheet, and I'm trying to pull through the data from the sheet to display in a table format using google scripts. Most of the table is displaying correctly, and even have the titles of the publications as hyperlinks, but the hyperlinks don't seem to work as they should, in that they don't go anywhere...any advice greatly appreciated!

here is the table

here is the google sheet table

<script>
  /*
  * THIS FUNCTION CALLS THE getData() FUNCTION IN THE Code.gs FILE,
  * AND PASSES RETURNED DATA TO showData() FUNCTION
  */
  google.script.run.withSuccessHandler(showData).getData();

  // THIS FUNCTION GENERATES THE DATA TABLE FROM THE DATA ARRAY
  function showData(dataArray){
    $(document).ready(function(){
      $('#data-table').DataTable({
        data: dataArray,
        // CHANGE THE TABLE HEADINGS BELOW TO MATCH WITH YOUR SELECTED DATA RANGE
        columns: [
          {
            "title": "Title",
            "render": function(data, type, row, meta){
              if(type === 'display'){
                // Assuming each row has a unique URL in the first element of the row array
                data = '<a href="' + row[0] + '">' + data + '</a>';
              }
              return data;
            }
          },
          {"title": "Author(s)"},
          {"title": "Type"},
          {"title": "Source"},
          {"title": "Reason"},
        ]
      });
    });
  }
</script>

I was attempting to have the first column of the table pull through the URL data from the google sheets table to the google scripts one and be a working hyperlink, but instead when I click on it it doesn't go anywhere

Answer 1

Modification points:

• From your reply, I noticed that your Google Apps Script of getData() as follows.

  function getData() {
    var spreadSheetId = "###";
    var dataRange = "Sheet1!A2:F";
    var range = Sheets.Spreadsheets.Values.get(spreadSheetId, dataRange);
    var values = range.values;
    return values;
  }
  

• From your showing Spreadsheet image, I think that the cell values of column "A" are texts with hyperlinks. In this case, when Sheets.Spreadsheets.Values.get is used, the hyperlinks are not included in the response values. I think that this is the reason for your current issue.

• In order to retrieve both texts and the hyperlinks, here, RichText is used.

When these points are reflected in your script, how about the following modification?

Google Apps Script:

Please set your Spreadsheet ID.

function getData() {
  var spreadSheetId = "###";
  var sheet = SpreadsheetApp.openById(spreadSheetId).getSheetByName("Sheet1");
  var range = sheet.getRange("A2:F" + sheet.getLastRow());
  var values = range.getRichTextValues().map(r => r.map(c => {
    var text = c.getText();
    var url = c.getLinkUrl();
    return url ? `<a href="${url}">${text}</a>` : text;
  }));
  return values;
}

• By this modification, the value of column "A" is <a href="${url}">${text}</a>. This is used with the data table.
• If you want to open the URL as a new tab, please modify return url ? `<a href="${url}">${text}</a>` : text; to return url ? `<a href="${url}" target="_blank">${text}</a>` : text;.
• In your situation, it's "A2:E" instead of "A2:F"?

HTML:

<script>
  /*
  * THIS FUNCTION CALLS THE getData() FUNCTION IN THE Code.gs FILE,
  * AND PASSES RETURNED DATA TO showData() FUNCTION
  */
  google.script.run.withSuccessHandler(showData).getData();

  // THIS FUNCTION GENERATES THE DATA TABLE FROM THE DATA ARRAY
  function showData(dataArray){
    $(document).ready(function(){
      $('#data-table').DataTable({
        data: dataArray,
        // CHANGE THE TABLE HEADINGS BELOW TO MATCH WITH YOUR SELECTED DATA RANGE
        columns: [
          {"title": "Title"},
          {"title": "Author(s)"},
          {"title": "Type"},
          {"title": "Source"},
          {"title": "Reason"},
        ]
      });
    });
  }
</script>

Note:

• Although I'm not sure about your actual situation, if you are using Web Apps, please reflect the latest script to the Web Apps, and test it again. Please be careful about this.