From Spreadsheet to Stack Auto Generating Terraform with Google Sheets and Gemini

May 05, 2026

As your project’s success breeds complexity, the manual infrastructure processes that once served you well become your biggest bottleneck. Learn how to manage configuration at a scale that outpaces human capacity for manual intervention.

The Challenge of Scalable Infrastructure Configuration

In the beginning, there is simplicity. A new project, a handful of virtual machines, a single VPC, and a small, dedicated team. Managing this environment feels straightforward. A few clicks in the cloud console here, a manually crafted Terraform file there. It works. But this idyllic state is fleeting. Success breeds complexity. The project explodes into a multi-environment, multi-region, microservices-based architecture. The team grows, and suddenly, the simple, manual processes that once served you well become your biggest bottleneck. The challenge isn’t just about building infrastructure; it’s about managing its configuration at a scale that outpaces human capacity for manual intervention.

Why manual configuration doesn’t scale

The leap from managing ten resources to ten thousand is not a linear one. The strategies that work for the former fail catastrophically for the latter. The initial allure of “ClickOps”—using the cloud provider’s web UI to provision resources—quickly gives way to a nightmare of unreproducible environments and zero auditability.

Even for teams who have embraced Infrastructure as Code (IaC), the most common scaling anti-pattern is “copy-paste engineering.” Need a new service? Copy the Terraform module for an existing one and tweak the variables. This seems efficient at first, but it litters your codebase with duplication.

This approach also creates immense cognitive overhead. No single engineer can hold the entire state of a complex system in their head. Which subnets are used by which services? What are the firewall rules for the prod-analytics-v3 environment? As the system grows, manual configuration becomes a high-stakes game of memory, leading to mistakes, forgotten dependencies, and security vulnerabilities born from simple oversight. It creates a bottleneck, where only a few “tribal elders” know how the system really works, grinding progress to a halt whenever they’re unavailable. Manual configuration is a linear solution to an exponential problem, and it simply cannot keep up.

The hidden costs of inconsistency in DevOps

The most insidious byproduct of manual, unscalable configuration is inconsistency. This isn’t just a matter of untidiness; it carries real, tangible costs that ripple through engineering, security, and the business itself.

