Google Apps Script – emails to spreadsheet by a given pattern – part 041.

By | 26/06/2022
0 Comment

In this tutorial, I will show you how you can add emails from your Gmail account into a spreadsheet by a given pattern.
First, you need to create a spreadsheet and use the Google Apps Script editor from the main menu Extensions – Apps Script.
The script will need to run with the onOpen function and this will create a new Get Emails on the spreadsheet menu entry.
Using this new entry will add all of the emails sent by catafest@yahoo.com into the spreadsheet.
You can change this line of source code with any email address:

JavaScript
1
  const pattern = 'from:catafest@yahoo.com';

This screenshot show you the output of the running script:

This is the source code I used:

JavaScript
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
function onOpen()
{
  //Creates a custom menu on the spreadsheet.
  SpreadsheetApp.getUi()
      .createMenu('Get Emails')
      .addItem('Get Emails Using Pattern...', 'getEmailsUsingPattern')
      .addToUi();
}
 
function getEmailsUsingPattern()
{
  // retrieves email information using a search pattern.
 
  
  // set the search pattern.
  // use a specific email or name of the sender:
  const pattern = 'from:catafest@yahoo.com';
  // this calls the main function using the preferred pattern and the sheet name.
  getEmailInfo(pattern, 'Sheet3');
  
}
function getEmailInfo(searchPattern, sheetName)
{
    
  // check if the search pattern parameter is empty.
  if(searchPattern === '')
  {
    Browser.msgBox('Please provide a search pattern!');
    return;
  }
  
  // check if the sheet name parameter is empty.
  if(sheetName === '')
  {
    Browser.msgBox('Please provide a sheet name!');
    return;
  }
  
  // get the sheet that will contain the data.
  var date = Utilities.formatDate(new Date(), "GMT+1", "dd/MM/yyyy")
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(date);  
 
  if (sheet != null) {
        //activeSpreadsheet.deleteSheet(yourNewSheet);
        var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(date);
  } else   (sheet == null)
   // check that the sheet object is not null (i.e. the sheet name is correct).
  {
    Browser.msgBox('Invalid sheet name for today. I will create a new one !');
    var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
    sheet = activeSpreadsheet.insertSheet();
    sheet.setName(date);
    //return;
  }
  
  // write the headers on sheet.
  sheet.getRange('A1:D1').setValues([['Date', 'Name', 'Email', 'Subject']]);
  
  // make the headers bold.
  sheet.getRange('A1:D1').setFontWeight('bold');
  
  // set the first row that will contain the data (after headings).
  var startRow = 2;
  
  // get the last row containing data (in column A).
  var lastRow = sheet.getRange("A1:A").getValues().filter(String).length;
  
  // clear any existing data bellow the headers.
  if(lastRow > startRow)
    sheet.getRange(startRow, 1, lastRow, 4).clearContent();
    
  // set all the threads for the specified search pattern.
  var threads =  GmailApp.search (searchPattern);
  
  // check if there are threads for the particular search pattern.
  if(threads.length == 0)
  {
    Browser.msgBox('There are no threads for the specified pattern!');
    return;
  }
  
  // create an empty array that will hold the email data.
  var emailInfo = [];
  
  // loop through all the threads.
  for (var i = 0; i < threads.length; i++)
  {
    // get all the email messages from the thread.
    var messages = threads[i].getMessages();
    
    // loop through all the email messages.
    for (var k = 0; k < messages.length; k++)
    {      
      // Try to get the name from the email (if possible).
      var matchesPattern = messages[k].getFrom().match(/s*"?([^"]*)"?s+<(.+)>/);
      
      // Temporary variables.
      var name;
      var email;
      
      if(matchesPattern)
      {
        // if success, get the name and the email address.
        name = matchesPattern[1];
        email = matchesPattern[2];
      }
      else
      {
        // if not, get the name/email as one.
        name = 'N/A';
        email = messages[k].getFrom();
      }
      // add the necessary information into the array (date, name, email, title).
      emailInfo.push([messages[k].getDate(), name, email, messages[k].getSubject()]);
    }
  }
  
  // add the array data into the sheet.
  if(emailInfo.length > 0)
    sheet.getRange(startRow, 1, emailInfo.length, 4).setValues(emailInfo);
  
  // set the width of the columns.
  sheet.autoResizeColumns(1, 4);
  
  // show a message box to the user about the process.
  Browser.msgBox('Information from ' + (emailInfo.length == 1 ? '1 email' : emailInfo.length + ' emails') + ' was successfully retrieved!');
  
}

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.