Convert url to clickable hyperlink

Question

I'm using the Discogs API to export my Wantlist to Google Sheets

Is there a way to add the HTML to make the release-url (resource_url) clickable in the output?

function logTheData (url){
  var sheet = SpreadsheetApp.getActiveSheet();
  var url ='https://api.discogs.com/users/bartvanbelle/wants?per_page=100';
  var response = UrlFetchApp.fetch(url); // get feed
  var json = response.getContentText(); //

  var data = JSON.parse(response);
  var counter = 100;
  for (var i = 0; i< counter; i++) {
  var stats = [];
    // var instance_id = data.wants[i].instance_id;
    
    //if (typeof data.wants[i].basic_information.formats[0].descriptions[0] !== "undefined"){
    //  var description = data.wants[i].basic_information.formats[0].descriptions[0]
    //  };
    
   // stats.push(instance_id);//works a
    stats.push(data.wants[i].basic_information.title); //works a
    stats.push(data.wants[i].basic_information.formats[0].name); 
           // stats.push(description); //stringify array?
  stats.push(String(data.wants[i].basic_information.formats[0].descriptions));  
  
    stats.push(data.wants[i].basic_information.labels[0].name);    //works c
    stats.push(data.wants[i].basic_information.labels[0].catno);        // work d
    stats.push(data.wants[i].basic_information.year); //l
   stats.push(data.wants[i].basic_information.artists[0].name); //works j  
   stats.push(data.wants[i].basic_information.id); //   m   
        stats.push(data.wants[i].basic_information.resource_url); //   m   
    
  Logger.log(stats);
    SpreadsheetApp.getActiveSpreadsheet().appendRow(stats);
  } 
  
   
  var pages = data.pagination.pages;
  for (var a = 1; a < pages; a++){
       var next = data.pagination.urls.next;
       var response = UrlFetchApp.fetch(next); // get feed
  var json = response.getContentText(); //

  var data = JSON.parse(response);
  var counter = 100;
  for (var i = 0; i< counter; i++) {
  var stats = [];
    
    stats.push(data.wants[i].basic_information.title); //works a
    stats.push(data.wants[i].basic_information.formats[0].name); 
  stats.push(String(data.wants[i].basic_information.formats[0].descriptions)); 
    stats.push(data.wants[i].basic_information.labels[0].name);    //works c
    stats.push(data.wants[i].basic_information.labels[0].catno);        // work d
    stats.push(data.wants[i].basic_information.year); //l
         // stats.push(description); //stringify array?
    stats.push(data.wants[i].basic_information.artists[0].name); //works j  
   stats.push(data.wants[i].basic_information.id); //   m  
      stats.push(data.wants[i].basic_information.resource_url); //   m 
  Logger.log(stats);
  
    SpreadsheetApp.getActiveSpreadsheet().appendRow(stats);
  } 
  } 
  
}

The resource URL is also formatted as http://api.discogs.com/.... Is there a way to convert that to http://www.discogs.com?

See also [How do I create a link using JavaScript?](https://stackoverflow.com/q/4772774/215552) — Heretic Monkey, Dec 17 '22 at 00:10

score 0 · Answer 1 · answered Nov 24 '22 at 13:50

Maybe try something like:

stats.push('=HYPERLINK(data.wants[i].basic_information.resource_url,data.wants[i].basic_information.resource_url')

In general what works is to

get the cell
set the value
set the formula (HYPERLINK(...))
setShowHyperlink(true) on the cell.

Hope this helps.

You might aswell checkout this answer: https://stackoverflow.com/a/37488194/1698461

devseo · Answer 2 · 2022-12-16T21:02:41.983

I think you need to wrap data.wants[i].basic_information.resource_url into html tag

like this :

stats.push(
'<a href="' + data.wants[i].basic_information.resource_url + '">link</a>'
);

so full code ll be :

    function logTheData (url){
  var sheet = SpreadsheetApp.getActiveSheet();
  var url ='https://api.discogs.com/users/**********/wants?per_page=100';
  var response = UrlFetchApp.fetch(url); // get feed
  var json = response.getContentText(); //
  
  var data = JSON.parse(response);
  var counter = 100;
  for (var i = 0; i< counter; i++) {
    var stats = [];

    stats.push(data.wants[i].basic_information.title); //works a
    stats.push(data.wants[i].basic_information.formats[0].name); 
    stats.push(String(data.wants[i].basic_information.formats[0].descriptions));  
    
    stats.push(data.wants[i].basic_information.labels[0].name);    //works c
    stats.push(data.wants[i].basic_information.labels[0].catno);        // work d
    stats.push(data.wants[i].basic_information.year); //l
    stats.push(data.wants[i].basic_information.artists[0].name); //works j  
    stats.push(data.wants[i].basic_information.id); //   m   
    stats.push(data.wants[i].basic_information.resource_url); //   m   
    
    Logger.log(stats);
    SpreadsheetApp.getActiveSpreadsheet().appendRow(stats);
  } 
  
  
  var pages = data.pagination.pages;
  for (var a = 1; a < pages; a++){
    var next = data.pagination.urls.next;
    var response = UrlFetchApp.fetch(next); // get feed
    var json = response.getContentText(); //
    
    var data = JSON.parse(response);
    var counter = 100;
    for (var i = 0; i< counter; i++) {
      var stats = [];
      
      stats.push(data.wants[i].basic_information.title); 
      stats.push(data.wants[i].basic_information.formats[0].name); 
      stats.push(String(data.wants[i].basic_information.formats[0].descriptions)); 
      stats.push(data.wants[i].basic_information.labels[0].name);    
      stats.push(data.wants[i].basic_information.labels[0].catno);       
      stats.push(data.wants[i].basic_information.year); 
      stats.push(data.wants[i].basic_information.artists[0].name); 
      stats.push(data.wants[i].basic_information.id); 
      stats.push( '<a href="' + data.wants[i].basic_information.resource_url + '">link</a>');
      Logger.log(stats); 
      SpreadsheetApp.getActiveSpreadsheet().appendRow(stats);
      } 
    } 
    
  }

I m happy that s working, have nice coding day Bart – devseo Nov 24 '22 at 15:37 — devseo, Nov 24 '22 at 15:37

Convert url to clickable hyperlink

2 Answers2