メルカリの購入履歴をExcelへ。GASでGmailからデータを抽出して経理。

メルカリで買ったモノをExcelデータにしています。
使っているのは、Google Apps Script（GAS）＝Googleのプログラミングツールです。

 

※メルカリとGoogleスプレッドシート　by　Leica M10

メルカリの購入履歴の弱点

 

メルカリは、出品数も多く、安く手に入ることもあるので便利です。
ただ、何かを買えば、記録しておきたいもの。
（本来は）

経費にする場合の経理でも、プライベートの場合の家計簿でも、買った記録ができるかどうかが大事です。
振込であれば、振込履歴が残り、カードで払えばカードの履歴に残りますが、何を買ったかまでは残りません。
その買った店やサービスからデータを取り出す必要があるのです。

たとえば、Amazonだと、私は、マネーフォワード（家計簿版）を使っています。

メルカリの場合は、マイページで、買った履歴を見ることができるのですが、1つずつ見る必要があり、とてもデータとしては使えません。

 

そこで、プログラミングして、買った後のメールを使い、データにしています。

 

 

GASでメルカリをデータ化する流れ

私は、メルカリをGmailで利用しています。
Gmailから特定のメールを抽出して、データ化するプログラムをつくれば、買ったモノのデータが集まります。
今回の事例は、「Gmailからデータを抽出する」というもので、応用がきくものです。

様々なプログラミング言語（RPAも含めて）で、Gmail、Googleカレンダー、GoogleアシスタントといったGoogleのサービスを効率化するなら、Google Apps Script（GAS。ガス）＝Googleのプログラミングツールを使います。

Googleのサービスを使っているなら、それだけのために覚えておいても損はありません。
・Google ドキュメントの音声認識結果を好みのパターンで置換
GAS正規表現置換で、Googleドキュメント音声認識入力のデメリットを補う方法 | EX-IT
・音声認識入力で Google スプレッドシートに記憶したデータを変換
音声認識経理で売上・経費の重みをより感じる方法。Googleアシスタント＆GoogleAppsScript ＆ Googleスプレッドシート | EX-IT
・Googleカレンダーの予定をスプレッドシートに転記
Googleカレンダーの予定をデータ形式（スプレッドシート）にするGAS（プログラム） | EX-IT
・ Googleフォームの自動返信
新Googleフォームで問い合わせフォームを作る方法＆自動返信メール設定 | EX-IT

プログラミングする手順は次の通りです。

1　Gmailからメールを抽出

メルカリからの購入データのメールを抽出するには、どういった検索をすればいいか。
そのパターンをみつけるのが効率化の秘訣です。
（多くの場合、そのパターンを破り、イレギュラーにするのはヒトですが）

【メルカリ】ご購入ありがとうございます

で検索すれば、目当てのメールが検索でき、例外はなさそうです。

 

そして、メールはこうなっており、商品名と、商品代金が抜き出せれば、データとして使えます。
あとは、日付。
その他の項目もある程度自動化できますが、今回は、日付、商品名、金額のデータをつくることを事例にしました。
なお、これはPS4ソフト。
経費にしてません。
（ジェダイからは仕事に必要なことを多く学んでいますけど）

 

抽出したデータは、GoogleのExcel、スプレッドシートに記録していきます。

プライベートばっかですが……。
（夏に家族でももクロのライブに行き、そのギガライトも仕入れました）

このスプレッドシートはExcelに変換できますし、コピペしてExcelにすることもできます。

 

 

GASでGmailから抽出したメールをスプレッドシートへ

GASについては、こちらの記事を参考にしていただければ。

プログラミングやるならExcelマクロ（VBA）？Googleスプレッドシート（GAS）？それぞれのはじめ方と最初の一歩 | EX-IT

 

新規のスプレッドシートをつくり（メルカリ処理用に）、［ツール］→［スクリプトエディタ］で、

 

こういったスクリプトエディタ（プログラムを書くツール）が出てきますので、ここにプログラムを書いていきます。

 

 

 

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

まず、スプレッドシートを読み込みます。

var sp =SpreadsheetApp.getActiveSheet();

 

