Agregar validación de datos en rangos de Excel

La Biblioteca de JavaScript de JavaScript proporciona varias API para habilitar tu complemento para agregar validación automática de datos a tablas, columnas, filas y otros rangos en un libro de trabajo. Para comprender los conceptos y la terminología de la validación de datos, consulte los siguientes artículos sobre cómo los usuarios agregan la validación de datos a través de la interfaz Excel datos.

Control de programación de la validación de datos

La Range.dataValidation propiedad, que toma un objeto Validación de datos es el punto de entrada para el control programático de la validación de datos en Excel. Hay cinco propiedades para el objeto DataValidation:

  • rule — Define qué constituye datos válidos para el rango. Consulte DataValidationRule.
  • errorAlert - Especifica si aparece un error si el usuario introduce datos no válidos y define el texto de alerta, el título y el estilo; por ejemplo, Informativo, Advertencia y Detener. Consulte DataValidationErrorAlert.
  • prompt - Especifica si aparece un mensaje cuando el usuario se desplaza sobre el rango y define el mensaje de solicitud. Consulte DataValidationPrompt.
  • ignoreBlanks - Especifica si la regla de validación de datos se aplica a celdas en blanco en el rango. Valores predeterminados de true.
  • type — Una identificación de solo lectura del tipo de validación, como WholeNumber, Date, TextLength, etc. Se establece indirectamente cuando configura la propiedad rule.

Nota

La validación de datos agregada mediante programación se comporta como la agregada manualmente. En particular, ten en cuenta que la validación de datos se activa solo si el usuario introduce directamente un valor en una celda o copia y pega una celda de otro lugar en el libro de trabajo y elige la opción Valores pegar. Si el usuario copia una celda y realiza un simple pegado en un intervalo con validación de datos, la validación no se desencadena.

Crear reglas de validación

Para agregar la validación de datos a un intervalo, su código debe configurar la propiedad rule del objeto DataValidation en Range.dataValidation. Esto toma un objeto DataValidationRule que tiene siete propiedades opcionales. No puede estar presente más de una de estas propiedades en cualquier objeto DataValidationRule. La propiedad que incluyas determinará el tipo de validación.

Tipos de reglas de validación básicas y de fecha y hora

Las primeros tres propiedades DataValidationRule (es decir, tipos de reglas de validación) toman un objeto BasicDataValidation como su valor.

  • wholeNumber — Requiere un número entero además de cualquier otra validación especificada por el objeto BasicDataValidation.
  • decimal — Requiere un número decimal además de cualquier otra validación especificada por el objeto BasicDataValidation.
  • textLength — Aplica los detalles de validación en el objeto BasicDataValidation a la longitud del valor de la celda.

Aquí hay un ejemplo de creación de una regla de validación. Tenga en cuenta lo siguiente sobre este código.

  • El operator es el operador binario "GreaterThan". Siempre que utilice un operador binario, el valor que el usuario intenta introducir en la celda es el operando de la izquierda y el valor especificado en formula1 es el operando de la derecha. Así que esta regla dice que solo los números enteros que son mayores que 0 son válidos.
  • El formula1 es un número codificado de forma rígida. Si no sabes en el momento de la codificación cuál debe ser el valor, también puedes usar una fórmula de Excel (como una cadena) para el valor. Por ejemplo, "=A3" y "=SUMA (A4,B5)" también podrían ser valores de formula1.
Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getActiveWorksheet();
    var range = sheet.getRange("B2:C5");

    range.dataValidation.rule = {
            wholeNumber: {
                formula1: 0,
                operator: "GreaterThan"
            }
        };

    return context.sync();
})

Consulte BasicDataValidation para obtener una lista de los otros operadores binarios.

También hay dos operadores ternarios: "Between" y "NotBetween". Para usar estos, debe especificar la propiedad opcional formula2. Los valores formula1 y formula2 son los operandos del límite de la selección. El valor que el usuario intenta introducir en la celda es el tercer operando (evaluado). A continuación se muestra un ejemplo de uso del operador "Between".

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getActiveWorksheet();
    var range = sheet.getRange("B2:C5");

    range.dataValidation.rule = {
            decimal: {
                formula1: 0,
                formula2: 100,
                operator: "Between"
            }
        };

    return context.sync();
})

Las siguientes dos propiedades de reglas toman un objeto DateTimeDataValidation como su valor.

  • date
  • time

El objeto DateTimeDataValidation está estructurado de manera similar al BasicDataValidation: tiene las propiedades formula1, formula2 y operator y se usa de la misma manera. La diferencia es que no puede usar un número en las propiedades de la fórmula, pero puede introducir una cadena ISO 8606 datetime (o una fórmula de Excel). El siguiente es un ejemplo que define como valores válidos fechas de la primera semana de abril de 2018.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getActiveWorksheet();
    var range = sheet.getRange("B2:C5");

    range.dataValidation.rule = {
            date: {
                formula1: "2018-04-01",
                formula2: "2018-04-08",
                operator: "Between"
            }
        };

    return context.sync();
})

Tipo de regla de validación de lista

Utilice la propiedad list en el objeto DataValidationRule para especificar que los únicos valores válidos son los de una lista finita. Esto es un ejemplo. Tenga en cuenta lo siguiente sobre este código.

  • Supón que hay una hoja de trabajo llamada "Nombres" y que los valores en el rango "A1: A3" son nombres.
  • La propiedad source especifica la lista de valores válidos. El argumento cadena hace referencia a un rango que contiene los nombres. También puede asignar una lista delimitada por comas; por ejemplo: "Susana, Ricky, Liz".
  • La propiedad inCellDropDown especifica si aparecerá un control desplegable en la celda cuando el usuario la seleccione. Si está configurado para true, aparece el menú desplegable con la lista de valores del source
Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getActiveWorksheet();
    var range = sheet.getRange("B2:C5");   
    var nameSourceRange = context.workbook.worksheets.getItem("Names").getRange("A1:A3");

    range.dataValidation.rule = {
        list: {
            inCellDropDown: true,
            source: "=Names!$A$1:$A$3"
        }
    };

    return context.sync();
})

Tipo de regla de validación personalizada

Utilice la propiedad custom en el objeto DataValidationRule para especificar una fórmula de validación personalizada. Esto es un ejemplo. Tenga en cuenta lo siguiente sobre este código.

  • Asume que hay una tabla de dos columnas con columnas Nombre del atleta y Comentarios en las columnas A y B de la hoja de trabajo.
  • Para reducir la verbosidad en la columna Comentarios hace que los datos que incluyen el nombre del atleta no sean válidos.
  • SEARCH(A2,B2) devuelve la posición de inicio, en cadena en B2, de la cadena en A2. Si A2 no está contenido en B2, no devuelve un número. ISNUMBER() devuelve un valor booleano. Entonces la propiedad formula dice que los datos válidos para la columna Comentario son datos que no incluyen la cadena en la columna Nombre del atleta.
Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getActiveWorksheet();
    var range = sheet.getRange("B2:C5");
    var commentsRange = sheet.tables.getItem("AthletesTable").columns.getItem("Comments").getDataBodyRange();

    commentsRange.dataValidation.rule = {
            custom: {
                formula: "=NOT(ISNUMBER(SEARCH(A2,B2)))"
            }
        };

    return context.sync();
})

Crear alertas de error de validación

Puede crear una alerta de error personalizada que aparezca cuando un usuario intente introducir datos no válidos en una celda. A continuación puede ver un ejemplo simple. Tenga en cuenta lo siguiente sobre este código.

  • La propiedad style determina si el usuario recibe una alerta informativa, una advertencia o una alerta de "detención". En realidad, solo Stop impide que el usuario agregue datos no válidos. La ventana emergente de Warning y Information tiene opciones que permiten al usuario introducir datos no válidos de todos modos.
  • La propiedad showAlert toma como valor predeterminado a true. Esto significa que Excel aparecerá una alerta genérica (de tipo ) a menos que cree una alerta personalizada que establece o establece un mensaje, un título y Stop showAlert un estilo false personalizados. Este código establece un mensaje personalizado y un título.
Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getActiveWorksheet();
    var range = sheet.getRange("B2:C5");

    range.dataValidation.errorAlert = {
            message: "Sorry, only positive whole numbers are allowed",
            showAlert: true, // default is 'true'
            style: "Stop", // other possible values: Warning, Information
            title: "Negative or Decimal Number Entered"
        };

    // Set range.dataValidation.rule and optionally .prompt here.

    return context.sync();
})

Para obtener más información, vea DataValidationErrorAlert.

Crear pedidos de confirmación de la validación

Puedes crear un mensaje de instrucción que aparece cuando un usuario se desplaza sobre una celda o la selecciona, a la cual se le ha aplicado la validación de datos. A continuación se muestra un ejemplo.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getActiveWorksheet();
    var range = sheet.getRange("B2:C5");

    range.dataValidation.prompt = {
            message: "Please enter a positive whole number.",
            showPrompt: true, // default is 'false'
            title: "Positive Whole Numbers Only."
        };

    // Set range.dataValidation.rule and optionally .errorAlert here.

    return context.sync();
})

Para obtener más información, vea DataValidationPrompt.

Quitar la validación de datos de un intervalo

Para eliminar la validación de datos de un rango, llame al método Range.dataValidation.clear ().

myrange.dataValidation.clear()

No es necesario que el intervalo que desactive sea exactamente el mismo intervalo que el intervalo en que agregó la validación de datos. Si no es así, solo se borran las celdas superpuestas, si las hay, de los dos rangos.

Nota

Desactivar la validación de datos de un intervalo también desactivará cualquier validación que un usuario haya agregado manualmente al intervalo.

Vea también