Last active
April 30, 2020 06:02
-
-
Save sohsatoh/9f4d723b03e219f0d9d5d910e1c3d65e to your computer and use it in GitHub Desktop.
Gmailからヨドバシカメラでの購入金額合計を取得、スプレッドシートに記入するGoogle Apps Script
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
function getTotalOnYodobashi() { | |
//ベースコード:https://note.com/ky120930/n/n97cd39a4ad2e | |
//スプレッドシートの内容をリセット | |
var sheet = SpreadsheetApp.getActiveSheet().clear(); | |
// Gmailの検索条件 | |
var subject = 'ヨドバシ・ドット・コム:ご注文商品出荷のお知らせ'; | |
var threads = GmailApp.search('subject:' + subject); //条件にマッチしたスレッドを検索して取得 | |
var row = 2; // 1行目はヘッダ | |
for(var n in threads){ | |
var thread = threads[n]; | |
var msgs = thread.getMessages(); | |
for(m in msgs){ | |
var msg = msgs[m]; | |
var body = msg.getBody(); | |
body = body.substring(body.indexOf("今回出荷のお買い物合計金額")+1,body.length); | |
//日時を取得 | |
var date = msg.getDate(); | |
//商品名を取得 | |
var itemNameIdx = body.indexOf("・「"); | |
var itemNameEndIdx = body.indexOf("」"); | |
var itemName = body.substring(itemNameIdx+2, itemNameEndIdx).replace(/<("[^"]*"|'[^']*'|[^'">])*>/g,''); | |
//価格を取得 | |
var valueIdx = body.indexOf("円"); | |
var value = body.substring(0, valueIdx).replace(/[^0-9]/g, ''); | |
//日時を入力 | |
sheet.getRange(1,1).setValue('日付'); | |
sheet.getRange(row,1).setValue(date); | |
//商品名を入力 | |
sheet.getRange(1,2).setValue('商品名'); | |
sheet.getRange(row,2).setValue(itemName); | |
//価格を入力 | |
sheet.getRange(1,3).setValue('価格'); | |
sheet.getRange(row,3).setValue(value); | |
row++; | |
} | |
} | |
//合計を入力 | |
sheet.appendRow(["", "合計", "=SUM(C2:C" + sheet.getLastRow() + ")"]); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment