Optimize App Script function and apply it to a column

Question

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('-')
}

It looks like you included code from somewhere else. If you're using someone else's work without giving them credit, that constitutes plagiarism, which is not welcome on Stack Exchange. To fix it, edit to make sure you do ALL the following: 1.Include a link to the source, 2. Mention the author's name, and 3. Quote the copied content. For more details, see referencing help and this FAQ. — TheMaster
– TheMaster, Commented Nov 4, 2022 at 15:38
@TheMaster, if I were to take a script from stackoverflow and use it in my program as is do I have to put some sort of copyright notice in that script? And without a copyright notice isn't it public domain? — TheWizEd
– TheWizEd, Commented Nov 4, 2022 at 15:46
@TheWizEd Yes. You need to give credit. See human readable description of the licence here(with pictures). Also see attribution policy — TheMaster
– TheMaster, Commented Nov 4, 2022 at 15:53
If you will not be sharing the script with anyone, nobody will care if you are providing appropriate attribution. If you think that eventually you might share your scripts, i.e. by posting a question here, it will be a good practice to provide proper attribution at the time that you add the code to your script (the Google Apps Script project). — Wicket
– Wicket, Commented Nov 4, 2022 at 16:06
The answer below is perfect. Basically, you'd use =dash_case(E2:E100) in the sheet and change the custom function to adapt to arrays. The link below in the answer gives a great example. Some of my samples: stackoverflow.com/questions/69947422/… stackoverflow.com/questions/57945431/… using Array.map — TheMaster
– TheMaster, Commented Nov 4, 2022 at 17:37

TheMaster · Accepted Answer · 2022-11-04 19:18:05Z

2

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('-')
}

edited Nov 4, 2022 at 19:18

answered Nov 4, 2022 at 17:46

TheMaster

51.6k7 gold badges76 silver badges102 bronze badges

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

8 Comments

helder.silva.mavrolert Over a year ago

I've tried your recommendation but it gives an error when I try and specify a range in the column. Example:(E2:E120) But when I only select a single cell it works. This is the error message: "TypeError: str.toLowerCase is not a function "

TheMaster Over a year ago

@helder.silva.mavrolert you're sending numbers or dates. Those won't have toLowerCase() Send only strings. Alternatively, try String(arg).toLowerCase.....

helder.silva.mavrolert Over a year ago

You're right about the problem being numbers, what I'm trying to convert is a list of product names that have some numbers in the name itself. Is there a way for the code to see if its a number and just skip it or something like that?

helder.silva.mavrolert Over a year ago

It only gives that error whe I try to identify the full range of the column but if I specify a cell with text and numbers it returns the Handle correctly. PROTECTOR ANTI-SHOCK IK06 LENOVO -> protector-anti-shock-ik06-lenovo

TheMaster Over a year ago

@helder.silva.mavrolert Read my last comment again. I already told you the code for how to fix it for numbers.

|

Wicket · Accepted Answer · 2022-11-04 16:11:47Z

1

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.

answered Nov 4, 2022 at 16:11

Wicket

39.6k9 gold badges81 silver badges201 bronze badges

2 Comments

TheMaster Over a year ago

IMHO, While code only answers are bad, I think no code answers(where code is appropriate) are the just the same - in the opposite end of the same spectrum. As the top answer in this meta post says, balance is the key. Even if you don't want to post full code, some snippets may help. Of course you're free to post no code answers, just as others are free to post code only answers.

helder.silva.mavrolert Over a year ago

@Rubén Thanks for the help, the dcumentation also gave me a better understanding of how to optimize my future App Script.

Collectives™ on Stack Overflow

Optimize App Script function and apply it to a column

2 Answers 2

8 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related