Google脚本-将Gmail中的数据获取到表格中 [英] Google Script - get data from Gmail into Sheet

问题描述

我一直在四处寻找SO和Google，但找不到任何有效的方法.

I have been looking around here on SO and on Google, but I cannot find anything that is working.

因此，当我在下面运行我的代码时，我在图像上得到了结果.

So when I am running my code below, I am getting the result on the image.

我想从具有特定标签的邮件中的最新/最新 thread中提取数据.

I want to extract data from the newest/most recent thread in mails that have a specific label.

但是，在我的Gmail中，我只有"Action"-label下用粗体突出显示的3封邮件.
其他邮件已被删除，因此它们处于垃圾箱中，但仍带有操作"标签.

However, in my Gmail, I only have the 3 mails under "Action"-label that I have highlighted in bold.
The other mails have been deleted, hence, they are in trash, but do still have the "Action" label.

我只想显示我已打开"Action"-label的邮件-这意味着我只想要最新的thread时间/日期，主题行以及ID，因此我可以创建到该邮件的链接.

I want to only show the mails that I have "Action"-label on - meaning that I only want the newest thread time/date, subject line as well as the ID, so I can create a link to that mail.

function myFunction() {

  var ss = SpreadsheetApp.getActiveSheet();

  var query = "label:action -label:trash -label:action-done -from:me";

  var threads = GmailApp.search(query);

    for (var i = 0; i < threads.length; i++)
    {
      var messages = threads[i].getMessages();

      for (var j = 0; j < messages.length; j++)
      {
        var mId = messages[j].getId()
        var from = messages[j].getFrom();
        var cc = messages[j].getCc();
        var time = messages[j].getDate()
        var sub = messages[j].getSubject();

        ss.appendRow([from, cc, time, sub, 'https://mail.google.com/mail/u/0/#inbox/'+mId])
      }
    }
  }
}

推荐答案

所以我设法通过在数组中找到最大索引来解决了这个问题.
我已经注释了该代码，因此它可以帮助其他人.谢谢，一切.

So I managed to solve it, by finding the max index in the array.
I have commented the code, so it can help others. Thanks, all.

function myFunction() {
  // Use sheet
  var ss = SpreadsheetApp.getActiveSheet();
  // Gmail query
  var query = "label:support -label:trash -label:support-done -from:me";
  // Search in Gmail, bind to array
  var threads = GmailApp.search(query);
  // Loop through query results
  for (var i = 0; i < threads.length; i++)
  {
    // Get messages in thread, add to array
    var messages = threads[i].getMessages();

    // Used to find max index in array
    var max = messages[0];
    var maxIndex = 0;

    // Loop through array to find maxIndexD = most recent mail
    for (var j = 0; j < messages.length; j++) {
      if (messages[j] > max) {
        maxIndex = j;
        max = messages[j];
      }
    } 
    // Find data
    var mId = messages[maxIndex].getId() // ID used to create mail link
    var from = messages[maxIndex].getFrom();
    var cc = messages[maxIndex].getCc();
    var time = threads[i].getLastMessageDate()
    var sub = messages[maxIndex].getSubject();
    // Write data to sheet
    ss.appendRow([from, cc, time, sub, 'https://mail.google.com/mail/u/0/#inbox/'+mId])
  }
}

这篇关于Google脚本-将Gmail中的数据获取到表格中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！