Apply validation on whole column using appscript

Question

I'm trying to create a data validation using appscript. The code below is working but only for a single cell, Now I want to apply the validation for the whole column.

The validation gets applied on cell G5 when the cell C5 = Office and cell D5 = Retail and the values for the validation are sitting in different tab named "rentals".

If I try try to use C:C and D:D nothing happens.

Can someone please explain me where I went wrong?

Thanks!

function onEdit() {

var ss = SpreadsheetApp.getActive();
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('final rough');
var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('rentals');
var cell2 = sheet.getRange("C5").getValues(); //<< Get the values
var cell3 = sheet.getRange("D5").getValues(); //<< of these cells
var cell4 = sheet2.getRange("E2").getValue();
var cell5 = sheet2.getRange("F2").getValue();
var cell6 = sheet.getRange("G5");
var rule = SpreadsheetApp.newDataValidation()
.requireNumberBetween(cell4, cell5)
.setAllowInvalid(false)
.build();


if(cell2 == "Office" && cell3 == "Retail"){
cell6.setDataValidation(rule);
}
}

Related: https://stackoverflow.com/questions/63720612/what-does-the-range-method-getvalues-return-and-setvalues-accept — TheMaster, Jul 13 '22 at 08:03

score 0 · Answer 1 · answered Jul 13 '22 at 13:34

I'd rewrite it like this:

function onEdit(e) {
  const sheet = e.source.getSheetByName('final rough');
  const sheet2 = e.source.getSheetByName('rentals');
  const cell2 = sheet.getRange("C5").getValue(); 
  const cell3 = sheet.getRange("D5").getValue(); 
  const cell4 = sheet2.getRange("E2").getValue();
  const cell5 = sheet2.getRange("F2").getValue();
  const cell6 = sheet.getRange("G5");
  const rule = SpreadsheetApp.newDataValidation()
    .requireNumberBetween(cell4, cell5)
    .setAllowInvalid(false)
    .build();
  if (cell2 == "Office" && cell3 == "Retail") {
    cell6.setDataValidation(rule);
  }
}

But I'd also probably want to limit the action on this script to a particular unique action like clicking a check box. Other it will run on every edit to any cell in the spreadsheet. Also some of these actions require permissions so if they are not working for you it may require the use of an installable onEdit trigger.

More information is required to complete the script.

Apply validation on whole column using appscript

1 Answers1