Blob Text - Does AppSheet have a way to extract values based on field identifiers?

[WillowMobileSys]

I am creating an automated process that grabs content from emails as they arrive (using Integromat). This allows me to capture the email body as a blob of text into a Google sheet.

Within this blob of text are a set of field identifiers with their corresponding values, like so:

...Field1: Value 1...Field 2: Value 2...Field 30: Value 30

I am looking for a way to parse the blob, find “Field1” and then return “Value 1”. Then repeat for the other fields.

I am kind of familiar with the OCR feature and understand the EXTRACT…() functions but it doesn’t seem like these will help with this use case.

Is there anything within AppSheet that can parse the text blob in the manner described above?

[GreenFlux]

How consistent and structured is the email body? Same number of fields in the same order every time? Any blank values?

You might be able to SPLIT() the email body and INDEX() individual values if you can count on the same structure. But I can’t think of a clean/easy way to make it dynamic.

[WillowMobileSys]

I believe it would be fairly standard but things happen so I am not relying on that since its an email from an un-controlled source.

I think I may be able to resolve using Integromat. There is a text parser module that uses regex’s.

Although, I would prefer to try to stay within the AppSheet ecosystem whenever possible.

[GreenFlux]

WillowMobileSystems:

I think I may be able to resolve using Integromat. There is a text parser module that uses regex’s.

Although, I would prefer to try to stay within the AppSheet ecosystem whenever possible.

I’m with you 100%. I think regex is the answer, and if you’re already using Integromat to gather the text then you might as well parse it there, too. Then, the output of the Text Parse module will provide each individual value, ready to map back to the GSheet in the final step of the scenario.

Hopefully AppSheet will add regex at some point.

Regular Expressions, Please Feature Requests

Regular expressions are my favourite. They’re amazing for processing data of all kinds, especially in the context of data input by many people because it recognises patterns. They’re also so universal, which makes them great for AppSheet, which integrates data from outside sources. My Google Sheets are full of array formulas that use them. Extract and Match are my favourites, but Replace makes the occasional appearance as well. For example, I’m trying to extract the time digits from a time stam…

REGEX() expressions and an optional occurrence parameter for SUBSTITUTE() Feature Requests

@praveen @tony Is it possible to have powerful expressions like: REGEXMATCH() (returns a TRUE/FALSE) REGEXEXTRACT() (returns TEXT) REGEXREPLACE() (returns TEXT) I’m aware that the EXTRACT() expression can perform alike but at the end this function returns a list and it’s a bit limited in nature. Additionally we need an optional occurrence parameter for the SUBSTITUTE expression which can save us from creating nested SUBSTITUTE expressions where expressions can become cleaner. SUBSTITUTE( t…

[LeventK]

@WillowMobileSystems
John, is the Value for each pair numbers or texts or mixed? What’s the type of those values?

[WillowMobileSys]

A very wide variety of values - Names, addresses, ID’s, phone numbers.

Actually, I played around with Integromat a LOT today and was able to find a relatively simple solution using the parser discussed above. I have been able to catch an incoming email, extract its content, parse into the fields I need and then populate the end results into my Google sheets.

The parser is using regex expressions - more specifically groups - to extract the info. I have used regex throughout my entire development career and never have put any time into understanding how they work. But I was able to create an acceptable expression for this need. I do fully expect there will need to be tweaks to it as differing samples come through.