google-sheets google-apps-script filter hyperlink

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

As another approach, how about the following modification? In this modification, first, the showing row numbers are retrieved, and the URLs are retrieved using the row numbers.

Modified script 1:

In this modification, it supposes that there is only one selected range.

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

  const start = selection.getRow();
  const end = start + selection.getNumRows();
  const showingRows = [];
  for (var r = start; r <= end; r++) {
    if (!sheet.isRowHiddenByFilter(r)) {
      showingRows.push(r - start);
    }
  }
  const withLinks = selection.getRichTextValues()
    .flatMap((row, i) => {
      if (showingRows.includes(i)) {
        return row.flatMap(cellRichTextValue => {
          const links = cellRichTextValue.getRuns().filter(run => run.getLinkUrl());
          return links.length > 0 ? links[0].getLinkUrl() : [];
        });
      }
      return [];
    });

  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!');
}

When this script is run after you filtered the sheet and selected URLs from column "A", only URLs of the selected cells are retrieved using the method isRowHiddenByFilter of the Class Sheet.

Modified script 2:

In this modification, it supposes that there are multiple scattered selected ranges.

function openAllLinks2() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const selections = sheet.getActiveRangeList().getRanges();

  const withLinks = selections.flatMap(selection => {
    const start = selection.getRow();
    const end = start + selection.getNumRows();
    const showingRows = [];
    for (var r = start; r <= end; r++) {
      if (!sheet.isRowHiddenByFilter(r)) {
        showingRows.push(r - start);
      }
    }
    return selection.getRichTextValues()
      .flatMap((row, i) => {
        if (showingRows.includes(i)) {
          return row.flatMap(cellRichTextValue => {
            const links = cellRichTextValue.getRuns().filter(run => run.getLinkUrl());
            return links.length > 0 ? links[0].getLinkUrl() : [];
          });
        }
        return [];
      });
  });

  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!');
}

Reference:

isRowHiddenByFilter(rowPosition)