how to append rows from 2nd row in spreadsheet

237 views
Skip to first unread message

Rahul Gupta

unread,
May 24, 2023, 3:38:29 AM5/24/23
to Google Apps Script Community
i have written code to grab the details of  mail messages in a particular label in gmail like mail body,  subject,  date,  and who sent the mail, and to append all these information in google sheets, however when the code runs the details append from first row of spreadsheet, and i want to keep this row blank for heading, kindly suggest how can i append rows from second row of spreadsheet, below is my code sample

function newMailLabel() {
  var sheet = SpreadsheetApp.openById('1-FFJlGZ_hdYISsReKunaiWrRv_KHXbxqIpyxBdOvxvc').getSheetByName('Sheet2');
  var tester = GmailApp.getUserLabelByName('New Mails');
  var emails = tester.getThreads();
  Logger.log(emails);
  for (var x = 0; x < emails.length; x++) {
    var msg = emails[x].getMessages();
    // Logger.log(msg);
    for (var i = 0; i < msg.length; i++) {
      var message = msg[i].getPlainBody();
      var subject = msg[i].getSubject();
      var dateSent = msg[i].getDate();
      var fromSender = msg[i].getFrom();
      // Logger.log(message);
      sheet.appendRow([subject, dateSent, fromSender]);
    }
  }
};

Tanaike

unread,
May 24, 2023, 3:56:19 AM5/24/23
to Google Apps Script Community
"appendRow" appends the value to the last row. When you want to skip the 1st row, In your script, how about the following modification?

function newMailLabel() {
  var sheet = SpreadsheetApp.openById('1-FFJlGZ_hdYISsReKunaiWrRv_KHXbxqIpyxBdOvxvc').getSheetByName('Sheet2');
  var tester = GmailApp.getUserLabelByName('New Mails');
  var emails = tester.getThreads();
  Logger.log(emails);
  var ar = [];
  for (var x = 0; x < emails.length; x++) {
    var msg = emails[x].getMessages();
    // Logger.log(msg);
    for (var i = 0; i < msg.length; i++) {
      var message = msg[i].getPlainBody();
      var subject = msg[i].getSubject();
      var dateSent = msg[i].getDate();
      var fromSender = msg[i].getFrom();
      // Logger.log(message);
      ar.push([subject, dateSent, fromSender]);
    }
  }
  sheet.getRange(2, 1, ar.length, ar[0].length).setValues(ar);
}

Rahul Gupta

unread,
May 24, 2023, 5:40:35 AM5/24/23
to Google Apps Script Community
this modification is only appending subject of first mail in all rows & columns, image below
Screenshot_1.jpg

Tanaike

unread,
May 24, 2023, 8:10:07 AM5/24/23
to Google Apps Script Community
Thank you for replying. Unfortunately, I cannot replicate your situation using my proposed modified script. When I tested my proposed modified script, the values of `subject, dateSent, fromSender` of each mail are put into "Sheet2". I apologize for this situation. In this case, it is required to correctly replicate your situation. So, can you provide the detailed flow for correctly replicating your current situation? From the detailed flow, I would like to confirm it.
Reply all
Reply to author
Forward
0 new messages