CODEWITHSUNDEEP
google-sheets
Joshua Snider
Joshua Snider

Reputation: 795

Open a column of links as new tabs in Google Sheets

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.

Upvotes: 1

Views: 431

Answers (2)

Rafa Guillermo
Rafa Guillermo

Reputation: 15377

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/.

Upvotes: 1

Joshua Snider
Joshua Snider

Reputation: 795

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

}

Upvotes: 0

Related Questions