CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Ben Williams
Ben Williams

Reputation: 3

How to insert the URL for the specific sheet into a cell in a Google Sheets

I have a sheet called volunteers with a list of names of volunteers, and another sheet with those same volunteer names with additional information. I have managed to setup hyperlinks between the two sheets, but to do this I need to know the unique URL of a specific sheet (called volunteers).

Despite searching here for several hours, I cannot find a simple way to find the URL of the sheet in question and insert it into a specified cell on another sheet (called Formulas).

The code I have found is provided below. Unfortunately when I try to make a copy of the sheet this formula doesn't always work.

Any help someone could provide would be gratefully received. Thanks

    var SS = SpreadsheetApp.getActiveSpreadsheet();
    var ss = SS.getSheetByName('Volunteers');
    var urlVol = '';
    urlVol += SS.getUrl();
    urlVol += '#gid=';
    urlVol += ss.getSheetId(); 
    return urlVol; 
  }

Then inserting "=hyperlink(GetVolUrl())" into the specified cell to retrieve the URL.

Upvotes: 0

Views: 300

Answers (1)

Cooper
Cooper

Reputation: 64032

Function to get Sheet Urls:

function getSheetUrls() {
  const ss = SpreadsheetApp.getActive();
  ss.getSheets().forEach(sh=>{
    let url = Utilities.formatString('Name:%s Url:%s#gid=%s',sh.getName(),ss.getUrl(),sh.getSheetId())
    Logger.log(url)
  });  
}



function postSheetUrls() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1');
  sh.clearContents();
  let vs = [['Sheet Name', 'Link']];
  ss.getSheets().forEach(s => {
    let url = Utilities.formatString('%s#gid=%s', ss.getUrl(), s.getSheetId());
    vs.push([s.getName(), `=hyperlink("${url}","${s.getName()}")`]);
  });
  sh.getRange(1, 1, vs.length, vs[0].length).setValues(vs);
}

Image:

enter image description here

Upvotes: 1

Related Questions