Cloudability logo
Registration is Now Open for CloudyCon 2019
Spend Visibility

Unleash your Cloudability data with Google Sheets

By Graham Murphy on March 4, 2014

Do you regularly find yourself exporting cost and usage data from Cloudability? Now you can make the process easier with our Cloudability data API.

We love data

I wouldn’t work at Cloudability if I didn’t like a bit of data. After all, that’s exactly what we’re about—providing you with all the data you need to make great decisions about your cloud spending. Cloudability even gives you the ability to use your cost data anywhere through reporting exports and a robust API.

Therein lies the issue: I despise the process of exporting/importing via CSV, and I’m far too crummy of a developer to efficiently pull data from our API in a scripted manner. After a bit of tinkering, I found a way to have a dynamic set of data from Cloudability in a spot where I can easily compare it to other datasets. The key lies in Google Sheets’ importData function.

Pulling your dynamic data set

  1. Enable API access for your Cloudability account and take note of your access key.api token

  2. Create a report in Cloudability with the data you are looking for.Spend_by_Product__7_days_

  3. Combine the following:

    The base URL for a csv export (including your access key):

    https://app.cloudability.com/api/1/reporting/cost/run.csv?auth_token=your_access_key

    In this example, we’ve used a AWS Cost report; for an EC2 Usage report, just remove “/cost” from the url.

    The URL parameters from the report you just generated:

    &start_date=2014-02-01&end_date=2014-02-07&dimensions=service_name&metrics=invoiced_cost&sort_by=invoiced_cost&order=desc&title=Spend%20by%20Product%20(7%20days)&category=Invoice%20Costs&id=1501&saveable=true&shared=true&owned_by_user=true&custom=true

    The resulting URL:  

    https://app.cloudability.com/api/1/reporting/cost/run.csv?auth_token=your_access_key&start_date=2014-02-01&end_date=2014-02-07&dimensions=service_name&metrics=invoiced_cost&sort_by=invoiced_cost&order=desc&title=Spend%20by%20Product%20(7%20days)&category=Invoice%20Costs&id=1501&saveable=true&shared=true&owned_by_user=true&custom=true

  4. Toss your newly created URL into a Google Sheet using the IMPORTDATA function, i.e. =IMPORTDATA(“your_url_here”)

You’ll end up with something like this:Untitled_spreadsheet_-_Google_Sheets_and_Slack

 

The best part? Google refreshes the data every hour or so, so even if you’ve used a dynamic date range (e.g., Last 7 Days) in your report, the data will be close to real time.

If you want to take it to the next level, check out our API docs.

Being in the know feels great