Google Apps Scripts - Extract data from gmail into a spreadsheet

Question

this is the first script i try to write from scratch. It's been no good up to now so i'm going to ask for some help.

Case: I recieve e-commerce confirmation emails from e-commerce sites no reply email address. In the email's body they send email address from buyers. I want to send an automated mail to the body's email address.

How i plan to do this (any suggetions to eliminate steps will be thanked).

1. Use a rule to tag incoming emails with a unique tag.

2. Use that tag to identify emails in gmail with a script, go one by one and extract the info i need. Use regex with the emails body content to extract the email address i need to send the automated emails. Plan is to get: subject, date, email from body.

3. Write all that info to a spreadsheet.

4. Get rid of unique tag info to prevent duplicate runs.

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

So far, i've dealt with steps 1 (easy), and been stuggling with steps 2 and 3 (im not a coder, i can read, undestrand and hack. writing from scratch is a completely different thing). Ive dealt with 4 before i think this is the best way to deal with it.

With the script i extract info to the spreadsheet, with the addon i use the info from the spreadsheet to send emails.

This is the code ive written so far. I've left the regex part for later cause i cant even write anything into the spreadsheet yet. once i get that working, ill start working in the regex and "remove the label" 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 appreciated.

Thanks Sebastian

Answer 1

The @pointNclick answers will not paginate the search, and will get less than 1000 emails.

I made a ready-to-use script, explaining how to use it (from the start) as well, for those who need more assistance.

It's on gmail-to-google-sheets-script repository. Just read the content and follow the instructions.

How to use

• Create a new Google Sheet
• Access menu Tools > Script Editor
• Copy the content from gmailt-to-sheets.gs to editor, replacing the sample code there
• Replace the value on SEARCH_QUERY to your real query (Do your search on gmail first, copy and paste the search terms there)
• Select saveEmails on menu (near "run" and "debug" buttons)
• Click on "Run" button
• It will ask for authorization at first run, proceed accepting it (it's your Gmail account authorizing your Google Script account)
• After run, the results will be applied to you sheet

Changing fields

If you want to save different message attributes, take a look at gmail-message class and change your script file the code below comments with a ✏️ (pencil).