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

Reputation: 31

Data is not written to Google Spreadsheet

My script copies "unread" incoming messages from mail from the marked folder to the google table, and then marks it as read. But sometimes there are failures: the script is executed, marks the letters "as read", but does not write to the table. That is, in fact, it turns out in the logger there is a record with all the contents of these letters, but they are not written to the table. In theory, you need to check whether the data was written to the table and only then mark the letters "as read". Maybe there is an error in the code that periodically makes itself felt? Guys, help me, I'm just learning.

        function GmailmarkReadEnd() {
  
  //this is just the stuff that recognizes what spreadsheet you're in
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var sheet = ss.getSheetByName('Лист2'); //gets the right sheet
  
  /* searches your GMail for emails matching things "label:unread" + " label:support"
  */
  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]);
        Logger.log(supportStats) // The log about which he spoke.
      }
      
    }
  }
  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
  
  GmailApp.markThreadsRead(threads); // marks as read
  
  // ***Sorting Recorded Data by Date***
  sheet.getRange('D:D').activate();
  sheet.sort(4, false);
}

Upvotes: 0

Views: 101

Answers (2)

Артур Петров
Артур Петров

Reputation: 31

Thanks to everyone who helped. If anything, read the comments.

function GmailmarkReadEnd() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('Лист2'); //gets the right sheet
  const 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
      supportStats.push([from,to,time,subject,body,'https://mail.google.com/mail/u/0/#inbox/'+mId]);
      Logger.log(supportStats) // The log about which he spoke.
    }
  }
  if(!threads.length) return; //  if there are no unread ones, do nothing.
  sheet.getRange(sheet.getLastRow()+1,2,supportStats.length,supportStats[0].length).setValues(supportStats); //writes to the spreadsheet
  GmailApp.markThreadsRead(threads); // marks as read
  sheet.sort(4, false);
}

Upvotes: 0

Cooper
Cooper

Reputation: 64032

function GmailmarkReadEnd() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('Лист2'); //gets the right sheet
  const 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
      supportStats.push([from,to,time,subject,body,'https://mail.google.com/mail/u/0/#inbox/'+mId]);
      Logger.log(supportStats) // The log about which he spoke.
    }
  }
  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
  GmailApp.markThreadsRead(threads); // marks as read
  sheet.sort(4,false);
}

Upvotes: 3

Related Questions