Gmail body to google sheets as seperate items

Question

I’m trying to import data from an email message received in gmail.

I’ve used the zapier formatter → split text option to get different items but I’m stuck with the following data string:

Naam: samplename ,E-mailadres: sampleemail ,Telefoon nr: samplephone ,Datum: 22 juli 2020 ,Start: 13:00 ,Eind: 17:00 ,Duur: 4 uur (240 min) ,Boot type: boatname ,Betalings methode: ideal ,Bedrag: € 140,00 , Borg: € 150,00 ,Totaal: € 290,00 ,Boeking ID: samplebooking ,Activatie code: sampleactivationcode

I’d like to import the data after each “:”, so I don’t want the name of the data in the sheet since that would mess up the google sheets document.

For most data a simple “:” as seperator would do the trick but for example the time info already has a colon in it’s data.

How would you format the zap for it not to use too many steps with formatting.

icon

Best answer by ikbelkirasan 23 July 2020, 01:17

View original

ikbelkirasan · Accepted Answer

@nicksimard- Sure, thanks for the mention!  @reblrebl- You might want to try the following JavaScript code snippet. You should map thestring you want to parse to an input field called input.  const { input } = inputData;const regex = /([^:,]+):\s+((.(?<!\s,))+)/g;let m;const matches = {};while ((m = regex.exec(input)) !== null) {  if (m.index === regex.lastIndex) {    regex.lastIndex++;  }  const [, key, value] = m;  matches[key.trim()] = value    .trim()    .replace(/&nbsp;/g, " ")    .replace(/&euro;/g, "€");}output = [matches];

nicksimard · Answer

Hi @reblrebl!

One idea I have is that (assuming that your individual pieces of data don’t have commas in them) you could split at the comma, and as your output choose the line item option. Then you run all the line items through another split text that will use the : as the separator. But at this point you’ve already used 3 Formatter steps and you may need to use another one in order to get things the way you need them for Google Sheets.

You could see if sending those emails to an email parser would be a better option, or if you know any developers, this could likely be done with a Code step. We have a few developers who hang out in the Community. For example, @ikbelkirasan, have you got any Code step ideas here that wouldn’t be super complex?

Sign up

Use your Zapier credentials

Log in to the Community

Use your Zapier credentials

Scanning file for viruses.

This file cannot be downloaded