Build a Cloud Cost Control Plane with Google Sheets and Gemini

May 05, 2026

The promise of simple cloud computing has morphed into a multi-cloud financial hydra. Learn how to tame cost sprawl before it turns your engineering agility into a black hole.

The Challenge of Multi-Cloud Cost Sprawl

The cloud promised utility-style computing: a simple, pay-as-you-go model that would liberate us from the shackles of on-premise capital expenditure. And for a while, it was true. But as our architectures evolved, embracing the best-of-breed services from AWS, GCP, Azure, and others, that simple utility bill has morphed into a complex, multi-headed digital hydra. We’re living in the era of multi-cloud, and with it comes the insidious problem of cost sprawl—a chaotic, often invisible, and relentlessly growing expenditure that eats into margins and turns engineering agility into a financial black hole.

This isn’t just about a high bill at the end of the month. It’s about the loss of control. It’s the staging environment a developer spun up for a PoC and forgot about, now costing thousands. It’s the unoptimized data transfer between regions. It’s the subtle but significant pricing differences between a c6i.large and an n2-standard-4 for a workload that could run on either. Each one is a small leak, but together they create a flood.

Why traditional billing dashboards fall short

Every cloud provider offers a native billing dashboard. AWS has Cost Explorer, GCP has its Billing reports, and Azure has Cost Management. These tools are powerful in their own right, but in a multi-cloud reality, they are fundamentally flawed. They operate as walled gardens, offering a pristine view of their own little patch of land while being completely blind to the world outside.

This leads to several critical failures:

The Silo Effect: To get a complete picture of your spending, you’re forced into a Sisyphean routine of context-switching. You open three browser tabs, export three different CSVs, and attempt to manually stitch together a coherent narrative. It’s inefficient, error-prone, and a colossal waste of time for highly-paid engineers.
**Reactive by Design: These dashboards are excellent tools for forensic accounting. They can tell you, with granular detail, precisely how you blew your budget last month. They are lagging indicators in a world that operates in milliseconds. By the time a cost anomaly is prominent enough to show up on a monthly trend graph, the damage is already done.
**Divorced from Business Context: A native dashboard will tell you that service-mesh-proxy-us-east-1 cost you $4,200. What it won’t tell you is that this service powers the checkout flow for your most profitable product line, or that it belongs to the “Phoenix” team. Without this business context—the why behind the spend—the data is just noise. You can’t make intelligent trade-offs between cost and value if you can’t connect the two.

The need for a centralized real-time control plane

What we need is not another dashboard. Dashboards are passive. They are read-only views of the past. What we need is a control plane.

In infrastructure, a control plane is the system that manages and orchestrates the state of the system. A cost control plane applies the same principle to your cloud finances. It’s a single, centralized hub that doesn’t just display data but empowers you to understand, decide, and act upon it in near-real-time.

This ideal control plane must be:

Unified: It must ingest and normalize cost data from every provider, every account, and every service you use, presenting it in a single, coherent interface.
Context-Aware: It must enrich raw billing data with your internal business logic. It should know which costs map to which team, which project, which feature, and even which customer.
Proactive: It must move beyond historical reporting to anomaly detection and forecasting, alerting you to deviations from the norm the moment they happen, not weeks later.
Actionable: Crucially, it must close the loop. An insight is useless without a corresponding action. The control plane should facilitate decision-making, whether it’s by flagging an idle resource for termination or by providing a clear cost-benefit analysis for migrating a workload.

This might sound like a million-dollar enterprise SaaS platform. But what if we could build the core of this intelligent control plane using two tools you almost certainly already have access to?

That’s exactly what we’re going to do. We will build our cost control plane on a foundation that seems almost too simple to be true: Google Sheets, supercharged with the intelligence of Gemini.

Before you scoff, consider the genius of this combination.

