Googleスプレッドシート & Gmailでメルカリ取引履歴（仕入れ台帳）を作ろう！
中古品を扱う個人eBayセラーの仕入れ元として「メルカリ」は外せません。しかし、メルカリは、個人利用がメインのため、取引履歴をCSVなどに出力する機能はありません。メルカリの取引履歴を手作業で整理するのはメンドウ。そこで、今回は、Gmailに届くメルカリ取引関連のメールをGoogleスプレッドシートに取り込むことで、メルカリの取引履歴を作成する方法を紹介します。
完成イメージ
下記のように、メルカリ商品購入時に配信される「【メルカリ】ご購入ありがとうございます」というタイトルのメールから必要な情報を取得し、スプレッドシートに転記する流れになります。
本ツールの利用には、次の2つの条件があります。
・Gmailにメルカリの取引履歴メールが届いていること
・Gmailと同一アカウントでツールを作成する
ポイント
本ツールのポイントはGoogleスプレッドシートでGmailのデータを取得することです。
Googleスプレッドシートのプログラム・マクロ機能（Google Apps Script:GAS）では、Gmailを含むGoogleの他のサービスのデータを取得・編集することが可能です。
ツールの処理内容を「言語化」
ツールを作成するためには、どのような手順で欲しい機能を実現するか「言語化」する必要があります。最初から完璧な手順を作ることは難しいですが、試行錯誤するためのたたき台として、ツールの作業手順を「言語化」してみます。
(1)Gmailでメルカリ取引を検索
(2)メールから必要な情報を取得
(3)データを張り付け
この流れにそって、ツールを作成していきます。
(1)Gmailでメルカリ取引を検索
var sheet= SpreadsheetApp.getActive().getSheetByName('メルカリ購入');
//datecond="after:2022/1/1 before:2022/6/1"
//最終取得日確認
datecond=""
lastdate=sheet.getRange(sheet.getLastRow(),1).getValue()
if ( Object.prototype.toString.call(lastdate) == "[object Date]" ){
datecond="after:"+Utilities.formatDate(lastdate,'JST','yyyy/MM/dd')
Logger.log(datecond)
}
//Gmail検索
var selltit = "subject:【メルカリ】ご購入ありがとうございます"
var myThreads = GmailApp.search(datecond+" "+selltit, 0, 20); //条件にマッチしたスレッドを取得
var myMsgs = GmailApp.getMessagesForThreads(myThreads); //配列に格納
Gmailでは次のような「演算子」を用いてメールを検索・抽出することが可能です。
Gmail検索条件について（Googleサイト）
Google Apps Script(GAS）でも、Gmailの演算子をそのまま用いることができます。本ツールでは、次の2つの演算子を使用します。
after：最終取得日 subject:【メルカリ】ご購入ありがとうございます
・After:メールの受信年月日が指定した「年月日以降」のメールを抽出
・subject:指定した「タイトル」のメールを抽出
取引履歴用のシートである「メルカリ購入」シートの最終取得日(変数名:datecond)を取得したのち、その日以降のメールを20件検索します。
（初回は、2022/1/1など日付を直接指定します。）
(2)メールから必要な情報を取得
var valMsgs = [];
var matomeArray = [];
for (var Thread of myMsgs) {
Logger.log("○スレッド内のメール数:" + Thread.length);
for (var Message of Thread) {
valMsgs[0] = Message.getDate();
valMsgs[1] = Message.getPlainBody().split("商品ID : ")[1].split("\n")[0];
valMsgs[2] = Message.getPlainBody().split("商品名 : ")[1].split("\n")[0];
Logger.log(valMsgs[2])
valMsgs[3] = Message.getPlainBody().split("出品者 : ")[1].split("\n")[0];
try{
valMsgs[4] = Message.getPlainBody().split("クーポン：")[1].split("\n")[0];
}catch(e){valMsgs[4]=""}
try{
valMsgs[5] = Message.getPlainBody().split("ポイント利用 : P")[1].split("\n")[0];
}catch(e){valMsgs[5]=""}
try{
valMsgs[6] = Message.getPlainBody().split("dポイント利用 : P")[1].split("\n")[0];
}catch(e){valMsgs[6]=""}
valMsgs[7] = Message.getPlainBody().split("支払い金額 : ￥")[1].split("\n")[0];
valMsgs[8] = Message.getPlainBody().split("支払い方法 : ")[1].split("\n")[0];
matomeArray.push([valMsgs[0],valMsgs[1],valMsgs[2],valMsgs[3],valMsgs[4],valMsgs[5],valMsgs[6],valMsgs[7],valMsgs[8]])
}
}
//日付順にソート
matomeArray.sort((a, b) => {return a[0] - b[0];} );
(1)で取得したスレッド内の各メールから必要なデータを抽出します。
valMsgs[0] ：購入日時（メール受信日時）
valMsgs[1] ：商品ID
valMsgs[2] ：商品名
valMsgs[3] ：出品者
valMsgs[4] ：クーポン
valMsgs[5] ：ポイント利用
valMsgs[6] ：dポイント利用
valMsgs[7] ：支払金額
valMsgs[8] ：支払い方法
上記のうち、商品ID～支払い方法については、メール本文を取得後、必要なデータ部分を「Split関数」で抽出しています。
これらのデータを取引単位で、配列（matomeArray）に格納します。
格納したデータを見ると最新のメールが上に来ています。台帳は時系列（=新しいデータが下）としたいので、作成した配列の並べ換え(sort)を行います。
(3)データを張り付け
// スプレッドシートに出力
if(matomeArray.length>=1){
sheet.getRange(sheet.getLastRow()+1, 1, matomeArray.length, 9).setValues(matomeArray); //シートに貼り付け
}
(2)で作成したデータの件数が1件以上を確認したのち、データを表に張り付けます。
GASの欠点の１つが、スプレッドシートへのデータ張り付け処理が遅いこと。1セルずつ張り付けていると処理が遅くなり、GASの制限時間に引っかかってしまいます。
そのため、上記のように貼り付けるデータを配列をまとめ、一括貼り付けを行います。
テストしてみる
無事取得できました。
定期実行を模擬するため、最新の1,2行分のデータを削除してから、実行します。
最新データ日「以降」という検索条件なので、最新データ日のデータが重複してしまいます。
そこで、張り付け後、「データの重複を削除する」という処理を追加します。
//重複削除
tbl=sheet.getRange("A3:I" +sheet.getLastRow())
tbl.removeDuplicates([2])
完成版のコード
function merubuy_Mail() {
//メルカリ購入履歴取得
var sheet= SpreadsheetApp.getActive().getSheetByName('メルカリ購入');
//datecond="after:2022/1/1 before:2022/6/1"
//最終取得日確認
datecond=""
lastdate=sheet.getRange(sheet.getLastRow(),1).getValue()
if ( Object.prototype.toString.call(lastdate) == "[object Date]" ){
datecond="after:"+Utilities.formatDate(lastdate,'JST','yyyy/MM/dd')
Logger.log(datecond)
}
//Gmail検索
var selltit = "subject:【メルカリ】ご購入ありがとうございます"
var myThreads = GmailApp.search(datecond+" "+selltit, 0, 20); //条件にマッチしたスレッドを取得
var myMsgs = GmailApp.getMessagesForThreads(myThreads); //配列に格納
var valMsgs = [];
var matomeArray = [];
for (var Thread of myMsgs) {
Logger.log("○スレッド内のメール数:" + Thread.length);
for (var Message of Thread) {
valMsgs[0] = Message.getDate();
valMsgs[1] = Message.getPlainBody().split("商品ID : ")[1].split("\n")[0];
valMsgs[2] = Message.getPlainBody().split("商品名 : ")[1].split("\n")[0];
Logger.log(valMsgs[2])
valMsgs[3] = Message.getPlainBody().split("出品者 : ")[1].split("\n")[0];
try{
valMsgs[4] = Message.getPlainBody().split("クーポン：")[1].split("\n")[0];
}catch(e){valMsgs[4]=""}
try{
valMsgs[5] = Message.getPlainBody().split("ポイント利用 : P")[1].split("\n")[0];
}catch(e){valMsgs[5]=""}
try{
valMsgs[6] = Message.getPlainBody().split("dポイント利用 : P")[1].split("\n")[0];
}catch(e){valMsgs[6]=""}
valMsgs[7] = Message.getPlainBody().split("支払い金額 : ￥")[1].split("\n")[0];
valMsgs[8] = Message.getPlainBody().split("支払い方法 : ")[1].split("\n")[0];
matomeArray.push([valMsgs[0],valMsgs[1],valMsgs[2],valMsgs[3],valMsgs[4],valMsgs[5],valMsgs[6],valMsgs[7],valMsgs[8]])
}
}
//日付順にソート
matomeArray.sort((a, b) => {return a[0] - b[0];} );
// スプレッドシートに出力
if(matomeArray.length>0){
sheet.getRange(sheet.getLastRow()+1, 1, matomeArray.length, 9).setValues(matomeArray); //シートに貼り付け
}
//重複削除
tbl=sheet.getRange("A3:I" +sheet.getLastRow())
tbl.removeDuplicates([2])
}
定期実行
Googleスプレッドシートは、シートを開いていない状態でも定期的にスクリプトを処理してくれる「トリガー機能」を持っています。トリガー機能で毎週1回など適当な周期で作成したツールを実行すれば、常にシートが最新の状態になります。
まとめ
今回は、GoogleスプレッドシートとGmailを連携させ、メルカリの取引履歴を作成するツールを作成しました。検索条件やデータ抽出条件を見直すことで、ヤフオク・ラクマなど他のフリマサイトの取引履歴を整理することができます。
いろいろ試してみてください。
