Automated Script not creating dynamic table

[WxShady13]

When I record a script the first step is to create a table from the data. Obviously the data could be any amount of rows, however it is a set number of columns. When I attempt to run the script (even on the same data without the table) I get an error. Here is the Code for the script. The error is Line 4 (which begins with let newTable) Workbook addTable: You cannot perform the requested operation. Why will it not at least create the table with the provided range, and how would I make it create the table dynamically to account for any amount of rows?


function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
// Add a new table at range A1:R87 on selectedSheet
let newTable = workbook.addTable(selectedSheet.getRange("A1:R87"), true);
// Set wrap text to false for table newTable
newTable.getRange().getFormat().setWrapText(false);
// Indent set to 0 for table newTable
newTable.getRange().getFormat().setIndentLevel(0);
// Insert at range M:M on selectedSheet, move existing cells right
selectedSheet.getRange("M:M").insert(ExcelScript.InsertShiftDirection.right);
// Set number format for range M:M on selectedSheet
selectedSheet.getRange("M:M").setNumberFormatLocal("0.00");
// Set range M2 on selectedSheet
selectedSheet.getRange("M2").setFormulaLocal("=DAYS(TODAY(),L2)");
let conditionalFormatting: ExcelScript.ConditionalFormat;
// Create cell value criteria for range M:M on selectedSheet
conditionalFormatting = selectedSheet.getRange("M:M").addConditionalFormat(ExcelScript.ConditionalFormatType.cellValue);
conditionalFormatting.getCellValue().setRule({operator: ExcelScript.ConditionalCellValueOperator.greaterThan, formula1: "=29"});
conditionalFormatting.getCellValue().getFormat().getFill().setColor("#ffc7ce");
conditionalFormatting.getCellValue().getFormat().getFont().setColor("#9c0006");
conditionalFormatting.setStopIfTrue(false);
conditionalFormatting.setPriority(0);
// Sort on table: newTable column index: '12'
newTable.getSort().apply([{key: 12, ascending: true}]);
}

 

[ExcelToDAX]

Are you trying to run it from Power Automate? selectedSheet won't work when running a Power Automate flow

 

[WxShady13]

I may be confused by your question. I have the data open on Sheet1 and then click to run the script. Is that not what I am supposed to be doing?

 

[RobinAtRestore]

hi
I'm having exactly the same issue, weirdly this was working about 10 days ago, so maybe something changed somewhere, any help or advice would be appreciated.
My code:
function main(workbook: ExcelScript.Workbook) {
console.log("starting");
//let selectedSheet = workbook.getWorksheet("PIM");
// selectedSheet.activate;
let selectedSheet = workbook.getActiveWorksheet();
//let date = new Date(Date.now());
//let date = new Date(2023,5,31);
//selectedSheet.getRange("A1").setValue(date.toDateString());
// Rename worksheet to "OAR"
//selectedSheet.setName("OAR Details");
// Add a new table at range A1:L38 on selectedSheet
let newTable = workbook.addTable(selectedSheet.getRange("A1:M46"), true);
let usedRange = selectedSheet.getUsedRange();
console.log("rows : " + usedRange.getRowCount());
console.log("cols : " + usedRange.getColumnCount());
console.log(usedRange.getAddress());

//let newTable = selectedSheet.addTable(usedRange,true);
//let newTable = selectedSheet.addTable(selectedSheet.getRange("A1:L100"), true);
//let newTable = workbook.addTable(selectedSheet.getRange("A1:L35"), true);
// Delete range H:H on selectedSheet
selectedSheet.getRange("H:H").delete(ExcelScript.DeleteShiftDirection.left);
then there is some more basic manipulation stuff.
The script generates the following error:

Line 13: Workbook addTable: You cannot perform the requested operation. (highlighted above)

none of the other let newTable= constructs work either.

Thanks

Robin