varというのは、「定義する」という意味で、今開いているスプレッドシートをspとして、この後に使っていくということを伝えるものです。

SpreadsheetApp.getActiveSheet(); が、「今開いているスプレッドシート」という意味であり、必須ではないのですが、これを毎回使っていたら、プログラムが長くなり、見づらくなるからspにします。
spは、別の言葉でもかまいません。任意です。
こういったものを変数といい、varは、variableの略で、変わるもの＝変数を意味します。
「今開いている」ということは、開いているシートが変われば、spは変わるわけです。

 

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

このプログラムを繰り返し使うことを考えて、まずはスプレッドシートをクリア、まっさらにします。
sp.clear();

ここで、clearを使い、さきほど変数としたspを使うのです。

SpreadsheetApp.getActiveSheet.clear();

でもいいのですが、やはり長くなりますので。

3 Gmailからメルカリからの購入メールを抽出

Gmailからメールを抽出します。
ここでも変数を使い、search_mail に抽出条件を入れていきましょう。

var search_mail = GmailApp.search("【メルカリ】ご購入ありがとうございます",0,10);

GmailApp.search（〇）;

で、Gmailから〇という条件で検索するという意味です。
〇の中身は、（△,□,◇）で、

△という条件で、□番目から◇個を検索するという意味になります。