Google Sheets is the ultimate, universally understood interface. It’s collaborative, infinitely flexible, and with [AI Powered Cover Letter [Automated Job Creation in Real Time Jobber and Google Sheets Integration from Gmail](https://votuduc.com/Automated-Job-Creation-in-Jobber-from-Gmail-p115606) Engine](https://votuduc.com/AI-Powered-Cover-Letter-Automated Quote Generation and Delivery System for Jobber-Engine-p111092), it’s a surprisingly powerful data aggregation and Automated Work Order Processing for UPS platform. It will serve as our centralized canvas, the single source of truth where all our multi-cloud cost data lives.
Gemini is the brain. It elevates our spreadsheet from a static grid of numbers into an interactive, intelligent analyst. We’ll leverage its advanced reasoning and natural language capabilities to query our data conversationally, automatically identify anomalies, summarize complex spending patterns, and even generate optimization recommendations.

Together, they form a potent, accessible, and customizable solution. We’re not just building a dashboard; we’re building a dynamic, AI-assisted partner in our journey to tame the chaos of multi-cloud cost sprawl. Let’s get started.

Solution Architecture: From API to Insight

Before we dive into the code, let’s zoom out and look at the blueprint for our cost control plane. The beauty of this solution lies in its simplicity and the powerful synergy between a few core Google services. We’re essentially building a serverless data pipeline that transforms raw billing numbers into a dynamic, intelligent dashboard, all with minimal infrastructure overhead.

At its heart, this is a classic ETL (Extract, Transform, Load) process, but with a modern, AI-powered twist. We extract data from the authoritative source (Cloud Billing APIs), transform it into a usable format using a lightweight script, and load it into a familiar interface (Google Sheets) where our AI analyst, Gemini, is waiting to help us make sense of it all.

Core components: Apps Script, Cloud Billing APIs, Google Sheets, and Gemini

Our architecture is composed of four key pillars. Each plays a distinct and critical role in bringing our cost control plane to life.

Google Cloud Billing APIs: This is our single source of truth for all cost data. We’ll primarily use the Cloud Billing API to programmatically fetch detailed, filterable cost and usage data for our projects. Think of it as the firehose of raw financial information about your cloud consumption.
Genesis Engine AI Powered Content to Video Production Pipeline: This is the engine and the orchestrator of our entire solution. Apps Script is a serverless JavaScript platform that lives within the Automatically create new folders in Google Drive, generate templates in new folders, fill out text automatically in new files, and save info in Google Sheets ecosystem. It will act as the “glue” that connects everything. We’ll use it to:
Schedule automated, recurring API calls to fetch data.
Authenticate securely with our Google Cloud project.
Process the raw JSON response from the API.
Write the cleaned-up data directly into our Google Sheet.
Google Sheets: This is far more than just a spreadsheet; it’s our database, our data warehouse, and our visualization canvas all in one.
Data Store: A dedicated sheet will act as our “raw data” log, storing the historical cost information pulled by Apps Script.
Analysis Layer: We’ll use pivot tables, formulas, and native charting tools to aggregate and summarize the data into meaningful KPIs.
Dashboard UI: A primary sheet will serve as our interactive dashboard, presenting key charts and metrics for an at-a-glance view of our cloud spend.
Gemini in Google Sheets: This is our on-demand FinOps analyst. Integrated directly into the Sheets experience, Gemini brings generative AI to our data. Instead of just looking at static charts, we can have a conversation with our data. We’ll use Gemini to:
Generate natural language summaries of spending trends.
Answer ad-hoc questions like, “Which service saw the biggest cost increase last month?”
Identify anomalies or outliers that might be missed by manual inspection.
Help create formulas or even suggest new ways to visualize the data.

The data workflow explained: Poll, Process, Analyze, Visualize

The data flows through our system in a simple, automated, and cyclical process. Here’s a step-by-step breakdown of how a piece of cost data travels from Google’s billing backend to an actionable insight on your screen.

Poll (Automated Fetch): It all starts with a time-driven trigger in [Architecting Multi Tenant AI Workflows in Building Modular Agentic Apps Script with Gemini Function Calling](https://votuduc.com/architecting-multi-tenant-ai-workflows-in-google-apps-script-p-20260321290501). On a schedule you define (e.g., every morning at 2 AM), the script wakes up. It authenticates with the Google Cloud Billing API using a service account and sends a request for the latest cost data from the previous day.
Process (Data Wrangling): The Billing API returns a structured, but often complex, JSON object. Our Apps Script code doesn’t just dump this into the sheet. It acts as a transformation layer, parsing the JSON, extracting the key fields we care about (like cost, service, project ID, and date), and organizing this information into a clean, flat, tabular format—perfect for a spreadsheet.
Load (Append to Sheet): The script then opens our target Google Sheet and appends these newly processed rows to our “Raw Data” tab. This creates a continuously growing, historical record of our daily cloud spend.
Analyze & Visualize (Insight Generation): The moment new data lands in the sheet, the magic happens.

Automated Dashboards: Pivot tables and charts on our main “Dashboard” tab, which are sourced from the “Raw Data” tab, automatically refresh to include the latest information. Your trend lines extend, and your daily spend charts update without any manual intervention.
AI-Powered Analysis: Now, you can open the sheet and use the Gemini side panel. You can highlight the new data and ask, “Summarize the key spending changes in this data” or “Create a pie chart showing cost by project for yesterday.” Gemini reads the data in your sheet and generates the insights or visualizations you asked for, turning your passive dashboard into an active analytical tool.

Prerequisites: What you need before you start

To follow along and build this solution, you’ll need to have a few things set up and ready to go. Please ensure you have the following before proceeding to the next section.

A Google Cloud Project: You need an active GCP project with billing enabled. This is the project whose costs you intend to monitor.
Appropriate IAM Permissions: Your user account (or a dedicated service account that Apps Script will use) needs sufficient permissions to read billing information. The key role is Billing Account Viewer (roles/billing.viewer) on your Cloud Billing account.
Enabled APIs: In your Google Cloud Project, you must enable the Cloud Billing API. You can do this by navigating to “APIs & Services” > “Library” in the Google Cloud Console and searching for it.
A Google Account: This can be a standard Gmail account or a AC2F Streamline Your Google Drive Workflow account. You will use this account to own the Google Sheet and the associated Apps Script project.
Gemini for Automated Client Onboarding with Google Forms and Google Drive. Enabled: To use the AI analysis features, Gemini must be enabled for your Automated Discount Code Management System account. Check with your Workspace administrator if you’re unsure. This may be a paid add-on depending on your plan.
Basic Familiarity: A working knowledge of Google Sheets and a beginner’s understanding of JavaScript will be very helpful. We’ll provide all the code, but knowing the basics will help you customize it later.

Step 1: Setting Up Your Google Sheets Dashboard

Before we can automate, we must architect. Our Google Sheet is the foundation of this entire system. It’s not just a spreadsheet; it’s our database, our user interface, and the canvas for our cost analysis. A well-structured sheet is the difference between a powerful, scalable dashboard and a chaotic mess. Let’s build it right from the start.

Designing the sheet layout: Raw Data, Processed Metrics, and Anomaly Summary

A clean separation of concerns is a core principle in software engineering, and it applies just as well to spreadsheet design. We will create three distinct sheets (tabs) within our Google Sheet, each with a specific purpose. This modular approach makes the system easier to manage, debug, and extend.

RawBillingData: This is the landing zone. All the detailed, unaltered billing data exported from your cloud provider’s billing account (e.g., a Google Cloud billing export to BigQuery) will be programmatically dumped here. The key is that this data remains pristine. We never manually edit this sheet; it’s our single source of truth.

Create a new sheet and name it RawBillingData. The columns will depend on your specific billing export, but a typical structure from a Google Cloud export would look like this:

| :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- |

| … | … | … | … | … | … | … | … |

ProcessedMetrics: Raw data is comprehensive but noisy. To spot trends, we need to aggregate it. This sheet will use formulas to pivot, summarize, and calculate key metrics from the RawBillingData sheet. This is where we’ll calculate things like daily cost per project, week-over-week changes, and moving averages.

Create a second sheet named ProcessedMetrics. Here, we’ll use powerful spreadsheet functions like QUERY to transform the raw data. For example, to get a daily summary of costs per project, your header might look like this, with a single QUERY formula in cell A2 populating the entire table.

| :--- | :--- | :--- | :--- | :--- |

| 2023-10-26 | my-prod-project | 150.75 | 145.50 | +3.6% |

| 2023-10-26 | my-staging-project | 25.10 | 22.00 | +14.1% |

| … | … | … | … | … |

A sample formula for cell A2 might look like this:


=QUERY(RawBillingData!A:H, "SELECT E, SUM(G) WHERE G > 0 GROUP BY E PIVOT TO_DATE(D)", 1)

(Note: You will adapt this query and add more columns for advanced metrics later.)

**AnomalySummary: This is the action center. While the other sheets show us what is happening, this sheet will highlight why it might be happening and what’s most important. It will be populated automatically by our Apps Script after Gemini analyzes the data from ProcessedMetrics. It’s a clean, high-level summary designed for quick reviews and decision-making.

Create a final sheet named AnomalySummary. Its columns will be populated by our script, not by formulas.

| :--- | :--- | :--- | :--- | :--- | :--- | :--- |

| … | … | … | … | … | … | … |

With this three-sheet structure, we have a logical data flow: raw data comes in, it’s processed into meaningful metrics, and significant anomalies are surfaced for analysis and action.

Creating the Apps Script project within your Sheet

Google Apps Script is the magic that turns our passive spreadsheet into an active control plane. It’s a cloud-based JavaScript platform that lets us automate tasks across Automated Email Journey with Google Sheets and Google Analytics. Let’s create the script project that will live inside our sheet.

From your newly created Google Sheet, navigate to the menu bar.
Click Extensions > Apps Script.
This will open the Apps Script editor in a new browser tab. The editor is directly bound to your spreadsheet.
By default, you’ll see a file named Code.gs with an empty myFunction block. This is where we will write our automation logic.
At the top left, click on Untitled project. Rename it to something descriptive, like Cloud Cost Control Plane.

You are now inside the development environment for your dashboard’s engine. Any code you write here can read from and write to your sheets, connect to external APIs, and run on automated triggers.

Configuring necessary permissions and API keys

For our script to do its job, it needs permission. It needs to talk to other Google Cloud services—specifically, the [Building Self Correcting Agentic Workflows with Building Self-Correcting Agentic Workflows with Vertex AI](https://votuduc.com/building-self-correcting-agentic-workflows-with-vertex-ai-p-20260321542526) API to access Gemini and potentially BigQuery to fetch the raw billing data. This is a critical security step.

1. Associate the Script with a Google Cloud Project

Your Apps Script project runs with certain permissions, but to use advanced services like Vertex AI, it must be explicitly linked to a standard Google Cloud Platform (GCP) project. This is the project where you have billing enabled and where the necessary APIs are activated.

In the Apps Script editor, click on the Project Settings (⚙️) icon in the left-hand navigation pane.
Scroll down to the Google Cloud Platform (GCP) Project section.
You’ll see a message that the script is currently using a default project. Click the Change project button.
Enter the Project Number of your GCP project. You can find this on the main dashboard of the Google Cloud Console.
Click Set project.

2. Enable the Vertex AI API

Now that your script is linked to your GCP project, you must enable the specific API it needs to call.

Navigate to the Google Cloud Console.
Ensure you have the correct project selected (the one you just linked).
In the search bar, type “Vertex AI API” and select it.
On the API page, click the Enable button if it is not already enabled.

3. Authenticate Securely (No Hardcoded API Keys!)

A major advantage of running Apps Script within the Google Cloud ecosystem is seamless and secure authentication. By linking the script to a GCP project and running it as yourself, the script automatically inherits the necessary permissions via OAuth. You do not need to generate and paste an API key into your code. This is a more secure and robust practice.

When you first run a function that calls a Google Cloud service, Apps Script will present a pop-up authorization flow. You will be asked to grant your script permission to:

View and manage your spreadsheets in Google Drive.
Connect to an external service.
Act on your behalf to access Google Cloud Platform data (this is the key for Vertex AI).

You must approve these permissions for the script to function. This one-time approval establishes a secure OAuth token that the script uses for all subsequent executions.

Step 2: Fetching GCP and AWS Cost Data with Apps Script

With our Google Sheet primed and ready, it’s time to roll up our sleeves and write the code that will serve as the engine for our cost control plane. We’ll use Google Apps Script, the JavaScript-based platform that lives inside Automated Google Slides Generation with Text Replacement, to act as our serverless orchestrator. It will reach out to the GCP and AWS APIs, pull in the raw cost data, and populate our ‘Raw Data’ sheet.

Let’s open the Apps Script editor by going to Extensions > Apps Script in your Google Sheet.

Connecting to the GCP Billing Export API

The most reliable and detailed source for GCP cost data is a BigQuery billing export. While you can use the Cloud Billing API directly, the BigQuery export is the canonical source of truth, containing granular, un-aggregated data. Our script will query this BigQuery table directly.

Prerequisite: You must have a Cloud Billing export configured to send data to a BigQuery dataset. If you haven’t done this, follow the official Google Cloud documentation to set it up. It can take up to 24 hours for the first export to appear.

First, we need to enable the BigQuery API within our Apps Script project.

In the Apps Script editor, click the + icon next to “Services”.
Select “BigQuery API” and click “Add”.

Apps Script handles the OAuth 2.0 flow for Google services seamlessly. When you run the script for the first time, it will prompt you for permission to access your BigQuery data on your behalf.

Now, let’s write the function to query our billing data. The following SQL query pulls daily costs per service for a given project. We also add a static ‘GCP’ column to help us differentiate the data later.


/**

* Fetches cost data from the GCP Billing Export table in BigQuery.

* @returns {Array<Array<any>>} A 2D array of cost data rows.

*/

function getGcpCostData() {

// --- CONFIGURATION ---

// Replace with your GCP project ID and BigQuery table details

const projectId = 'your-gcp-project-id';

const bqTableReference = 'your_billing_dataset.gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX';

// ---------------------

const sqlQuery = `

SELECT

CAST(usage_start_time AS DATE) AS usage_date,

service.description AS service,

SUM(cost) AS total_cost,

project.id AS project_id,

'GCP' AS cloud_provider

FROM

\`${projectId}.${bqTableReference}\`

WHERE

cost > 0

AND CAST(usage_start_time AS DATE) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)

GROUP BY

usage_date,

service,

project_id

ORDER BY

usage_date DESC;

`;

const request = {

query: sqlQuery,

useLegacySql: false

};

try {

let queryResults = BigQuery.Jobs.query(request, projectId);

const jobId = queryResults.jobReference.jobId;

// Poll for job completion

let sleepTimeMs = 500;

while (!queryResults.jobComplete) {

Utilities.sleep(sleepTimeMs);

sleepTimeMs *= 2; // Exponential backoff

queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);

}

const rows = queryResults.rows;

if (!rows) {

console.log('No GCP cost data returned.');

return [];

}

// The BigQuery API returns data in a specific format { f: [{ v: 'value' }] }.

// We need to parse it into a simple 2D array.

const data = rows.map(row => row.f.map(cell => cell.v));

console.log(`Successfully fetched ${data.length} rows from GCP.`);

return data;

} catch (err) {

console.error('ERROR fetching GCP data: ' + err.message);

// Return an empty array with the correct number of columns to prevent errors downstream

return [['ERROR', err.message, 0, 'GCP', 'ERROR']];

}

}

Replace the projectId and bqTableReference placeholders with your specific details. This function executes the query, waits for it to complete, and then parses the results into a clean, two-dimensional array ready to be written to our sheet.

Authenticating and polling the AWS Cost Explorer API

Connecting to AWS requires a bit more manual setup, as we’re stepping outside the Google ecosystem. We’ll use the AWS Cost Explorer API, which requires requests to be signed with AWS Signature Version 4. This is a security protocol to verify the identity of the requester.

Prerequisite: You need an IAM User in your AWS account with programmatic access. This user must have an Access Key ID and a Secret Access Key. Grant it the ce:GetCostAndUsage permission via a policy.

Security First: Storing Credentials

Never, ever hardcode your AWS credentials directly in your script. We’ll use Apps Script’s PropertiesService to store them securely.

In the Apps Script editor, go to File > Project Properties > Script Properties.
Add two properties:

AWS_ACCESS_KEY_ID: Your IAM user’s access key.
AWS_SECRET_ACCESS_KEY: Your IAM user’s secret key.

Now, we can access them in our script using PropertiesService.getScriptProperties().getProperty('PROPERTY_NAME').

Implementing AWS Signature V4

Since we don’t have the luxury of an AWS SDK in Apps Script, we must implement the signing process ourselves. The logic is complex, but the function below handles it for you. You don’t need to understand every line, but in essence, it creates a canonical request, generates a signature based on your secret key, and formats the necessary authorization headers.


/**

* A helper function to generate AWS Signature Version 4 headers.

* This is complex but necessary for authenticating with AWS APIs from Apps Script.

* @param {string} service - The AWS service (e.g., 'ce' for Cost Explorer).

* @param {string} region - The AWS region (e.g., 'us-east-1').

* @param {string} amzDate - The request date in 'YYYYMMDDTHHMMSSZ' format.

* @param {string} dateStamp - The request date in 'YYYYMMDD' format.

* @param {string} payload - The JSON string of the request body.

* @returns {Object} An object containing the required headers for the AWS API call.

*/

function getAwsSignatureV4Headers(service, region, amzDate, dateStamp, payload) {

const scriptProperties = PropertiesService.getScriptProperties();

const accessKey = scriptProperties.getProperty('AWS_ACCESS_KEY_ID');

const secretKey = scriptProperties.getProperty('AWS_SECRET_ACCESS_KEY');

if (!accessKey || !secretKey) {

throw new Error("AWS credentials not found in Script Properties. Please set AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY.");

}

const method = 'POST';

const host = `${service}.${region}.amazonaws.com`;

const canonicalUri = '/';

const canonicalQuerystring = '';

const canonicalHeaders = `host:${host}\nx-amz-date:${amzDate}\nx-amz-target:AWSInsightsIndexService.GetCostAndUsage\n`;

const signedHeaders = 'host;x-amz-date;x-amz-target';

const payloadHash = Utilities.sha256Hex(payload);

const canonicalRequest = `${method}\n${canonicalUri}\n${canonicalQuerystring}\n${canonicalHeaders}\n${signedHeaders}\n${payloadHash}`;

const algorithm = 'AWS4-HMAC-SHA256';

const credentialScope = `${dateStamp}/${region}/${service}/aws4_request`;

const stringToSign = `${algorithm}\n${amzDate}\n${credentialScope}\n${Utilities.sha256Hex(canonicalRequest)}`;

const kDate = Utilities.computeHmacSha256Signature(Utilities.newBlob(`AWS4${secretKey}`).getBytes(), dateStamp);

const kRegion = Utilities.computeHmacSha256Signature(kDate, region);

const kService = Utilities.computeHmacSha256Signature(kRegion, service);

const kSigning = Utilities.computeHmacSha256Signature(kService, 'aws4_request');

const signature = Utilities.computeHmacSha256Signature(kSigning, stringToSign).map(byte => ('0' + (byte & 0xFF).toString(16)).slice(-2)).join('');

const authorizationHeader = `${algorithm} Credential=${accessKey}/${credentialScope}, SignedHeaders=${signedHeaders}, Signature=${signature}`;

return {

'Content-Type': 'application/x-amz-json-1.1',

'X-Amz-Target': 'AWSInsightsIndexService.GetCostAndUsage',

'X-Amz-Date': amzDate,

'Authorization': authorizationHeader

};

}

Fetching the Cost Data

With the authentication helper in place, we can now write the function to call the Cost Explorer API.


/**

* Fetches cost data from the AWS Cost Explorer API.

* @returns {Array<Array<any>>} A 2D array of cost data rows.

*/

function getAwsCostData() {

// --- CONFIGURATION ---

const service = 'ce';

const region = 'us-east-1'; // Cost Explorer is a global service, use us-east-1

// ---------------------

const now = new Date();

const endDate = now.toISOString().slice(0, 10);

const startDate = new Date(now.setDate(now.getDate() - 30)).toISOString().slice(0, 10);

const requestPayload = {

TimePeriod: {

Start: startDate,

End: endDate,

},

Granularity: 'DAILY',

Metrics: ['UnblendedCost'],

GroupBy: [

{

Type: 'DIMENSION',

Key: 'SERVICE',

},

],

};

const payload = JSON.stringify(requestPayload);

const amzDate = new Date().toISOString().replace(/[:-]|\.\d{3}/g, '');

const dateStamp = amzDate.substr(0, 8);

try {

const headers = getAwsSignatureV4Headers(service, region, amzDate, dateStamp, payload);

const url = `https://${service}.${region}.amazonaws.com/`;

const options = {

method: 'post',

headers: headers,

payload: payload,

muteHttpExceptions: true // Important to debug errors

};

const response = UrlFetchApp.fetch(url, options);

const responseCode = response.getResponseCode();

const responseBody = response.getContentText();

if (responseCode !== 200) {

console.error(`ERROR fetching AWS data: Status ${responseCode}, Body: ${responseBody}`);

return [['ERROR', `AWS API Error: ${responseCode}`, 0, 'AWS', 'ERROR']];

}

const json = JSON.parse(responseBody);

const resultsByTime = json.ResultsByTime;

// Normalize the nested AWS data structure into a flat 2D array

const data = [];

resultsByTime.forEach(day => {

const date = day.TimePeriod.Start;

if (day.Groups.length > 0) {

day.Groups.forEach(group => {

const serviceName = group.Keys[0];

const cost = parseFloat(group.Metrics.UnblendedCost.Amount);

// We don't have a project ID here, so we'll use the AWS Account ID.

// For simplicity, we'll leave it blank for now.

const accountId = 'N/A';

if (cost > 0) {

data.push([date, serviceName, cost, accountId, 'AWS']);

}

});

}

});

console.log(`Successfully fetched ${data.length} rows from AWS.`);

return data;

} catch (err) {

console.error('ERROR fetching AWS data: ' + err.message);

return [['ERROR', err.message, 0, 'AWS', 'ERROR']];

}

}

This function constructs the API request, calls our signing helper to get the right headers, makes the call using UrlFetchApp, and then carefully parses the nested JSON response into the same flat [Date, Service, Cost, Project/Account, Cloud] format we used for GCP.

Writing the script to parse and write data to your ‘Raw Data’ tab

Now for the final and most satisfying step: bringing it all together. We’ll create a main function that calls our GCP and AWS functions, combines their data, and writes it cleanly into our ‘Raw Data’ sheet.


/**

* Main function to orchestrate fetching data from all cloud providers

* and writing it to the 'Raw Data' sheet.

*/

function fetchAndWriteAllCostData() {

const sheetName = 'Raw Data';

const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

const sheet = spreadsheet.getSheetByName(sheetName);

if (!sheet) {

spreadsheet.insertSheet(sheetName);

console.log(`Sheet '${sheetName}' was not found and has been created.`);

}

// Show a toast message to the user that the process has started

spreadsheet.toast('Fetching cloud cost data... This may take a moment.', 'Status', -1);

// Fetch data from both providers in parallel

const gcpData = getGcpCostData();

const awsData = getAwsCostData();

// Combine the normalized data

const combinedData = gcpData.concat(awsData);

if (combinedData.length === 0) {

spreadsheet.toast('No data fetched. Check logs for errors.', 'Complete', 5);

console.log('No data to write to the sheet.');

return;

}

// Define headers

const headers = ['Date', 'Service', 'Cost', 'Project_Account', 'Cloud'];

// Clear existing data and write new data

sheet.clear();

sheet.getRange(1, 1, 1, headers.length).setValues([headers]).setFontWeight('bold');

sheet.getRange(2, 1, combinedData.length, combinedData[0].length).setValues(combinedData);

// Auto-resize columns for readability

for (let i = 1; i <= headers.length; i++) {

sheet.autoResizeColumn(i);

}

// Apply number formatting to the Cost column

sheet.getRange(2, 3, combinedData.length, 1).setNumberFormat('$#,##0.00');

spreadsheet.toast(`Successfully wrote ${combinedData.length} rows of cost data!`, 'Complete', 10);

console.log('Data write complete.');

}

This master function is what you’ll run. It clears the sheet to ensure fresh data, writes the headers, and then dumps the entire combined dataset in a single, efficient operation using setValues().

To run it, save your script (Ctrl + S or Cmd + S) and select fetchAndWriteAllCostData from the function dropdown at the top, then click Run. The first time, you will be prompted to authorize the script’s permissions. Review and allow them.

Finally, to make this a truly automated system, set up a trigger:

In the Apps Script editor, click the clock icon on the left for “Triggers”.
Click ”+ Add Trigger”.
Choose fetchAndWriteAllCostData to run.
Select “Time-driven” as the event source.
Choose a “Day timer” and a time that works for you (e.g., “2am - 3am”).
Click Save.

Your script will now run automatically every day, populating your sheet with the latest multi-cloud cost data without any manual intervention.

Step 3: Using Gemini for Automated Anomaly Detection

This is where the magic happens. Instead of setting up brittle, static alerts (e.g., “alert me if cost > $100”), we’ll leverage the contextual understanding of a large language model. Gemini can analyze trends, spot unusual patterns that a simple threshold would miss, and—most importantly—summarize its findings in plain English. This transforms our dashboard from a simple data repository into an intelligent advisory tool.

Structuring the cost data for analysis

Before we can ask Gemini for insights, we need to present our cost data in a clean, machine-readable format. The model works best with structured text, so simply pointing it at our spreadsheet isn’t an option. We’ll convert our relevant cost data into a simple, text-based format like CSV.

Our goal is to create a single string that contains the historical data we want to analyze. An Apps Script function can easily handle this by iterating through our Cost Data sheet.

Here’s a function that reads the last 30 days of data and formats it into a CSV string.


// In your Code.gs file

/**

* Reads the last 30 days of cost data and formats it as a CSV string for Gemini.

* @returns {string} A string containing the cost data in CSV format.

*/

function prepareDataForGemini() {

const ss = SpreadsheetApp.getActiveSpreadsheet();

const sheet = ss.getSheetByName('Cost Data');

// Get data for the last 30 days. Assumes data is sorted by date.

// In a real-world scenario, you might add more robust date filtering.

const lastRow = sheet.getLastRow();

const startRow = Math.max(2, lastRow - 29); // Get up to 30 rows of data

const range = sheet.getRange(startRow, 1, lastRow - startRow + 1, 3); // Columns A, B, C

const values = range.getValues();

// Add a header row for context

let csvString = "Date,Service,Cost\n";

// Convert rows to CSV format

values.forEach(row => {

// Format date as YYYY-MM-DD

const date = new Date(row[0]);

const formattedDate = date.toISOString().split('T')[0];

const service = row[1];

const cost = row[2];

// Append the formatted row to our string

csvString += `${formattedDate},"${service}",${cost}\n`;

});

return csvString;

}

When run, this function will produce a clean string that looks something like this, ready to be fed into our prompt:


Date,Service,Cost

2023-10-01,"Compute Engine",55.12

2023-10-01,"Cloud Storage",10.45

2023-10-02,"Compute Engine",56.01

2023-10-02,"BigQuery",22.89

...

Crafting the perfect prompt to ask Gemini for budget anomalies

[Prompt Engineering for Reliable Autonomous Workspace Agents for Reliable Autonomous Workspace Agents](https://votuduc.com/prompt-engineering-for-reliable-autonomous-workspace-agents-p-20260319404106) is the art and science of getting the desired output from an AI model. A well-crafted prompt is the difference between a vague, unhelpful response and a precise, actionable insight.

For our use case, a good prompt should include:

Persona: Tell the model what role to play (e.g., a “FinOps expert”).
Context: Explain what the data represents.
Task: Clearly state what you want it to do (identify anomalies).
Constraints: Guide the model on what to focus on (e.g., “top 3 most significant increases”) and what to ignore (“minor daily fluctuations”).
Output Format: This is crucial. We’ll instruct Gemini to return its findings in a structured JSON format so our script can easily parse it.

Here is the prompt we’ll use. We’ll build this as a template in our Apps Script code.


const PROMPT_TEMPLATE = `

You are an expert FinOps analyst responsible for monitoring cloud spending.

Analyze the following daily cloud cost data, provided in CSV format.

Your task is to identify the top 2-3 most significant cost anomalies or unexpected spending increases from the last 30 days. Focus on spikes that deviate from established patterns. Ignore normal, minor daily fluctuations.

For each anomaly you identify, provide a brief, one-sentence summary explaining the potential cause or observation.

Return your findings as a valid JSON array of objects. Each object in the array must have the following three keys:

- "service": The name of the service with the anomaly.

- "summary": Your one-sentence analysis of the anomaly.

- "cost_increase": An estimated string representing the cost increase (e.g., "~$50").

If you find no significant anomalies, return an empty array [].

Here is the cost data:

[DATA]

`;

Notice the [DATA] placeholder. Our script will replace this with the CSV string we generated in the previous step.

Making the API call to Gemini from Apps Script

Now it’s time to connect Google Sheets to the Gemini API. We’ll use Apps Script’s built-in UrlFetchApp service, which can make requests to any external API.

Prerequisites:

Link to a GCP Project: Your Apps Script project must be associated with a Google Cloud Platform project. Go to Project Settings > Google Cloud Platform (GCP) Project and link it.
Enable the Vertex AI API: In your linked GCP project, make sure the “Vertex AI API” is enabled.
Set OAuth Scopes: In your Apps Script editor, open the appsscript.json manifest file and add the necessary scopes.


{

"timeZone": "America/New_York",

"dependencies": {},

"exceptionLogging": "STACKDRIVER",

"runtimeVersion": "V8",

"oauthScopes": [

"https://www.googleapis.com/auth/script.external_request",

"https://www.googleapis.com/auth/cloud-platform",

"https://www.googleapis.com/auth/spreadsheets"

]

}

With the setup complete, here is the function to call the Gemini API:


// In your Code.gs file

/**

* Sends cost data to the Gemini API and returns the AI-generated analysis.

* @param {string} costDataCsv The cost data formatted as a CSV string.

* @returns {string | null} The JSON string response from Gemini, or null on error.

*/

function getGeminiAnomalies(costDataCsv) {

// Get your GCP Project ID from Project Settings

const projectId = 'your-gcp-project-id'; // <--- IMPORTANT: REPLACE WITH YOUR PROJECT ID

const location = 'us-central1'; // Or another supported region

const modelId = 'gemini-1.0-pro';

const apiEndpoint = `https://${location}-aiplatform.googleapis.com/v1/projects/${projectId}/locations/${location}/publishers/google/models/${modelId}:generateContent`;

// Replace the placeholder with our actual data

const fullPrompt = PROMPT_TEMPLATE.replace('[DATA]', costDataCsv);

const requestBody = {

"contents": [

{

"parts": [

{ "text": fullPrompt }

]

}

],

"generationConfig": {

"temperature": 0.2,

"topK": 1,

"topP": 1,

"maxOutputTokens": 2048,

}

};

const options = {

'method': 'post',

'contentType': 'application/json',

'headers': {

'Authorization': 'Bearer ' + ScriptApp.getOAuthToken()

},

'payload': JSON.stringify(requestBody),

'muteHttpExceptions': true // Prevents script from stopping on HTTP errors

};

try {

const response = UrlFetchApp.fetch(apiEndpoint, options);

const responseCode = response.getResponseCode();

const responseBody = response.getContentText();

if (responseCode === 200) {

const jsonResponse = JSON.parse(responseBody);

// Extract the text content, cleaning up markdown code fences (```json ... ```)

const content = jsonResponse.candidates[0].content.parts[0].text;

return content.replace(/```json\n|```/g, '').trim();

} else {

console.error(`API Error: ${responseCode} - ${responseBody}`);

return null;

}

} catch (e) {

console.error(`Failed to call Gemini API: ${e.toString()}`);

return null;

}

}

Parsing the AI-generated summary and updating the dashboard

The final piece of the puzzle is to take Gemini’s JSON response and write it to our “Dashboard” sheet. Because we explicitly asked for a JSON array, this process is reliable and simple.

We’ll create a main function, refreshDashboard, that ties everything together. It will prepare the data, call the Gemini API, parse the response, and update the sheet.


// In your Code.gs file

/**

* Main function to orchestrate the entire process of fetching and displaying anomalies.

*/

function refreshDashboard() {

const ss = SpreadsheetApp.getActiveSpreadsheet();

const dashboardSheet = ss.getSheetByName('Dashboard');

// Define the range where anomalies will be written

const anomalyRange = dashboardSheet.getRange('B10:D12');

// 1. Show a "loading" message

anomalyRange.clearContent();

dashboardSheet.getRange('B10').setValue('Analyzing costs with Gemini...');

// 2. Prepare the data

const costData = prepareDataForGemini();

// 3. Get insights from Gemini

const anomalyJsonString = getGeminiAnomalies(costData);

if (!anomalyJsonString) {

dashboardSheet.getRange('B10').setValue('Error fetching analysis from Gemini.');

return;

}

// 4. Parse the response and update the sheet

try {

const anomalies = JSON.parse(anomalyJsonString);

// Clear the loading message

anomalyRange.clearContent();

if (anomalies.length === 0) {

dashboardSheet.getRange('B10').setValue('No significant anomalies found.');

return;

}

// Create a 2D array for efficient writing to the sheet

const valuesToWrite = anomalies.map(anomaly => [

anomaly.service,

anomaly.summary,

anomaly.cost_increase

]);

// Write the data to the dashboard

dashboardSheet.getRange(10, 2, valuesToWrite.length, 3).setValues(valuesToWrite);

} catch (e) {

console.error(`Failed to parse Gemini's response: ${e.toString()}`);

console.log(`Raw response was: ${anomalyJsonString}`);

dashboardSheet.getRange('B10').setValue('Error parsing AI response.');

}

}

Now, you can run the refreshDashboard function directly from the Apps Script editor or connect it to a menu item or a trigger. When it completes, the “Key Cost Anomalies” section of your dashboard will be populated with intelligent, context-aware insights directly from Gemini.

Step 4: Automating the Workflow

A script that you have to manually run every morning is a chore, not a tool. The real power of this cost control plane comes from automation. We want this system to be a “set it and forget it” guardian of our cloud spend. In this step, we’ll configure our script to run on a schedule, add some resilience with error handling, and make the output immediately useful with some native Sheets visualization magic.

Setting up time-driven triggers for daily or hourly updates

Google Apps Script has a built-in scheduler, known as Triggers, that can execute your functions on a timer. This is perfect for our use case. We’ll set up a trigger to run our main function every morning, so the cost analysis is waiting for you with your first cup of coffee.

Here’s how to set it up:

Open the Apps Script Editor: From your Google Sheet, go to Extensions > Apps Script.
Navigate to Triggers: In the left-hand sidebar of the editor, click on the clock icon labeled “Triggers”.
Add a New Trigger: In the bottom-right corner, click the + Add Trigger button. This will open a configuration dialog.
Configure the Trigger: You’ll need to set the following options:

Choose which function to run: Select your main function, for example, fetchAndAnalyzeCosts.
Choose which deployment should run: Leave this as Head. This means it will always run the latest saved version of your script.
Select event source: Change this from the default “From spreadsheet” to Time-driven.
Select type of time-based trigger: You have several options. Day timer is ideal for a daily summary. If you need more granular, near-real-time updates, you could select Hour timer.
Select time of day: For a daily timer, choose a time that makes sense for your workflow. Something like 4am - 5am ensures the report is ready by the start of the business day.

Save and Authorize: Click Save. Google will likely ask you to authorize the script one more time to allow it to run on your behalf, even when you’re not there. Grant the necessary permissions.

That’s it. Your script will now run automatically every day at the time you specified, populating your sheet with fresh cost data and Gemini’s analysis without any manual intervention.

Implementing basic error handling and logging

What happens when your automated script fails? A temporary network hiccup, an API change, or an expired token could cause it to crash silently. Without error handling, you’ll just see stale data and have no idea something went wrong. Let’s add some basic robustness.

The cornerstone of error handling in JavaScript (and by extension, Apps Script) is the try...catch block. We’ll wrap our core logic in a try block. If anything inside that block throws an error, the script execution immediately jumps to the catch block, where we can log the error instead of just crashing.

A great practice is to log errors directly back into a separate tab in our Google Sheet. This creates a persistent, easily accessible log file.

Create a “Logs” Sheet: In your Google Sheet, create a new tab and name it Logs. Add two headers in the first row: Timestamp and Message.
Update Your Main Function: Modify your main function to include the try...catch logic.


function fetchAndAnalyzeCosts() {

const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

const logSheet = spreadsheet.getSheetByName('Logs');

try {

// --- START of your existing logic ---

const costData = getCostDataFromCloudProvider(); // Your function to get costs

const prompt = buildGeminiPrompt(costData); // Your function to create the prompt

const analysis = callGeminiAPI(prompt); // Your function to call Gemini

const dataSheet = spreadsheet.getSheetByName('Cost Data');

updateSheetWithData(dataSheet, costData, analysis); // Your function to write to the sheet

// --- END of your existing logic ---

// If everything succeeds, log the success

const successMessage = `Successfully ran and updated costs at ${new Date().toUTCString()}`;

logSheet.appendRow([new Date(), successMessage]);

Logger.log(successMessage);

} catch (e) {

// If any part of the 'try' block fails, this code will run

const errorMessage = `SCRIPT FAILED: ${e.message}\nStack: ${e.stack}`;

// Log to our sheet for a persistent record

logSheet.appendRow([new Date(), errorMessage]);

// Also log to the Apps Script logger for real-time debugging

Logger.log(errorMessage);

// For critical alerts, you can even send an email

MailApp.sendEmail('[email protected]', 'Cloud Cost Script Failure', errorMessage);

}

}

This simple structure dramatically improves the reliability of our automated system. You now have a persistent log and can even set up email alerts for immediate notification of any failures.

Visualizing the data with charts and conditional formatting in Sheets

Raw numbers in a spreadsheet are informative, but visuals are intuitive. We can use Google Sheets’ built-in features to turn our data into a true dashboard that highlights what matters.

Conditional Formatting for At-a-Glance Insights

Conditional formatting changes a cell’s appearance based on its value. This is incredibly effective for drawing attention to anomalies, like a sudden cost spike.

Let’s say you have a column for “Day-over-Day % Change”. We can set up a rule to highlight significant changes:

Select the entire column containing the percentage change (e.g., column E).
Go to the menu and click Format > Conditional formatting.
Under “Format rules,” in the “Format cells if…” dropdown, select Greater than.
In the value box, enter 0.1 (for a 10% increase).
Under “Formatting style,” choose a light red fill color to signify a warning.
Click + Add another rule.
Set the second rule to Less than and enter -0.1 (for a 10% decrease).
Choose a light green fill color for this rule to signify a positive change.
Click Done.

Now, your sheet will automatically color-code significant cost fluctuations, making them impossible to miss.

Charts for Trend Analysis

Charts are essential for understanding trends over time. A simple line chart can tell you more about your cost trajectory than a hundred rows of numbers.

Let’s create a chart showing the total cost over the last 30 days:

Select the data you want to plot. Hold Cmd (on Mac) or Ctrl (on Windows) to select non-adjacent columns, like your Date column and your Total Cost column.
Go to the menu and click Insert > Chart.
Google Sheets is usually smart enough to suggest a Line Chart, which is perfect for time-series data. If not, you can select it from the “Chart type” dropdown in the Chart editor sidebar.
Use the Chart editor to customize your chart. Give it a clear title like “Daily Cloud Spend (Last 30 Days),” label your axes, and adjust the colors to your liking.
Drag the chart to the top of your sheet to create a dashboard-like feel.

You can create multiple charts. A Pie Chart or Bar Chart is excellent for showing the cost breakdown by service for the most recent day, helping you instantly identify which service is contributing most to the total bill. By combining these visual elements, your simple spreadsheet is transformed into a powerful and intuitive cost control plane.

Conclusion: From Reactive Monitoring to Proactive Optimization

You’ve done more than just connect a few APIs and format some cells. You’ve fundamentally changed your relationship with cloud cost data. The days of dreading the end-of-month bill or manually wrestling with sprawling CSV files are over. What you’ve built is the kernel of a true FinOps engine—a system that transforms raw billing data into actionable intelligence, moving you from a state of reactive monitoring to one of proactive, data-driven optimization.

Recap: The power of your new automated cost control plane

Let’s take a moment to appreciate the elegant and powerful system you’ve just assembled. By integrating your cloud billing exports with Google Sheets and layering Gemini’s analytical capabilities on top, you’ve unlocked several key advantages:

Automated Data Ingestion: Your cost data now flows into a centralized location automatically. This single source of truth is always up-to-date, eliminating manual effort and the risk of human error.
Democratized Access: You’ve placed sophisticated cost analysis tools into the hands of your entire team. Anyone who can use a spreadsheet can now ask complex questions about cloud expenditure using natural language, thanks to the Gemini integration.
**AI-Powered Insights: This isn’t just a dashboard; it’s a conversation. You can ask Gemini to summarize spending trends, identify the root cause of an anomaly, or explain the cost impact of a specific service. This moves you beyond what is happening to why it’s happening.
Cost-Effective Foundation: You built this entire control plane using tools that are either free or part of your existing Automated Order Processing Wordpress to Gmail to Google Sheets to Jobber subscription. It’s a testament to the power of leveraging existing platforms to solve complex problems without incurring significant new costs.

You’ve successfully bridged the gap between raw data and genuine understanding, creating a living document that serves as the foundation for smarter cloud financial management.

Next steps: Expanding functionality with alerting and forecasting

The architecture you’ve built is not a final destination; it’s a launchpad. The combination of Google Apps Script, Sheets, and the Gemini API is incredibly flexible. Here are a few ways you can expand its capabilities:

Proactive Alerting: Use Google Apps Script to write functions that run on a schedule (e.g., every hour or every day). These scripts can scan for cost anomalies that meet specific criteria. For instance, you could configure an alert that triggers if a specific project’s daily cost jumps more than 30% day-over-day. When a condition is met, the script can send a detailed notification to a Google Chat space or a Slack channel via a webhook, complete with a link to the relevant data in your sheet.
Smarter Forecasting: Your sheet now contains a rich history of your spending. Leverage this data to predict future costs. You can start with simple FORECAST functions within Sheets. For more advanced predictions, you can prompt the Gemini API directly from your script: “Based on the cost data in range A2:G90 for the ‘production-database’ service, project the total cost for the next 30 days, accounting for the growth trend seen in the last quarter.”
Budget Tracking: Add a new tab for budget allocation by team, project, or service. Write a script that compares actual spend against these budgets in near-real-time and visually flags any projects that are trending over budget long before the month ends.

Ready to scale your architecture? Let’s talk

This Google Sheets-based control plane is a powerful, agile solution that delivers immense value for individuals, startups, and mid-sized organizations. It excels at making cost data accessible and understandable.

However, as your organization grows, so will the complexity and volume of your billing data. When you’re processing billions of billing lines per month across multiple cloud providers, managing complex chargeback models, or require enterprise-grade governance and security, you may find the limits of a spreadsheet-based approach.

Think of this project as your FinOps “boot camp.” It’s taught you the principles of cost visibility and accountability. When you’re ready to graduate to a dedicated, enterprise-scale FinOps platform that offers real-time data ingestion, advanced anomaly detection, and prescriptive recommendations, the lessons you’ve learned here will be invaluable.

If you’re starting to think about that next step in your cloud cost management journey, let’s connect. Share your challenges and successes in the comments below or reach out to me on [Your Social Media Link Here]. Let’s build smarter, more efficient cloud environments together.

Vo Tu Duc

A Google Developer Expert, Google Cloud Innovator

Stop Doing Manual Work. Scale with AI.

Hi, I'm Vo Tu Duc (Danny), a recognised Google Developer Expert (GDE). I architect custom AI agents and Google Workspace solutions that help businesses eliminate chaos and save thousands of hours.

Want to turn these blog concepts into production-ready reality for your team?