How do you create a custom DataValidationBuilder in Google Apps Script for SpreadSheets?

0
votes

Google Apps Script for spread sheets has a number of built in require- methods [1] but what if I want to create a my own validation routine. Effectively I'd like to create my own routine and have to return true/false and bind it to 'newDataValidation'

I see that 'withCriteria(criteria, args)' exists but that seems to be for modifying an existing rule set rather then creating one from scratch.

The two rules that I need added are maximum string length per line of text and maximum number of new lines per cell. If you can do this with the existing rule sets then bonus points for you.

[1] - https://developers.google.com/apps-script/reference/spreadsheet/data-validation-builder

1
google-apps-script

1 Answers

1
votes

If you're just using this as a form for another user, you could bypass all these things and just boot the contents of the cell and set a note or message, i.e. if the max characters allowed in a cell in column A is 8:

function onEdit(e) {
  var ss = e.source;
  var cell = ss.getActiveCell();
  var max_length = 8;

  if(cell.getColumn() === 1) {

    var value = String(cell.getValue());

    if(value.length > max_length) {

      cell.clearContent();
      cell.setNote("Oops, less than 8 characters please");
      //or perhaps this instead of a note
      //ss.toast('Less than 8 characters please', 'Oops', 3);

    } else {
      cell.setNote(null);
    }
  }
}