I am trying to create a script in Google Sheets that will automatically open a URL when a checkbox is checked within the same row. The checkboxes are located in cells A3:A and their corresponding URLs are in cells C3:C.
Output for the project:
The current script only opens the first cell with a checked checkbox:
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() {
// Accessing the sheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("n-gadget");
// Getting values from specified range
var range = sheet.getRange("A1:A");
var rangevalues = range.getValues();
// Loop through the results
for (var i in rangevalues) {
for (var j in rangevalues[i]) {
var x = parseInt(j, 10) + 1;
var y = parseInt(i, 10) + 1;
// Applying rules logic
if (rangevalues[i][j] == 1) {
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");
}