Skip to content

Instantly share code, notes, and snippets.

@zircote

zircote/AWS.js

Last active Jan 31, 2020
Embed
What would you like to do?
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')
}
@aegixx

This comment has been minimized.

Copy link

@aegixx aegixx commented Nov 20, 2015

Excellent, thank you for this!

@maheshgawali

This comment has been minimized.

Copy link

@maheshgawali maheshgawali commented Feb 21, 2018

awesome, is there any way to get previous generation instance pricing ?

@RanMaroely

This comment has been minimized.

Copy link

@RanMaroely RanMaroely commented Apr 25, 2018

It works perfect!
Though, is there any updates for the new instance generations? I couldn't find any reserved m5 for example...
Thanks,
Ran

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment