I have a code to create a data validation on a cell from a range next to it.
example:
var cellC4 = cell.getRange('F11');
var range = SpreadsheetApp.getActive().getRange('MAIN!AG11:AG11');
var rule = SpreadsheetApp.newDataValidation().requireValueInRange(range).build();
cellC4.setDataValidation(rule);
var cellC5 = cell.getRange('F12');
var range = SpreadsheetApp.getActive().getRange('MAIN!AG12:AG12');
var rule = SpreadsheetApp.newDataValidation().requireValueInRange(range).build();
cellC5.setDataValidation(rule);
var cellC6 = cell.getRange('F13');
var range = SpreadsheetApp.getActive().getRange('MAIN!AG13:AG13');
var rule = SpreadsheetApp.newDataValidation().requireValueInRange(range).build();
cellC6.setDataValidation(rule);
var cellC7 = cell.getRange('F14');
var range = SpreadsheetApp.getActive().getRange('MAIN!AG14:AG14');
var rule = SpreadsheetApp.newDataValidation().requireValueInRange(range).build();
cellC7.setDataValidation(rule);
can someone help me do it correctly
Im stuck here:
function onOpen(){
var ss0 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('MAIN');
var EndRow = ss0.getLastRow();
for ( var c = 16;c <= 25; c) {
for ( var i = 11;i <= EndRow; i ) {
//►PO# VALIDATION►
var range1 = ss0.getRange(i, c);
var rule1 = SpreadsheetApp.newDataValidation().requireValueInRange(range1).build();
ss0.getRange(i, 5).setDataValidation(rule1);
}}}
CodePudding user response:
Findings:
- Your looping for this part
for ( var c = 16;c <= 25; c) {
has a wrong iteration withc
instead of usingc
. Thus, this looping will never finish running.
SUGGESTION:
==UPDATE===
You can try this sample script below:
function onOpen(){
var ss0 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('MAIN');
var EndRow = ss0.getLastRow();
for(row=11; row<=EndRow; row ){
var data = ss0.getRange("P" row ":Y" row).getDisplayValues();
var rule = SpreadsheetApp.newDataValidation().requireValueInList(data[0]).build();
ss0.getRange(row,5).setDataValidation(rule);
}
}
Sample:
Result on column 5 or column
E
after theonOpen()
function finishes running