Created
April 13, 2014 12:08
-
-
Save sagaraya/10581282 to your computer and use it in GitHub Desktop.
ISBN入れたらAmazonから自動で本のデータを取得する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 onOpen() { | |
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); | |
var entries = [{ | |
name : "全データを再取得", | |
functionName : "getAmazonDataBySheet" | |
}]; | |
spreadsheet.addMenu("GAS", entries); | |
} | |
// A: ISBN | |
// B: タイトル | |
// C: 購入日 | |
// D: 仕入れ値 | |
// E: 定価 | |
// F: Amazonでの最安値 | |
// G: Kindle版の有無 | |
var isbnCoulumnNumber = 1; //A列を表す | |
var firstColumn = 'B'; | |
var lastColumn = 'G'; | |
var helper = { | |
cachedActiveSheet: undefined, | |
getActiveSheet: function() { | |
if (this.cachedActiveSheet) return this.cachedActiveSheet; | |
return (this.cachedActiveSheet = SpreadsheetApp.getActiveSheet()); | |
}, | |
getRowRangeByRow: function(rowNum) { | |
var sheet = helper.getActiveSheet(); | |
return sheet.getRange(firstColumn + rowNum + ":" + lastColumn + rowNum); | |
} | |
}; | |
//Amazonからデータを取得 | |
var amazon = { | |
bridgeURL: 'http://lookup-book-by-amazon-api.herokuapp.com/lookup/', | |
_requestURL: function(isbn) { | |
return this.bridgeURL + isbn; | |
}, | |
lookup: function(isbn) { | |
var response = UrlFetchApp.fetch( this._requestURL(isbn) ); | |
var jsonStr = response.getContentText(); | |
var jsonData = JSON.parse(jsonStr); | |
Logger.log(jsonData); | |
return book(jsonData); | |
}, | |
parse: function(data) { | |
} | |
}; | |
var book = function(jsonData) { | |
try { | |
var items = jsonData.ItemLookupResponse.Items.Item; | |
var item, hasKindle = 'なし'; | |
if (items instanceof Array) { | |
item = items[0]; | |
if (items[1].ItemAttributes.Format.match(/kindle/i)) hasKindle = 'あり'; | |
} else { | |
item = items; | |
} | |
return { | |
title: item.ItemAttributes.Title, | |
lowestNewPrice: item.OfferSummary.LowestNewPrice.Amount, | |
lowestUsedPrice: item.OfferSummary.LowestUsedPrice.Amount, | |
hasKindle: hasKindle | |
}; | |
} catch(e) { | |
Logger.log(e); | |
return {}; | |
} | |
}; | |
//特定行のA列に入力されているISBNのデータをAmazonから取得しシートに反映 | |
var getAmazonData = function(isbn, rowNum) { | |
var rowRange = helper.getRowRangeByRow(rowNum); | |
var book = amazon.lookup(isbn); | |
rowRange.setValues([[book.title, '', '', book.lowestNewPrice, book.lowestUsedPrice, book.hasKindle]]); | |
}; | |
//isbnがセルに入力されたらリアルタイムにAmazonからデータを取得しシートに反映する | |
var getAmazonDataByRow = function() { | |
var sheet = helper.getActiveSheet(); | |
var inputtedISBNCell = sheet.getActiveCell(); | |
if (inputtedISBNCell.getColumn() !== isbnCoulumnNumber) return; | |
var isbn = inputtedISBNCell.getValue(); | |
if ( !isbn.toString().match(/^[0-9]+$/) ) return; | |
getAmazonData(isbn, inputtedISBNCell.getRow()); | |
}; | |
//シート全体のA列に入力されているISBNのデータをAmazonから取得しシートに反映 | |
function getAmazonDataBySheet() { | |
//高速化のため全体のvaluesを取得し、rowNumと入力されているISBNを抽出し、getAmazonData()にかける | |
var sheet = helper.getActiveSheet(); | |
var rows = sheet.getDataRange(); | |
var numRows = rows.getNumRows(); | |
var values = rows.getValues(); | |
for (var i = 0; i < numRows; i++) { | |
var isbn = values[i][0]; | |
if ( !isbn.toString().match(/^[0-9]+$/) ) continue; | |
getAmazonData(isbn, i+1); | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment