Dynamically updating rspreadsheet.getRange() column depending on the location of sheet with macro/script

Question

I am trying to run a macro on Sheet1 for data validation on Sheet2 by dynamically updating the column. It selects the full column (except for the first row) in Sheet1 to then validate on that same column in Sheet2. As seen in the example spreadsheet.getRange('\'Sheet2'!$P$2:$P$9') is hardcoded to always use P2:P9. This is fine if I am validating the P column, how do I dynamically update this for when I start the script in column N?

function test2() {
  var spreadsheet = SpreadsheetApp.getActive();
  var currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  spreadsheet.getActiveRange().setDataValidation(SpreadsheetApp.newDataValidation()
  .setAllowInvalid(false)
  .requireValueInRange(spreadsheet.getRange('\'Sheet2'!$P$2:$P$9'), false)                                           
  .build());
};

Answer 1

I believe your goal as follows.

• You want to set the data validation rule to the active sheet using the column of "Sheet2" which is the same with the column selected at the active sheet.

For this, how about this answer?

Modification points:

• In this modification, the A1Notation is retrieved from the selected range, and the column letter is retrieved from it. By this, the values for setting the data validation rule can be used from the selected column letter.

When your script is modified, it becomes as follows.

Modified script:

Pease copy and paste the following script. In order to use this script, please select the range on "Sheet1" and run this function myFunction. By this, the same column with the selected column is used from "Sheet2", and the data validation rule is set.

function myFunction() {
  var spreadsheet = SpreadsheetApp.getActive();
  var currentCell = spreadsheet.getCurrentCell();
  var range = spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();  // Modified
  var column = range.getA1Notation().split(":")[0].replace(/\d+/g, "");  // Added
  currentCell.activateAsCurrentCell();
  spreadsheet.getActiveRange().setDataValidation(SpreadsheetApp.newDataValidation()
  .setAllowInvalid(false)
  .requireValueInRange(spreadsheet.getRange(`'Sheet2'!$${column}$2:$${column}$9`), false).build());  // Modified
}

• In this modification, the rows are used from 2 to 9 by your script. So when you want to modify this, please modify above script.

Reference:

• getA1Notation()