Search code examples
google-apps-scriptgoogle-sheetsgoogle-sheets-formula

Implementing a Dynamic "Lookup Criteria" for a Column in Google Sheets

In google sheet, I list dropdown options in a column. I get the options of this dropdown from a certain range in the other sheet. Data validation for single cell(g2) As you can see in the example, my data validation lookup criterion for the G2 cell is F133 to F260 in the "Lookup" Sheet.

What I want here is to get this data from the next column in the Lookup sheet in each cell that continues as G3-G4-G5.... For instance: "G133-G260" for G3, "H133-H260" for G4.

I can do this manually one by one with creating a individual data validation per cell, but is there a quick way to do it?

Solution

  • I believe your goal is as follows.

    • You want to create the data validation rules into the cells "G3:G" of the target sheet.
    • The data validation rules use the range of Lookup!F133:F260, Lookup!G133:G260, Lookup!H133:H260,,, to "G2", "G3", "G4",,, of the target sheet, respectively.
    • You want to achieve this using Google Apps Script.

    In this case, how about the following sample script?

    Sample script:

    Please copy and paste the following script to the script editor of Google Spreadsheet and set your target sheet name. And, save the script.

    function myFunction() {
  const sheetName = "Sheet1"; // Please set your target sheet name.

  let offset = 6;
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const loopupSheet = ss.getSheetByName("Lookup");
  const range = sheet.getRange("G2:G" + sheet.getLastRow());
  const dataValidations = range.getValues().map(_ => [SpreadsheetApp.newDataValidation().requireValueInRange(loopupSheet.getRange(133, offset++, 128)).build()]);
  range.setDataValidations(dataValidations);
}

    • When this script is run, the ranges of Lookup!F133:F260, Lookup!G133:G260, Lookup!H133:H260,,, are used as the data validation rules, and they are put to the cells "G2", "G3", "G4",,,.

    • In this case, sheet.getRange("G2:G" + sheet.getLastRow()) is used as the destination range. If you want to manually set the range, please modify it with like sheet.getRange("G2:G10").

    • let offset = 6; means that the 1st column is column "F". If you want to change this, please modify it.

    References: