Optimize App Script function and apply it to a column-CodePudding

I'm trying to create a script that reads the text from a cell and converts it into a URL Handle:

Example: This is a test -> this-is-a-test

I´ve created a code that can convert the text just like the example, but im trying to apply this to a column with 20.000 rows and the sheet gets very slow or crashes.

Is there a way to optimize the code so that it wont crash and take less time to convert?

This is the code that I've been trying to implement.

This function applies the DASH_CASE to the whole column:

function ApplySeperatedateToColumnEsprinet() 
{
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Esprinet_Original");
    ss.getRange("AH2").setFormula("=DASH_CASE(E2)")


    var lr = ss.getLastRow();
    var fillDownRange = ss.getRange(2,34,lr-1);
    ss.getRange("AH2").copyTo(fillDownRange);
}

Code that converts the text to a Handle:

function DASH_CASE(str) {
  return str
      .toLowerCase()
    .split(' ').filter(e => e.trim().length).join('-')
}

CodePudding user response：

As written in the previous answer by @ Rubén, use

=dash_case(E2:E100)

Then, change your custom formula to support arrays with recursion:

function DASH_CASE(arg) {
  return Array.isArray(arg) 
    ? arg.map(el => DASH_CASE(el))
    : String(arg).toLowerCase()
    .split(' ').filter(e => e.trim().length).join('-')
}

CodePudding user response：

One way to optimize your script is by changing the approach, instead of using a Google Apps Script function for adding an scalar formula (a formula that returns a single value) with a custom function to multiple contiguous cells, make your custom function able to return an two dimisons Array, then use a single formula. https://developers.google.com/apps-script/guides/sheets/functions#optimization has an example of this.