I have a spreadsheet with many sheets. Each sheet is a report and has a different link. How could I get the link of this active sheet?

Eg: Output > The link of the report is available here.

I have a spreadsheet with many sheets. Each sheet is a report and has a different link. How could I get the link of this active sheet?

Eg: Output > The link of the report is available here.

• javascript
• google-sheets
• google-sheets-api

3 Answers 3

Good luck!

var thisDocumentUrl = SpreadsheetApp.getActiveSpreadsheet().getUrl();

Your 'here' link doesn't work, however, in lieu of that, it is possible to create a public hyperlink to a specific sheet.

When you open the Google Sheet and navigate the sheets in your browser, you will notice that there is an anchor element to the URL that is #gid=some_number .

Appending that same #gid=some_number piece to any hyperlink you have for the spreadsheet will link directly to that sheet.

If you are generating the sheets programmatically using a script, so don't know the gid in advance, then you will need to use the getSheetID function in your Google Script

A link to all of the spreadsheet sheets in whatever ever folder you wish to specify.

If it's your root it could take a while

function getFnFX(folder = DriveApp.getRootFolder()) {
  var tree = JSON.parse(PropertiesService.getScriptProperties().getProperty('FnF'));
  //Logger.log(JSON.stringify(tree));
  if (tree.level < level) {
    tree.level = level;
    PropertiesService.getScriptProperties().setProperty('FnF', JSON.stringify(tree));
  }
  const files = folder.getFilesByType(MimeType.GOOGLE_SHEETS);
  let row = Array.from([...Array(level).keys()], ((x, i) => { if (i == level - 1) { x = folder.getName(); } else { x = ''; } return x; }));
  tree.txt.push(row);
  row = Array.from([...Array(level).keys()], ((x, i) => { if (i == level - 1) { x = 'bold'; } else { x = 'normal'; } return x; }));
  tree.fwt.push(row);
  PropertiesService.getScriptProperties().setProperty('FnF', JSON.stringify(tree));
  if (files.hasNext()) {
    let row = Array.from([...Array(level).keys()], ((x, i) => { if (i == level - 1) { x = 'Files:'; } else { x = ''; } return x; }));
    tree.txt.push(row);
    tree.fwt.push(['normal']);
    PropertiesService.getScriptProperties().setProperty('FnF', JSON.stringify(tree));
  }
  while (files.hasNext()) {
    let file = files.next();
    let row = Array.from([...Array(level + 1).keys()], ((x, i) => { if (i == level) { x = file.getName(); } else { x = ''; } return x; }));
    tree.txt.push(row);
    //The next section of the code is hard wired for an array of 4 columns because I knew it  would work with a folder that has two levels of subfolders so it won't work for my whole drive.  I did that just for simplicity for this example.
    SpreadsheetApp.openById(file.getId()).getSheets().forEach(s =>
      tree.txt.push(["", "", "", `=HYPERLINK("https://docs.google./spreadsheets/d/${file.getId()}/edit#gid=${s.getSheetId()}","${s.getName()}")`]));
    tree.fwt.push(['normal']);
    PropertiesService.getScriptProperties().setProperty('FnF', JSON.stringify(tree));
  }
  const subfolders = folder.getFolders()
  while (subfolders.hasNext()) {
    let subfolder = subfolders.next();
    level++;
    getFnFX(subfolder);
  }
  level--;
}

function getFilesAndFoldersX() {
  const fldr = null;
  const ss = SpreadsheetApp.getActive();
  ss.toast("Entry");
  const sh = ss.getSheetByName('Sheet1');
  sh.clearContents();
  SpreadsheetApp.flush();
  PropertiesService.getScriptProperties().setProperty('FnF', JSON.stringify({ txt: [], fwt: [], level: 0 }));
  getFnFX();//if you have no parameters here it will start with the root and recursive access every folder in your drive.  If the provide it with DriveApp.getFolderById() then it will limit itself to that folder
  //Logger.log(PropertiesService.getScriptProperties().getProperty('FnF'));
  let tree = JSON.parse(PropertiesService.getScriptProperties().getProperty('FnF'));
  const l = tree.level + 1
  tree.txt.forEach(r => {
    if (r.length < l) {
      //Array.from(Array(l - r.length).keys()).forEach(e => r.push(''));
      r.splice(r.length, 0, ...Array(l - r.length).fill(''));
    }
  });
  tree.fwt.forEach(r => {
    if (r.length < l) {
      //Array.from(Array(l - r.length).keys()).forEach(e => r.push('normal'));
      r.splice(r.length, 0, ...Array(l - r.length).fill('normal'));
    }
  });
  Logger.log(JSON.stringify(tree));
  sh.getRange(1, 1, tree.txt.length, tree.level + 1).setValues(tree.txt);
  sh.getRange(1, 1, tree.fwt.length, tree.level + 1).setFontWeights(tree.fwt);
  PropertiesService.getScriptProperties().deleteProperty('FnF');
  ss.toast("EOF");
}

I only unleashed on one folder which only has a couple of levels of folders and here's the results:

The fourth column is hyperlinks to each sheet of each spreadsheet in your selection folder. If you unleashed it on your entire drive you may have to adjust the row size because I wrote this script a long time ago for a specific reason which did not include unleashing it on my entire drive because I know that will take about 20 minutes.

The basic recursion will travers all of the folders in what ever root folder that you wish and I've set it up to on look for spreadsheets.