I need help applying a formula to every row in a Google sheet

2
votes

I have a google sheet that gets updated from a Google Form. Unfortunately, Google forms insert a new row for every response so I can't get the formula applied to every row automatically. I found a workaround using arrayformulas here: https://www.labnol.org/google-form-formulas-050520#auto-number-form-responses-with-a-unique-id

and tried this:

=ArrayFormula(
  IFS(
    ROW(L:L)=1, "Invoice amounts",
    LEN(L:L)=0, IFERROR(1/0),
    LEN(L:L)>0, ARRAYFORMULA(JOIN(",",REGEXEXTRACT(SPLIT(ROW(L:L),","), "[0-9]{4}")))
  )
)

But that produces this error screenshot

I would be grateful if anyone can help or suggest an alternative. Here is the sheet link: https://docs.google.com/spreadsheets/d/166jmHFHQoa6Q88AEJy39w8yfXShg7ml6Q_YRckP3oEk/edit?usp=sharing

1
regexgoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-forms

1 Answers

1
votes

try:

=ARRAYFORMULA({"Invoice amounts"; IF(L2:L="",,
 REGEXREPLACE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(IF(LEN(
 SPLIT(REGEXREPLACE(L2:L, "[A-Za-z\(\)-\.]", ), " "))>3, 
 SPLIT(REGEXREPLACE(L2:L, "[A-Za-z\(\)-\.]", ), " ")&",", )),,9^9))), " |,$", ))})

enter image description here