Google Apps script to export Google Talk chat logs to a Google Docs spreadsheet

gistfile1.js

/*
Quick instructions:

1. Go to Google Docs and open a new spreadsheet.
2. Go to Tools > Script editor...
3. Delete everything, paste this code in the script editor, and save it.
4. Go back to the spreadsheet, Tools > Script manager...
5. Select getChats, and press the "run" button.
6. It'll ask for a bunch of authorizations. Grant them.
7. When it says "now you can run the script", repeat step 5.
8. Your chats should being filling the spreadsheet. It'll probably take a while.

When it's done, you can save it, and export it as CSV, Excel or whatever.

*/

function getChats() {
  var sheet = SpreadsheetApp.getActiveSheet();

  sheet.getRange(1, 1).setValue("Date");
  sheet.getRange(1, 2).setValue("Subject");
  sheet.getRange(1, 3).setValue("Body");
  var row = 2;
  
  var chats = GmailApp.getChatThreads();
  var chat_count = chats.length;
  
  for (var i = 0; i < chat_count; i++) {
   var count = chats[i].getMessageCount();
   var messages = chats[i].getMessages();
    
    for (var j = 0; j < count; j++) {
      var chat_date = messages[j].getDate();
      var subject = messages[j].getSubject();
      var body = messages[j].getBody();
      sheet.getRange(row, 1).setValue(chat_date);
      sheet.getRange(row, 2).setValue(subject);
      sheet.getRange(row, 3).setValue(body);
      row++;
    }
    
  }
  Browser.msgBox("All done!");
}

This will fail if you have a lot of chats - you'll need to paginate the call to getChatThreads().

Actually, even with pagination ( https://gist.github.com/1053937 ) , this seems to fail due to execution time limits.

Hmm. Good to know, thanks. (I only had a few hundred chats, so I didn't run into trouble.)

…

On Jun 29, 2011, at 9:23 AM, ***@***.*** wrote: This will fail if you have a lot of chats - you'll need to paginate the call to getChatThreads(). ## Reply to this email directly or view it on GitHub: https://gist.github.com/1051628

I have tens of thousands. I'm working on something right now to maybe solve the problem, I'll let y'all know.

It looks like this might not be possible within a spreadsheet - it's just too slow.

I thought maybe setValue was slowing things down, so I made a version ( https://gist.github.com/1054300 ) that uses setValues instead, but it still ends up timing out.

I keep running up against "Service invoked too many times: gmail rateMax (line xxx)"