Structuring Google Sheets for Hugo Data Files

The data Directory: Your Site's Mini-Database

First, let's understand the role of the /data directory in a Hugo project. While the /content directory is for content that gets rendered into individual HTML pages, the /data directory is for supplementary data that can be accessed from any template on your site.

You can store data in formats like JSON, YAML, or TOML inside this folder. Hugo automatically loads these files, making the data available to you globally under the .Site.Data variable.

This video from Giraffe Academy provides a concise explanation of the data folder and demonstrates how to access its contents from a template.

As you saw, the core idea is simple: place a structured data file (like states.json) in the /data directory, and you can instantly access it from your layouts using .Site.Data.<filename>.

Designing a Google Sheet for a Data File

Now, let's apply this to our workflow. How do we structure a Google Sheet so it can be easily converted into a useful JSON data file? The most common and flexible pattern is to design your sheet to represent a list of objects.

• Each row represents an item in your list (e.g., a testimonial, a feature, a team member).
• Each column header represents a property of that item (e.g., author, quote, photo).

Let's imagine we're building a homepage and want to manage a "Features" section. Each feature has an icon, a title, and a short description.

Our Google Sheet would look like this:

Here's the sheet structure for our "Features" section:

When processed, this sheet would ideally be converted into a JSON file named features.json inside the /data folder, looking like this:

[
  {
    "icon": "fas fa-chart-line",
    "title": "Advanced Analytics",
    "description": "Track your performance with our powerful analytics dashboard."
  },
  {
    "icon": "fas fa-users",
    "title": "Team Collaboration",
    "description": "Invite your team members and work together seamlessly."
  },
  {
    "icon": "fas fa-shield-alt",
    "title": "Enterprise-Grade Security",
    "description": "Your data is safe and secure with our robust security measures."
  }
]

This clean, structured data is now ready to be used by our homepage template to dynamically generate the "Features" section.

Making Sheet Data Accessible to Hugo

Okay, we've designed our sheet. How do we get that data into Hugo's build process? There are two primary methods, each with its own advantages.

Method 1: Publish as CSV and Fetch Remotely

This is a straightforward and robust method. Google Sheets allows you to publish any sheet to the web as a CSV (Comma-Separated Values) file. Hugo can then fetch this remote CSV file during its build process.

This article provides a clear guide on the first part of this process. While the article's end goal is to generate pages (like we did in the last lesson), the initial steps of preparing and publishing the sheet are exactly what we need for creating data files.

With this method, you get a URL for your CSV data. In a future lesson on templating, you'll see how to use that URL with resources.GetRemote to load the data directly into your homepage layout.

Method 2: Create a JSON API with Google Apps Script

This method is slightly more technical but incredibly powerful, fitting perfectly with the "vibe coding" ethos of creating custom automated workflows. By adding a small script to your Google Sheet, you can turn it into a live JSON API endpoint.

The following video is a complete walkthrough of this process. It shows you how to write the script, convert the sheet's row-and-column data into a clean JSON format, and deploy it as a web app that serves the JSON.

This method gives you a clean JSON endpoint that your AI tools can easily be prompted to interact with, or that Hugo can fetch directly during its build. It essentially turns your spreadsheet into a lightweight, headless CMS.

Structuring for Complexity

For truly complex websites, you might need to manage related data. Drawing on your experience with large-scale digital projects, you can think of this like a relational database. You can apply the same principles within Google Sheets.

The article "Run a website off a Google Sheets Database, with Hugo" explores this advanced concept. The author uses different tabs within a single Google Sheet to represent different "tables" of data (e.g., a "Profilers" table and a "Formats" table) and uses IDs to link them, essentially creating foreign keys. This is a powerful technique for managing complex, interrelated data without needing a traditional database. While we won't implement this today, it's a valuable concept to keep in mind for future projects.

Conclusion

You have now learned how to manage structured, non-page data for your Hugo site using Google Sheets. This complements what you learned in the previous lesson, giving you a comprehensive system for content and data management.

Key Takeaways:

• Use the /data directory for structured data that doesn't represent a standalone page, like lists of items for a homepage.
• Structure your sheet as a list of objects: Each row is an item, and each column is a property. This translates cleanly to JSON.
• You have two primary ways to access the data:
  1. Publish to web as CSV: A simple, reliable method for getting a public URL to your data.
  2. Create a JSON API with Apps Script: A more powerful method that provides a clean JSON endpoint, perfect for automated workflows.

Preview of the next lesson:
We've now explored using Google Sheets for both page content (/content) and structured data (/data). In the next lesson, we will solidify this knowledge by explicitly comparing these two approaches. We'll discuss the key question: When should you store content in the /content directory versus the /data directory? This will give you a clear framework for making architectural decisions in your Hugo projects.