Can I use Data Validation in a table in Loop? (like in Quip)

Question

I want to add data validation into a cell in a table in loop. Like Status: Green, Yellow, Red. The I want to do math based on the data validation.

---

Moved from: Microsoft 365 Insider | Loop | Windows

Answer 1

You can implement data validation in Excel tables programmatically using the Excel JavaScript API. To add data validation for a specific cell or range within a table, you can utilize the dataValidation property. For instance, if you want to create a dropdown list with options like "Green", "Yellow", and "Red", you can define these values in the source property of the dataValidation object.

Here’s a simplified example of how to apply data validation in a loop:

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    let table = sheet.tables.getItem("YourTableName");
    let range = table.getDataBodyRange();

    // Loop through each cell in the range to apply data validation
    range.values.forEach(async (row, rowIndex) => {
        row.forEach(async (cell, colIndex) => {
            let cellRange = range.getCell(rowIndex, colIndex);
            cellRange.dataValidation = {
                rule: {
                    list: {
                        source: "Green, Yellow, Red"
                    }
                },
                inCellDropDown: true
            };
        });
    });

    await context.sync();
});

In this code snippet, data validation is applied to each cell in the specified range of the table, allowing users to select from the defined statuses. You can then perform calculations based on the selected values in these cells as necessary.

Answer 2

I mean in Loop NOT in Excel.

Answer 3

Hello, no, Loop does not currently support true data validation or formulas in tables the way Excel or Quip does. Sorry.