Is there a faster way to delete table rows so my script doesn't take hours to run?-CodePudding

My script functions fine when there are only a few rows of data to remove. However, the larger the dataset gets, it becomes slower and unusable. Deleting 50 table rows took multiple hours to run. I think the loop to go through each address in the array is slowing it down, as I can see it deleting one row at a time. However, I am not sure that there is a way to delete all rows in the array without going through a loop.

const rowAddressToRemove = rangeView.getRows().map((r) => r.getRange().getAddress());
rowAddressToRemove.splice(0, 1);

const sheet = sourceTable.getWorksheet();

rowAddressToRemove.reverse().forEach((address) => {
sheet.getRange(address).delete(ExcelScript.DeleteShiftDirection.up);
});

The current code is working, but it is just slow, and I'm thinking there is something (or some things) horribly optimized in my code that is slowing this down to the point of unusability.

Here is an example of the rowAddressToRemove variable output on the console: (2) ["Pending!A7:G7", "Pending!A8:G8"] 0: "Pending!A7:G7" 1: "Pending!A8:G8"

CodePudding user response：

I don't understand this:

...getRange(address).delete(ExcelScript.DeleteShiftDirection.up)

You say that you want to remove entire rows, so why not opt for this:

getRange(address).entireRow.delete

(I don't know if the entireRow.delete needs any arguments, so you might need to see my proposal as pseudo-code)

CodePudding user response：

I think your issue is that you're getting row addresses individually instead of getting the address at once. This is for your rowAddressToRemove variable. So instead of having code like:

    const rowAddressToRemove = rangeView.getRows().map((r) => r.getRange().getAddress());

You can have code like this:

    const rowAddressToRemove = rangeView.getRange().getAddress()

So you can try to see if this makes things faster. If it doesn't, it may also have to put the code for your rangeView variable in the post as well.