1

Im putting together a pretty lengthy spreadsheet in google spreadsheets. This spreadsheet contains information about products ie name, brand, part number, ect... I was helped previously and given some nice solutions but I am still running into limitations.

What I am trying to do is generate, automatically, a description field based on information in other cells.

The formula I am using in the descriptions column is ="Brand Name"&" "&A3&" "&B3&" "&(joinVals(E3:G3," x "))&" "&K3

joinVals(E3:G3," x ") is joining together separate columns containing Length(E) Width(F) Height(G) and adding 'x' between the values. That results in E x F x G

This Script does work for the above formula

function joinVals( rangeValues, separator ) {
function notBlank(element) {return element != '';}

return rangeValues[0].filter(notBlank).join(separator);
}

However I keep getting this error

Script invoked too many times per second for this Google user account.

I am wondering If I can do this as an array to avoid the error as this doc contains 1000+ ROWS.

When all is said and done my result I would like to achieve should be something like this "Brand Name" Part Number(A) Product Name(B) Dimensions(E x F x G) Size(K)

Should I be running an ARRAY Script?

Thank you all so much, this forum has been such a help!

Rubén
  • 34,714
  • 9
  • 70
  • 166
user2970972
  • 83
  • 1
  • 12

1 Answers1

1

It is possible to use an Array solution, but you'd have to change a lot how you use formulas in your spreadsheet. The easiest solution is to use built-in spreadsheet formulas directly. There's no need for a custom Apps Script formula for this.

="Brand Name"&" "&A3&" "&B3&" "&(join(" x ";filter(E3:G3;E3:G3<>"")))&" "&K3

As shown by AdamL in the comments, here's an ArrayFormula solution that uses only built-in formulas.

=ArrayFormula(IF(LEN(A3:A),REGEXREPLACE("Brand Name "&A3:A&" "&B3:B&" "&REPT(E3:E&" x ",E3:E<>"")&REPT(F3:F&" x ",F3:F<>"")&REPT(G3:G&" x ",G3:G<>"")&CHAR(9)&" "&K3:K,"( x \t)|\t",""),))

As I said, such ArrayFormula style of solution can be used when writing custom Apps Script as well. I just don't think that's worth it when there's (arguably) simpler built-in solutions (but surely faster and way larger quotas).

Community
  • 1
  • 1
Henrique G. Abreu
  • 17,406
  • 3
  • 56
  • 65
  • 1
    Array formula solution, starting in row 3, will populate down the column only if there is something in the A column of corresponding row: `=ArrayFormula(IF(LEN(A3:A),REGEXREPLACE("Brand Name "&A3:A&" "&B3:B&" "&REPT(E3:E&" x ",E3:E<>"")&REPT(F3:F&" x ",F3:F<>"")&REPT(G3:G&" x ",G3:G<>"")&CHAR(9)&" "&K3:K,"( x \t)|\t",""),))` – AdamL Jan 08 '14 at 22:49
  • Thanks Adam. I always laugh a little when I first see your formulas. Then there's a several minutes staring until I understand it. Very nice solution! I think my newbie version is more didactic though :) – Henrique G. Abreu Jan 08 '14 at 23:33
  • AdamL That works Perfectly. I also Figured this works as well `="Brand Name"&" "&A3&" "&B3&" "&IF(E3="","",E3&"''"&IF(AND(F3="",G3=""),""," x "))&IF(F3="","",F3&"''"&IF(G3="",""," x "))&IF(G3="","",G3&"''")` Could you explain what `&CHAR(9)&" "&K3:K,"( x \t)|\t",""),))` is doing? – user2970972 Jan 08 '14 at 23:35
  • Henrique, Yours does work but is dependent on if there are dimensions in the row. Some serious quick and awesome replies guys! feeling blessed. And I'm such a newb im like wth (my brain froze) is that formula Adam wrote! hahaa – user2970972 Jan 08 '14 at 23:40
  • Oh Henrique, absolutely, that's why I had no intention of posting an answer rather than a comment, you answered the question. – AdamL Jan 09 '14 at 00:44
  • 1
    user2970972, the formula appends a separator after each and every value that exists in the E:G columns, and then appends a tab character (`CHAR(9)`) after that. Then the REGEXREPLACE searches for a separator immediately followed by the tab character (or failing that, when there are no values in E:G of that row, just a tab character by itself) and strips it away. I hope that makes sense. – AdamL Jan 09 '14 at 00:48
  • You Rock! Thank you so much, Im very appreciative, you answer really made this bullet proof even more so then I was thinking! – user2970972 Jan 09 '14 at 01:54