Solved: Visualizing AWS Billing Costs in Google Sheets via API

#devops #programming #tutorial #cloud

🚀 Executive Summary

TL;DR: Manually managing AWS billing data in spreadsheets is inefficient and costly SaaS alternatives exist. This solution automates fetching AWS Cost Explorer data into Google Sheets using Python, boto3, and gspread, enabling custom, cost-effective visualization and analysis.

🎯 Key Takeaways

Programmatic access to AWS Cost Explorer data requires a dedicated IAM user with ce:GetCostAndUsage and related permissions, adhering to the principle of least privilege.
Secure authentication to Google Sheets for data ingestion is achieved via a Google Service Account, requiring a JSON key file and ‘Editor’ access granted to the target Google Sheet.
A Python script leveraging boto3 for AWS Cost Explorer API interaction (get\_cost\_and\_usage) and gspread for writing data to Google Sheets forms the core of this automated billing visualization pipeline.

Visualizing AWS Billing Costs in Google Sheets via API

Introduction

In the fast-paced world of cloud computing, managing and understanding your AWS expenditures can often feel like navigating a labyrinth.

While the AWS Billing Console provides a wealth of information, extracting and visualizing this data in a custom, flexible, and repeatable manner for deeper analysis can be a cumbersome, manual process.

Exporting CSVs and manually importing them into spreadsheets is not only time-consuming but also prone to errors and lacks the agility required for timely financial insights.

SaaS solutions exist, but they come with their own costs and might not always align with your specific reporting needs or data governance policies.

What if you could pull your AWS billing data directly into a familiar tool like Google Sheets, automate the process, and build custom dashboards without incurring extra subscription fees?

This tutorial from TechResolve will guide SysAdmins, Developers, and DevOps Engineers through building a robust, automated solution to fetch AWS billing costs using the AWS Cost Explorer API and push that data into Google Sheets via its API.

You’ll gain the power to create bespoke visualizations, perform ad-hoc analysis, and share insights seamlessly with your team, all while keeping costs in check and minimizing manual effort.

Prerequisites

Before we dive into the implementation, ensure you have the following:

An AWS Account: With permissions to access Cost Explorer data.
An AWS IAM User: With programmatic access (Access Key ID and Secret Access Key) and an attached policy allowing ce:GetCostAndUsage.
A Google Cloud Project: To enable the Google Sheets API and create a service account.
Google Sheets API Enabled: Within your Google Cloud Project.
Google Service Account: With a generated JSON key file. This account will need editor access to your target Google Sheet.
A Google Sheet: Created and ready to receive data. Make sure it’s shared with the email address of your Google Service Account.
Python 3.x: Installed on your local machine or a server where you’ll run the script.
pip: Python’s package installer, usually included with Python 3.x.

Step-by-Step Guide

Step 1: Set Up AWS IAM User and Permissions

To programmatically access AWS billing data, we need an IAM user with specific permissions.

It’s a best practice to create a dedicated IAM user with the least privilege required, rather than using root credentials.

Navigate to the AWS IAM console.
Go to “Users” and click “Add user”.
Provide a user name (e.g., techresolve-billing-api-user).
Select “Programmatic access” as the access type.
On the “Permissions” screen, choose “Attach existing policies directly” and click “Create policy”.
In the new tab, select the “JSON” tab and paste the following policy. This grants read-only access to Cost Explorer data.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "ce:GetCostAndUsage",
                "ce:GetDimensionValues",
                "ce:GetCostForecast",
                "ce:GetTags"
            ],
            "Resource": "*"
        }
    ]
}

Give your new policy a name (e.g., TechResolveCostExplorerReadOnly) and create it.

Then, attach this newly created policy to your IAM user.

Complete the user creation process. You will be presented with the Access Key ID and Secret Access Key.

Store these securely; you will not be able to retrieve the Secret Access Key again.

You can configure these credentials as environment variables (AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY) or in your AWS CLI configuration file (~/.aws/credentials).

Step 2: Configure Google Cloud Project and Sheets API

Next, we need to enable the Google Sheets API and create a service account that our Python script will use to authenticate and write data.

Go to the Google Cloud Console.
Create a new project or select an existing one.
Navigate to “APIs & Services” > “Library”.
Search for “Google Sheets API” and enable it for your project.
Go to “APIs & Services” > “Credentials”.
Click “Create Credentials” > “Service Account”.
Provide a service account name (e.g., techresolve-sheets-writer) and description.
Grant the service account a role. For basic writing to a spreadsheet, “Project” > “Editor” is sufficient, or a more granular role like “Sheets” > “Google Sheets Editor”.
In the next step, create a new JSON key for the service account. This will download a JSON file to your computer. Keep this file secure and remember its path. We’ll refer to it as service_account_key.json.
Finally, open your target Google Sheet. Click “Share” and paste the email address of your newly created service account. Grant it “Editor” access.

