validation valid wholeNumber range is too small

[duane-j-wagner]

When setting dataValidation to a whole number between -x and y with a script, x and y can be 9999, but not anything bigger than 99999. I would have expected it to be integer min and max for excel. For 64-bit OS that should be quite large. This also impacts Excel javascript API. Setting it with the Excel UI works fine.

function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet();
  const range = sheet.getCell(2, 16);
  range.getDataValidation().setErrorAlert({
    title: 'Invalid Data',
    message: 'The value must be a decimal number',
    style: ExcelScript.DataValidationAlertStyle.stop,
    showAlert: true,
  });
   range.getDataValidation().setRule({
      wholeNumber: {
       formula1: "0",
       formula2: "99999",
        operator: ExcelScript.DataValidationOperator.between,
      },
    });

}

---

Document Details

⚠ Do not edit this section. It is required for learn.microsoft.com ➟ GitHub issue linking.

• ID: 15ac8cbd-64c1-5500-c40d-7d95ea909366
• Version Independent ID: e995ca82-332a-614e-f9ac-db71838677c4
• Content: ExcelScript.DataValidationRule interface - Office Scripts
• Content Source: docs/docs-ref-autogen/excel/excelscript/excelscript.datavalidationrule.yml
• Service: excel
• Sub-service: scripts
• GitHub Login: @o365devx
• Microsoft Alias: o365devx

[duane-j-wagner]

Looks like there is already a similar bug against the code. The document should mention any restrictions in how values can be set. I've seen some examples with quoted numbers and some without quotes. Does it matter? https://github.com/OfficeDev/office-js/issues/3088

[ElizabethSamuel-MSFT]

@duane-j-wagner Thanks for raising this issue.

@AlexJerabek Can you take a look?

Thanks.

[AlexJerabek]

Hi, @duane-j-wagner

Sorry for the delay. I was trying to get some internal traction. The information you linked to in the office-js bug is correct: the range is currently 0-65535.

[AlexJerabek]

Internal tracking number 8009234.