Convert url to clickable hyperlink-CodePudding

i'm using the discogs api to export my wantlist to a google spreadsheet

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 ?

CodePudding user response：

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

CodePudding user response：

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/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(
'<a href="'   data.wants[i].basic_information.resource_url   '">link</a>'
);
  Logger.log(stats);
  
    SpreadsheetApp.getActiveSpreadsheet().appendRow(stats);
  } 
  } 
  
}