Generate URL for each sheet from a list of tab names

Question

Google Sheets question here. Suppose I had a column in my workbook where each cell contained the name of a tab in the workbook. Is there a way (formula or script) to generate a column that contains the URL for each of those tabs?

Answer 1

Suppose you had names of sheets that you wanted urls for in column1 of Sheet1.

The below function will read those names in column1 of Sheet1 and create their urls and place them next to the names in Column2.

function listedSheetUrls() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Sheet1');
  var rg=sh.getRange(1,1,sh.getLastRow(),1);
  var vA=rg.getValues();
  var nA=vA.map(function(r){return r[0];});
  var shts=ss.getSheets();
  shts.forEach(function(sh,i){
    var idx=nA.indexOf(sh.getName());
    if(idx!=-1) {
      var url=Utilities.formatString('https://docs.google.com/spreadsheets/d/%s/edit#gid=%s',ss.getId(),sh.getSheetId());
      vA[idx].splice(1,0,url);
    }
  });
  sh.getRange(1,1,vA.length,vA[0].length).setValues(vA);
}