Question

0

148

Views

Open a URL using check box by embedding a script

I want to make a google script which will open a URL in the same row as the checkbox when it is ticked or marked check. My checkboxes starts in A3:A and it's corresponding links are in C3:C

correct output

Project output

URL opener checkbox

This script opens only the first cell:

function processSelectedRows() {
  var rows = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("n- 
   gadget").getDataRange().getValues();
  var headers = rows.shift();
  rows.forEach(function(row) {
   if(row[0]) {
    Logger.log(JSON.stringify(row));
    setCellColors();
    openURL();
   }
  });
}

function setCellColors() {
  //Get the sheet you want to work with. 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("n-gadget");
  //Grab the entire Range, and grab whatever values you need from it. EX: 
   rangevalues
  var range = sheet.getRange("A1:A");
  var rangevalues = range.getValues();
  //Loops through range results
  for (var i in rangevalues) {
   for (var j in rangevalues) {
   //Get the x,y location of the current cell.
    var x = parseInt(j, 10) + 1;
    var y = parseInt(i, 10) + 1;
   //Set the rules logic
    if (rangevalues[i][j] == 1) {
   //Set the cell background
     sheet.getRange(y,x).setBackground("green");
     sheet.getRange(y,x).setFontColor("white");
    }
   }
  }
}


function openURL(){
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheetByName("n-gadget");
 var link = sheet.getRange("C3:C").getValue();
 var title = sheet.getRange("B3:B").getValue();
 showAnchor(title,link);
}

function showAnchor(name,url) {
 var html = '<html><body><a href="'+url+'" target="blank" 
 onclick="google.script.host.close()">'+name+'</a></body></html>';
 var ui = HtmlService.createHtmlOutput(html)
 SpreadsheetApp.getUi().showModelessDialog(ui,"check stats");
}

about 4 years ago · Santiago Gelvez

1 answers

Answer question

0

Answer question

Find remote jobs

Answer 1 · 2022-07-08T17:20:57.994Z

I've just figured out the solution:

var link = sheet.getRange(y,x+2).getValue();
var title = sheet.getRange(y,x+1).getValue();

Here's the full code (The data validation is either 1 and 0 instead of TRUE or FALSE):

function processSelectedRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("n-gadget");
  var rows = SpreadsheetApp.getActiveSpreadsheet().getDataRange().getValues();
    var headers = rows.shift();
  rows.forEach(function(row) {
    if(row[0]) {
      Logger.log(JSON.stringify(row));
      setCellColors();
    }
  });
}

function setCellColors() {
  //Get the sheet you want to work with. 
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheetByName("n-gadget");
  //Grab the entire Range, and grab whatever values you need from it. EX: rangevalues
  //if lower cell is set highlight will move upward
 var range = sheet.getRange("A1:A");
 var rangevalues = range.getValues();
  //Loops through range results
 for (var i in rangevalues) {
  for (var j in rangevalues) {
   //Get the x,y location of the current cell. y is row x is column
      var x = parseInt(j, 10) + 1;
      var y = parseInt(i, 10) + 1;
      //var link;
      //var title;
   //Set the rules logic
       if (rangevalues[i][j] == 1) {
        //Set the cell background
        //test location
        //sheet.getRange(y,x+3).setValue('OK');
        var link = sheet.getRange(y,x+2).getValue();
        var title = sheet.getRange(y,x+1).getValue();
        //title.getText(x+1);
        sheet.getRange(y,x).setBackground("green");
        sheet.getRange(y,x).setFontColor("white");
        showAnchor(title,link);
        range.clear();
      }
   }
  }
}

function showAnchor(name,url) {
  var html = '<html><body><a href="'+url+'" target="blank" onclick="google.script.host.close()">'+name+'</a></body></html>';
  var ui = HtmlService.createHtmlOutput(html)
  SpreadsheetApp.getUi().showModelessDialog(ui,"check stats");
}

see the output

P.S. This still has loop holes like if multiple checkboxes were marked (added the range clear function) so I'm still open for suggestions or tweaks.

0

148

Open a URL using check box by embedding a script

1 answers

0

Find remote jobs

Andres GPT