0

I'm using the new scripting functionality (not VBA) in Excel Online to use with Power Automate. I am trying to create a script that deletes rows after a given point. Initially, it worked, but then I keep getting errors.

I have used the functions from:

  1. getRangeEdge(direction, activeCell),
  2. getRowIndex(),
  3. getHeaderRowRange() and
  4. deleteRowsAt(index, count).

My final script is:

function main(workbook: ExcelScript.Workbook) {
  // [Directly from link 1]
  // Get the current worksheet.
  let selectedSheet = workbook.getActiveWorksheet();

// [Directly from link 3]
  // Get the first table on the current worksheet.
  const currentSheet = workbook.getActiveWorksheet();
  const table = currentSheet.getTables()[0];


// [Directly from link 1]
  // Get the last used cell at the end of the column.
  // This recreates the Ctrl+Down arrow key behavior.
  let firstCell = selectedSheet.getRange("A1");
  let firstColumn = selectedSheet.getRange("A1").getRangeEdge(ExcelScript.KeyboardDirection.down);
  let cellAfter = firstColumn.getOffsetRange(1, 0);


// [My own creation using link 2]
  // Saves the row number of the cell after the Ctrl+Down
  let FinalRow = cellAfter.getRowIndex();


// [My own creation using link 4]
  // Deletes all rows from the last row for 2000 rows.
  // Without '-1' a blank row is left. '-1' deletes all the empty rows.
  table.deleteRowsAt(FinalRow - 1,2000);


}

When I first ran the script, I didn't get any issues and it works. The second time I ran the script, it worked, but I received an error saying "Line 25: Table deleteRowsAt: The argument is invalid or missing or has an incorrect format.". After refreshing, I believe, I ran into a third issue where the script gives me an error at "ExcelScript" - but works and gives me the "Line 25..." error. Screenshot below.

Script showing "ExcelScript" and "Line 25..." errors"Script showing "ExcelScript" and "Line 25..." errors"

Same code, but without comments"Same code, but without comments"

I need this to function without errors, because when I add it to my Power Automate flow, errors cause it to break.

I do realise that I can configure 'run after' in Power Automate to continue even though the 'working' script provides an error. However, when running the script through Power Automate, it does not 'work', even though running it through Excel Online does 'work'.

I appreciate your assistance.

PainfulTruth
  • 41

0 Answers0