Step 3: Develop the Python Script for Data Extraction and Ingestion

Now for the core logic. We’ll write a Python script that uses boto3 to fetch AWS Cost Explorer data and gspread to write it to Google Sheets.

First, install the necessary Python libraries:

pip install boto3 gspread

Here’s a comprehensive Python script. Remember to replace placeholders like YOUR_GOOGLE_SHEET_NAME and YOUR_SERVICE_ACCOUNT_KEY_PATH.

import boto3
import gspread
from datetime import datetime, timedelta

# --- Configuration ---
# Your Google Sheet details
GOOGLE_SHEET_NAME = "My AWS Billing Dashboard"
GOOGLE_WORKSHEET_NAME = "Monthly Costs"
SERVICE_ACCOUNT_KEY_PATH = "./service_account_key.json" # Path to your downloaded JSON key

# AWS Cost Explorer Configuration
AWS_REGION = "us-east-1" # Or your preferred AWS region
GRANULARITY = "MONTHLY" # DAILY, MONTHLY
METRICS = ["UnblendedCost"] # Can also include "BlendedCost", "UsageQuantity"
GROUP_BY = [{"Type": "DIMENSION", "Key": "SERVICE"}] # Group by service, linked account, etc.

# Time Period (Last month's data)
END_DATE = datetime.now().strftime('%Y-%m-%d') # Today
START_DATE = (datetime.now() - timedelta(days=30)).replace(day=1).strftime('%Y-%m-%d') # Start of last full month

# --- AWS Data Fetching Function ---
def get_aws_cost_data():
    """Fetches AWS cost and usage data from Cost Explorer."""
    client = boto3.client("ce", region_name=AWS_REGION)

    response = client.get_cost_and_usage(
        TimePeriod={
            "Start": START_DATE,
            "End": END_DATE
        },
        Granularity=GRANULARITY,
        Metrics=METRICS,
        GroupBy=GROUP_BY
    )

    data = []
    # Extract headers from the first group's keys if available
    headers = ["TimePeriod", "Service"] + [metric.strip() for metric in METRICS]
    data.append(headers)

    for item in response["ResultsByTime"]:
        time_period = f"{item['TimePeriod']['Start']} to {item['TimePeriod']['End']}"
        for group in item["Groups"]:
            service_name = group["Keys"][0]
            cost_value = float(group["Metrics"]["UnblendedCost"]["Amount"])
            data.append([time_period, service_name, cost_value])

    # Handle pagination if more data exists (for large accounts/longer periods)
    next_page_token = response.get('NextPageToken')
    while next_page_token:
        response = client.get_cost_and_usage(
            NextPageToken=next_page_token,
            TimePeriod={
                "Start": START_DATE,
                "End": END_DATE
            },
            Granularity=GRANULARITY,
            Metrics=METRICS,
            GroupBy=GROUP_BY
        )
        for item in response["ResultsByTime"]:
            time_period = f"{item['TimePeriod']['Start']} to {item['TimePeriod']['End']}"
            for group in item["Groups"]:
                service_name = group["Keys"][0]
                cost_value = float(group["Metrics"]["UnblendedCost"]["Amount"])
                data.append([time_period, service_name, cost_value])
        next_page_token = response.get('NextPageToken')

    return data

# --- Google Sheets Update Function ---
def update_google_sheet(data_to_write):
    """Authenticates with Google Sheets and updates the specified worksheet."""
    try:
        # Authenticate with Google using the service account key
        gc = gspread.service_account(filename=SERVICE_ACCOUNT_KEY_PATH)

        # Open the spreadsheet by name
        spreadsheet = gc.open(GOOGLE_SHEET_NAME)

        # Select the worksheet by name
        # If the worksheet doesn't exist, create it (optional, but robust)
        try:
            worksheet = spreadsheet.worksheet(GOOGLE_WORKSHEET_NAME)
        except gspread.exceptions.WorksheetNotFound:
            print(f"Worksheet '{GOOGLE_WORKSHEET_NAME}' not found, creating it...")
            worksheet = spreadsheet.add_worksheet(title=GOOGLE_WORKSHEET_NAME, rows="100", cols="20")


        # Clear existing content and write new data
        worksheet.clear()
        worksheet.update('A1', data_to_write)
        print(f"Successfully updated Google Sheet '{GOOGLE_SHEET_NAME}' - Worksheet '{GOOGLE_WORKSHEET_NAME}'")

    except Exception as e:
        print(f"Error updating Google Sheet: {e}")

# --- Main Execution ---
if __name__ == "__main__":
    print("Fetching AWS cost data...")
    aws_data = get_aws_cost_data()

    if aws_data:
        print(f"Fetched {len(aws_data) - 1} data rows from AWS.")
        update_google_sheet(aws_data)
    else:
        print("No AWS data fetched.")

