Gmail sends me a notification when someone fills out a form on the website. I would like to be able to drag information from the content of this email to the table converting some words into column names. Below is an example of the content of the email and desired table layout.
You can put these inputs in the table and try parsing them either with regular expressions (more powerful but requires regex knowledge), or with basic “split into lines, filter where the string starts with “p1” etc” formulas.
Some inspiration:
1 Like
This is where I typically say - that’s problem #1. 
In this day and age, any form that sends its data through email is far from a modern design and should be reconsidered because almost every modern approach is near-free.
This human-centric parsing approach is loaded with friction (human tasks, copy, paste, mistakes, etc), it exposes your data to hackers, and increases security risks – so why not take advantage of a better forms process? Forms-to-email-to-human parsing pretty much ended in the early part of the previous decade.
If you have no other choice, consider a Google Apps Script script running inside Gmail that identifies forms messages, reads the content, parses the content into structured fields and submits them into your Coda table.
This would run silently 24/7 and it would no longer require your attention.
The script for this is relatively straightforward and scripting in your Google account is free.
This example, extracts data from a message and stores it into a Google sheet where it could easily be moved into a Coda table using a number of different approaches.
It employs regular expressions (as @Paul_Danyliuk recommends) to make quick work of data value extraction.
function parseEmailMessages(start) {
start = start || 0;
var threads = GmailApp.getInboxThreads(start, 100);
var sheet = SpreadsheetApp.getActiveSheet();
for (var i = 0; i < threads.length; i++) {
// Get the first email message of a threads
var tmp,
message = threads[i].getMessages()[0],
subject = message.getSubject(),
content = message.getPlainBody();
// Get the plain text body of the email message
// You may also use getRawContent() for parsing HTML
// Implement Parsing rules using regular expressions
if (content) {
tmp = content.match(/Name:\s*([A-Za-z0-9\s]+)(\r?\n)/);
var username = (tmp && tmp[1]) ? tmp[1].trim() : 'No username';
tmp = content.match(/Email:\s*([A-Za-z0-9@.]+)/);
var email = (tmp && tmp[1]) ? tmp[1].trim() : 'No email';
tmp = content.match(/Comments:\s*([\s\S]+)/);
var comment = (tmp && tmp[1]) ? tmp[1] : 'No comment';
sheet.appendRow([username, email, subject, comment]);
} // End if
} // End for loop
}
1 Like
@Paul_Danyliuk Thank you for the inspiration. The table you have created to collect data from the Typeform will surely be useful to me.
@Bill_French Thank you for the information. I would love to be able to do what you wrote me about Google App Script but I’m afraid it’s far out of my reach. I am a simple scientist with no previous experience with coding, who fell in love with Coda and who thanks to this great community hasn’t given up yet and despite many failures is still learning Coda syntax.
Just saying, if you can switch to using Typeform, then you don’t have to parse anything yourself — if you integrate Typeform into Coda through Zapier, you can fill in each column from each separate answer. That would in fact be much better than the email solution. Typeform or Google Forms. And it wouldn’t even require a paid Zapier plan.
My mechanism was meant for something else (huge, 80+ question forms that would be a huge pain to map field by field). It’s just an example how one can split one big chunk of text into pieces and process each one individually.
1 Like
I’m an accountant. It’s not out of your reach because scientists are generally smarter than accountants. LOL (Ironically, I’ll bet there’s science that proves this)
If you and your company are G-Mail/G-Suite users, I recommend you try a simple [scientific] experiment to see just how incompatible you may be with the art (and science) of programming.
Take thirty minutes and run through this tutorial to see how you can automatically create a Google document and send it to another user in your company just by running a script.
If you hate the experience, never look back. But, if you enjoyed it or you were simply intrigued, get more data by running through a few more tutorials.
And once you complete that first tutorial and you’ve examined the code at each step, come back to this message the review the code I referenced - you’ll likely be surprised that some of it is actually recognizable.
2 Likes
@Paul_Danyliuk typeform is quite expensive tool in my opinion…
@Bill_French Does using Google Apps Script mean I have to use Google forms to do my surveys?
Not at all. The script I referenced above interacts with the data flowing from your current form. It was suggested only as one pathway to embrace your legacy situation - i.e., a form sending emails.
However, Once you become familiar with the nature of apps scripts, you will come to understand that a single line of code in apps script can send data into Coda using their apps script library. I don’t want to push you too far into the technical weeds, but realize that Coda engineers have thought deeply about these integration requirements and provided some pre-built functions that allow us to move data from apps script into Coda tables.
@Paul_Danyliuk is right about the cost of Typeform - it’s pricey. Google forms is free, but not as powerful nor as elegant as Typeform. Again, this is about requirements and more so about business requirements.
Typeform (especially the paid service) makes data-gathering look and feel very professional. If that matters to your business and your customers, it may be (relatively) cheap, not expensive. This is why business requirements matter far more than a rush to define implementation details because they shape decisions with great business-centric precision.
Almost every forms system work with Zapier and Google Apps Script (Survey Monkey comes to mind, and Survey Sparrow is gaining traction as well). And while they each have the ability to email forms (a legacy option from the previous century), they each support better and more streamlined ways to collect data.
But since you are surveying customers, the most important requirement is getting the answers you need, thus making the choice of survey platform a key implementation detail. If it’s a crappy survey experience, it’ll produce poor results (I suspect).
And to be clear, @Paul_Danyliuk makes a very compelling point - you are in the business of servicing customer demand; not in the business of writing code. As such, the core-vs-context debate should weigh heavily against the idea of becoming a coder [too]. 
1 Like