Google Script for Excel and AWS instance pricing + Reserved Instances
/**= | |
* User: zircote | |
* Date: 16/10/2013 | |
* Time: 08:59 | |
*/ | |
var SERVICE_HOST = 'http://aws.amazon.com' | |
var data_sources = { | |
"linux-od": { | |
"uri": '/ec2/pricing/json/linux-od.json', | |
"name": "OD Linux" | |
}, | |
"linux-ri-light": { | |
"uri": '/ec2/pricing/json/linux-ri-light.json', | |
"name": "RI Light [Linux]" | |
}, | |
"linux-ri-medium": { | |
"uri": '/ec2/pricing/json/linux-ri-medium.json', | |
"name": "RI Medium [Linux]" | |
}, | |
"linux-ri-heavy": { | |
"uri": '/ec2/pricing/json/linux-ri-heavy.json', | |
"name": "RI Heavy [Linux]" | |
} | |
} | |
/** | |
* A special function that runs when the spreadsheet is open, used to add a | |
* custom menu to the spreadsheet. | |
*/ | |
function onOpen() { | |
buildMenu() | |
} | |
function buildMenu() { | |
menuItems = [] | |
on_demand = { | |
name: "On Demand", | |
subMenus: [ | |
{ | |
name: "Linux", | |
functionName: "linux_od" | |
} | |
] | |
} | |
menuItems.push(on_demand) | |
ri_light = { | |
name: "RI Light", | |
subMenus: [ | |
{ | |
name: "Linux", | |
functionName: "linux_ri_light" | |
} | |
]} | |
menuItems.push(ri_light) | |
ri_medium = { | |
name: "RI Medium", | |
subMenus: [ | |
{ | |
name: "Linux", | |
functionName: "linux_ri_medium" | |
} | |
]} | |
menuItems.push(ri_medium) | |
ri_heavy = { | |
name: "RI Heavy", | |
subMenus: [ | |
{ | |
name: "Linux", | |
functionName: "linux_ri_heavy" | |
} | |
]} | |
menuItems.push(ri_heavy) | |
var spreadsheet = SpreadsheetApp.getActive(); | |
for (e in menuItems) { | |
SpreadsheetApp.getActive().addMenu(menuItems[e].name, menuItems[e].subMenus); | |
} | |
} | |
function parsePrices(indexKey) { | |
var jsondata = UrlFetchApp.fetch(SERVICE_HOST + data_sources[indexKey].uri) | |
items = [] | |
data = JSON.parse(jsondata.getContentText()); | |
for (region in data['config']['regions']) { | |
var region = data['config']['regions'][region] | |
for (instanceType in region['instanceTypes']) { | |
var instanceType = region['instanceTypes'][instanceType] | |
for (size in instanceType['sizes']) { | |
var size = instanceType['sizes'][size] | |
for (valueColumn in size['valueColumns']) { | |
var valueColumn = size['valueColumns'][valueColumn] | |
var rate = ('rate' in valueColumn) ? valueColumn['rate'] : '' | |
var model = [region['region'], instanceType['type'], size['size'], valueColumn['prices']['USD'], valueColumn['name'], rate] | |
items.push(model) | |
} | |
} | |
} | |
} | |
return items | |
} | |
function loadSource(source) { | |
var spreadsheet = SpreadsheetApp.getActive(); | |
data = parsePrices(source) | |
sheet = spreadsheet.getSheetByName(source) | |
if (sheet == null) { | |
sheet = spreadsheet.insertSheet(source) | |
} else { | |
sheet.clearContents() | |
} | |
sheet.setFrozenRows(1) | |
sheet.appendRow(["Region", "Instance Type", "Size", "Cost USD", "Rate Type", "Rate Term"]) | |
for (i in data) { | |
sheet.appendRow(data[i]) | |
} | |
} | |
function linux_od() { | |
loadSource('linux-od') | |
} | |
function linux_ri_light() { | |
loadSource('linux-ri-light') | |
} | |
function linux_ri_medium() { | |
loadSource('linux-ri-medium') | |
} | |
function linux_ri_heavy() { | |
loadSource('linux-ri-heavy') | |
} |
This comment has been minimized.
This comment has been minimized.
awesome, is there any way to get previous generation instance pricing ? |
This comment has been minimized.
This comment has been minimized.
It works perfect! |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This comment has been minimized.
Excellent, thank you for this!