1

Zapier's parser is reading innaccurately despite confirming our template, so the pipeline (slightly Odd, I know - not my choice) is: Google Voice > Gmail (filter > automatic label) > Zapier detects label > writes to google sheets in column A. Column B = Column A so that we maintain raw text in Column A.

The incoming data looks like this:

Google voice message message **AAA+######** **BBB+#######** **CCC+######** Message message message **DATE** **TIME** **URL** message message message

I'm having trouble automatically getting the following values into the various columns I need them in for datastudio. Desired Column headers are underlined, and the values that go in the column follow the colon.

AAA: +#######
BBB: +#######
CCC: +#######
Date: DATE
Time: TIME
URL: URL

I'm stuck between writing a script for it to automatically split data and work like that, upon each new row being created (aka automatically applying formulas in columns B through whatever, to any new entry in column A, for each new row) OR Using a RegEx (still new to this so any help is welcome)! for each of these values. OR Seeking a 3rd party parser. OR your suggestion, if I'm missing something!

Does anyone have any advice on how to approach this?

All the best & thanks so much!

Improve this question
1
  • 1
    If you intended to show things in your post with line breaks, underlining or other formatting, none of that is showing in the actual published post. So the nature of your problem is completely obscured. Consider sharing a link to a sample spreadsheet that shows the layout coming in and then shows (manually entered by you) the results you want and where you want them. This will be the most efficient use of time for the volunteer contributors here who may be inclined to help you solve this problem.
    – Erik Tyler
    Commented Dec 6, 2021 at 14:10

1 Answer 1

Reset to default
0

Try this: =arrayformula({substitute(substitute(index(SPLIT(query({arrayformula(split(query({B1:B},"select * where Col1 !=''",0)," "))},"select Col5"), " "), 0, 1),"**AAA",),"**",),substitute(substitute(index(SPLIT(query({arrayformula(split(query({B1:B},"select * where Col1 !=''",0)," "))},"select Col6"), " "), 0, 1),"**BBB",),"**",),substitute(substitute(index(SPLIT(query({arrayformula(split(query({B1:B},"select * where Col1 !=''",0)," "))},"select Col7"), " "), 0, 1),"**CCC",),"**",),substitute(substitute(index(SPLIT(query({arrayformula(split(query({B1:B},"select * where Col1 !=''",0)," "))},"select Col11"), " "), 0, 1),"**",),"**",),substitute(substitute(index(SPLIT(query({arrayformula(split(query({B1:B},"select * where Col1 !=''",0)," "))},"select Col12"), " "), 0, 1),"**CCC",),"**",),substitute(substitute(index(SPLIT(query({arrayformula(split(query({B1:B},"select * where Col1 !=''",0)," "))},"select Col13"), " "), 0, 1),"**",),"**",)})

No doubt this could be improved and/or made more efficient, but it has two things in its favour.

  • it will process every value in Column B
  • where Col1 !='': number of rows processed equals only the populated rows in Column B rather than every row.
Improve this answer
2
  • This didn't work for every single data point, but it worked well for the two I needed most (AAA & BBB)- so thank you so much!! I have a feeling I need to dive into this a bit deeper to make it work but it's an amazing start. And ArrayFormulas are so freaking useful. Thanks again!!
    – correlator
    Commented Dec 19, 2021 at 23:05
  • 1
    If you would like to post (in your question) some actual data, and highlight where the formula is failing, then I’d be happy to look at it more closely. O/wise, if this answer was helpful, perhaps you might consider accepting the answer.
    – Tedinoz
    Commented Dec 19, 2021 at 23:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.