Google Script for Excel and AWS instance pricing + Reserved Instances

AWS.js

/**=
 * 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')
}