Skip to content

Instantly share code, notes, and snippets.

@xtetsuji
Created April 18, 2018 03:46
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save xtetsuji/c9669d7d964722f725e0ad852a1e5b1a to your computer and use it in GitHub Desktop.
Save xtetsuji/c9669d7d964722f725e0ad852a1e5b1a to your computer and use it in GitHub Desktop.
Yodobashi net shopping price watcher by Google Apps Script. Please see https://qiita.com/xtetsuji/items/953da9e71eb28741804c for detail.
var mailto = "YOUR_PHONE_ADDRESS";
// 定期的に呼ばれる
function walkWritePrice () {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName("価格ウォッチ");
if ( !sheet ) return;
var dateCell = _dateInsertionCell(sheet);
dateCell.setValue(today());
var urlColumnNumber = 2;
var inputCell, url;
for ( var i = 2 ; (url = sheet.getRange(urlColumnNumber, i).getValue()).length > 0 ; i++) {
if ( !url.match(/^https?:/) ) continue;
if ( i > 1000 ) break; // for safe
inputCell = sheet.getRange(dateCell.getRow(), i );
inputCell.setValue(yodobashiPrice(url));
coloringCell(sheet, inputCell);
}
}
// _dataInsertionCell: sheet -> cell ; 日時を入れるセルを割り出す
function _dateInsertionCell (sheet) {
var i, cell;
for ( i = 1 ; i < 1000 ; i++ ) {
cell = sheet.getRange(i, 1);
if ( cell.getValue().length === 0 ) break;
}
return cell;
}
function coloringCell (sheet, cell) {
// 一つ上のセルより価格が安かったら黄色にする
var upperCell = sheet.getRange(cell.getRow() - 1, cell.getColumn());
if (!upperCell) return;
var upprice = parseInt(upperCell.getValue());
var curprice = parseInt(cell.getValue());
var subject, body, kataban = sheet.getRange(1, cell.getColumn()).getValue();
if ( upprice > curprice ) {
cell.setBackground("yellow"); // 値下がり
subject = "値下がりレポート";
body = kataban + "が"+upprice.toString()+"円から"+curprice.toString()+"円に値下がりしました";
} else if ( upprice < curprice ) {
cell.setBackground("red"); // 値上がり
subject = "値上がりレポート";
body = kataban + "が"+upprice.toString()+"円から"+curprice.toString()+"円に値上がりしました";
}
if ( mailto && subject && body ) {
MailApp.sendEmail(mailto, subject, body) ;
}
}
function pad (arg) {
var s = arg.toString();
return s.length == 1 ? "0" + s : s;
}
function today() {
var now = new Date();
var string = now.getFullYear().toString() + "-" + pad(now.getMonth()+1) + "-" + pad(now.getDate()) +
" " + pad(now.getHours()) + ":" + pad(now.getMinutes());
return string;
}
// URL の価格を調べる
function yodobashiPrice (url) {
var res = UrlFetchApp.fetch(url);
var price, m;
if ( res.getResponseCode() == 200 ) {
m = res.getContentText().match(/id="js_scl_unitPrice".*?([\d,]+)/);
price = m ? m[1].replace(/,/, "") : -1;
} else {
// TODO: 別の方法で知らせたほうがいいかも
Logger.log("URL="+url+" fetch error.");
}
return price;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment