Open a column of links as new tabs in Google Sheets

Question

I have a column of links in Google Sheets, let's just say they're in C2:C11 for now. What I want is a cell that when clicked on or executed or whatever will go through the list and open all of them as webpages. I'd prefer to open them in Chrome, but the default browser (which is also Chrome) works too.

I know that the equivalent in python would be:

for link in column:
    webbrowser.open(link)

but not how to translate that into Sheets functions.

Edit:

With @DevenRamini, I was able to write the following:

function myFunction() {
  
  var range = SpreadsheetApp.getActiveSheet().getRange("H2:H7");
  var links = range.getValues();
  for (var i = 0; i < links.length; i++){
    var link = links[i][0];
    console.log(link);
    var html = "<script>window.open('" + link + "');google.script.host.close();</script>";

    var userInterface = HtmlService.createHtmlOutput(html);

    SpreadsheetApp.getUi().showModalDialog(userInterface, 'Open Tab');
  };

}

It almost works, but it's only opening one tab and that tab is the last link in the column.

plz refer this https://stackoverflow.com/questions/42632394/open-link-in-a-window-using-script-google-sheet — Deven Ramani, Jul 01 '21 at 04:01
@DevenRamani: That seems a lot like what I want. I'll look into it some more when I wake up tomorrow. — Joshua Snider, Jul 01 '21 at 04:17

score 1 · Accepted Answer · edited Nov 03 '21 at 08:55

Answer:

You can only have one modal dialog open at a given time, so instead of attempting to open one for each link, put all links in the same HTML string and open the dialog outside of the loop.

Code Changes:

First define html outside of the loop:

let html

Then remove the HtmlService and showModalDialog lines outside the loop:

function myFunction() {
  var range = SpreadsheetApp.getActiveSheet().getRange("H2:H7")
  var links = range.getValues()
  let html
  for (var i = 0; i < links.length; i++) {
    var link = links[i][0]
    console.log(link)
    html += "<script>window.open('" + link + "');google.script.host.close();</script>"
  }
  var userInterface = HtmlService.createHtmlOutput(html)
  SpreadsheetApp.getUi().showModalDialog(userInterface, 'Open Tabs')
}

This will open all links in new tabs - just remember that the links need to start with a protocol (such as https://) otherwise the link will be opened as a path of *-script.googleusercontent.com/.

score 0 · Answer 2 · answered Jul 01 '21 at 13:48

Thanks for the help guys! I've already accepted an answer above, but I figured I should post my "finished" code for the curious.

function myFunction() {
  
  var range = SpreadsheetApp.getActiveSheet().getRange("H2:H7");
  var links = range.getValues();
  var html = "";
  for (var i = 0; i < links.length; i++){
    var link = links[i][0];
    if (link.length) {
      console.log(link);
      html += "<script>window.open('" + link + "');google.script.host.close();</script>";
    }
  }
  var userInterface = HtmlService.createHtmlOutput(html);

  SpreadsheetApp.getUi().showModalDialog(userInterface, 'Open Tab');

}

Open a column of links as new tabs in Google Sheets

2 Answers2

Answer:

Code Changes: