I have create an Office script in Excel to expand the columns and also add DataValidation. The script works fine in excel. But when I try to run it with Power Automate I get this error. Line 9: tables.findAsync is not a function. I'm not understanding the error as I don't have anything in the script that says findAsync. I would greatly Appreciate any help. Below is the script. Thanks in advance!
async function main(workbook: ExcelScript.Workbook) {
// Get the active worksheet
let sheet: ExcelScript.Worksheet = workbook.getActiveWorksheet("Sheet1");
// Get all tables in the worksheet
let tables: ExcelScript.Table[] = sheet.getTables();
// Find the table named "Checklist"
let table: ExcelScript.Table | undefined = tables.find(tbl => tbl.getName() === "Checklist");
// If the table wasn't found, log an error and exit the script
if (!table) {
console.log("Error: Table 'Checklist' not found.");
return;
}
// Get the range for Column C (Removed?) in the table
let removedColumnRange: ExcelScript.Range = table.getColumnByName("Removed?").getRangeBetweenHeaderAndTotal();
// Clear existing data validation (if any)
removedColumnRange.getDataValidation().clear();
// Define dropdown options as a comma-separated string
const dropdownValues: string = "Yes,No";
// Create validation rule
const validationRule: ExcelScript.DataValidationRule = {
inCellDropdown: true,
list: {
inCellDropdown: true, // Corrected the typo here
source: dropdownValues
}
};
// Set the validation rule
removedColumnRange.getDataValidation().setRule(validationRule);
// Auto-fit the columns in the table
let tableColumns: ExcelScript.TableColumn[] = table.getColumns();
tableColumns.forEach((column) => {
column.getRange().getFormat().autofitColumns();
});
}
