Google Apps Scripts. Extract data from gmail to a spreadsheet.

Question

this is the first script i am trying to write from scratch. It was not good so far, so I will ask for help.

Happening. I receive e-commerce confirmation emails from e-commerce sites with no response to the email address. In the body of the letter, they send an email address from buyers. I want to send automatic mail to the email address of the authority.

How I plan to do this (any suggestions for eliminating the steps will be thanked).

• Use the rule to tag incoming emails with a unique tag.

• Use this tag to identify emails in gmail using a script, go one by one and extract the information I need. Use a regular expression with the contents of the email body to extract the email address that I need to send to automatic emails. The plan is to get: subject, date, email from the body.

• Enter all this information in the spreadsheet.

• Get rid of unique tag information to prevent reruns.

• Then use the mule form addon to send emails from the spreadsheet.

Until now, I understood steps 1 (easily) and was confused with steps 2 and 3 (it’s not an encoder, I can read, rebuild and crack, write from scratch - quite another). Ive reviewed 4 before I think this is the best way to handle this.

Using the script, I extract the information to the spreadsheet, and the addon I use the information from the spreadsheet to send letters.

This is the code written so far. I left part of the regex for a later reason, I can't even write anything in a spreadsheet. as soon as I get this working, I started working in regex and "remove shortcut" aspects of the script.

function myFunction() { function getemails() { var label = GmailApp.getUserLabelByName("Main tag/subtag"); var threads = label.getThreads(); for (var i = 0; i < threads.length; i++) { var messages=threads[i].getMessages(); for (var j = 0; j < messages.length; j++) { var message=messages[j]; var subject=message.getSubject(); tosp(message); } } } function tosp(message){ var body=message.getBody() var date=message.getDate(); var subject=message.getSubject(); var id= "my spreasheet id"; var ss = SpreadsheetApp.openById(id); var sheet = ss.getActiveSheet(); sheet.appendRow(subject,date,body); } } 

Any help would be greatly appreciated.

Thanks Sebastian

Answer 1

Below is the code that I wrote and tested that perfectly performs steps 2, 3, and 4 you described.

 function myFunction() { var ss = SpreadsheetApp.getActiveSheet(); var label = GmailApp.getUserLabelByName("MyLabel"); var threads = label.getThreads(); for (var i=0; i<threads.length; i++) { var messages = threads[i].getMessages(); for (var j=0; j<messages.length; j++) { var msg = messages[j].getBody(); var sub = messages[j].getSubject(); var dat = messages[j].getDate(); ss.appendRow([msg, sub, dat]) } threads[i].removeLabel(label); } } 

One of the errors in your code was that the appendRow function takes an array of elements specified in brackets [ ] .

Depending on where you are attaching this script, your line of code:

 var ss = SpreadsheetApp.openById(id); 

not required if the script is written in the table script editor where you want these emails to be registered. However, if there are multiple sheets in this table, you can replace my row

 var ss = SpreadsheetApp.getActiveSheet(); 

by

 var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName("Sheet1"); 

Another suggestion is that the current code will provide you with HTML messages. Therefore, if you want to receive the message in plain text, as you see, use:

 var msg = messages[i].getPlainBody(); 

Now you can write another function for the regular expression and pass the msg message to this. Hope this helps!