将电子邮件中的特定文本复制到电子表格 [英] Copy specific text from email message to spreadsheet

问题描述

我每天收到一次我的支票帐户余额。我想使用Google Apps脚本从电子邮件中提取余额并将其插入到我的支票帐户电子表格中。我是编码的新手，但到目前为止，我已经想出了如何执行以下操作，这会得到正确电子邮件消息的HTML代码日志： p> function myFunction（）{
var thread = GmailApp.getUserLabelByName（CHK BAL）。getThreads（0,1）[0]; //获取收件箱中的第一个线程
var message = thread.getMessages（）[0]; //获取第一条消息
Logger.log（message.getBody（））; //正文的内容
}


但是，因为此方法返回HTML代码对于电子邮件消息而不是实际的消息文本，它不包含电子邮件本身显示的余额号。

我尝试用getPlainBody替代getBody ，但它在Log中返回一个空值。

这里发布的问题与我的问题非常相似（ Google脚本，可以在我的gmail中找到文本并将它添加到电子表格中？），但即使Mogsdad的回复和帮助链接我一直无法弄清楚发生了什么问题。

任何人都可以帮助我重定向如何获取电子邮件内容而不是空值吗？ （一旦解决了这个问题，我不能说Mogsdad的其他答复中的链接非常清楚如何识别货币并将其复制到电子表格中，但是当然我还没有玩过，因为我甚至无法访问这些内容。）

谢谢！

---

编辑1

有关如何解析HTML的说明，请参阅下面的Serge的回答。我使用这些功能从Gmail标签/过滤器中获取最近的银行帐户余额电子邮件的文本，并将其放入电子表格的单元格中。

然后，我可以在相邻的单元格中使用下面的公式将其去除为货币数量：

左（右（A5，LEN（A5）-find（ $，A5）），FIND（CHAR（10），RIGHT（A5，LEN（A5）-find（ $，A5））） -1）+0

当然，这对我来说很合适，因为货币总是在$前面（第一个，在我的情况下只有$出现在文本）并始终跟着CHAR（10）。任何尝试应用此公式的人都需要在他们寻求隔离的值之前和之后具有相似的一致性。 您可以试试这个代码段最初由Corey G在SO 从html内容中获取文本。我经常使用它，它在大多数情况下工作得很好:)（感谢Corey）

  function getTextFromHtml（html）{
 return getTextFromNode（Xml.parse（html，true）.getElement（））; 
 
 
 $ b函数getTextFromNode（x）{
 switch（x.toString（））{
 case'XmlText'：return x.toXmlString（ ）; 
 case'XmlElement'：return x.getNodes（）。map（getTextFromNode）.join（''）; 
 default：return''; 
} 
} 
  

以及一个测试函数来试试它：

  function test（）{
 var html = GmailApp.getInboxThreads（）[0] .getMessages（）[0] .getBody （）; 
 throw（getTextFromHtml（html））; 
} 
  

I get my checking account balance emailed to me once per day. I want to use Google Apps Script to pull the balance from the email message and plug it into my checking account spreadsheet. I am a novice to coding, but so far I have figured out how to do the following, which gets me a log of HTML code of the correct email message:

function myFunction() {
 var thread = GmailApp.getUserLabelByName("CHK BAL").getThreads(0,1)[0]; // get first thread in inbox
 var message = thread.getMessages()[0]; // get first message
 Logger.log(message.getBody()); // log contents of the body
}

However, because this method returns the HTML code for the email message and not the actual message text, it doesn't contain the balance number that shows up in the email itself.

I tried substituting getPlainBody in place of getBody, but it returns a null value in the Log.

The question posted here is pretty much my same question (Google script that find text in my gmail and adds it to a spreadsheet?), but even with Mogsdad's reply and helpful links I haven't been able to figure out what's going wrong.

Can anyone help redirect me on how to get the email content instead of the null value?

(Once that's solved, I can't say that the link on Mogsdad's other reply is very clear about how to identify the currency and copy it into the spreadsheet, but of course I haven't been able to play around yet since I can't even access the content yet.)

Thanks!

---

EDIT 1

See Serge's answer below for instructions on how to parse the HTML. I used those functions to grab the text of the most recent Bank Account Balance email from a Gmail label/filter and drop it into a cell in my spreadsheet.

Then I was able to use the following equation in an adjacent cell to strip it down to just the currency number:

LEFT(RIGHT(A5,LEN(A5)-FIND("$",A5)),FIND(CHAR(10),RIGHT(A5,LEN(A5)-FIND("$",A5)))-1)+0

Of course, this works for me because the currency number is always preceded by $ (the first, and in my case, only $ to appear in the text) and always followed by CHAR(10). Anyone trying to apply this formula would need similar consistency before and after the value they are seeking to isolate.

解决方案

You could try this code snippet originally written by Corey G on SO to get the text from the html content. I use it quite often and it works nicely most of the time :) (Thanks Corey)

function getTextFromHtml(html) {
  return getTextFromNode(Xml.parse(html, true).getElement());
}


function getTextFromNode(x) {
  switch(x.toString()) {
    case 'XmlText': return x.toXmlString();
    case 'XmlElement': return x.getNodes().map(getTextFromNode).join('');
    default: return '';
  }
}

And a test function to try it :

function test(){
  var html = GmailApp.getInboxThreads()[0].getMessages()[0].getBody();
  throw(getTextFromHtml(html));
}

这篇关于将电子邮件中的特定文本复制到电子表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！