Introduction
When developing solutions for Google Workspace, whether it’s add-ons, automations, or custom workflows, managing configuration data is a common requirement. JSON files are a natural fit for this – they are human-readable, easy to work with, and flexible. While there are many ways to store these configurations, using Google Drive offers some distinct advantages, particularly when combined with the power of Google Apps Script. This article explores the benefits of this approach, shares a real-world scenario where quick editing was needed, and discusses the journey from a rapid AI-assisted solution to a more robust tool.
Why Store JSON Configuration or Data in Google Drive?
For developers already working within the Google Workspace platform, storing project-related JSON files directly in Google Drive makes a lot of sense:
- Centralised & Secure Storage: Store your JSON files within organised Google Drive folder hierarchies alongside other project assets. This not only centralises your project data but also allows you to utilise Drive’s robust sharing controls (Access Control Lists – ACLs) to securely manage who can view or edit sensitive configuration data.
- Version History: Google Drive automatically tracks file versions, providing a safety net if you need to revert changes.
- Native Workspace Data Format: Many Google Workspace APIs (like the Admin SDK, Calendar API, etc.) return data formatted as JSON. Storing these responses directly in Drive can be a convenient way to cache or process this information using Apps Script without immediate complex database setups.
- Seamless Apps Script Integration: This is the key technical benefit. Google Apps Script provides native services (DriveApp) to easily access and manipulate files stored in Drive, making reading, writing, and processing this JSON data straightforward.
The Editing Challenge: When Manual Tweaks are Needed
Recently, while working on a project, I encountered a situation where I needed to quickly manually modify several JSON configuration files stored in Google Drive.
The standard workflow would involve downloading, editing locally, and re-uploading – cumbersome for quick changes. Google Drive doesn’t have a built-in editor suitable for JSON syntax highlighting and validation. I needed a faster, in-browser solution.
Gemini to the Rescue: “Vibe Coding” a Quick Fix
Not wanting to get sidetracked building a full-fledged tool, I turned to Gemini. My initial request was simple:
“Can you write me a google apps script web app for me to use to edit a json file in Google Drive. I would like to provide a drive url which open the json file in a basic code editor and includes a save button which updates the existing json file in drive”
Gemini immediately provided the necessary Apps Script code (Code.gs and index.html) for a basic, functional online editor. It solved my immediate problem rapidly and it even looked nice.
From Quick Prototype to Robust Solution: The Development Reality
While the Gemini-generated solution was a lifesaver, the journey highlighted the significant difference between a quick fix and a more robust solution. To do so required a degree of technical knowledge and skill. My subsequent requests to Gemini illustrate this evolution:
- Improving the UI/UX (Syntax Highlighting): The basic <textarea> worked, but for JSON, syntax highlighting and basic validation are crucial. My next prompt was: “Is there a way to add a basic json linter/syntax highlighter to the web app?” This introduced the need for external JavaScript libraries (like CodeMirror or Ace Editor) and the associated CSS, adding complexity.
- Managing Dependencies (Local Hosting): Relying on external CDNs can be problematic and introduces risks like external dependency, security vulnerabilities, and version inconsistencies. With this in mind I then asked: “Rather than using cdn hosted versions… I would like to host locally in the apps script web app.” This led down a rabbit hole of trying to include local files within Apps Script’s HTML Service, involving techniques like custom include functions and wrestling with HtmlService limitations (like the “Malformed HTML content” errors I encountered). This required specific knowledge of Apps Script templating, its constraints, and other existing solutions (in this case I was already aware of Bruce Mcpherson’s Reusing html stuff between Apps Script projects method. I even explored alternative methods based on online examples.
- Evaluating Libraries: Realising the limitations of CodeMirror, I considered more powerful options that I already knew about: “Ok – do you think it would be better to implement JSON Editor https://github.com/josdejong/jsoneditor“
The AI was fantastic for the initial boost, but building the polished, reliable tool required specific technical knowledge, iterative refinement, and problem-solving based on understanding the underlying technologies and their limitations.If you would like to mark my final homework and some further Gemini wrangling/tweaking below is a screenshot of my own little JSON Editor for Google Drive, which you can view/copy.
Conclusion
Storing JSON files in Google Drive is a practical and efficient strategy for Google Workspace developers, thanks to the ease of integration with Google Apps Script. While Drive lacks a native JSON editor, AI tools like Gemini can be incredibly effective at rapidly prototyping solutions for specific needs, such as the quick web-based editor described here.
However, it’s important to recognise the difference between a quick, functional prototype and a polished, robust application. As demonstrated by the evolution from a simple editor request to tackling syntax highlighting, local dependencies, and styling frameworks, building robust tools still requires development effort, knowledge of relevant frameworks, libraries, and the specific constraints of the Apps Script platform. Understanding both the power of quick solutions and the requirements of robust engineering is still a required skill for effective development in the Google Workspace ecosystem.
