MENU

GASでGmailの件名を抜き出してExcelに自動連携する流れ。

2022年5月22日2022年6月25日

URLをコピーしました！

GAS（GoogleAppScript）をつかって、Gmailの日時や件名といった情報だけを取り出すことができます。

その流れをメルマガタイトルをExcelで加工する事例でまとめてみました。

目次

メルマガのタイトルをHPに載せるまで

今回とりあげたい事例は、メルマガのタイトルデータだけを抜き出すというもの。
毎日書いているメルマガ。今日現在で278回でした。

そのメルマガの申し込みフォームにこれまで発行したメルマガのタイトルを載せています。

タイトルを見ていただき、どんなメルマガなのかを知っていただくようにしています。

ただ、これをHPに毎回1つずつ入力するのは、避けたいものです。
そこで紹介するのが今回の事例。

Gmailのデータからメルマガの件名（タイトル）を抜き出してExcelで自動で加工するという流れ。

具体的には、

GAS（GoogleAppScript）を使って、メルマガの件名と日付の情報を抜き出してスプレッドシートに反映
↓
ExcelにできたデータをHPにコピーする

という流れです。

GASでGmailの件名（メルマガタイトル）を抽出

まずは、GAS（GoogleAppScript）を起動します。スプレッドシートからApps Scriptを選び、

起動したGASには次のように書いておきます。

function gettytle(){

  var tytle = 'subject:(GO方見聞録)';
  var start =   0;
  var max =    30;

  //1　スプレッドシートを読み込む
  var gas =SpreadsheetApp.getActiveSheet();

  //2 スプレッドシートをクリア
  gas.clear();

  //3 指定の数だけメールを探す

  var threads = GmailApp.search(tytle,start,max);
  var messages = GmailApp.getMessagesForThreads(threads);

  //4 メルマガのタイトルを抽出

   for(i=max-1,r=1;i>start;i--,r++){

     var day = messages[i][0].getDate();
     var subject = messages[i][0].getSubject();


    gas.getRange('a'+r).setValue(threads[i].getLastMessageDate());
    gas.getRange('b'+r).setValue(threads[i].getFirstMessageSubject());
   }



};

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

function gettytle(){

var tytle = 'subject:(GO方見聞録)';

var start = 0;

var max = 30;

//1　スプレッドシートを読み込む

var gas =SpreadsheetApp.getActiveSheet();

//2 スプレッドシートをクリア

gas.clear();

//3 指定の数だけメールを探す

var threads = GmailApp.search(tytle,start,max);

var messages = GmailApp.getMessagesForThreads(threads);

//4 メルマガのタイトルを抽出

for(i=max-1,r=1;i>start;i--,r++){

var day = messages[i][0].getDate();

var subject = messages[i][0].getSubject();

gas.getRange('a'+r).setValue(threads[i].getLastMessageDate());

gas.getRange('b'+r).setValue(threads[i].getFirstMessageSubject());

}

};

これを実行をすると、スプレッドシートに

5秒ほどでメルマガの日付とタイトルを表示してくれます。

GASの書き方の特徴は対象になる範囲を「｛…｝」で囲み、文の末尾は「;」を使うという点。

「start」と「max」で上から見て1つ目から何個目までのデータを対象にするかを決めています。

「start」を「0」にしているのはデータは「1」からではなく「0」から始まるという決まりがあるからです。

For{…}は繰り返し。「i」や「r」は変数。0から30までその都度変わります。

検索したメールのメッセージから日付と件名の情報を抽出し、結果をスプレッドシートのA列とB列に1行ずつ入力していくという処理。

これを繰り返しているのです。

もう少し詳しいデータの指定方法については、こちらの記事を参照していただければ。

GAS（Google App Script）でGmailの必要部分だけを抽出する方法。 – GO for IT 〜税理士植村豪 Official Blog

A列に日付、B列にタイトルのデータが反映され、GASでの処理はこれで完了です。

ExcelにGoogleスプレッドシートを連携

次にGASでGmailから取り出したデータをExcelに反映させます。
GASから直接HPにコピーすればいいのでしょうが、表示が崩れますし、望むデータ形式でもありません。

そこでExcelからデータを取り出すようにしています。

スプレッドシートの情報はExcelでWebクエリで連携させています。

Excelを開き、「データ」→データ取得の「Webから」

この枠にGoogleスプレッドシートの共有リンクを貼り付けます。ただし、共有リンクの最後を「edit?usp=sharing」を「export?format=xlsx」と変更しておきましょう。

スプレッドシートの共有リンクのコピー方法はこちらの記事に載せています。

GoogleスプレッドシートをExcelに自動連携する方法

設定後、スプレッドシートのデータを読み込むことができます。

次に「クエリ」タブから「プロパティ」を選び、

赤枠の部分にチェックを入れておきましょう。自動でデータ更新をしてくれます。

これで設定は一区切りです。

その後は、GASでGmailから取り出した日付やタイトルをスプレッドシートに書き出し、その直後にExcelファイルを開けば、「data」シートでスプレッドシートの読み込みが始まり、

数秒後にデータが反映されます。

さらにそのデータをもとに別シート「tytle」で関数を使って必要な情報だけを取り出しています。

テキスト（文字）はMID、FINDといった関数を使って取り出すことができます。

今回はこういう関数を。

今回設定した関数

A1セル　=MID(data!B2,9,6)
B1セル　=data!A2
C1セル　＝=MID(data!B2,FIND(“】”,data!B2)+1,30)

この設定の詳細はこちらの記事にも。

Excelとデータがあれば加工できる。メルマガタイトルの事例。 – GO for IT 〜税理士植村豪 Official Blog

このシート「tytle」のデータをWordPressのHPの固定ページにコピーします。

以後の処理としては、

GASを動かして、データをスプレッドシートに書き出す
Excelを開いてWordPressの固定ページにコピーする

という2つです。

データがあれば、Excelやプログラミングを使えば入力やデータのコピペは減らせます。
メルマガでなくても応用できることはあるかと。参考になればうれしいです。

【編集後記】
昨日はオフ。雨でしたが長男(7)の小学校の運動会は予定通りに開催。
雨は降っていましたが大雨でもなく、お父さんたちのことを考えると結果的にやってもらってよかったかなと。学校の英断でした。

写真撮影にはカメラがぬれないように、上からタオルをかけて撮影に望みました。ただ、かけっこは他の子の影にかくれてしまい上手く撮れず。妻にお願いしたスマホの動画で見ることができ助かりました。

【昨日の1日1新】
※「1日1新」→詳細はコチラ
長男(7)の小学校の運動会

この記事が気に入ったら
いいねまたはフォローしてね！

Follow Me

よかったらシェアしてね！

URLをコピーしました！

URLをコピーしました！