excel.datavalidation.yml

### YamlMime:TSType
name: Excel.DataValidation
uid: 'excel!Excel.DataValidation:class'
package: excel!
fullName: Excel.DataValidation
summary: >-
  Represents the data validation applied to the current range. To learn more about the data validation object model,
  read [Add data validation to Excel
  ranges](https://learn.microsoft.com/office/dev/add-ins/excel/excel-add-ins-data-validation)<!-- -->.
remarks: '\[ [API set: ExcelApi 1.8](/javascript/api/requirement-sets/excel/excel-api-requirement-sets) \]'
isPreview: false
isDeprecated: false
type: class
properties:
  - name: context
    uid: 'excel!Excel.DataValidation#context:member'
    package: excel!
    fullName: context
    summary: >-
      The request context associated with the object. This connects the add-in's process to the Office host
      application's process.
    remarks: ''
    isPreview: false
    isDeprecated: false
    syntax:
      content: 'context: RequestContext;'
      return:
        type: '<xref uid="excel!Excel.RequestContext:class" />'
  - name: errorAlert
    uid: 'excel!Excel.DataValidation#errorAlert:member'
    package: excel!
    fullName: errorAlert
    summary: Error alert when user enters invalid data.
    remarks: >-
      \[ [API set: ExcelApi 1.8](/javascript/api/requirement-sets/excel/excel-api-requirement-sets) \]


      #### Examples


      ```TypeScript

      // Link to full sample:
      https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/22-data-validation/data-validation.yaml


      await Excel.run(async (context) => {
          const sheet = context.workbook.worksheets.getItem("Decision");
          const commentsRange = 
              sheet.tables.getItem("NameOptionsTable").columns.getItem("Comments").getDataBodyRange();

          // When you are developing, it is a good practice to
          // clear the dataValidation object with each run of your code.
          commentsRange.dataValidation.clear();   

          // If the value of A2 is contained in the value of C2, then 
          // SEARCH(A2,C2) returns the number where it begins. Otherwise,
          // it does not return a number.
          let redundantStringRule = {
              custom: {
                  formula: "=NOT(ISNUMBER(SEARCH(A2,C2)))"
              }
          };
          commentsRange.dataValidation.rule = redundantStringRule;
          commentsRange.dataValidation.errorAlert = {
              message: "It is redundant to include the baby name in the comment.",
              showAlert: true,
              style: "Information",
              title: "Baby Name in Comment"
          };

          await context.sync();
      });

      ```
    isPreview: false
    isDeprecated: false
    syntax:
      content: 'errorAlert: Excel.DataValidationErrorAlert;'
      return:
        type: '<xref uid="excel!Excel.DataValidationErrorAlert:interface" />'
  - name: ignoreBlanks
    uid: 'excel!Excel.DataValidation#ignoreBlanks:member'
    package: excel!
    fullName: ignoreBlanks
    summary: Specifies if data validation will be performed on blank cells. Default is `true`<!-- -->.
    remarks: '\[ [API set: ExcelApi 1.8](/javascript/api/requirement-sets/excel/excel-api-requirement-sets) \]'
    isPreview: false
    isDeprecated: false
    syntax:
      content: 'ignoreBlanks: boolean;'
      return:
        type: boolean
  - name: prompt
    uid: 'excel!Excel.DataValidation#prompt:member'
    package: excel!
    fullName: prompt
    summary: Prompt when users select a cell.
    remarks: >-
      \[ [API set: ExcelApi 1.8](/javascript/api/requirement-sets/excel/excel-api-requirement-sets) \]


      #### Examples


      ```TypeScript

      // Link to full sample:
      https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/22-data-validation/data-validation.yaml


      await Excel.run(async (context) => {
          const sheet = context.workbook.worksheets.getItem("Decision");
          const rankingRange = sheet.tables.getItem("NameOptionsTable").columns.getItem("Ranking").getDataBodyRange();

          // When you are developing, it is a good practice to
          // clear the dataValidation object with each run of your code.
          rankingRange.dataValidation.clear();

          let greaterThanZeroRule = {
              wholeNumber: {
                  formula1: 0,
                  operator: Excel.DataValidationOperator.greaterThan
              }
          };
          rankingRange.dataValidation.rule = greaterThanZeroRule;

          rankingRange.dataValidation.prompt = {
              message: "Please enter a positive number.",
              showPrompt: true,
              title: "Positive numbers only."
          };

          rankingRange.dataValidation.errorAlert = {
              message: "Sorry, only positive numbers are allowed",
              showAlert: true,
              style: "Stop",
              title: "Negative Number Entered"
          };

          await context.sync();
      });

      ```
    isPreview: false
    isDeprecated: false
    syntax:
      content: 'prompt: Excel.DataValidationPrompt;'
      return:
        type: '<xref uid="excel!Excel.DataValidationPrompt:interface" />'
  - name: rule
    uid: 'excel!Excel.DataValidation#rule:member'
    package: excel!
    fullName: rule
    summary: Data validation rule that contains different type of data validation criteria.
    remarks: >-
      \[ [API set: ExcelApi 1.8](/javascript/api/requirement-sets/excel/excel-api-requirement-sets) \]


      #### Examples


      ```TypeScript

      // Link to full sample:
      https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/22-data-validation/data-validation.yaml


      await Excel.run(async (context) => {
          const sheet = context.workbook.worksheets.getItem("Decision");
          const nameRange = 
              sheet.tables.getItem("NameOptionsTable").columns.getItem("Baby Name").getDataBodyRange();

          // When you are developing, it is a good practice to
          // clear the dataValidation object with each run of your code.
          nameRange.dataValidation.clear();

          const nameSourceRange = context.workbook.worksheets.getItem("Names").getRange("A1:A3");

          let approvedListRule = {
              list: {
                  inCellDropDown: true,
                  source: nameSourceRange
              }
          };
          nameRange.dataValidation.rule = approvedListRule;

          await context.sync();
      });

      ```
    isPreview: false
    isDeprecated: false
    syntax:
      content: 'rule: Excel.DataValidationRule;'
      return:
        type: '<xref uid="excel!Excel.DataValidationRule:interface" />'
  - name: type
    uid: 'excel!Excel.DataValidation#type:member'
    package: excel!
    fullName: type
    summary: 'Type of the data validation, see `Excel.DataValidationType` for details.'
    remarks: '\[ [API set: ExcelApi 1.8](/javascript/api/requirement-sets/excel/excel-api-requirement-sets) \]'
    isPreview: false
    isDeprecated: false
    syntax:
      content: >-
        readonly type: Excel.DataValidationType | "None" | "WholeNumber" | "Decimal" | "List" | "Date" | "Time" |
        "TextLength" | "Custom" | "Inconsistent" | "MixedCriteria";
      return:
        type: >-
          <xref uid="excel!Excel.DataValidationType:enum" /> | "None" | "WholeNumber" | "Decimal" | "List" | "Date" |
          "Time" | "TextLength" | "Custom" | "Inconsistent" | "MixedCriteria"
  - name: valid
    uid: 'excel!Excel.DataValidation#valid:member'
    package: excel!
    fullName: valid
    summary: >-
      Represents if all cell values are valid according to the data validation rules. Returns `true` if all cell values
      are valid, or `false` if all cell values are invalid. Returns `null` if there are both valid and invalid cell
      values within the range.
    remarks: '\[ [API set: ExcelApi 1.8](/javascript/api/requirement-sets/excel/excel-api-requirement-sets) \]'
    isPreview: false
    isDeprecated: false
    syntax:
      content: 'readonly valid: boolean;'
      return:
        type: boolean
methods:
  - name: clear()
    uid: 'excel!Excel.DataValidation#clear:member(1)'
    package: excel!
    fullName: clear()
    summary: Clears the data validation from the current range.
    remarks: '\[ [API set: ExcelApi 1.8](/javascript/api/requirement-sets/excel/excel-api-requirement-sets) \]'
    isPreview: false
    isDeprecated: false
    syntax:
      content: 'clear(): void;'
      return:
        type: void
        description: ''
  - name: getInvalidCells()
    uid: 'excel!Excel.DataValidation#getInvalidCells:member(1)'
    package: excel!
    fullName: getInvalidCells()
    summary: >-
      Returns a `RangeAreas` object, comprising one or more rectangular ranges, with invalid cell values. If all cell
      values are valid, this function will throw an `ItemNotFound` error.
    remarks: '\[ [API set: ExcelApi 1.9](/javascript/api/requirement-sets/excel/excel-api-requirement-sets) \]'
    isPreview: false
    isDeprecated: false
    syntax:
      content: 'getInvalidCells(): Excel.RangeAreas;'
      return:
        type: '<xref uid="excel!Excel.RangeAreas:class" />'
        description: ''
  - name: getInvalidCellsOrNullObject()
    uid: 'excel!Excel.DataValidation#getInvalidCellsOrNullObject:member(1)'
    package: excel!
    fullName: getInvalidCellsOrNullObject()
    summary: >-
      Returns a `RangeAreas` object, comprising one or more rectangular ranges, with invalid cell values. If all cell
      values are valid, this function will return `null`<!-- -->.
    remarks: '\[ [API set: ExcelApi 1.9](/javascript/api/requirement-sets/excel/excel-api-requirement-sets) \]'
    isPreview: false
    isDeprecated: false
    syntax:
      content: 'getInvalidCellsOrNullObject(): Excel.RangeAreas;'
      return:
        type: '<xref uid="excel!Excel.RangeAreas:class" />'
        description: ''
  - name: load(options)
    uid: 'excel!Excel.DataValidation#load:member(1)'
    package: excel!
    fullName: load(options)
    summary: >-
      Queues up a command to load the specified properties of the object. You must call `context.sync()` before reading
      the properties.
    remarks: ''
    isPreview: false
    isDeprecated: false
    syntax:
      content: 'load(options?: Excel.Interfaces.DataValidationLoadOptions): Excel.DataValidation;'
      parameters:
        - id: options
          description: Provides options for which properties of the object to load.
          type: '<xref uid="excel!Excel.Interfaces.DataValidationLoadOptions:interface" />'
      return:
        type: '<xref uid="excel!Excel.DataValidation:class" />'
        description: ''
  - name: load(propertyNames)
    uid: 'excel!Excel.DataValidation#load:member(2)'
    package: excel!
    fullName: load(propertyNames)
    summary: >-
      Queues up a command to load the specified properties of the object. You must call `context.sync()` before reading
      the properties.
    remarks: ''
    isPreview: false
    isDeprecated: false
    syntax:
      content: 'load(propertyNames?: string | string[]): Excel.DataValidation;'
      parameters:
        - id: propertyNames
          description: A comma-delimited string or an array of strings that specify the properties to load.
          type: 'string | string[]'
      return:
        type: '<xref uid="excel!Excel.DataValidation:class" />'
        description: ''
  - name: load(propertyNamesAndPaths)
    uid: 'excel!Excel.DataValidation#load:member(3)'
    package: excel!
    fullName: load(propertyNamesAndPaths)
    summary: >-
      Queues up a command to load the specified properties of the object. You must call `context.sync()` before reading
      the properties.
    remarks: ''
    isPreview: false
    isDeprecated: false
    syntax:
      content: |-
        load(propertyNamesAndPaths?: {
                    select?: string;
                    expand?: string;
                }): Excel.DataValidation;
      parameters:
        - id: propertyNamesAndPaths
          description: >-
            `propertyNamesAndPaths.select` is a comma-delimited string that specifies the properties to load, and
            `propertyNamesAndPaths.expand` is a comma-delimited string that specifies the navigation properties to load.
          type: |-
            {
                        select?: string;
                        expand?: string;
                    }
      return:
        type: '<xref uid="excel!Excel.DataValidation:class" />'
        description: ''
  - name: 'set(properties, options)'
    uid: 'excel!Excel.DataValidation#set:member(1)'
    package: excel!
    fullName: 'set(properties, options)'
    summary: >-
      Sets multiple properties of an object at the same time. You can pass either a plain object with the appropriate
      properties, or another API object of the same type.
    remarks: ''
    isPreview: false
    isDeprecated: false
    syntax:
      content: 'set(properties: Interfaces.DataValidationUpdateData, options?: OfficeExtension.UpdateOptions): void;'
      parameters:
        - id: properties
          description: >-
            A JavaScript object with properties that are structured isomorphically to the properties of the object on
            which the method is called.
          type: '<xref uid="excel!Excel.Interfaces.DataValidationUpdateData:interface" />'
        - id: options
          description: Provides an option to suppress errors if the properties object tries to set any read-only properties.
          type: '<xref uid="office!OfficeExtension.UpdateOptions:interface" />'
      return:
        type: void
        description: ''
  - name: set(properties)
    uid: 'excel!Excel.DataValidation#set:member(2)'
    package: excel!
    fullName: set(properties)
    summary: 'Sets multiple properties on the object at the same time, based on an existing loaded object.'
    remarks: ''
    isPreview: false
    isDeprecated: false
    syntax:
      content: 'set(properties: Excel.DataValidation): void;'
      parameters:
        - id: properties
          description: ''
          type: '<xref uid="excel!Excel.DataValidation:class" />'
      return:
        type: void
        description: ''
  - name: toJSON()
    uid: 'excel!Excel.DataValidation#toJSON:member(1)'
    package: excel!
    fullName: toJSON()
    summary: >-
      Overrides the JavaScript `toJSON()` method in order to provide more useful output when an API object is passed to
      `JSON.stringify()`<!-- -->. (`JSON.stringify`<!-- -->, in turn, calls the `toJSON` method of the object that is
      passed to it.) Whereas the original Excel.DataValidation object is an API object, the `toJSON` method returns a
      plain JavaScript object (typed as `Excel.Interfaces.DataValidationData`<!-- -->) that contains shallow copies of
      any loaded child properties from the original object.
    remarks: ''
    isPreview: false
    isDeprecated: false
    syntax:
      content: 'toJSON(): Excel.Interfaces.DataValidationData;'
      return:
        type: '<xref uid="excel!Excel.Interfaces.DataValidationData:interface" />'
        description: ''
extends: '<xref uid="office!OfficeExtension.ClientObject:class" />'