Skip to content

Instantly share code, notes, and snippets.

@chigirits
Last active July 22, 2023 05:21
Show Gist options
  • Save chigirits/7ab398797d498fbe5152d9d86fd13133 to your computer and use it in GitHub Desktop.
Save chigirits/7ab398797d498fbe5152d9d86fd13133 to your computer and use it in GitHub Desktop.
GmailのBOOTH注文確認メールから購入履歴をスプレッドシートにまとめるGAS
// GmailのBOOTH注文確認メールから購入履歴をスプレッドシートにまとめるGAS
// 空スプレッドシートを作成して、そのIDを bookID に設定して実行してね
// 参考: https://tech.torico-corp.com/blog/gmail-export-to-google-spreadsheet-via-google-apps-script-gas/
function exportGmails() {
const bookID = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';
const criteria = 'ご注文の確認 [BOOTH]';
const columns = [
'注文日時',
'注文番号',
'決済方法',
'小計',
'送料',
'支払手数料',
'合計',
'ショップ名',
'ショップURL',
'注文詳細',
'注文内容',
];
let book = SpreadsheetApp.openById(bookID);
let threads = GmailApp.search(criteria);
let sheet = book.getActiveSheet();
sheet.clear();
for (let i = 0; i < columns.length; i++) {
sheet.getRange(1, 1+i).setValue(columns[i]);
}
let row = 2;
for (let thread of threads) {
for (let message of thread.getMessages()) {
let lines = message.getBody().split(/\r?\n/);
let data = {};
for (let i = 0; i < lines.length; i++) {
let line = lines[i].trim();
let m1 = line.match(/^(.+?):(.*)/);
if (m1 != null) {
data[m1[1]] = m1[2];
continue;
}
let m2 = line.match(/^\[(.+?)\] *(.*)/);
if (m2 != null) {
let c = m2[1];
let d = m2[2];
if (c == '注文番号') {
let e = d.match(/(注文詳細: *(\S+)/);
if (e != null) {
data['注文詳細'] = e[1];
d = d.replace(/ *(注文詳細:.+/, '');
}
} else if (c == '注文内容') {
let d2 = [];
while (++i < lines.length) {
let l = lines[i].trim();
if (l.startsWith('[')) break;
if (l != '') d2.push(l);
}
console.log(d2);
d = d2.join("\n").trim();
}
data[c] = d;
continue;
}
}
console.log(message.getBody());
for (let i = 0; i < columns.length; i++) {
let c = columns[i];
let d = data[c] ?? '';
d = d.replace(/^¥ /, '');
sheet.getRange(row, 1+i).setValue(d);
}
row++;
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment