Showcase of Email with Table using Text parser to Get content of HTML Table and a text aggregator with group by to make the data more usable throughout the scenario

Showcase
, ,
1

I feel this took me too much time to figure out, so I am posting a quick showcase to maybe save future makers some time.

Background: Our company gets email notifications from our bank whenever there are collections/payments received. The email always includes an HTML table, but it can be in a different order or contain extra fields.

Solution: Using a mailhook, we can setup a make scenario that instant triggers every time an email is sent to our inbox we can ingest it in make and send the relevant data to where it needs to get to.

Showcase: See the screenshots below, but what I wanted to share with the community is how I used the ‘Text parser’ to ‘get content from an HTML table’ and then iterate and aggregate that data to make it usable in our scenario.

The interesting finding was the Get Content from HTML Table worked really well, but the data was Array to Collection to Array. So I was struggling to get the Key/Value pairings with the get and map functions (maybe someone can share on this thread how to do that more easily).

image
image1357×835 78.1 KB

So the solution was using the ‘text aggregator’ module, and specifically the group by with the text to aggregate, I could effectively create a Key:Value pairing that I was used to having.

image
image1080×683 85 KB

Ultimately I end up with very easy to work with bundles, where each bundle has a key and a value. From here I ended up putting in a data store, then I can use ‘get a record’ calls to the data store to pull out the exact data I want to pass to a Google Sheet (and Slack).

image
image874×658 58.7 KB

I am always eager to learn and continue to grow my make skills, so if anyone else in the community has a more efficient or nice way to solve for this, I would be happy to hear about it. In the meantime, I hope someone else finds this helpful, as I couldn’t find much on the community regarding the ‘Get content from HTML table’ and how to actually transform that to a usable array or bundle of data.

4 Likes
2

Thanks, that’s good to know! I faced a similar situation, I got turned off by the complex ouput, gave up and turned to Zapier’s text parser. The tool is quite powerful and easy to use with plain text templates, which can be refined via iterations.

I will try to give your approach a try on the next use case. :+1:

1 Like
3

Fantastic stuff @Joseph_Accountant :clap:

Thank you so much for sharing your solution with the community! This is awesome and I’m sure it will be super helpful to many folks in the future.

Keep up the great work :muscle:

4

Hey Joseph,

This is really helpful and. In my case, I had to use Array Aggregator to create a single bundle since I’m getting multiple bundles at once. Hope this will helpful to someone :slight_smile:

Screenshot 2023-09-29 at 11.13.40

Screenshot 2023-09-29 at 11.15.59
Screenshot 2023-09-29 at 11.15.59600×560 40.5 KB

2 Likes
5

A quick update, as my company is Enterprise user of Make.com, we do have access to create custom functions.

So the actual way I handle this type of situation now is with a couple of custom functions (written in Javascript in Make). My favorite is this ‘findBetweenWords’ function, here is the code below. I pass it the email text, write the start word and end word, and the function returns everything between. I understand not everyone has access to enterprise version of Make, but the custom functions can be extremely powerful. (Also note I am a real amateur at javascript so if you see any errors in this code feel free to let me know, but so far it has worked as intended).

function findBetweenWords(emailText, startWords, endWords) {
  // Find the start and end positions
  const startPos = emailText.indexOf(startWords);
  const endPos = emailText.indexOf(endWords, startPos + startWords.length);

  // Check if start and end positions are valid
  if (startPos === -1 || endPos === -1 || endPos <= startPos) {
    return null;
  }

  // Extract the text between start and end positions
  const textBetween = emailText.slice(startPos + startWords.length, endPos).trim();

  // Check if the extracted text contains only blank spaces
  if (!textBetween.replace(/\s/g, '').length) {
    return null;
  }

  return textBetween;
}
6

Thank you @Joseph_Accountant your original post was extremely helpful. However, as it only works for a single key/value pair, I’m wondering if anyone has a solution for a table with three or more columns? Unless I’m missing something, there is no way to get at objects in a second-level nested array that are in positions other than 1 and 2.

With thanks,

Barnaby

7

Hi @Barnaby_Marshall - thank you for the reply, and you are correct that my original solution is not helpful when dealing with three or more columns.

Maybe you can look at using the “Match Pattern (Advanced)” within the Text Parser module? Some of the free AI models are pretty good at writing the REGEX code to extract data, but be warned that REGEX has it’s shortcomings and will require a lot of unit testing if your relying on it to process a lot of data.

2 Likes