CODEWITHSUNDEEP
foreachpower-automateoffice-scripts
jwb2546
jwb2546

Reputation: 11

Office Script Loop

I'm trying to write a script to link into a Power Automate flow which will clean data for me before creating a table out of the data.

The script needs to delete the first 7 rows of data, and then find the first cell in column A which is blank, and delete the 30 rows below it (including the row which it evaluates)

Then it needs to select the data above and convert into a table

I can't find an in-depth explanation of for each/if loops in office scripts, any help would be appreciated

Upvotes: 1

Views: 12159

Answers (1)

Petra
Petra

Reputation: 426

I have included comments in the script below to explain how the for loop works, but let me know if you have any questions.

function main(workbook: ExcelScript.Workbook) {
  //gets the active worksheet. If you are using this script in Power Automate, you should get the sheet by its name.
  let sheet = workbook.getActiveWorksheet();

  //gets the first table in the sheet
  let table = workbook.getActiveWorksheet().getTables()[0];

  //deletes the first 7 rows in the table (excluding the header row)
  table.deleteRowsAt(0, 7);

  //gets the new table range between the header and total
  let tableRange = table.getRangeBetweenHeaderAndTotal();

  //gets the table values
  let tableValues = tableRange.getValues();

  //gets the number of rows and columns
  let tableRows = table.getRowCount();
  let tableColumns = table.getColumns().length;

  //endRow is specified at -1 in the case that there isn't an empty cell.
  let endRow = -1;

  /*now that I have the values of the table and the number of rows and columns, I will start at the first row (index 0 since arrays start at 0 instead of 1). This for loop continues until the last row of the table.
   */
  for (var i = 0; i < tableRows; i++) {
    /*for each row, I get the value of the cell in the first column and check to see if the cell is empty. When you get the values of a table, it is returned as a double array [][]. To access a cell in the tableValues double array, you would use the following syntax: tableValues[rowIndex][columIndex]
     */
    if (tableValues[i][0].toString() == "") {
      //if the value is empty, then I will make the endRow value be the row number. So if row 8 has an empty cell in columnA, the endRow value would be 8.
      endRow = i;
      //The line below deletes the rows from the row of the empty cell (inclusive) as well as the 30 rows below it (31 rows total).
      table.deleteRowsAt(i, 31);
      //"break" ends the for loop since we only want to find the FIRST empty cell rather than EVERY empty cell in column A in the table.
      break;
    }
  }

  //The value of endRow never changed so there were no empty cells in the table in column A
  if (endRow == -1) {
    console.log("No empty cells in Column A");
  }
  //the first row in the table is empty so we don't have any data above it to make a new table
  else if (endRow == 0) {
    console.log("New table would be empty since an empty cell was in the first row");
  } else {
    //get the range of the table - but stopping at the row that had the empty cell (this is including the table headers as well)
    let newTableRangeValues = table
      .getRange()
      .getAbsoluteResizedRange(endRow + 1, tableColumns)
      .getValues();

    /*create a new worksheet with a table containing the values specified in the line above. This table's leftmost cell is A1 (you can change this locaton in the getRangeByIndexes method)
     */
    let newSheet = workbook.addWorksheet();
    let newRangeAddress = newSheet.getRangeByIndexes(0, 0, i + 1, tableColumns);
    newRangeAddress.setValues(newTableRangeValues);
    newSheet.addTable(newRangeAddress, true);
  }
}

Upvotes: 5

Related Questions