Sync Google Sheets with Hugo

Method 1: The Simple "Publish to the Web" Approach

The most direct way to get data out of a Google Sheet is to publish it. This method is excellent for data that isn't sensitive and needs to be accessible via a public URL.

Google Sheets allows you to publish a sheet as a CSV (Comma-Separated Values) file.

Once you have this URL, you can:

1. For the /data directory: Use Hugo's built-in resources.GetRemote function directly in your templates to fetch this CSV data in real-time when the site builds. This is a very efficient way to pull in external data.
2. For the /content directory: This is less direct. You would need a script to download the CSV from the public URL and then process it to create individual Markdown files.

Pros: Extremely simple to set up. No coding is required on the Google Sheets side.
Cons: The data is public. It's less flexible for complex data transformations.

This method is good for simple datasets, but for a true CMS experience, we need more control.

Method 2: The API-Driven Approach with Google Apps Script

A more robust and secure method is to turn your Google Sheet into a private API. This gives you precise control over what data is exposed and how it's formatted. We'll use Google Apps Script, a JavaScript-based platform built into Google's services.

Don't worry if you're not a JavaScript expert. The goal here is to understand the logic of the script so you can later instruct an AI to write it for you. This video provides an excellent walkthrough of the entire process.

After following the video, you will have:

• A script attached to your Google Sheet.
• A private URL that, when visited, returns the data from your sheet in a clean JSON format.

This API endpoint is the key. Now, instead of dealing with a raw CSV file, we have a structured JSON feed that a script can easily consume.

Method 3: "Vibe Coding" the Synchronization Script

We now have an API endpoint serving our content. The final step is to create a local script that fetches data from this endpoint and writes the corresponding files to our Hugo project's /content or /data folders.

This is where "vibe coding" shines. We won't write this script by hand; we'll instruct an AI to do it for us. The process involves clearly defining what we want and the rules for how it should be built.

The "Vibe Coding" Workflow

A powerful pattern for this is to separate your instructions into two files: a specification and a style guide.

From Prompt to Python: A Real-World Example

Now, let's see this in action. How do you translate a specification into a working script that creates Hugo content files? The following article provides a perfect example. The author uses AI to generate a Python script that fetches data from an API (Raindrop.io) and creates Markdown files for a Hugo blog. The logic is identical to what we need.

The key takeaways from this example are:

• The Prompt: The prompt is a simple, clear list of requirements written as comments. It specifies where to get the data, how to format the output files, and what logic to apply (e.g., don't re-process existing files).
• The Script: The generated Python script uses common libraries (requests to fetch from the API, os to handle files) to perform the exact steps outlined in the prompt.
• The Result: It successfully creates .md files in the content/bookmarks directory, formatted with the correct Hugo front matter.

{
  "title": "SEO Strategy",
  "slug": "seo-strategy",
  "summary": "Comprehensive SEO plans to boost your visibility.",
  "content": "Full markdown content about our SEO services..."
}

# This script synchronizes services from our Google Sheet API to our Hugo site.

# 1. Define the API endpoint URL.
# API_URL = "https://your-google-apps-script-url/exec"

# 2. Fetch the JSON data from the API endpoint.

# 3. For each service object in the JSON array:
#    a. Create a filename using the 'slug' field (e.g., "seo-strategy.md").
#    b. The file should be placed in the 'content/services/' directory.
#    c. If a file with that name already exists, skip it to avoid duplicates.

# 4. Generate the content for each markdown file with the following front matter and content structure:
#    +++
#    title = "<title from JSON>"
#    summary = "<summary from JSON>"
#    date = <current date in YYYY-MM-DD format>
#    +++
#
#    <content from JSON>

# 5. Print a message indicating how many new service files were created.

Conclusion

You have just learned the critical technical component of the "Google Sheets as a CMS" workflow. You now have a complete path from a simple spreadsheet row to a fully rendered page on your Hugo site, all automated through a script generated with AI assistance.

Key Takeaways:

• There are multiple ways to get data out of Google Sheets, from a simple public CSV to a robust private JSON API using Google Apps Script.
• The API-driven approach is more secure and flexible for a production website.
• The "vibe coding" workflow involves writing a clear specification (SPEC.md) that an AI tool can use to generate a synchronization script (e.g., in Python).
• This script acts as the bridge, fetching data from your API and writing the necessary files to your Hugo project's /content or /data directories.

Preview of the next lesson:
Now that we've built the technical pipeline, our next step is to create a process around it. In the final lesson of this module, we will focus on defining a content update process for non-technical users. We'll document the workflow, establish conventions, and ensure that anyone on your team can confidently update the website by simply editing the Google Sheet we've configured.