LoginSignup
8
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@tatsuya1970inプロトアウトスタジオ

Googleアラートの内容をGASを使ってスプレッドシートへ転記する

Last updated at Posted at 2020-09-29

Gmailに送られてくるGoogleアラートの内容を、GAS(Google Apps Script）を使って、Googleスプレッドシートに転記する仕組みをやってみました。

いわゆるRPAっていうやつですね。

何で作ったのか

私は日頃から気になるキーワードのニュースが自動的に入ってくるようにGoogleアラートを利用してます。

Image from Gyazo

こんな感じでメールが送られてきます

Image from Gyazo

最近、これらのニュースをコピペでエクセルの転記する作業をしたことがありました。
結構面倒でした。
最近　GASなるものの存在を知ったので、この作業の自動化に挑戦しました。

##まず
まずメールがどんな形で来るか調べます。
Googleアラートは　googlealerts-noreply@google.com から送られてきます。
試しに、直近１ヶ月程度（2020/9/1から）のものをGmailApp.search(検索条件)で抽出します。
GmailApp.search()はメール単位ではなく、スレッド単位で取得しますので、配列になります。

コードは、Googleスプレッドシート　＞ツール　＞スクリプトエディタ　に書きます。

コード.gs
function myFunction() {
  // 検索条件に該当するスレッド一覧を取得
  var threads = GmailApp.search('from:googlealerts-noreply@google.com 
  after:2020/09/01');
  
  for(let n in threads){
    let thread = threads[n];   
    let msgs = thread.getMessages();
    for(m in msgs){
      let msg = msgs[m];    
      let plainBody = msg.getPlainBody();
      //　ログをとる
      Logger.log(plainBody);
  } 
}

実行後、ログをみます。

[20-09-27 14:54:33:075 JST] === ニュース - 次のキーワードの新しい検索結果が 10 件あります: [Maas] ===

再エネを活用したEVタクシーで「地域MaaS」、浜松市佐久間町で実証へ
環境ビジネスオンライン
そこで同実証実験ではこの課題解決を目指して、TISがタクシーのオンデマンド配車 
システムとしてMaaSプラットフォームを提供する。EVは、佐久間 ...
<https://www.google.com/url?rct=j&sa=t&url=https://www.kankyo-business.jp/news/026013.php&ct=ga&cd=CAEYACoTNjIwNTgwMDQ3MjQ2MDM5NDI5NjIcNWNkODQzNmFhMGFkOTM4ZDpjby5qcDpqYTpKUA&usg=AFQjCNFKE8jT6PV7xZ18-G2vkbL3YxMGtQ>

このままでは使い物にならないので、スプレッドシートに取り込みやくするために、
replace()を使って加工します。

以下のコードを追加し、カテゴリー、タイトル、ウェブサイトという文字を挿入します。

コード.gs
plainBody = plainBody.replace("あります:", "あります\nカテゴリー：");
plainBody = plainBody.replace("] ===", "] ===\nタイトル：");
plainBody = plainBody.replace("<https", "\ウェブサイト：<https");
Logger.log(plainBody);

すると、

[20-09-27 14:54:33:075 JST] === ニュース - 次のキーワードの新しい検索結果が 10 件あります
カテゴリー： [Maas] ===
タイトル：

再エネを活用したEVタクシーで「地域MaaS」、浜松市佐久間町で実証へ
環境ビジネスオンライン
そこで同実証実験ではこの課題解決を目指して、TISがタクシーのオンデマンド配車 
システムとしてMaaSプラットフォームを提供する。EVは、佐久間 ...
ウェブサイト：<https://www.google.com/url?rct=j&sa=t&url=https://www.kankyo-business.jp/news/026013.php&ct=ga&cd=CAEYACoTNjIwNTgwMDQ3MjQ2MDM5NDI5NjIcNWNkODQzNmFhMGFkOTM4ZDpjby5qcDpqYTpKUA&usg=AFQjCNFKE8jT6PV7xZ18-G2vkbL3YxMGtQ>

加工した文字列 plainnBody　から必要な文字を正規表現で抜き取ります。

カテゴリとウェブサイトは、１行ですむので
(.*)

タイトルは複数行にわたるで、
\s([\s\S]*)
を使います。

コード.gs
let category = plainBody.match(/カテゴリー：(.*)/); 
let title =    plainBody.match(/タイトル：\s([\s\S]*)ウェブサイト：/);
let url =      plainBody.match(/ウェブサイト：(.*)/);

//ログをとる　なぜか配列になります
Logger.log("category[1]: "+category[1]);
Logger.log("title[1]: "+title[1]);
Logger.log("url[1]: "+url[1]);

ここで、なぜか配列になります。
（ハマったところ）

すると、
ずいぶんすっきりしました。

category[1]:  [MaaS]

title[1]: 
再エネを活用したEVタクシーで「地域MaaS」、浜松市佐久間町で実証へ
環境ビジネスオンライン
そこで同実証実験ではこの課題解決を目指して、TISがタクシーのオンデマンド配車 
システムとしてMaaSプラットフォームを提供する。EVは、佐久間 ...

url[1]: <https://www.google.com/url?rct=j&sa=t&url=https://www.kankyo-business.jp/news/026013.php&ct=ga&cd=CAEYACoTNjIwNTgwMDQ3MjQ2MDM5NDI5NjIcNWNkODQzNmFhMGFkOTM4ZDpjby5qcDpqYTpKUA&usg=AFQjCNFKE8jT6PV7xZ18-G2vkbL3YxMGtQ>

と、category[1],title[1],url[1]に代入することに成功しました。

あとは、replace()を駆使して見栄えよく加工します。
もう、このへんになると、作った本人しか分からない状態ですね。

なぜか、toString()を入れるとエラーがでません。

コード.gs
let category0 = category[1].toString();
let category1 = category0.toString().replace("[","");
let category2 = category1.toString().replace("]","");
      
let title0 = title[1] .toString().replace(/\s/,"");
let title1 = title0.toString().replace(/タイトル：\s/,"");
let title2 = title1.toString().replace(/ウェブサイト：/,"");
      
let url0 = url. toString().replace("ウェブサイト：<https://www.google.com/url?rct=j&sa=t&url=","");
let url1 = url0.substring(0,url.indexOf("&ct=ga"));

最後に、スプレッドシートに転記します。

コード.gs
sheet.getRange(row,1).setValue(category2);
sheet.getRange(row,2).setValue(date);
sheet.getRange(row,3).setValue(title2);
sheet.getRange(row,4).setValue(url1);

ちゃんと、スプレッドシートに転記されました。

Image from Gyazo

##全コード
以上からループ文を追加した全コードです。

コード.gs
function myFunction() {
  
  //シートをいったんクリアー
  let sheet = SpreadsheetApp.getActiveSheet().clear();
  
  // 検索条件に該当するスレッド一覧を取得
  let threads = GmailApp.search('from:googlealerts-noreply@google.com -label:処理済み after:2020/09/01');
  let row = 2; // 1行目はヘッダ
  
  for(var n in threads){
    let thread = threads[n];   
    let msgs = thread.getMessages();
    for(m in msgs){
      let msg = msgs[m];    
      let date = msg.getDate();
      let plainBody = msg.getPlainBody();
     
      //GASに抜き出し易くするために加工
      plainBody = plainBody.replace("あります:", "あります\nカテゴリー：");
      plainBody = plainBody.replace("] ===", "]\n ===\nタイトル：");
      plainBody = plainBody.replace("<https", "\nウェブサイト：<https");
      
      //必要な情報を取得
      let category = plainBody.match(/カテゴリー：(.*)/);    
      let title = plainBody.match(/タイトル：\s([\s\S]*)\nウェブサイト/); //複数行にわたる場合の書き肩
      let url =   plainBody.match(/ウェブサイト：(.*)/);
      
      //たまにnullなものがある（原因不明）ので、nullがあったら次へ
      if (title == null) {
        continue;
      } 
      
      //見栄え良く加工、 なぜかtoString()を入れるとエラーがでない
      let category0 = category[1].toString();
      let category1 = category0.toString().replace("[","");
      let category2 = category1.toString().replace("]","");
      
      let title0 = title[1] .toString().replace(/\s/,"");
      let title1 = title0.toString().replace(/タイトル：\s/,"");
      let title2 = title1.toString().replace(/ウェブサイト：/,"");
      
      let url0 = url. toString().replace("ウェブサイト：<https://www.google.com/url?rct=j&sa=t&url=","");
      let url1 = url0.substring(0,url.indexOf("&ct=ga"));
         
     　//スプレッドシートに転記
      sheet.getRange(row,1).setValue(category2);
      sheet.getRange(row,2).setValue(date);
      sheet.getRange(row,3).setValue(title2);
      sheet.getRange(row,4).setValue(url1);
      row++;
    }
  }
  
}

##今後

RPAもどきなことをしましたが、そもそもの元のメールの形式が整ってないと、前処理が大変で、このようにカオスになってしまい作った人しか分からない状態になりますね。
今後は、以下のことに挑戦したいと思います。

  • 毎日自動的に取得するようにする。
  • スプレッドシートには最初の１つのニュースしかとりこめていないので、そこをなんとかする

##参考サイト

[【GAS】Gmailのメール本文をスプレッドシートに転記する方法](https://valmore.work/how-to-copy-gmail-message-to-spreadsheet/
https://developer.mozilla.org/ja/docs/Web/JavaScript/Guide/Regular_Expressions)
Gmailで知らないと損する10種類の検索コマンド──フィルタ作成にも便利
GoogleAppsScriptでいろいろな条件を指定してメールを取得する
Google Apps Scripts 公式ページ

8
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
8
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?