How to Create Dynamic Data Validation in Google Sheets

Having consistently formatted data is a must in an organization heavily reliant on Google Sheets & Excel

Photo by Mika Baumeister on Unsplash

If you have spent time in a Google Sheet you have probably shared a sheet with a coworker and gotten it back and wondered why they entered data a certain way. When you have a sheet that is built using formulas these ad-hoc changes can wreak havoc.

The easy fix for this is to create data validation. But the problem with this method is that every time you add data to the Sheet you will have to add data validation to each new row.

Using Google Apps Script, it is possible to create a script that will ensure no matter how many rows of data you have your data validation rules are applied to all and future rows.

---

Do you have an 💡?

If you have automation ideas you would like built, reach out to me for a free consultation!

---

In my example, I will ensure that the email column requires the text to be a valid email, and in the state column adding a dropdown with state abbreviations.

function setDataValidation(){
  const sheet3 = ss.getSheetByName('Sheet3');
  const firstRow = 2;
  const lastRow = sheet3.getLastRow();
  const [emailCol, stateCol] = [3, 4];

  const emailRule = SpreadsheetApp.newDataValidation()
    .requireTextIsEmail()
    .build();

    const states = ["AK", "AL", "AR", "AZ", "CA", "CO", "CT", "DE", "FL", "GA", "HI", "IA", "ID", "IL", "IN", "KS", "KY", "LA", "MA", "MD", "ME", "MI", "MN", "MO", "MS", "MT", "NC", "ND", "NE", "NH", "NJ", "NM", "NV", "NY", "OH", "OK", "OR", "PA", "RI", "SC", "SD", "TN", "TX", "UT", "VA", "VT", "WA", "WI", "WV", "WY"];

  let stateRule = SpreadsheetApp.newDataValidation()
    .requireValueInList(states)
    .build();

  let i = 0;
  while(i < lastRow - 1){
    let row = firstRow + i;
    let emailCell = sheet3.getRange(row, emailCol).setDataValidation(emailRule);
    let stateCell = sheet3.getRange(row, stateCol).setDataValidation(stateRule);
    i++;
  }
}

---

If this tutorial was helpful to you and you want to support my work, consider buying me a slice of 🍕, my favorite food, by quite a wide margin!

---

Having proper data validation is very important especially once you start trying to build more complex programs with Apps Scripts!