CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgmail
Артур Петров
Артур Петров

Reputation: 31

Record incoming Gmail messages in Google sheets

Hi people. I am just starting to learn GS. I cannot understand why the data is not written to the Google sheets. Everything is logged correctly. I ran the script through a container, granted access. And I need this script to only receive unread messages and mark them as read.

I would be very happy if you correct my script.

    function Gmail() {
 
    //this is just the stuff that recognizes what spreadsheet you're in
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheets = ss.getSheets();
    var sheet = ss.getSheetByName('sheet2'); //gets the right sheet

    /* searches your GMail for emails matching things "label:unread" + " label:support"
    (support is the name of the folder into which letters are collected) */
    var query = "label:unread" + " label:support"; 

    var threads = GmailApp.search(query);

    var supportStats = [];
    for (var i = 0; i < threads.length; i++) {
        var messages = threads[i].getMessages();

        for (var m = 0; m < messages.length; m++) {
            var from = messages[m].getFrom(); //from field
            var to = messages[m].getTo(); //to field
            var time = messages[m].getDate(); //date field
            var subject = messages[m].getSubject(); //subject field
            var body = messages[m].getPlainBody(); //body field
            var mId = messages[m].getId(); //id field to create the link later
   
            if (query === "label:unread" + " label:support") {
               supportStats.push([from,to,time,subject,body,'https://mail.google.com/mail/u/0/#inbox/'+mId])
            }
            messages[m].markRead(); // marks as read
        }
    }
 if(!threads.length) return; //  if there are no unread ones, do nothing
 sheet.getRange(SpreadsheetApp.getActiveSheet().getLastRow()+1,2,supportStats.
 length,supportStats[0].length).setValues(supportStats); //writes to the spreadsheet
}

Upvotes: 1

Views: 382

Answers (1)

Marios
Marios

Reputation: 27350

Explanation:

  • One of the main issues is that your code gets the html body which contains a large amount of text since it returns the html body of the message.
  • However, if you still want to get the body, I would advice you to get the plain body instead. To achieve that you can use getPlainBody() which gives you the content of the body of this message without HTML formatting.
  • Also you call appendRow() inside a for loop which is a very computationally expensive approach. Instead, I would recommend you to use setValues().

Solution:

function myFunction() {

//this is just the stuff that recognizes what spreadsheet you're in
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheets = ss.getSheets();
   var sheet = ss.getSheetByName("data"); //gets the right sheet

//this chunk gets the date info  
 var today = new Date();
 var dd = today.getDate()-1;
 var mm = today.getMonth()+1; //January is 0 DO NOT FORGET THIS
 var yyyy = today.getFullYear();
 var yesterday = yyyy + '/' + mm + '/' + dd;

//****************************************************  
/*searches your GMail for emails matching two things, written after yesterday and with the label support*/
  var query = "label:unread after:" + yesterday + " label:support";

  var threads = GmailApp.search(query);

  var supportStats = [];
  for (var i = 0; i < threads.length; i++) {
    var messages = threads[i].getMessages();

    for (var m = 0; m < messages.length; m++) {

      messages[m].markRead();
      var from = messages[m].getFrom(); //from field
      var to = messages[m].getTo();//to field
      var time = messages[m].getDate();//date field
      var subject = messages[m].getSubject();//subject field
      var body = messages[m].getPlainBody();//body field
      var mId = messages[m].getId();//id field to create the link later
      var mYear = time.getFullYear();
      var mMonth = time.getMonth()+1;
      var mDay = time.getDate();
      var messageDate = mYear + '/' + mMonth + '/' + mDay;

      if (messageDate === yesterday) {
      
      supportStats.push([from,to,time,subject,body,'https://mail.google.com/mail/u/0/#inbox/'+mId])
      
      }
      
    }

  }
  SpreadsheetApp.getActiveSheet().getRange(SpreadsheetApp.getActiveSheet().getLastRow()+1,1,supportStats.length,supportStats[0].length).setValues(supportStats); //writes to the spreadsheet

}

Upvotes: 1

Related Questions