Power Autoamate BUG/PROBLEM with Office Script

[ikmalfikri]

Hi,

I need help with Power Automate with Office Script/Excel Script.
So..I want to run a script to automatically create a table but power automate have problem with things such as GetSurroundingRegion(),getRowCount(),getValues(),

The script works perfectly fine if i run it manually but it all become problem when Power Automate runs it.

There's Create Table connector in Power Automate but how do i want to let the table height be dynamic as the function only alllows for constant value of row.

[DamoBird365 · Accepted solution]

Hi @ikmalfikri 

I've recently written the following which will build a table starting from A9 and finishing at L (last active row number).

You might be able to work with this?

function main(workbook: ExcelScript.Workbook) {
  
  // Get the first worksheet 
  const sheet = workbook.getFirstWorksheet();
  
  //get active range of WorkSheet
  let range = workbook.getActiveWorksheet().getUsedRange();

  // Get last used row of WorkSheet
  let lastrow = range.getRowCount();

  //get table range  
  const TableRange = `A9:L${lastrow}`;

  // Create a table using the data range.
  let newTable = workbook.addTable(sheet.getRange(TableRange), true);
  newTable.setName("TestTable");
  
  // Get the first (and only) table in the worksheet.
  let table = sheet.getTables()[0];

  // Get the data from the table.
  let tableValues = table.getRangeBetweenHeaderAndTotal().getValues();

  console.log(lastrow);
  return tableValues

}

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

[ikmalfikri]

thank you very much..finally it works..