(“【メルカリ】ご購入ありがとうございます”,0,10）
は、
“【メルカリ】ご購入ありがとうございます”という条件で、0番目から10個
ということです。

0番目というのは、プログラムの数え方が、0、1、2、3……なので、「最初から」だと0になります。
10個というのは、任意です。
100でもかまいません。
もっと工夫すれば、何月、何年のデータという抽出もできますが、ある程度ざっくり抽出できればいいかと。

次に、変数mail_messageへ、Gmailのメールをsearch_mail という条件で検索します。

var mail_message = GmailApp.getMessagesForThreads(search_mail);

 

ここで、出てくるThreads（スレッド）という語は、Gmail特有のものです。

同じ日に来た同じ件名のメールは、Gmailではこのようにまとめられます。

 

開くとこんな感じで、スレッド（ひとまとまり）になっており、これがプログラミング上、ちとやっかいなのです。

 

 

 

4 抽出したメールから日付、商品名、金額を抽出

 

スレッドを考えず、1つ目のメールだけを抽出するなら、こうなります。
解説のため、こちらの事例から考えてみましょう。

for(var i= 0,r=1;i<mail_message.length;i++,r++){

 var date = mail_message[i][0].getDate();
 sp.getRange('a'+r).setValue(date); 　　　　　　　　　//日付

 var body = mail_message[i][0].getPlainBody();
 var item = body.match(/商品名 :.*/)[0].replace('商品名 : ','');
 sp.getRange('b'+r).setValue("メルカリ "+item); 　　　　　　　　　//商品名

 var amount = body.match(/商品代金 :.*/)[0].replace('商品代金 : ','');
 sp.getRange('c'+r).setValue(amount); 　　　　　　　　　//金額
};

 

この結果はこうなり、一見完成したようですが、7/26のデータが1つしかできていません。
2つあるデータのうち、最初のものだけを抽出しているのです。
「なんでもう1つも抽出せんの？常識やろ？」といっても、こちらが伝えたとおりにやってくれただけに過ぎません。
上のプログラムだと、「スレッドの1つ目」ということしか伝えてませんので。
伝える側、仕事を依頼する側に問題があるのです。
（人と人でも同じことであり、上司や社長はすべからくプログラミングをやるといいと思っています。伝え方を学ぶのにプログラミングは最適です。ご要望あらば承ります。）

 

この部分を見ていきます。

forは、繰り返すということで、｛｝で囲んだ部分を、検索したメールの数だけ繰り返しています。
ここで、iとrという変数を使い、iは、メール、rは、貼り付けるスプレッドシートの行（row）を示すものです。

まずはiから解説すると、varで定義しつつ、初期値をiは0と設定します。
0、1、2、3…と数え、最初のメールから繰り返すので、0です。

次に、何回繰り返すかを指定し、iがmail_message.length、つまり変数mail_messageのlength（長さ＝数）まで繰り返します。
最後に、i++とし、iを1、2、3、と増やして、1回目は、0（実質1つ目）、2回目は1（実質2つ目）となるのです。

rは、初期値を1にします。
セルを指定するので、A1、A2、A3とする必要があり、0が初期値だと、A0となり存在しないものを指定するのでエラーが出るからです。
何回繰り返すかは、iで指定し、rも1つずつ増やす（1行ずつ下を指定する）ので、r＋＋となります。

 

 

日付の抽出は、変数dateにmail_message[i][0].getDate();で指定します。

var date = mail_message[i][0].getDate();

このmail_message[i][0]は、変数mail_message、つまり1つ1つのメールのうち、1つめ（i=0）のメールで1つ目（0）のスレッドという意味です。
1つ目のスレッドを抽出するプログラムを書いているので、0とします。
getData()でそのメールを受信した日付、つまり買った日付です。

その日付を、スプレッドシートのA列に書き込んでいきます。
セルの指定は、getRange()、入力は、setValue()で、中に入力するもの変数dateを入れましょう。

sp.getRange('a'+r).setValue(date); 　　　　　　　　　//日付

セルの指定はaという文字（’で囲む）と、rという変数を＋でつなげて、a1、a2、a3……と指定していきます。
1つずつ下に書き込まないと、上書きしてしまいますので。

 

商品名の抽出で、まずはメール本文を、getPlainBody()で抽出し、変数bodyに入れます。

var body = mail_message[i][0].getPlainBody();

変数itemに、bodyから商品名のみ抽出します。

var item = body.match(/商品名 :.*/)[0].replace('商品名 : ','');

メール本文bodyから、まず、「商品名 :」を探すのが、body.match(/商品名 :./)です。
matchというのは検索であり、body.match(/商品名 :./)は、正規表現というもので、「商品名で始まる行」を意味します。
商品名 :ジェダイフォールンオーダー
をまず抽出するのです。

そのあとに、［0］をつけて、body.match(/商品名 :./)[0]としています。
body.match(/商品名 :./)を出力すると［］で囲まれており、配列というものになるのです。

このうち、0番目（実質1つ目）を指定するという意味で、body.match(/商品名 :.*/)[0]にしています。

この場合は検索の結果が1つですが、検索の結果は複数ある場合もあり、そのときのために、〇番目という指定ができたほうが都合はいいので、このようなしくみなのです。
まあ、こういうもんだと思っておいていただければ。

 

 

試しに、body.match(/商品*/)とすると、メール本文で最初に「商品」がつくものが検索されます。

body.match(/商品.*/g)と、gをつけると、検索にヒットしたものがすべて出力され、この場合に、0番目か1番目か2番目かといった指定が必要なのです。
こういったしくみなので、今回の事例では1つですが、0という指定をしなければいけません。
（他の事例でメールによっては、特有の語が複数あり、こういう指定を使っています）

 

商品名 :ジェダイフォールンオーダー

と抽出され、「商品名 :」はいりませんので、消します。
いらないものを消すには、置換を使って、空白に置き換えると楽です。
（Excelでも使います）
商品名 :ジェダイフォールンオーダー

の

商品名 :

を空白にすれば、
ジェダイフォールンオーダー

です。

body.match(/商品名 :.*/)[0].replace('商品名 : ','');

で、純然たる商品名だけ取り出せます。
（このしくみがおもしろいと思えるかどうかが、プログラミングを楽しめるかどうかの分岐点かも。私は大好きです。）

ようやくここで、スプレッドシートのB列へ商品名を書き込みます。
ただ、どこで買ったかが分かったほうが記録としては好ましいので、「メルカリ　」という語を頭につけました。

sp.getRange('b'+r).setValue("メルカリ "+item); 　　　　　　　　　//商品名

 

 

最後に金額を、「商品代金」で同様に抽出して置換します。
（ポイントを使ったあとの、「支払い金額」で抽出する方法もあります。）

var amount = body.match(/商品代金 :.*/)[0].replace('商品代金 : ','');
sp.getRange('c'+r).setValue(amount); 　　　　　　　　　//金額

 

 

スレッド、複数のメールに対応するには、次のように変数をもう1つ（j）を使い、貼り付ける場所を、その都度、getLastRowで数えています。

 

for(var i= 0,r=1;i<mail_message.length;i++,r++){

 for(var j= 0,r=sp.getLastRow()+1;j<mail_message[i].length;j++,r++){

  var date = mail_message[i][j].getDate();
  sp.getRange('a'+r).setValue(date); 　　　　　　　　　//日付

  var body = mail_message[i][j].getPlainBody();
  var item = body.match(/商品名 :.*/)[0].replace('商品名 : ','');
  sp.getRange('b'+r).setValue("メルカリ "+item); 　　　　　　　　　//商品名

  var amount = body.match(/商品代金 :.*/)[0].replace('商品代金 : ','');
  sp.getRange('c'+r).setValue(amount); 　　　　　　　　　//金額
  };
};

 

スレッドとi、jはこのような関係です。
スレッド内に複数のメールがあれば、jが0、1、2……と増えていきます。

 

複数のメールがあったとき、r（行）の数え方は、たとえば、すでに3行のデータがあれば、sp.getLastrow()で数え、3とし、その1つ下、つまりsp.getLastrow()+1に入力しています。

 

 

複数のパターンは難易度がかなり高いので、1つのメールだけでもやってみていただければ。
「複数あるなら、2つ目は手入力」でもいいでしょうし、「1日に複数注文しない」というルールをつくって対応することもできます。

 

また、パターンが崩れたときはプログラムも変えなければいけません。
・”【メルカリ】ご購入ありがとうございます”の件名が変わる
・商品名という表現が変わる
といったときです。

こういったことはよくあるので、中身をある程度わかってプログラムを使ったほうがいいでしょう。
(その理由もあり、プログラミングを教えても、外注は承っていません。）

プログラムの完成形は、こちらです。

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

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

//3 Gmailからメルカリからの購入メールを抽出 

  var search_mail = GmailApp.search("【メルカリ】ご購入ありがとうございます",0,10);
  var mail_message = GmailApp.getMessagesForThreads(search_mail);

//4 抽出したメールから日付、商品名、金額を抽出       
  for(var i= 0,r=1;i<mail_message.length;i++,r++){

    for(var j= 0,r=sp.getLastRow()+1;j<mail_message[i].length;j++,r++){
    
      var date = mail_message[i][j].getDate();
      sp.getRange('a'+r).setValue(date); 　　　　　　　　　//日付
      
      var body = mail_message[i][j].getPlainBody();
      var item = body.match(/商品名 :.*/)[0].replace('商品名 : ','');
      sp.getRange('b'+r).setValue("メルカリ "+item); 　　　　　　　　　//商品名
  
      var amount = body.match(/商品代金 :.*/)[0].replace('商品代金 : ','');
      sp.getRange('c'+r).setValue(amount); 　　　　　　　　　//金額
    };
  };
};

 

---

■編集後記

昨日は、セミナー＆イベントに参加。
前日告知でしたけど、空いててよかったです。
夜は、会食。
その前にレンズをテストし、立ち寄ったソフマップで掘り出し物の値段のPS4ソフトを見つけたので購入。
22時前に帰宅したので、届いていた接写リングをテストしながら、ソフトをインストールして最初の最初をやっただけで力尽きました。

 

■「1日1新」

勉強法の7つの習慣セミナー参加
神保町の会議室
神保町　キムラヤ
神保町　MIYABI
神保町　熟成肉バル
新宿　ティンタイフォン
新宿　出店してたベーグル
K&F Concept　LeicaMレンズ用　接写リング
アナ雪2　絵本
PS4　SEKIRO
Leica 75mm、90mm、10mmレンズ　テスト

 

■娘(2歳）日記

朝は、欲しがっていたアンパンマン号を。
ねじで締めて組み立てて喜んでいました。
早めに起こしてなんとか一通り遊んでから保育園へ。