Logic Explanation:

Configuration: Defines variables for Google Sheet names, service account key path, AWS region, and how to group/aggregate billing data (e.g., monthly by service).
get_aws_cost_data():
- Initializes a boto3 client for the AWS Cost Explorer service.
- Calls get_cost_and_usage with the specified time period, granularity, metrics, and grouping.
- Iterates through the response to extract the time period, service name, and the unblended cost amount.
- Handles potential pagination using NextPageToken to ensure all data is retrieved.
- Formats the data into a list of lists, suitable for Google Sheets, with headers included.
update_google_sheet():
- Authenticates with Google Sheets using the gspread.service_account() method, pointing to your JSON key file.
- Opens the specified Google Sheet and then the target worksheet. If the worksheet doesn’t exist, it attempts to create it (a robust feature).
- Clears the existing data in the sheet (you could modify this to append if you want historical records without clearing).
- Writes the entire data_to_write list, including headers, starting from cell A1.
Main Execution Block: Calls the AWS data fetching function, and if data is retrieved, passes it to the Google Sheets update function.

Step 4: Run the Script and Verify

Save the Python script (e.g., as aws_billing_to_sheets.py).

Ensure your service_account_key.json is in the same directory or adjust the SERVICE_ACCOUNT_KEY_PATH variable accordingly.

Execute the script from your terminal:

python aws_billing_to_sheets.py

After execution, open your Google Sheet. You should see the monthly AWS cost data, grouped by service, populated in the specified worksheet.

You can then use Google Sheets’ built-in features (pivot tables, charts, conditional formatting) to visualize and analyze your costs.

Step 5: Automation and Visualization Ideas

To make this solution truly powerful, automate its execution. You can use:

Cron Job: On a Linux server or EC2 instance, schedule the Python script to run daily or monthly.
AWS Lambda: Package your Python script and its dependencies into a Lambda function, triggered by CloudWatch Events (e.g., a scheduled rule). This provides a serverless, cost-effective way to run the script.
GitHub Actions / GitLab CI: Integrate the script into your CI/CD pipeline for regular updates.

Once the data is in Google Sheets, unleash its potential:

Create Pivot Tables to break down costs by service, time period, or other dimensions.
Generate Charts (bar charts for service breakdown, line charts for trend analysis) to visualize cost fluctuations.
Implement Conditional Formatting to highlight unusually high costs or significant changes.
Build a comprehensive Dashboard sheet that pulls data from your raw data sheet using formulas like IMPORTRANGE or direct cell references.

Common Pitfalls

Even with careful planning, you might encounter some issues. Here are a couple of common ones:

AWS Permissions Denied (AccessDeniedException):If your script fails with an “AccessDenied” error from AWS, it’s almost certainly an issue with the IAM policy attached to your AWS user. Double-check that the policy includes ce:GetCostAndUsage. Ensure your credentials (environment variables or ~/.aws/credentials) are correctly configured for the IAM user you created in Step 1.
Google Sheets Authentication/Permissions Issues:Errors like “WorksheetNotFound” or general authentication failures usually point to:
1. Incorrect SERVICE_ACCOUNT_KEY_PATH.
2. The Google Sheets API is not enabled in your Google Cloud Project.
3. The target Google Sheet has not been shared with the service account’s email address, or the service account does not have “Editor” permissions.
4. The worksheet name in GOOGLE_WORKSHEET_NAME doesn’t match an existing sheet (if not set to auto-create).

Verify all these points.

AWS Cost Explorer Rate Limits:AWS Cost Explorer APIs have rate limits. If you’re querying a very large amount of historical data or making frequent requests, you might hit these limits. The current limits are generally generous for most use cases (e.g., 20 requests per second for GetCostAndUsage), but be mindful if you plan on complex, high-frequency automation. Implement exponential backoff and retry logic in your script for robustness, though for a monthly update, it’s rarely an issue.

Conclusion

You’ve successfully built an automated pipeline to extract AWS billing data and visualize it within Google Sheets.

This not only eliminates tedious manual work but also empowers you with granular control over your cost reporting and analysis.

By leveraging the AWS Cost Explorer API and the Google Sheets API, you’ve created a flexible, cost-effective solution tailored to your organization’s needs.

From here, the possibilities are endless. Consider enhancing your script to:

Fetch data for multiple accounts (using AWS Organizations).
Group by additional dimensions like Tags, Usage Type, or linked accounts.
Integrate with other data sources in Google Sheets.
Build more sophisticated reporting and alerting mechanisms directly within your spreadsheet.

Keep iterating, keep optimizing, and take control of your cloud spend with data-driven insights. Happy visualizing!