google-sheetsgoogle-apps-scriptfilterhyperlink

Google sheets open multiple hyperlinks when filtered! openallLinks()

I have a google sheet with multiple hyperlinks on it. I can select multiple links and click 'open selected links' and the links all open in separate tabs in chrome. This works well until I added a filter so that I can see hyperlinks with a similar theme, eg, 'Place Value' in Maths.

With the filter on, too many hyperlinks open, eg, if the filter is showing hyperlinks from rows 1, 4, 6 and 10 (4 links) then 10 links are opened - 1>10 inclusive!!

Is there a way to adapt the script to counter this? I have a working sheet link here and have filtered the rows to show 'place value'

https://docs.google.com/spreadsheets/d/1P5MS3mcAJRHURW5eWspl4uxiO8XyLPyT17GCNumry5I/edit?usp=sharing

Any help or ideas would be amazing! I am a teacher with little script knowledge! kind regards, Matt

Tried sorting the sheet in multiple ways, also tried selecting links individually

Here is a copy of the current script:

function openAllLinks() {
  // Get the selected range
  const selection = SpreadsheetApp.getActiveSheet().getActiveRange();

  // Filter for cells containing hyperlinks
  const withLinks = selection.getRichTextValues()
    .flatMap(row => row.flatMap(cellRichTextValue => {
      const links = cellRichTextValue.getRuns().filter(run => run.getLinkUrl());
      return links.length > 0 ? links[0].getLinkUrl() : [];
    }));

  if (withLinks.length == 0) {
    Browser.msgBox("No URLs were found.");
    return;
  }
  const opens = withLinks.map(url => `window.open('${url}', '_blank');`).join("");
  const html = HtmlService.createHtmlOutput(`<html><script>${opens};google.script.host.close();</script></html>`);
  SpreadsheetApp.getUi().showModalDialog(html, "sample");
  // ---

  // Show a confirmation message
  SpreadsheetApp.getUi().alert('Links opened successfully!');
}function myFunction() {
  
}
Solution

  • SUGGESTED SOLUTION

    The reason why, even With the filter on, too many hyperlinks open is because the script's logic goes through all the rows regardless if they're hidden.

    To open the showing hyperlinks that have been selected, another filter can be added in the code to only get the ones shown by the view.

    You can achieve that by changing the script from:

      const selection = SpreadsheetApp.getActiveSheet().getActiveRange();

  const withLinks = selection.getRichTextValues()
    .flatMap(row => row.flatMap(cellRichTextValue => {
      const links = cellRichTextValue.getRuns().filter(run => run.getLinkUrl());
      return links.length > 0 ? links[0].getLinkUrl() : [];
    }));

    To:

      const ss = SpreadsheetApp.getActiveSheet();
  const selection = ss.getActiveRange();

  const withLinks = selection.getRichTextValues()
    .flatMap((row, i) => row.filter(_ => !ss.isRowHiddenByFilter(selection.getRow() + i))
      .flatMap(cellRichTextValue => {
        const links = cellRichTextValue.getRuns().filter(run => run.getLinkUrl());
        return links.length > 0 ? links[0].getLinkUrl() : [];
      }));

    The full code would become:

    function openAllLinks() {
  const ss = SpreadsheetApp.getActiveSheet();
  const selection = ss.getActiveRange();

  const withLinks = selection.getRichTextValues()
    .flatMap((row, i) => row.filter(_ => !ss.isRowHiddenByFilter(selection.getRow() + i))
      .flatMap(cellRichTextValue => {
        const links = cellRichTextValue.getRuns().filter(run => run.getLinkUrl());
        return links.length > 0 ? links[0].getLinkUrl() : [];
      }));

  if (withLinks.length == 0) {
    Browser.msgBox("No URLs were found.");
    return;
  }
  const opens = withLinks.map(url => `window.open('${url}', '_blank');`).join("");
  const html = HtmlService.createHtmlOutput(`<html><script>${opens};google.script.host.close();</script></html>`);
  SpreadsheetApp.getUi().showModalDialog(html, "sample");

  SpreadsheetApp.getUi().alert('Links opened successfully!');
}

    This uses isRowHiddenByFilter(rowPosition) to check if the rows are hidden and selection.getRow() + i to iterate over them and return the ones selected that's not.

    OUTPUT

    OUTPUT