0

I am trying to get the chat log from google chat using google scripts, so far i manage to get all chat messages but its pulling all the data with the following script, what i want is to pull the messages from specific group/space and only from today, even following the documentation, i cant seem to find a way to do this, any ideas?

    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!");
}

2 Answers2

0

You need to use Google Chat API to retrieve messages from specific group/space. The specific method you need to use is Method: spaces.messages.get. This returns the following in the response body:

{
  "name": string,
  "sender": {
    object (User)
  },
  "createTime": string,
  "lastUpdateTime": string,
  "text": string,
  "cards": [
    {
      object (Card)
    }
  ],
  "previewText": string,
  "annotations": [
    {
      object (Annotation)
    }
  ],
  "thread": {
    object (Thread)
  },
  "space": {
    object (Space)
  },
  "fallbackText": string,
  "actionResponse": {
    object (ActionResponse)
  },
  "argumentText": string,
  "slashCommand": {
    object (SlashCommand)
  },
  "attachment": [
    {
      object (Attachment)
    }
  ],
  "matchedUrl": {
    object (MatchedUrl)
  }
}

You can use the createTime property to filter the messages sent on a specific day.

Further Reading:

Nikko J.
  • 5,319
  • 1
  • 5
  • 14
0

I'm not sure if you can specify chat threads, especially as there doesn't seem to be a way to apply labels to them. For pulling messages of today, try using GmailApp.search('newer_than:1d AND is:chat')

I wouldn't recommend using the row counter. Instead, you can either use sheet.appendRow() (which tends to be somewhat slow and will use up those quotas) or you can place the results in a list and use .setValues() (faster)

One idea for getting specific threads: you can try to say something unique in the chat that you want to get, only get that thread, and append those values to your sheet.

Here's the example using the .appendRow() method:

function getChats(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var chats = GmailApp.search('newer_than:1d AND is:chat');
  var msgs = GmailApp.getMessagesForThreads(chats);
  for (i=0; i<msgs.length; i++) {
    for (j=0; j<msgs[i].length; j++) {
      if (msgs[i][j].getBody().includes('RAnDOm thIng NObODy eLSe wILl saY')) {
        for (k=0; k<msgs[i].length; k++) {
          var date = msgs[i][k].getDate();
          var subj = msgs[i][k].getSubject();
          var body = msgs[i][k].getPlainBody();
          sheet.appendRow([date, subj, body]);
        }
      }
    }
  }
}

and here's the one using the list set method (faster but less convenient):

function getChats(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var chats = GmailApp.search('newer_than:1d AND is:chat');
  var msgs = GmailApp.getMessagesForThreads(chats);
  var results = [];
  for (i=0; i<msgs.length; i++) {
    for (j=0; j<msgs[i].length; j++) {
      if (msgs[i][j].getBody().includes('RAnDOm thIng NObODy eLSe wILl saY')) {
        for (k=0; k<msgs[i].length; k++) {
          var date = msgs[i][k].getDate();
          var subj = msgs[i][k].getSubject();
          var body = msgs[i][k].getPlainBody();
          results.push([date, subj, body]);
        }
      }
    }
  }
  var lr = sheet.getDataRange().getLastRow();
  sheet.getRange(lr+1,1,results.length,results[0].length).setValues(results);
}

After this you can then sort your sheet and format it as needed or liked. Hopefully this helped!

Xempt
  • 1