The most notorious issue is configuration drift. A developer, facing a production issue, makes a “quick fix” directly in the cloud console, bypassing the IaC pipeline. The immediate fire is out, but now your Terraform state no longer matches reality. The next terraform apply either fails unexpectedly or, worse, reverts the critical fix, causing the outage to reoccur. This drift silently erodes the trust in your [Automated Job Creation in Real Time Jobber and Google Sheets Integration from Gmail](https://votuduc.com/Automated-Job-Creation-in-Jobber-from-Gmail-p115606).

Inconsistency also creates the infrastructure equivalent of the classic developer complaint, “It works on my machine.” When your staging environment has subtly different IAM permissions or network ACLs than production, you’re setting yourself up for deployment failures and last-minute scrambles. These inconsistencies breed unpredictability, the mortal enemy of Site Reliability Engineering (SRE).

From a security and compliance perspective, inconsistency is a nightmare. A forgotten logging setting on a single S3 bucket, an overly permissive security group that was copied but not corrected, a missing encryption flag on a new database—these are the low-hanging fruit for attackers and the red flags for auditors. Auditing a system rife with one-off configurations is a Herculean task, making it nearly impossible to guarantee compliance. The hidden costs are measured in wasted engineering hours spent debugging phantom issues, the business impact of unpredictable deployments, and the unacceptable risk of a security breach caused by a simple, preventable inconsistency.

Introducing a new paradigm: Infrastructure as Spreadsheet

To solve this scaling problem, we need to reframe it. The challenge is less about writing HCL syntax and more about managing the data that defines the infrastructure. The names, the instance sizes, the IP ranges, the environment tags—this is the data. The Terraform code is merely the engine that acts upon it. So, what if we managed this data in a tool that everyone understands?

Enter the seemingly unlikely hero: the spreadsheet.

Before you recoil, consider its strengths. A spreadsheet is the ultimate low-code tool for structured data. It’s universally accessible, understood by engineers, project managers, and finance teams alike. It provides a simple, tabular, and highly visible way to define and review hundreds or thousands of resources and their parameters. It can serve as a canonical, human-readable single source of truth for the intended state of your infrastructure.

This is the core of the “Infrastructure as Spreadsheet” paradigm. It’s not about abandoning Terraform or IaC. It’s about separating the data plane (the “what,” defined in a Google Sheet) from the control plane (the “how,” implemented in Terraform). By doing this, we transform the problem of managing thousands of lines of duplicative HCL into a much more manageable problem of maintaining rows in a spreadsheet. The code becomes a template, an implementation detail that is dynamically generated from the true source of authority. This approach democratizes infrastructure definition, drastically reduces the chance of inconsistency, and finally provides a framework that is built to scale.

The Architectural Blueprint: Sheets, Gemini, and Terraform

Before we start wiring up APIs and writing scripts, let’s establish a clear architectural blueprint. At its core, this system is a powerful, event-driven pipeline designed to translate structured, human-friendly data into declarative, machine-readable infrastructure code. It’s a classic example of leveraging the right tool for each part of the job: the universal accessibility of a spreadsheet, the contextual intelligence of a large language model, and the robust, industry-standard execution of Terraform.

High-level overview of the Automated Quote Generation and Delivery System for Jobber workflow

The entire process can be broken down into a sequence of logical steps, moving from a simple data entry action to a fully-formed, ready-to-apply Terraform configuration.

Data Definition (The Request): An engineer, developer, or even a less-technical stakeholder defines a piece of infrastructure by filling out a new row in a designated Google Sheet. Each column in this row corresponds to a specific configuration parameter (e.g., application name, instance size, cloud region, owner).
Trigger (The Ignition): The workflow is initiated. This could be triggered manually by clicking a button in the Sheet, automatically on a schedule, or, in a more advanced setup, via a webhook called by an external system like a CI/CD pipeline or an IT service management tool.
Data Ingestion & [Prompt Engineering for Reliable Autonomous Workspace Agents for Reliable Autonomous Workspace Agents](https://votuduc.com/prompt-engineering-for-reliable-autonomous-workspace-agents-p-20260319404106) (The Translation): A [AI Powered Cover Letter Automated Work Order Processing for UPS Engine](https://votuduc.com/AI-Powered-Cover-Letter-Automation-Engine-p111092) reads the data from the triggered row. This is the critical translation step. The script doesn’t just pass the raw data along; it meticulously constructs a detailed, context-rich prompt for Gemini. This prompt includes the structured data from the Sheet, explicit instructions on the desired HCL structure, coding conventions, and potentially few-shot examples to guide the model’s output.
Code Generation (The Intelligence): The engineered prompt is sent to the Gemini API. The model processes the request, understands the relationship between the input data and the desired Terraform resources, and generates the corresponding HCL code.
Code Persisting (The Output): The script receives the HCL code back from the Gemini API. It then performs any final formatting and saves the output as one or more .tf files. These files can be stored in a designated Google Drive folder or, ideally, committed to a new branch in a version control repository (like GitHub or GitLab), automatically creating a pull request for review.
Review & Apply (The Execution): The generated code is reviewed by a human operator through the standard pull request process. Once approved, the code is merged and the standard terraform plan && terraform apply workflow takes over to provision the infrastructure.

You might be tempted to dismiss a spreadsheet as a primitive tool for managing infrastructure, but in this architecture, its simplicity is its greatest strength. Google Sheets serves as our lightweight, accessible, and surprisingly robust Configuration Management Database (CMDB).

Democratized Input: Nearly everyone in a tech organization knows how to use a spreadsheet. This flattens the learning curve for requesting infrastructure, allowing teams to self-serve without needing to learn HCL syntax or navigate a complex ticketing system.
Structured Schema: This isn’t just a free-for-all notepad. The sheet is a strictly defined schema. Columns are our data fields, and we can enforce their integrity using Google Sheets’ built-in data validation. Dropdown menus for instance sizes, regex patterns for naming conventions, and protected ranges ensure that the data fed into our automation is clean and predictable. This is the “garbage in, garbage out” principle in practice.
Inherent Audit Trail: The sheet provides a clear, chronological, and human-readable log of every piece of infrastructure requested. With Google’s built-in version history, you have an immutable record of who requested what, when they requested it, and what parameters they used.

By centralizing the intent for our infrastructure in one place, we create a single source of truth that drives the entire automation pipeline.

How Gemini acts as the intelligent code generation engine

This is where the magic happens. Gemini isn’t just a simple template-filling engine; it’s a sophisticated translator that understands context, syntax, and structure.

While a traditional templating engine (like Jinja2 or Go’s text/template) can substitute variables into a static skeleton, it struggles with conditional logic and structural variations. If you need to add an optional disk or a conditional security group rule, your template’s complexity explodes.

Gemini, on the other hand, operates on a higher level of abstraction. Its role is to:

Translate Intent to Syntax: It converts the “what” from the spreadsheet (e.g., “a t3.micro web server for the ‘billing-api’ app in us-east-1”) into the “how” of HCL syntax (resource "aws_instance" "billing_api_web" { ... }).
Handle Structural Complexity: The prompt can instruct Gemini to make intelligent decisions. For example, if a cell environment is set to prod, the prompt can instruct Gemini to generate a configuration for a high-availability database, whereas a dev value might result in a cheaper, single-instance configuration. This logic lives in the prompt, not in a complex web of template if/else statements.
Eliminate Boilerplate: Gemini excels at generating the verbose boilerplate code that surrounds the core logic—provider configurations, backend blocks, locals, variable definitions, and outputs. This frees up human engineers to focus on the architectural decisions, not the tedious minutiae of HCL syntax.

The quality of Gemini’s output is a direct function of the quality of the prompt. Our Apps Script is therefore less of a data-passer and more of a “prompt engineer,” responsible for giving the model all the context it needs to succeed.

The final output: Structured Terraform HCL files

The end product of this pipeline is not some proprietary configuration format; it’s standard, idiomatic, and ready-to-use Terraform HCL. This is a critical design choice.

The generated .tf files are:

Declarative and Version-Controlled: The output can be committed directly to a Git repository, allowing you to leverage all the benefits of version control—history, branching, and collaborative review via pull requests.
Modular and Maintainable: We can instruct Gemini to generate code that adheres to best practices. For instance, instead of creating monolithic files, we can ask it to generate a main.tf for resources, a variables.tf for inputs, and an outputs.tf for exports. Better yet, we can prompt it to generate a configuration that consumes your organization’s pre-approved, standardized Terraform modules.
Human-in-the-Loop Compatible: The automation’s goal is to create the first draft, not to have the final say. The generated code is intended to be reviewed by an engineer. This critical human oversight step ensures that the AI’s output is correct, secure, and cost-effective before a single terraform apply is ever run. It’s the perfect blend of machine-speed generation and human-led governance.

Step 1: Designing the Master Configuration Sheet

Before we even think about prompting an LLM or writing a single line of code, we must lay the foundation. In our system, the Google Sheet is not just a data dump; it’s the declarative source of truth for our entire infrastructure. A poorly designed sheet will lead to inconsistent, error-prone Terraform. A well-designed one, however, becomes a powerful, collaborative tool for infrastructure management. The principle is simple: garbage in, garbage out. Let’s make sure we’re starting with gold.

Defining a clear schema for your infrastructure resources

A “schema” in this context is simply a set of rules for how we structure our data. It’s the blueprint for our sheet. For an LLM like Gemini to reliably process this data and generate code, the input must be predictable and consistent. A free-for-all spreadsheet where columns change meaning from row to row is a recipe for disaster.

Our approach will be a one-resource-per-row model. Each row in our sheet will explicitly define a single infrastructure resource (like a VPC, a subnet, or a virtual machine). This keeps the logic clean and the mapping to Terraform resources one-to-one.

For more complex environments, consider using separate tabs (sheets) within the same Google Sheet for different categories of resources. For example:

Networking: For VPCs, Subnets, Firewall Rules, etc.
Compute: For VM Instances, Instance Templates, Managed Instance Groups.
Storage: For Cloud Storage Buckets, Disks.

This separation isn’t strictly necessary to start, but it improves organization and scalability as your infrastructure grows. For our example, we’ll keep it simple and use a single sheet.

Essential columns: parameters, naming conventions, and environment tags

The columns are the heart of our schema. While you can add more metadata as needed, a core set of columns is non-negotiable for this system to function effectively.

Here are the essential columns and their purpose:

| Column Name | Purpose | Example |

| ----------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------- |

| resource_type | (Required) The specific Terraform resource type you want to create. This is the most critical piece of information, as it dictates the structure of the generated HCL code. Use the exact Terraform provider name. | google_compute_network, google_compute_subnetwork |

| resource_name | (Required) A unique, human-readable name for the resource within your configuration. This will be used to generate the logical name in the Terraform code (e.g., resource "google_compute_network" "main_vpc" {}). | main_vpc, web_subnet_us_central1 |

| environment | (Required) The deployment environment (e.g., dev, staging, prod). This is crucial for applying consistent naming conventions, tags, and for filtering which resources to deploy. | dev, prod |

| region | The primary cloud region for the resource. While not all resources are regional, most are. | us-central1, europe-west2 |

| zone | The specific availability zone for the resource. Only applicable for zonal resources like VM instances. | us-central1-a, us-central1-c |

| parameters | (The Magic Column) A flexible, JSON-formatted string containing all other resource-specific arguments. Instead of creating a hundred columns for every possible Terraform attribute, we bundle them here. This makes our schema scalable and adaptable. | {"routing_mode": "REGIONAL", "auto_create_subnetworks": false} |

| enabled | A simple TRUE/FALSE flag to easily enable or disable the creation of a resource without deleting the row. This is incredibly useful for temporary resources or feature-flagging infrastructure. | TRUE |

A Note on Naming Conventions and Tagging:

The environment and resource_name columns are your building blocks for standardization. A good practice is to programmatically combine these to create the actual resource name in the cloud provider. For instance, a VM with resource_name = web_server_01 and environment = prod could be automatically named prod-web-server-01 in GCP. The environment column should also be used to automatically apply a corresponding env label or tag to every resource for cost tracking and policy enforcement.

Example template for a common web application stack (VPC, Subnets, Instances)

Let’s put theory into practice. Here is what a master configuration sheet might look like for a basic web application stack in Google Cloud. This single view defines an entire environment in a way that is both human-readable and machine-parseable.

Imagine this as a tab in your Google Sheet named Infrastructure:

| --------------------------- | ------------------- | ----------- | ----------- | ------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------ |

| google_compute_network | main_vpc | dev | us-central1 | | {"routing_mode": "REGIONAL", "auto_create_subnetworks": false, "description": "Main dev VPC"} - | TRUE |

| google_compute_subnetwork | web_subnet | dev | us-central1 | | {"ip_cidr_range": "10.10.1.0/24", "network": "google_compute_network.main_vpc", "private_ip_google_access": true} - | TRUE |

| google_compute_subnetwork | db_subnet | dev | us-central1 | | {"ip_cidr_range": "10.10.2.0/24", "network": "google_compute_network.main_vpc"} - | TRUE |

| google_compute_instance | web_server_01 | dev | us-central1 | us-central1-a | {"machine_type": "e2-medium", "boot_disk": {"initialize_params": {"image": "debian-cloud/debian-11"}}, "network_interface": [{"subnetwork": "google_compute_subnetwork.web_subnet"}], "tags": ["web-server", "dev"]} | TRUE |

| google_compute_instance | db_server_01 | dev | us-central1 | us-central1-c | {"machine_type": "e2-small", "boot_disk": {"initialize_params": {"image": "debian-cloud/debian-11"}}, "network_interface": [{"subnetwork": "google_compute_subnetwork.db_subnet"}], "tags": ["db-server", "dev"]} | TRUE |

Notice the power of the parameters column. For the VPC, it defines the routing mode. For the subnets, it defines the CIDR range and, importantly, references the parent VPC using a Terraform-style address (google_compute_network.main_vpc). This is a key detail: our sheet can encode dependencies between resources, which we’ll teach Gemini to interpret later. For the VM instances, the JSON is more complex, defining machine type, boot disk, network interface, and tags—all within that single, flexible column.

With this structured sheet in place, we have a solid, scalable, and easy-to-understand foundation. We’re now ready to move on to the next step: using this “infrastructure definition” to prompt Gemini and generate our Terraform code.

Step 2: Building the Automation with Genesis Engine AI Powered Content to Video Production Pipeline

With our Google Sheet structured as a source of truth, it’s time to build the engine that powers our automation. [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) provides the perfect, serverless environment to orchestrate this workflow. It lives directly within our spreadsheet, has native integrations with 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 services, and can make external API calls to Gemini. Let’s get our hands dirty and write some code.

Setting up your environment and required API access (Sheets, Gemini, Drive)

Before we can write a single line of logic, we need to configure our Apps Script project and grant it the necessary permissions to communicate with other Google services.

Open the Apps Script Editor: From your Google Sheet, navigate to Extensions > Apps Script. This will open a new browser tab with the script editor, bound to your spreadsheet.
Link to a Google Cloud Project: While Apps Script can do a lot on its own, accessing advanced services like 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 (for Gemini) requires a standard Google Cloud Platform (GCP) project.

In the Apps Script editor, click on the* Project Settings** (gear icon) on the left sidebar.

Scroll down to the “Google Cloud Platform (GCP) Project” section.
If a project isn’t already associated, click “Change project” and enter the Project Number of a GCP project you have access to. You can create a new one if needed from the Google Cloud Console.

Enable the Required APIs: Now we need to tell our GCP project which services our script will be using.

Click the link provided in the Project Settings to view the GCP project.

In the GCP Console, navigate to the* APIs & Services > Library**.

Search for and* Enable** the following APIs:

Google Drive API: Allows our script to create and manage files.
Vertex AI API: This is the crucial one that gives us access to Gemini models.

Note: The Google Sheets API is typically enabled by default for scripts bound to a sheet, so you usually don’t need to enable it manually.

Authentication and Scopes: The beauty of Apps Script is how it simplifies authentication. When you run the script for the first time, it will use OAuth 2.0 to prompt you for permission to act on your behalf. The script will automatically request the necessary scopes to access the Sheets and Drive APIs. For the Vertex AI API, the script authenticates using your user credentials, so you must have the appropriate IAM permissions (like the “Vertex AI User” role) in the linked GCP project.

The core script logic: parsing the sheet data

Our first task in the code is to read the data from our sheet and transform it from a simple grid of cells into a more useful data structure: an array of JavaScript objects. Each object will represent a single resource (in our case, a VM) with key-value pairs corresponding to our column headers.

This approach makes the rest of our code cleaner and more readable, as we can reference properties like vm.name instead of array indices like row[0].

Here’s a robust function to handle this parsing:


/**

* Parses a given sheet's data into an array of objects.

* The first row of the sheet is assumed to be the header row.

*

* @param {string} sheetName The name of the sheet to parse.

* @returns {Array<Object>} An array of objects, where each object represents a row.

*/

function parseSheetData(sheetName) {

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);

if (!sheet) {

throw new Error(`Sheet with name "${sheetName}" not found.`);

}

// Get all data from the sheet as a 2D array

const data = sheet.getDataRange().getValues();

// The first row contains the headers (e.g., "Name", "MachineType", "Zone")

const headers = data.shift();

// Map the remaining rows to objects

const resources = data.map(row => {

const resource = {};

headers.forEach((header, index) => {

// Only add non-empty cells to the object

if (row[index] !== "") {

resource[header] = row[index];

}

});

return resource;

});

console.log(`Parsed ${resources.length} resources from sheet: ${sheetName}`);

return resources;

}

Crafting the perfect prompt for Gemini to ensure consistent HCL output

This is the most critical part of our automation. We aren’t just asking Gemini a question; we are instructing it to perform a code generation task. The quality of our prompt directly determines the quality and consistency of the generated Terraform HCL. A poorly-worded prompt will result in errors, hallucinations, and invalid syntax.

Here are the key principles for a successful prompt:

Be Explicit and Authoritative: Tell the model exactly what to do. Use imperative commands like “Generate,” “Create,” “Output only,” and “Do not include.”
Provide Clear Context: Give the model all the necessary input data in a structured way. We’ll pass the JavaScript object for a single VM directly into the prompt.
Define the Output Format: Specify the exact language (HCL), the resource type (google_compute_instance), and any formatting constraints.
**Set Negative Constraints: Tell the model what not to do. This is crucial for preventing it from adding conversational fluff, explanations, or markdown code fences (```hcl) that would invalidate the .tf file.

Let’s build a function that dynamically creates a prompt for a single VM object and then calls the Gemini API.


// Replace with your Google Cloud Project ID

const GCLOUD_PROJECT_ID = 'your-gcp-project-id-here';

/**

* Generates Terraform HCL for a single VM resource object using the Gemini API.

*

* @param &#x7B;Object&#x7D; resourceData The object representing a single VM from the sheet.

* @returns &#x7B;string&#x7D; The generated HCL code as a string.

*/

function generateHclForResource(resourceData) &#x7B;

// 1. Craft the prompt

const prompt = `

You are an expert Terraform engineer.

Your task is to generate a single, valid Terraform HCL resource block for a Google Cloud VM.

Instructions:

- The resource type MUST be "google_compute_instance".

- The resource name MUST be the "Name" value from the provided data, with hyphens replaced by underscores.

- Use the following data to populate the resource attributes.

- The "boot_disk" block must be configured with an "initialize_params" block using the "Image" value.

- The "network_interface" block must be configured to use the default network.

- Output ONLY the raw HCL code for the resource block.

- DO NOT include any provider blocks, comments, explanations, or markdown formatting.

Data:

${JSON.stringify(resourceData, null, 2)}

`;

// 2. Prepare the API request

const API_ENDPOINT = `https://us-central1-aiplatform.googleapis.com/v1/projects/${GCLOUD_PROJECT_ID}/locations/us-central1/publishers/google/models/gemini-1.0-pro:streamGenerateContent`;

const payload = &#x7B;

contents: [&#x7B;

parts: [&#x7B; text: prompt &#x7D;]

&#x7D;]

&#x7D;;

const options = &#x7B;

method: 'post',

contentType: 'application/json',

headers: &#x7B;

Authorization: `Bearer ${ScriptApp.getOAuthToken()}`

&#x7D;,

payload: JSON.stringify(payload),

muteHttpExceptions: true // Important for debugging errors

&#x7D;;

// 3. Call the Gemini API

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

const responseText = response.getContentText();

const responseCode = response.getResponseCode();

if (responseCode !== 200) &#x7B;

throw new Error(`Gemini API Error ${responseCode}: ${responseText}`);

&#x7D;

// 4. Parse the response to extract the generated text

// The streaming response returns an array of chunks. We need to parse them and concatenate the text.

const jsonResponse = JSON.parse(responseText);

let generatedHcl = '';

jsonResponse.forEach(chunk => &#x7B;

if (chunk.candidates && chunk.candidates[0].content.parts[0].text) &#x7B;

generatedHcl += chunk.candidates[0].content.parts[0].text;

&#x7D;

&#x7D;);

console.log(`Successfully generated HCL for: ${resourceData.Name}`);

return generatedHcl.trim();

&#x7D;

Writing the generated code to files in Google Drive or a Git repository

Once Gemini returns the HCL code, the final step is to save it as a .tf file. We have two primary options here, ranging from simple to enterprise-grade.

Option 1: Save to Google Drive (The Simple Approach)

This is the most direct method and is perfect for getting started. We can create a folder in Google Drive and write each generated resource block into its own file.


/**

* Writes content to a file in a specified Google Drive folder.

* Creates the folder if it doesn't exist.

*

* @param &#x7B;string&#x7D; folderName The name of the target folder in Google Drive.

* @param &#x7B;string&#x7D; fileName The name of the file to create (e.g., "vm_web_server.tf").

* @param &#x7B;string&#x7D; content The content to write to the file.

*/

function writeToDrive(folderName, fileName, content) &#x7B;

let folders = DriveApp.getFoldersByName(folderName);

let targetFolder;

if (folders.hasNext()) &#x7B;

targetFolder = folders.next();

&#x7D; else &#x7B;

targetFolder = DriveApp.createFolder(folderName);

console.log(`Created new folder: ${folderName}`);

&#x7D;

// Check if file already exists and overwrite it, or create a new one.

let files = targetFolder.getFilesByName(fileName);

if (files.hasNext()) &#x7B;

files.next().setContent(content);

console.log(`Updated file: ${fileName}`);

&#x7D; else &#x7B;

targetFolder.createFile(fileName, content, MimeType.PLAIN_TEXT);

console.log(`Created new file: ${fileName}`);

&#x7D;

&#x7D;

Option 2: Commit to a Git Repository (The Advanced IaC Approach)

For a true Infrastructure-as-Code (IaC) workflow, your generated code should live in a Git repository where it can be reviewed, approved, and deployed via a CI/CD pipeline.

Disclaimer: Google Apps Script does not have a native Git client. This process involves making authenticated REST API calls to your Git provider (e.g., GitHub, GitLab, Bitbucket). It is significantly more complex but aligns with DevOps best practices.

Here’s the high-level workflow for committing to a GitHub repository:

Authentication:

Generate a Personal Access Token (PAT) from your GitHub account with repo scope.
Securely store this token using Apps Script’s PropertiesService, which encrypts script properties. Do not hardcode it in your script.

PropertiesService.getScriptProperties().setProperty('GITHUB_TOKEN', 'your_pat_here');

API Interaction (UrlFetchApp):

You will interact with the GitHub REST API for creating and updating files.

Execution Flow:

Define Repository Details: Store your username, repo name, and a target branch in variables.
Loop and Commit: For each generated HCL file:
Construct the API endpoint URL: https://api.github.com/repos/OWNER/REPO/contents/PATH/FILENAME.tf.
Create a JSON payload containing a commit message, the file content encoded in Base64, and the branch name.
Make a PUT request using UrlFetchApp, including your PAT in the Authorization: Bearer <TOKEN> header.
Create a Pull Request: After committing all files to a feature branch, you can make another API call to the Pull Requests API to open a PR for review.

While a full implementation is beyond the scope of this section, this outline provides a clear path for integrating your generator into a professional GitOps workflow. For most initial projects, starting with the Google Drive approach is perfectly sufficient.

Step 3: A Practical Demonstration: From Row to Resource

Alright, the theory is sound and our tooling is in place. Now, let’s get our hands dirty and watch this system in action. We’ll walk through the entire end-to-end process, from adding a single row in our Google Sheet to validating a Terraform execution plan. This is where the abstract concepts of prompts and scripts crystallize into tangible infrastructure-as-code.

Populating the sheet to define a new staging environment

Our goal is to provision a new GCE virtual machine for a staging environment. Instead of opening a text editor and writing HCL, we simply open our designated Google Sheet. We find the next empty row and fill in the columns with the desired state for our new resource.

Imagine our sheet has the following columns: environment, project_id, region, vm_name, machine_type, network, subnetwork, and image.

Here’s what our new row looks like:

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

That’s it. No code, no brackets, no quotation marks. We’ve just defined the desired state of a new server using a simple, structured data entry format that anyone on the team can understand and use. This single row is now the source of truth for our new VM.

Executing the script and triggering the Gemini API call

With our new row saved in the Google Sheet, we switch to our terminal. It’s time to run the JSON-to-Video Automated Rendering Engine script we built in the previous step. This script will read the sheet, identify our new row, and kick off the generation process.

In your project directory, you’ll execute the script:


python generate_tf.py

The script provides real-time feedback on its progress, giving you visibility into the workflow:


[INFO] Authenticating with Google Cloud...

[INFO] Fetching data from Google Sheet 'Terraform Requests'...

[INFO] Found 1 new row(s) to process.

[INFO] Processing row for resource 'stg-web-01'.

[INFO] Constructing prompt for Gemini API...

[INFO] Sending request to Gemini...

[INFO] Gemini API call successful!

[INFO] Received valid HCL. Writing to 'generated/stg-web-01.tf'...

[INFO] Process complete.

Behind these log messages, the script has performed several key actions:

It authenticated with the Google Sheets and Gemini APIs.
It read the entire sheet and identified the new row we just added.
It took the data from that row and dynamically inserted it into our carefully crafted prompt.
It sent that complete prompt to the Gemini API.
It received the HCL code in response and saved it to a new .tf file, neatly named after our resource.

Reviewing the auto-generated Terraform files

Now for the moment of truth. Let’s inspect the output. Navigate to the generated/ directory in your project, and you’ll find a new file named stg-web-01.tf.

Opening it up, you should see something remarkably clean and familiar:


# Auto-generated by Gemini on 2023-10-27

# Source: Google Sheet 'Terraform Requests'

resource "google_compute_instance" "stg-web-01" &#x7B;

project      = "my-gcp-project-stg"

zone         = "us-central1-a" # Note: Gemini intelligently selected a zone within our specified region

name         = "stg-web-01"

machine_type = "e2-medium"

boot_disk &#x7B;

initialize_params &#x7B;

image = "debian-cloud/debian-11"

&#x7D;

&#x7D;

network_interface &#x7B;

network    = "vpc-staging"

subnetwork = "subnet-staging-us-central1"

&#x7D;

metadata_startup_script = <<-EOT

#!/bin/bash

# Placeholder for startup script

echo "Instance stg-web-01 created." > /tmp/instance_creation.log

EOT

labels = &#x7B;

environment = "staging"

&#x7D;

&#x7D;

This is the magic. Gemini didn’t just dump our data into a template; it understood the intent behind the data. It correctly mapped our sheet columns to the corresponding Terraform resource attributes, formatted it as valid HCL, and even added helpful context like comments and labels. Notice how it also made an intelligent decision to select a specific zone (us-central1-a) based on the region we provided (us-central1), a detail we included in our prompt’s instructions.

Crucially, this step is not optional. Always review code generated by an AI. Treat it as a very capable junior developer’s first draft. Check for correctness, security best practices, and adherence to your organization’s standards before proceeding.

Running `terraform plan` to validate the results

The generated code looks good, but the ultimate test is whether Terraform agrees. This final step validates that the HCL is not only syntactically correct but also semantically valid and ready for deployment.

Navigate to the directory containing your Terraform configuration (which should now include our new file) and run the standard commands:


# Initialize the backend and providers, if you haven't already

terraform init

# Generate the execution plan

terraform plan

Terraform will now communicate with your GCP project to compare the desired state (our generated code) with the actual state (what currently exists). The output should be a resounding success:


Terraform used the selected providers to generate the following execution plan.

Resource actions are indicated with the following symbols:

+ create

Terraform will perform the following actions:

# google_compute_instance.stg-web-01 will be created

+ resource "google_compute_instance" "stg-web-01" &#x7B;

+ cpu_platform       = (known after apply)

+ id                 = (known after apply)

+ instance_id        = (known after apply)

+ label_fingerprint  = (known after apply)

+ machine_type       = "e2-medium"

+ name               = "stg-web-01"

+ project            = "my-gcp-project-stg"

+ self_link          = (known after apply)

+ zone               = "us-central1-a"

# ... other attributes

&#x7D;

Plan: 1 to add, 0 to change, 0 to destroy.

This plan is our confirmation. The entire workflow, from a simple row in a spreadsheet to a validated infrastructure change, is successful. We have officially turned a line of data into a planned cloud resource without writing a single line of HCL ourselves.

Expanding the Vision Beyond Terraform

The true power of this spreadsheet-driven approach isn’t confined to generating HCL. The core pattern—using a structured, human-friendly data source (Google Sheets) as the input for a powerful generative model (Gemini) to produce machine-readable configuration—is a versatile framework. Terraform is just the first application. By swapping out the prompt and the target format, we can extend this “Intent-to-Code” model to orchestrate a wide array of tools and platforms across the software development lifecycle.

Adapting the model for Kubernetes manifest generation

Managing raw Kubernetes YAML is a common pain point. It’s verbose, prone to copy-paste errors, and maintaining consistency across dozens of microservices for different environments (dev, staging, production) can become a significant operational burden. This is a perfect use case for our model.

Imagine a Google Sheet designed to define application deployments. The columns wouldn’t be instance_type or vpc_id, but rather Kubernetes-native concepts:

|--------------|-----------|-------------------------------------|----------|------|------------|-------------|-------------|----------------------|

| api-gateway | services | gcr.io/my-project/api-gateway:v1.2 | 3 | 8080 | 250m | 512Mi | prod | api.example.com |

| user-service | services | gcr.io/my-project/user-service:v0.9 | 2 | 3000 | 100m | 256Mi | prod | users.api.example.com|

| auth-service | security | gcr.io/my-project/auth-service:v2.1 | 2 | 5001 | 150m | 256Mi | prod | auth.api.example.com |

Each row is a clear, high-level declaration of an application’s desired state. The complexity of the underlying YAML is abstracted away. The prompt provided to Gemini would be engineered to translate a single row into a complete set of Kubernetes manifests.

Example Gemini Prompt Snippet:


You are a Kubernetes expert. Based on the following data row, generate a valid Kubernetes Deployment YAML and a corresponding ClusterIP Service YAML.

Data:

&#x7B;

"AppName": "api-gateway",

"Namespace": "services",

"Image": "gcr.io/my-project/api-gateway:v1.2",

"Replicas": 3,

"Port": 8080

&#x7D;

Ensure the Deployment includes standard labels for 'app' and 'environment'. The container port in the Deployment spec must match the 'Port' field, and the Service should target this port.

This approach enforces standardization. The prompt itself becomes a “template” that encodes best practices—such as mandatory labels, resource requests, and security contexts—that are applied consistently to every generated manifest, dramatically reducing configuration drift and errors.

Generating configurations for other tools like Ansible or Pulumi

The model’s flexibility shines when we consider the broader ecosystem of DevOps tools. The fundamental process remains the same; only the target syntax and the prompt’s instructions change.

For Ansible:

Instead of infrastructure resources, the spreadsheet could manage server inventories or application variables.

Use Case: Generating a dynamic inventory file or group_vars YAML.
Sheet Structure: Columns like Hostname, IPAddress, AnsibleGroup, OS, Datacenter.
Gemini’s Role: The prompt would instruct Gemini to format the sheet’s data into either the classic INI format for an inventory or a structured YAML file for variables. This allows teams to manage their server fleet from a central, easily-auditable spreadsheet.

For Pulumi:

This represents a more advanced application, as we are now generating imperative code instead of declarative configuration.

Use Case: Generating Python or TypeScript code that utilizes the Pulumi SDK to define infrastructure.
Sheet Structure: Similar to the Terraform example, defining cloud resources and their properties.
Gemini’s Role: Leveraging its strong code-generation capabilities, Gemini can translate a row of data into a complete, syntactically correct Pulumi program snippet.

Example Pulumi (Python) Prompt Snippet:


You are an expert in Pulumi with the Python SDK. Write a Python script snippet that declares an AWS S3 bucket resource using 'pulumi_aws'.

Data:

&#x7B;

"BucketName": "my-app-static-assets-prod",

"ACL": "private",

"EnableVersioning": true

&#x7D;

The generated code should import the necessary 'pulumi_aws' package and create a bucket resource with the specified name, ACL, and versioning configuration.

This demonstrates that the system is not limited to simple key-value configurations. It can generate complex logic, boilerplate code, and SDK usage patterns, adapting to any tool that consumes a text-based configuration or script.

Integrating this system into a larger CI/CD pipeline for true GitOps

The manual act of running the script and copying the output is a great proof-of-concept, but the real enterprise-grade value is unlocked when this generator becomes a fully automated component of a GitOps pipeline. This elevates the spreadsheet from a simple data source to the “source of intent” for your entire system.

Here’s how the automated workflow would look:

Intent Declaration: An engineer updates the Google Sheet—perhaps adding a new microservice, scaling up replicas, or changing an environment variable. This change is reviewed and approved directly within the familiar spreadsheet interface.
Trigger: Upon a confirmed change (e.g., a specific cell is marked “ready_for_deploy”), a Google Apps Script triggers a webhook, firing a notification to a CI/CD platform like GitHub Actions or GitLab CI.
Generation Job: The CI pipeline spins up a job that executes our generation script. The script authenticates with the Google Sheets API, pulls the latest data, and sends it to the Gemini API with the appropriate, pre-defined prompt (for Terraform, Kubernetes, etc.).
Commit to Git: The CI job takes the configuration code generated by Gemini and automatically commits it to a dedicated Git repository. This repository is the single source of truth for the system’s desired state. The commit message can even reference the change made in the spreadsheet for full traceability (e.g., “feat: Add user-service as per Sheet row 15”).
GitOps Synchronization: A GitOps controller, such as Argo CD or Flux, is constantly monitoring this Git repository. It detects the new commit.
Automated Reconciliation: The controller compares the new desired state defined in Git with the actual live state of the cluster or cloud environment. It then automatically applies the necessary changes—running terraform apply or kubectl apply—to bring the live state into alignment with the source of truth.

This closed-loop system is the essence of GitOps. It provides a fully auditable, version-controlled, and automated path from human intent to production reality. The spreadsheet becomes the user-friendly “control plane,” while Git provides the robust, auditable “state plane,” and the CI/CD pipeline acts as the engine that connects them.

Conclusion: The Future of AI-Driven Infrastructure Management

We’ve journeyed from a simple, familiar interface—a Google Sheet—to a fully-fledged, automated Terraform configuration, all orchestrated by the power of Gemini. This isn’t just a novel party trick; it’s a glimpse into a paradigm shift in how we manage and provision cloud infrastructure. By bridging the gap between human-friendly data entry and machine-executable code, we’ve unlocked a workflow that is fundamentally faster, more consistent, and scalable. But as with any powerful new technology, it’s crucial to weigh the benefits against the inherent considerations.

Recap of the benefits: scalability, consistency, and speed

The advantages of this AI-driven approach are compelling and address some of the most persistent challenges in Infrastructure as Code (IaC):

Scalability: The most profound benefit is the decoupling of effort from complexity. Instead of requiring a Terraform expert to manually author HCL for every new VM, database, or network rule, you empower your team to define infrastructure needs in a simple, structured format. A single new row in a spreadsheet can trigger the generation of dozens or hundreds of lines of correct, standardized code. This democratizes infrastructure provisioning and allows your platform team to scale its impact without scaling its headcount linearly.
Consistency: Human-written code, no matter how skilled the author, is prone to variation. By using a meticulously crafted prompt template as the “source of truth” for code generation, you enforce a level of consistency that is difficult to achieve manually. Every generated module will adhere to your organization’s best practices—mandatory tagging, specific security group configurations, naming conventions, and logging setups are baked in by default. This reduces configuration drift, simplifies audits, and makes your entire infrastructure codebase more predictable and maintainable.
Speed: The acceleration is dramatic. The cycle time from an infrastructure request to a ready-to-apply Terraform plan is reduced from hours or days to mere seconds. This velocity is a direct competitive advantage, enabling development teams to get the resources they need faster, shortening feedback loops, and accelerating the delivery of new features to market. The tedious, error-prone task of writing boilerplate HCL is effectively eliminated.

Potential considerations and limitations to be aware of

While the future is bright, it’s essential to proceed with a clear-eyed view of the current limitations. Adopting this model requires a shift in mindset and a new set of controls.

Prompt Engineering is the New Bottleneck: The quality of the generated code is entirely dependent on the quality of your prompt. A vague or poorly structured prompt will produce unreliable, incorrect, or insecure code. Crafting robust, deterministic, and context-aware prompts is a critical new skill. You are essentially programming the AI, and this requires iteration, testing, and a deep understanding of both the desired output and the model’s behavior.
Never Trust, Always Verify: AI-generated code should be treated as an untrusted, albeit highly capable, junior developer. It is non-negotiable that all generated code passes through a rigorous validation pipeline before ever being applied to a production environment. This includes automated checks with tools like tfsec or checkov for security and compliance, a mandatory terraform plan review by a qualified human, and deployment to a staging environment. The AI is a co-pilot, not the pilot-in-command.
Model Drift and Determinism: Large Language Models are not static binaries; they are constantly evolving. The version of Gemini you use today might produce slightly different output from a new version released next month, even with the exact same prompt. This introduces a variable that traditional IaC workflows don’t have. It’s crucial to have versioning strategies for your prompts and potentially pin to specific model versions where possible to ensure predictable outcomes.
The Complexity Ceiling: This approach excels at generating code for well-defined, repeatable patterns and resources. It’s perfect for stamping out standardized application environments or provisioning common infrastructure components. However, for highly bespoke, architecturally complex, or deeply interconnected systems that require nuanced logic, the cognitive overhead of creating a sufficiently detailed prompt may outweigh the benefits of manual creation by an expert.

Final thoughts on the evolving role of AI in DevOps

What we’re witnessing is not the replacement of the DevOps engineer, but the evolution of the role. AI is poised to become the single most impactful force multiplier for infrastructure professionals since the advent of the cloud itself.

The focus is shifting from the imperative how to the declarative what. Instead of spending our days meticulously writing HCL, our primary value will be in designing the systems, prompts, and guardrails that allow the AI to handle the implementation details. The role of an infrastructure engineer is being elevated from a coder of configurations to an architect of automated systems. We are becoming the curators of the prompts, the validators of the output, and the strategists who decide which patterns are best suited for automation.

The AI-augmented engineer will not be replaced; they will be the ones building the future. They will leverage AI to manage complexity at a scale previously unimaginable, freeing up their cognitive cycles to focus on the truly hard problems: system reliability, performance optimization, security architecture, and cost engineering. The fusion of deep human expertise with the speed and consistency of AI is the true promise of this new era. The journey from spreadsheet to stack is just the first step.

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?