CODEWITHSUNDEEP
arraysfor-loopgoogle-apps-scriptgoogle-sheets
Anthony Madle
Anthony Madle

Reputation: 393

Google Apps Script to add a Validation Dropdown automatically based on cell value

I'm looking to add a Validation dropdown everywhere a cell value is "test" in a specific range.

I thought I was close, but I think I'm running into an issue with the arrays, I believe.

Really struggling with this one, hoping it's a small tweak someone might see:

function setValueToEmptyCell(){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Sheet1');
  const valuesFlat = sheet.getRange('a1:c10').getValues().flat();
  //Empty array to store the output values;
  const output = [];

  valuesFlat.forEach(row => {
    if(row == 'test'){
      (SpreadsheetApp.newDataValidation()
      .setAllowInvalid(true)
      .requireValueInRange(sheet.getRange('$H1:H3'), true)
      .build());
    } else {
      output.push([row])
    }
  })

  //Because we have stored the original value we overwrite the whole range with the 'new' values
  sheet.getRange('a1:c10').setValues(output);
}

This is the error I'm seeing:

1:54:51 AM Notice Execution started
1:54:52 AM Error Exception: The number of rows in the data does not match the number of rows in the range. The data has 7 but the range has 10.

Upvotes: 0

Views: 1106

Answers (1)

Tanaike
Tanaike

Reputation: 201408

I believe your goal is as follows.

  • For the cells "A1:C10", when the cell value is test, you want to put a dropdown list of SpreadsheetApp.newDataValidation().setAllowInvalid(true).requireValueInRange(sheet.getRange('$H1:H3'), true).build().

Modification points:

  • In your script, the rows and columns cannot be parsed by flat() of const valuesFlat = sheet.getRange('a1:c10').getValues().flat();.
  • (SpreadsheetApp.newDataValidation().setAllowInvalid(true).requireValueInRange(sheet.getRange('$H1:H3'), true).build()) is not used.
  • In order to put the dropdown list, setDataValidations(rules) is used.

When these points are reflected in your script, how about the following modification?

Modified script:

function setValueToEmptyCell() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Sheet1');
  const range = sheet.getRange('a1:c10');
  const valuesFlat = range.getValues();
  const output = [];
  valuesFlat.forEach(row => {
    const temp = [];
    row.forEach(col => {
      if (col == 'test') {
        temp.push(SpreadsheetApp.newDataValidation()
          .setAllowInvalid(true)
          .requireValueInRange(sheet.getRange('$H1:H3'), true)
          .build());
      } else {
        temp.push(null);
      }
    });
    output.push(temp);
  });
  range.setDataValidations(output);
  
  // range.createTextFinder("test").matchEntireCell(true).replaceAllWith("");
}

  • When this script is run, when the cell value of "A1:C10" is test, a dropdown list of SpreadsheetApp.newDataValidation().setAllowInvalid(true).requireValueInRange(sheet.getRange('$H1:H3'), true).build() is put.

  • If you want to remove test from the cell value after the dropdown list was put, please use range.createTextFinder("test").matchEntireCell(true).replaceAllWith("");.

Reference:

Upvotes: 3

Related Questions