ExcelScript: Deleting rows stored in a 1D array

Question

I'm trying to get my Excelscript to delete unwanted rows. I think my logic should delete the rows but I'm finding that about 50% of unwanted rows disappear but some remain. I think this is related to row numbers changing as rows are deleted but I thought this wouldn't be an issue in the logic below.

Based on a comparison function earlier in the script, I can get my table to look something like this:

Header1	Header2	Header3	Header4	Update
First	row	value	data
Second	row	value	data	YES
Third	row	value	data
Fourth	row	value	data	YES
Fifth	row	value	data

The first part of the script works OK. Header1 (Column A) will have a value on every row. So I use that for the row count. I then get the values for the Update column but use the total row count from A because there will be some empty cells in the Update column.

I loop through the values to see which rows include YES or Update in the column to exclude the header itself. All the row numbers of the blank cells go into a 1D array.

I then thought if I start at the last entry of the array and delete the row before looping back around, all the row numbers before it will still be valid. I'll just skip over the rows that had an updated column. i.e, the array for the above table is 1, 3, 5. Delete 5, 4 isn't in the array, so 3 is the last entry. Delete 3. 2 is not in the array, so the only entry is row 1.

The code would make the table end up like this:

Header1	Header2	Header3	Header4	Update
First	row	value	data
Second	row	value	data	YES
Fourth	row	value	data	YES

With more data, such as 35 rows, and different YES placements, it may stop at 16 elements in the array, needing to delete row 18. However, it never truly deletes all the rows that it should.

Here's the code:

// Delete the rows without "YES" in the "Update" column
  let dataRows = sheet.getRangeByIndexes(0, 0, sheet.getUsedRange().getRowCount(), 1); // Get all rows in column A
  let dataValues = sheet.getRangeByIndexes(0, updateColumn, dataRows.getRowCount(), 1).getValues(); // Get "Update" column values
  let nAry: number[] = [];
  
  for (let rowIndex = 0; rowIndex < dataValues.length; rowIndex++) {
    if (dataValues[rowIndex][0] !== "YES" && dataValues[rowIndex][0] !== "Update") {
      
      nAry.push(sheet.getCell(rowIndex, updateColumn).getRowIndex()); //create 1D array of all rows requiring deletion

    }
  }
    
  for(let i = 0; i < nAry.length; i++) { //loop through 1D array
    let lastValue = nAry.pop(); //Put the last element from the array into a variable and remove from the array
    sheet.getCell(lastValue, updateColumn).getEntireRow().delete(ExcelScript.DeleteShiftDirection.up); //use the last row number from the array, get the entire row and delete the row
  }

When running through a list for deletion reasons, always to from end back to beginning, never from beginning to end. — Dominique
– Dominique, Commented Aug 1, 2023 at 9:34

taller · Accepted Answer · 2023-08-01 16:57:33Z

1

The logic in your code is correct. However, the way you populate the array using a for loop has issues.

function main(workbook: ExcelScript.Workbook) {
    let sheet = workbook.getActiveWorksheet();
    let updateColumn = 4;
    let rowCount = sheet.getUsedRange().getRowCount();
    let dataValues = sheet.getCell(0, updateColumn).getAbsoluteResizedRange(rowCount, 1).getValues()
    let nAry: number[] = [];
    for (let rowIndex = 0; rowIndex < dataValues.length; rowIndex++) {
        if (dataValues[rowIndex][0] !== "YES" && dataValues[rowIndex][0] !== "Update") {
            nAry.push(rowIndex);
        }
    }
    console.log(nAry);
    let cntRows = nAry.length
    for (let i = 0; i < cntRows; i++) { 
        let lastValue = nAry.pop(); 
        sheet.getCell(lastValue, updateColumn).getEntireRow().delete(ExcelScript.DeleteShiftDirection.up); 
        }
}

The code snippet demonstrates how array length changes when populated. It pushes 4 values into the array. However, the pop method only accesses the last 2 array elements. This illustrates that pop reduces array length by removing elements from the end.

function main(workbook: ExcelScript.Workbook) {
    let nArray: number[] = [];
    for(let i= 1; i<5; i++){
        nArray.push(i);
    }
    console.log(nArray.length);
    for (let i = 0; i < nArray.length; i++) {
        console.log(`BEFORE nArray.length = ${nArray.length}`);
        console.log(`Pop item: ${nArray.pop()}`);
        console.log(`AFTER nArray.length = ${nArray.length}`);
        console.log('-------------');
    }
}

edited Aug 1, 2023 at 16:57

answered Aug 1, 2023 at 16:34

taller

19.7k2 gold badges9 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dominic Over a year ago

Thank you. This solution worked successfully for me. I understand my error now. cntRows is fixing the array length before we go into the For loop because "i" is counting up, while the array size is counting down. So in your code snippet, you reach an equilibrium where "i" is now 2 and the array size is 2. 2 is not greater than 2, so the For loop quits. Fixing the array length ensures you can get all the way through the array.

Collectives™ on Stack Overflow

ExcelScript: Deleting rows stored in a 1D array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related