Gmailからタイトルと日付で検索した結果を、GoogleSpreadSheetに流し込みます

GoogleAppsScript.js

function searchAndSave() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  
  var offset = 0;
  var limit = 500;
  while (true) {
    var threads = GmailApp.search('(subject:日報 AND [report) after:2013/5/31 before:2013/7/1', offset, limit);
    offset += limit;
    if (threads.length == 0) break;
    
    var stack = [];
    for (var i = 0; i < threads.length; i++) {
      stack.unshift(getStackItem(threads[i]));
    }
    sheet.insertRows(1, stack.length);
    var range = sheet.getRange("A1:B" + stack.length);
    range.setValues(stack);

    // これ以降は無いと判断
    if (threads.length < limit) break;
  }
}

function getStackItem(thread) {
  if (thread.getMessageCount() != 1) {
    // 返信がある場合、LastMessageDateではなく1通目のDateを参照する
    Logger.log("has reply: " + thread.getFirstMessageSubject());
    return getStackItemFromMessage(thread);
  }
  return [
    thread.getFirstMessageSubject(),
    Utilities.formatDate(thread.getLastMessageDate(), "JST", "yyyy/MM/dd HH:mm:ss")
  ]
}

function getStackItemFromMessage(thread){
  var message = thread.getMessages()[0];
  return [
    message.getSubject(),
    Utilities.formatDate(message.getDate(), "JST", "yyyy/MM/dd HH:mm:ss")
  ];
}