Breaking the Widget Ceiling: How Curtis Krygier Brings Rich Custom UIs to Google Chat

Google Chat’s cardsV2 widget set is fixed — no custom layout, no charts beyond what Google ships. The fix: render the atom to a real image server-side and hand Chat a plain URL. This piece covers the real, self-hostable Cloud Run bundle, the exact wire-level trace of one request, and the Chat-setup gotchas Google’s own docs don’t lead with.

Back in June, we explored Beyond the Card Layout: A Deeper Dive into the Agent2UI (A2UI) and Google Workspace Development. That piece featured community contributor Curtis Krygier and the work he has been doing making it easier for AI agents to create UIs in platforms including Google Workspace.

Curtis recently shared an update to this work in Rich UI in Google Chat: a real, self-hostable bot built on the print-channel trick. It solves a limit a lot of developers will hit, the limitation of the card framework to render the required UI.

The clever solution is to render the complex visual elements using headless Chromium generating a PNG or animated GIF that can be shown as an image in Google Chat. Of course this has its limitations for interactive form elements but makes it possible to render complex dashboards, custom charts and more.

The really clever bit is as a developer, you create an AI agent that uses a single consistent A2UI vocabulary. If the platform supports live web components, these can be drawn interactively and if not your backend automatically “prints” the exact same A2UI element as a PNG or GIF.

Follow the source link to find out more or explore the cloud-run-renderer repo.

Source: Rich UI in Google Chat: a real, self-hostable bot built on the print-channel trick — A2UI Catalog Blog

Google Apps Script Editor: Gemini Side Panel Rolling Out!

Earlier in the year Google announced their plan to include Gemini in the side panel in the Apps Script IDE. The wait is now over for Gemini Beta and consumers on paid Google AI tiers as this feature is now rolling out!

The update brings AI assistance directly to the online Apps Script Editor. Rather than switching between browser tabs to copy code snippets, you can now generate, edit, and debug scripts with the help of Gemini. There is a dedicated Use the Gemini side panel in the Apps Script editor documentation page that includes a number of troubleshooting and best practices tips.

Testing the side panel with container bound scripts it was very useful to see that Gemini had the context of their parent document. For situations like building data processes in Google Sheets, this built-in context saves significant time as it is aware of the headings and existing data. In addition you can also use @drive_url to reference other files.

Less obvious is the option to run a custom Gem directly from the side panel menu. I’ve been experimenting using a Gem as a way of holding some Apps Script development skills. My current iteration is in this shared GASDev Gem.

[Update 06 Aug 2027: At this time it looks like Gems can be used in the Gemini side panel for the Apps Script IDE, but you can reference this skill doc e.g. Using @https://docs.google.com/document/d/1bng3y3ndTfRju0Ud9ANvdzXwdrhBvyvopQ0uniyUg_4/edit ...]

Overall, the side panel is a fantastic addition for both new and existing users looking to rapidly build custom solutions across Google Workspace. Personally it does cause some concern around users executing generated code without fully understanding its underlying logic or security permissions, particularly when you use services like Gmail and Drive.

Source: Use the Gemini side panel in the Apps Script editor  |  Google for Developers

Converting Files in Google Apps Script – From Lightweight Workarounds to Drive API Libraries

File format conversion in Google Apps Script ranges from simple string manipulations to multi-stage pipeline workflows. Depending on your source format and required output, the optimal solution might be a quick service workaround, a native Drive API call or a dedicated library capable of handling indirect conversions and OCR.

The Lightweight Approach: Converting HTML to Plain Text

At the simplest end of the spectrum, you can convert markup into plain text without writing custom regular expressions or importing heavy third-party parsers.

A clever technique shared by Kanshi Tanaike in his guide on converting HTML to plain text uses the Gmail Service. You pass the HTML string into GmailApp.createDraft() and retrieve the formatted plain text using getPlainBody().

Code Example: HTML Conversion via Temporary Gmail Draft

/**
 * Converts an HTML string to plain text using a temporary Gmail draft.
 */
function convertHtmlToPlainText() {
  const htmlContent = '<div><h1>Quarterly Summary</h1><p>Project completed on <b>Schedule</b>.</p></div>';
  
  // Create a temporary draft to utilise Gmail's HTML parser
  const draft = GmailApp.createDraft('', '', '', { htmlBody: htmlContent });
  const plainText = draft.getMessage().getPlainBody();
  
  // Immediately delete the draft to clean up
  draft.deleteDraft();
  
  Logger.log(plainText);
  // Output:
  // Quarterly Summary
  // Project completed on Schedule.
}

This clever workaround executes quickly and avoids manual string parsing. It is ideal when cleaning up web scraper output, preparing logs or stripping HTML tags from user input but comes with the downside of requiring a Gmail authorisation scope.

Direct Drive API Conversions

The Drive API provides a direct conversion mechanism. For example, by passing an HTML blob to Drive.Files.create(), Google Drive can convert this into a fully formatted Google Doc. You can then open the generated document using DocumentApp to extract clean text or manipulate document elements before trashing the temporary file.

Code Example: Converting HTML to a Doc and Extracting Text

To use this method, enable the Drive API under Services in the Apps Script editor (using Drive API v3).

/**
 * Converts an HTML string into a Google Doc via Drive API,
 * extracts the plain text from the Doc body, and trashes the file.
 */
function convertHtmlToDocAndExtractText() {
  const htmlContent = '<h1>Project Summary</h1><p>The roll-out was successful across all regions.</p>';
  const blob = Utilities.newBlob(htmlContent, MimeType.HTML);
  
  const fileMetadata = {
    name: 'Temporary Converted Doc',
    mimeType: MimeType.GOOGLE_DOCS
  };

  // Create a Google Doc from the HTML blob using Drive API v3
  const file = Drive.Files.create(fileMetadata, blob, { supportsAllDrives: true });
  
  // Open the created Doc and extract text content
  const doc = DocumentApp.openById(file.id);
  const extractedText = doc.getBody().getText();
  
  Logger.log(extractedText);
  // Output:
  // Project Summary
  // The roll-out was successful across all regions.
  
  // Trash the temporary Google Doc to maintain a clean Drive
  DriveApp.getFileById(file.id).setTrashed(true);
}

This pattern provides a reliable way to turn web markup into native document elements or extract structured text while preserving structural hierarchy.

The Heavy-Duty Approach: Multi-Step Conversions and OCR

Direct API calls work well when Google Drive natively supports the source and target MIME type pairing. However, problems arise when there is no direct conversion path – for instance, converting a Microsoft Word file (.docx) directly into a PDF, or converting an image into editable text via Optical Character Recognition (OCR).

To solve this, Bruce McPherson developed a comprehensive Apps Script file conversion library [June, 2023].

How the Library Works

  1. Path Resolution: The library evaluates the source MIME type and the target MIME type.
  2. Direct Conversion: If a direct route exists (e.g. Google Doc to PDF), it executes the transformation immediately.
  3. Indirect Pipeline: If no direct route exists (e.g. Word document to PDF), it automatically determines the intermediate steps. It first converts the Word file to an intermediate Google Doc, then exports that Google Doc as a PDF, before cleaning up temporary files.
  4. OCR Processing: For image formats (.png, .jpg), it passes the file through Drive API’s OCR engine to extract embedded text directly into a Google Doc.

This approach removes the manual work of tracking MIME compatibility matrices and chaining multiple Drive API requests manually.

Summary

Matching your conversion method to the task keeps your Google Apps Script projects lean and reliable. For rich document generation and extraction, Drive API conversions offer structured flexibility.

Bypassing Google Apps Script Limits: A Zero-Cost Pipeline for Large Datasets

Google Apps Script is easily my favorite runtime, so much so that I wrote a book about it. Yet, being a low-code environment it comes with its quirks, quotas and limitations. In a recent project I needed to process 300 spreadsheets totalling over 5 million cells in over 80,000 rows. I could eithr abandon the platform and spin up a “real” backend, or get creative. Really creative.

Dmitry Kostyuk has published a detailed guide on bypassing Google Apps Script limits to process very large JSON datasets. Rather than completely abandoning Apps Script, Dmitry shares an example zero-cost pipeline to process over 5 million spreadsheet cells without timing out. Here’s the breakdown of his architecture:

  1. Extract and compress the master data snapshot locally using Node.js and fflate (Dmitry highlights this could be hosted in Cloud Run).
  2. Encode to Base64 and slice it into text chunks.
  3. Automate multipart file uploads to Google Drive using the open-source Google Workspace CLI (gws).
  4. Use Apps Script as a thin proxy to fetch and return the text strings to the client browser.
  5. Reassemble, decompress and parse the chunks in the browser using Alpine.js.

A key component of this workflow  is the efficiency of the open-source Google Workspace CLI (gws). Rather than writing custom API upload scripts, it uses the gws +upload helper to automatically detect MIME types and execute multipart Drive uploads in a single command.

Crucially, this solution moves heavy computation to the developer machine and the user browser, keeping the Apps Script runtime focused purely on serving data. This relies on HtmlService for client-side processing and you can deploy it in standalone web apps or custom sidebars and dialogue boxes for editors like Google Docs and Sheets.

Dmitry also uses his open-source Apps Script Engine Template, which provides a pre-configured Vite, clasp and environment management setup to speed up development and allow you to standardise your build workflow. 

Read Dmitry’s full post for the complete code snippets and a look at the technical trade-offs of this approach. It is a fantastic example of using platform constraints to define a more efficient architecture!

Source: How I Shipped 341 MB of JSON Through Google Apps Script — and Lived to Tell the Tale

Reading Google Chat Spaces and Messages in Google Apps Script just got significantly easier!

In Episode 31 of the Google Workspace Developer News, an interesting announcement removes a major administrative hurdle for Google Apps Script developers interacting with Google Chat.

“You no longer need to manually configure a Google Chat app in the Google Cloud console for integrations that only use the Chat API to read data on behalf of a user using OAuth. For read-only actions, such as getting a space or listing messages, you now only need to enable the Chat API and create an OAuth client.”

The simplified Chat API setup means developers no longer need to configure a full Google Chat App in a standard Google Cloud project just to perform read-only actions. Previously, reading space messages via the Chat Advanced Service required detaching your script from the default GCP project, linking a standard GCP project, configuring an OAuth consent screen and formally enabling the Google Chat API.

Now, retrieving data is immediate. To list your Chat Spaces and read messages, you can add the Chat Advanced Service in your script editor and declare the read-only scopes in your appsscript.json manifest:

{
  "timeZone": "Europe/London",
  "dependencies": {
    "enabledAdvancedServices": [
      {
        "userSymbol": "Chat",
        "serviceId": "chat",
        "version": "v1"
      }
    ]
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/chat.messages.readonly",
    "https://www.googleapis.com/auth/chat.spaces.readonly"
  ]
}


Once your manifest is configured, you can use the Chat service to list the spaces you are a member of and the most recent messages in a specified space. Here are two example functions you can use:

/**
 * Lists the Google Chat spaces the authenticated user is a member of.
 * * Prerequisites:
 * 1. Enable the Google Chat API in Apps Script Advanced Services.
 * 2. Add the following OAuth scope to your appsscript.json manifest:
 * "https://www.googleapis.com/auth/chat.spaces.readonly"
 */
function listMySpaces() {
  try {
    let pageToken = null;
    do {
      // Call the Chat Advanced Service to list spaces
      const response = Chat.Spaces.list({
        pageSize: 50, // Adjust to retrieve more or fewer spaces per page
        pageToken: pageToken
      });

      if (!response.spaces || response.spaces.length === 0) {
        console.log("No spaces found.");
        break;
      }

      // Iterate through and log the retrieved spaces
      for (const space of response.spaces) {
        console.log(`Space Name: ${space.displayName || 'Unnamed Space'}`);
        console.log(`Resource ID: ${space.name}`);
        console.log(`Type: ${space.spaceType}`);
        console.log("-----------------------------------------");
      }
      
      // Update the page token to retrieve the next page of results
      pageToken = response.nextPageToken;
    } while (pageToken);
    
  } catch (err) {
    // Log any errors that occur during the API call
    console.error("Failed to fetch spaces with error: %s", err.message);
  }
}

/**
 * Lists the most recent messages in a Google Chat space.
 * * Prerequisites:
 * 1. Enable the Google Chat API in Apps Script Advanced Services.
 * 2. Add the following OAuth scope to your appsscript.json manifest:
 * "https://www.googleapis.com/auth/chat.messages.readonly"
 * * @param {string} spaceName The resource name of the space (e.g., "spaces/AAAAAAAAAAA").
 */
function listSpaceMessages(spaceName = "spaces/YOUR_SPACE_ID") {
  try {
    // Call the Chat Advanced Service to list messages
    const response = Chat.Spaces.Messages.list(spaceName, {
      pageSize: 10 // Adjust to retrieve more or fewer messages
    });

    // Check if the space has any messages
    if (!response.messages || response.messages.length === 0) {
      console.log("No messages found in this space.");
      return;
    }

    // Iterate through and log the retrieved messages
    for (const message of response.messages) {
      // Note: When authenticating as a user rather than a Chat app, 
      // the API only returns the sender's 'name' (resource ID) and 'type'.
      // Cross-reference this ID using the Admin SDK Directory or People API to retrieve display names.
      console.log(`Sender ID: ${message.sender ? message.sender.name : 'Unknown'}`);
      console.log(`Text: ${message.text}`);
      console.log(`Created At: ${message.createTime}`);
      console.log("-----------------------------------------");
    }
    
  } catch (err) {
    // Log any errors that occur during the API call
    console.error("Failed to fetch space messages with error: %s", err.message);
  }
}


This change drastically reduces the setup time for getting personal access to your Space data and messages. Happy scripting!!!

From AppSheet to Agents: Exploring Antigravity SDK, A2A and A2UI for Agent generation

Today marks the finish line of an intense coding sprint. I am excited to share my project: AppSheet2Agent (available at github.com/mhawksey/appsheet2agent).

If you are an AppSheet creator, you have already built amazing, structured databases and workflows. But what if your users want to interact with that same data conversationally, without leaving their chat?

Usually, connecting business applications to large language models is a massive headache:

  • You lose security context, often forcing you to rely on a single, risky service account.
  • You get flat, uninspiring text dumps that make structured data hard to read.
  • You waste hours writing manual boilerplate code.

AppSheet2Agent is an experimental solution designed specifically for existing AppSheet app creators. It allows them to rapidly create brand-new, highly interactive custom agents to interact with their existing apps. This makes it possible to use your AppSheet app directly inside platforms like Gemini Enterprise.

Here is what the MVP delivers:

  1. 🤖 Powered by Google Antigravity SDK Under the hood, it uses Google’s new Antigravity SDK to build the agent based on a conversation with the AppSheet creator. As part of this the user can provide the existing Application Definition and OpenAPI spec for their app, so Antigravity has the context if their app.
  2. 🎨 Rich Interactive Layouts (A2UI v0.8) Nobody wants walls of boring markdown text. The agent automatically renders search results and forms as beautiful, structured visual cards directly in the Gemini Enterprise chat window. It automatically reorders columns to put primary labels first, hides messy system rows, and maps data types to native elements like checklists and date pickers.
  3. 🔐 True User Identity Delegation (RunAsUserEmail) Security is paramount. The agent uses OAuth to request and access the users email address which is used directly in the AppSheet API. Every single query and update runs strictly under the identity of the person chatting with the model. No shared credentials. No security compromises.

Where the Project Stands (The Reality Check) Because this was a sprint, it is a work in progress! I have successfully compiled, registered, and deployed custom AppSheet agents to Gemini Enterprise. Right now, I am still ironing out the automated “Creator Studio” wizard workflow that compiles the packaging on the fly.

I would love to hear your thoughts! If you are an AppSheet creator, how would your users benefit from interacting with your apps inside Gemini Enterprise?

Google Cloud credits are provided for this project.

AppSheet Training YouTube Channel – Keep Up to Date with NOCODE NEWS

No-Code Business Automation – Made Simple. This channel is here to help you learn the basics of AppSheet so that you can make your business more efficient.

Many AppSheet creators are likely familiar with the AppSheet Training YouTube channel and its associated materials. Managed by QREW Technologies, a business specialising in AppSheet consulting from the early days of the platform, the channel offers hundreds of videos covering all aspects of building and managing applications.

A particularly useful addition is a “NOCODE NEWS” segment, where the QREW team highlights the latest platform updates and community contributions. Given that founder Stefan Quartemont and several team members, including Cam Lupton, hold Google Developer Expert status, this segment offers an authoritative view. This segment is also included in their wider regular live broadcasts, so hit like and subscribe to stay informed.

To get a flavour of what is included, I have picked out a few recent highlights:

  • Shift to Platform Stability [2 July 2026] – The QREW team highlights a recent community update where the AppSheet team openly acknowledged developer frustration and announced they are pausing new feature releases, including the highly anticipated Gantt chart view, and cancelling upcoming office hours. This hard choice is their way of finally tackling the bug backlog and system stability issues that creators have been pushing for.
  • User Limits Surfacing [2 July 2026] – The hosts discuss a wave of anxiety on the AppSheet community forum as Google actively enforces the 10-user limit on free-tier and prototype accounts, which includes guest users. They walk through how this sudden enforcement is temporarily blocking application access, leaving creators scrambling to either clean up user lists or upgrade to a paid licence.
  • Domain Authenticated Email Beta [18 June 2026] – The team shares details on a new “Bring Your Own Email” closed beta for Google Workspace customers. This is a massive relief for developers after AppSheet blocked external emails from the default [email protected] address to combat phishing, which broke many existing workflows. This new beta uses Google OAuth to let developers send automated emails directly from their own authenticated domains.

The QREW team does an excellent job of translating policy updates into practical developer steps, saving you the time of digging through the forums yourself. For all of these updates check out the video descriptions for additional links and resources.

Source: AppSheet Training – YouTube

 

Q2 2026 Developer Roundup – Apps Script Core Status, Drive Events GA and MCP Servers

Apps Script Becomes a Core Service

The biggest news this quarter is Apps Script is officially a core Google Workspace service [Update: June 22, 2026]! This is a significant milestone as this provides enterprise-grade data protection and technical support to Apps Script for Google Workspace customers. For enterprise adoption, this means your scripts are now safeguarded with the exact same security parameters as Gmail, Calendar and Drive.

Google Drive Events Go GA

Configure event-driven architectures for your files now that subscriptions for Google Drive events are generally available [Update: May 18, 2026]. You can use the Google Workspace Events API to monitor changes within Google Drive and trigger automated workflows via Google Cloud Pub/Sub. To get started and to understand the capabilities you can check out a recent tutorial we published on exploring the Google Workspace Events API for event-driven Google Drive automation.

Workspace Model Context Protocol (MCP) Servers

Building on other recent announcements, Google announced the Model Context Protocol (MCP) servers launched in Developer Preview to Google Workspace services [Developer Preview: April 22, 2026]. This initial release provided the base servers for Calendar, Drive, Gmail and Chat. This momentum has continued with a steady stream of MCP-related updates appearing in the release notes. Shortly after the initial launch, the Chat MCP server expanded with search_messages and send_message tools [Update: May 07, 2026]. By late May, the Drive MCP server gained a copy_file tool [Update: May 21, 2026] to duplicate and redirect files programmatically. This rapid addition of specific tools shows a clear commitment to expanding these integrations.

Workspace API Quota Changes

As we see a potential increase in consumption of Workspace data via MCPs a future consideration might be the new tiering model for API usage quotas, which started rolling out earlier this quarter [Update: May 01, 2026]. Developers executing data migrations must also plan for these changes, in particular, the change to Google Drive API usage which is now measured in quota units rather than request counts and users face a strict 1 TB daily egress limit. Quota units for Gmail methods like drafts.get, messages.get and threads.trash have also shifted.

Expanded Chat App Interactions

The Google Chat API continues to expand its interaction options. Update your Chat apps to use the quoteType field in QuotedMessageMetadata to quote messages across different spaces or threads [Update: June 12, 2026]. You can also control notification delivery by setting the createMessageNotificationOptions field to either force push notifications or send messages silently. To monitor user activity, the Google Workspace Events API now supports subscriptions to user read state updates for both spaces and threads [Developer Preview: May 28, 2026]. Configure message actions for Google Chat apps or Workspace Add-ons through the Developer Preview Program so users can invoke your app directly from the message context menu [Developer Preview: April 10, 2026]. Additionally, call the FindGroupChats method to find a group chat with specific members [Update: April 15, 2026], and use the Chat API to create a custom section to organise conversations into personalised views [Update: April 01, 2026]. To simplify access, several methods including getting messages and space events now support app authentication with administrator approval [Update: March 31, 2026].

Integrate with Google Meet

For Google Meet integrations, use the phoneAccess and gatewaySipAccess fields on the spaces resource to identify a meeting space and join calls [Update: April 16, 2026]. You can also work with artifacts by retrieving smart notes files from conference records using the get and list methods on the smartNotes resource, or subscribe to Google Meet events to monitor when notes are started, ended or generated [Update: April 02, 2026].

Manage Calendars and Drive Approvals

Following the secondary calendar deprecation notice in Q1, administrators can now programmatically transfer the ownership of secondary calendars between users within the same organisation using the Calendar API [Update: June 18, 2026]. To refine event visibility, apply the new writerWithoutPrivateAccess calendar access level [Update: June 01, 2026]. This permits read and write access to non-private events while keeping private events obscured as busy blocks. Over in Google Drive, implement the new approvals resource methods to start, decline and manage file approvals programmatically [Update: April 21, 2026].

These updates are clear evidence that there is a lot going on in Google Workspace development.

Pushing past single-tab data limits in Google Sheets canvas with =AI() and JSON

Following my previous look at the Google Sheets canvas tool I wanted to explore how we can push past single-tab data limits by using the =AI() function to generate structured JSON. If you are new to JSON, it is just a clean, standardised way of packaging information using plain text and clear labels. This technique is universally applicable to any workflow where you need to extract, evaluate and format complex data in a single step.

One of the key design rules of the new Canvas tool is that it can only build interfaces using data from a single sheet tab. If you want to show a detailed breakdown of multiple metrics, a standard spreadsheet setup would force you to create dozens of separate columns to hold every rating, piece of feedback and recommendation. This quickly becomes messy and difficult to manage.

By instructing the =AI() function to bundle all our evaluation data into a single, structured JSON block, we can squeeze a complete relational database into a single cell. This keeps our spreadsheet clean while giving the Canvas tool all the deep metadata it needs to build a rich, interactive dashboard.

What we will be making

When helping customers adopt generative AI tools in their daily workflows, one of the biggest challenges for new users is prompting. To solve this, we are going to build a personalised prompt coaching directly inside Google Sheets. Our tool will automatically analyse any draft prompt, grade its structure, highlight which key elements are missing and provide instant, actionable advice on how to improve it.

The finished, interactive Prompt Coach Dashboard rendering directly over your spreadsheet cells, complete with colour coded status badges, highlighting and a copyable master prompt. Click here to view the readonly version

To build this I will use the official Google Prompting Guide 101. The guide uses a framework with four core pillars:

  • Persona: The specific role, voice or professional perspective assigned to the model (e.g., “You are an expert technical writer”).
  • Task: The core instruction or action verb directing the model (e.g., “Draft an onboarding guide”). This is the only non-negotiable component.
  • Context: Relevant documents, emails, file identifiers or situational details that ground the model in accurate facts (e.g. referencing ambient tags like “@UAT_Results”).
  • Format: The layout, style, length limits or output type requested for the final response (e.g., “Output as a bulleted list of 5 steps”).

By checking draft prompts against these pillars, our Sheets-based coach can pinpoint exactly what a user needs to add to get a great result on their first try. Let us look at how to set up this workflow to parse, score and visualise your prompting templates using dynamic JSON.

Step 1: Set Up the Prompt Auditor Formula

Open your Google Sheet and prepare your columns. Label Column A as “Raw Prompt” and Column B as “JSON Evaluation”.

Enter a draft prompt in cell A2, such as: “Draft an email to my colleague who is running this UAT and explain what they need to do next.”

To construct a prompt to return structured JSON data from the Google Sheet =AI() function I used Gemini to draft and refine our system instructions. Developing a complex, multi-layered instruction that outputs strict JSON without broken formatting is an iterative process. Here is the system prompt I came up with (select cell B2 and write the following formula to analyse the prompt against the four prompting pillars):

=AI("Analyse the draft prompt provided against the Google Prompting Guide 101 framework, which utilises 4 pillars: Persona, Task, Context and Format.

### THE SYNERGY-BASED COACHING BLUEPRINT:
Your goal is to evaluate if the user's prompt is structurally matched to their operational goal, rather than strictly checking off all the components of a prompt. Always recommend including a strong verb or command as part of the prompt task; this is the most important part of a prompt.

1. Task Classification:
   - Identify if the task is primarily CREATIVE/WRITTEN (e.g., drafting emails, brainstorming, translating, slide content) or DATA/OPERATIONAL/ANALYTICAL (e.g., summarising documents, extracting metrics, creating task lists, setting limits).

2. Core vs. Enhancer Logic:
   - 'Task' is the only non-negotiable core pillar.
   - The other 3 pillars (Persona, Context, Format) are optional 'Enhancers'. Evaluate if the user chose the RIGHT enhancers for their specific goal:
     - For CREATIVE/WRITTEN goals: Look primarily for 'Persona', 'Tone' or 'Context' enhancers. Creative prompts do not strictly need constraints or format grids.
     - For DATA/OPERATIONAL goals: Look primarily for 'Format', 'Context' (referencing active file context) or 'Constraints'. Analytical prompts do not strictly need a persona or a tone.

3. Dynamic Tier Ratings:
   Based on the synergy check above, assign one of these three ratings:
   - Foundation: The prompt has a core command but lacks appropriate enhancers (leaving the AI guessing on essential details or voice).
   - Effective: The prompt provides a clear task accompanied by relevant enhancers (e.g., standard persona or basic format), but lacks precise structural boundaries, styling constraints or negative limitations.
   - Masterful: The prompt is highly optimised. It features a strong command, targeted context (or active ambient tags) and multiple precise constraints (such as explicit word counts, custom layout instructions, specific tone rules or negative exclusions). It is designed to get the perfect result on the first run with zero back-and-forth iteration.

### PILLAR MAPPING:

For each of the 4 pillars (task, persona, context, format), status must be exactly 'found', 'weak' or 'missing'. 

- Ambient references like 'this document', 'the open email' or '@Roadmap' represent strong contextual grounding. Mark them 'found'.
- Format and Constraints: Note that 'Format' governs structural layout, timing rules, explicit constraints and negative exclusions (e.g., 'Do not suggest...'). You MUST map these structural guidelines and behavioral boundaries to the 'format' pillar text array.
- Highlight Substrings: When extracting text substrings for the 'text' array, you must copy them EXACTLY as they appear in the original draft prompt. Do not correct spelling, alter punctuation, adjust capitalisation or paraphrase, as this will break the frontend highlighting engine.

### RULE OF COMPLETENESS:

- 'improvedPrompt' MUST NEVER BE EMPTY. You must upgrade the original prompt so that it strictly qualifies for the Masterful rating. To do this, you MUST enrich the original request by injecting:
  1. A highly tailored, expert persona suited to the task.
  2. Explicit structural and formatting guidelines (such as a markdown table with designated column headings or a multi-part layout).
  3. Precise constraints and absolute metrics (such as explicit time limits, word limits or step limits).
  4. At least one negative constraint or exclusion (such as what the model should avoid or ignore).
- 'keyRecommendations' MUST NEVER BE EMPTY. It must contain exactly 3 concrete, actionable pieces of advice explaining what they should add or adjust to improve their prompt's effectiveness inside Google Workspace apps.

### ANALYSIS DIRECTIVES:

- Not every prompt requires all 4 pillars. Think of it like a stool: it only needs 3 or 4 strong legs to stand. DO NOT force or hallucinate a pillar if it is not explicitly present. If a pillar is missing, status must be 'missing' and the text array should be empty.
- A single pillar may appear more than once in a prompt (for example, a prompt can have multiple distinct constraints or background contexts). Return ALL matched case-sensitive substrings for that pillar inside the 'text' string array.
You MUST respond with a single, raw, valid JSON object. Do not include markdown wraps, backticks or preamble text. Use this exact schema. Ratings must be one of these exact strings: [`Foundation` | `Effective` | `Masterful`].

{
  ""rating"": ""Effective"",
  ""improvedPrompt"": ""[Rewritten optimised prompt here]"",
  ""keyRecommendations"": [
    ""First action recommendation"",
    ""Second action recommendation"",
    ""Third action recommendation""
  ],
  ""pillars"": {
    ""task"": {""status"": ""found"", ""text"": [""[substring 1]"", ""[substring 2]""], ""feedback"": ""[guidance]""},
    ""persona"": {""status"": ""missing"", ""text"": [], ""feedback"": ""[guidance]""},
    ""context"": {""status"": ""weak"", ""text"": [""[substring]""], ""feedback"": ""[guidance]""},
    ""format"": {""status"": ""found"", ""text"": [""[substring]""], ""feedback"": ""[guidance]""}
  }
}", B2)

Writing a prompt that outputs perfectly formatted data every single time might feel intimidating, but there are two key features built into this instruction that helps with consistency:

  • Strict JSON Directives: We explicitly tell Gemini to return a single, raw, valid JSON object without any conversational filler or markdown formatting (like triple backticks ```). This ensures that the cell gets only the clean code it needs to be parsed, with nothing extra to cause formatting errors.
  • Exemplar Output: At the very end of the prompt, we provide an empty example structure of the exact JSON we expect. By providing this template, Gemini does not have to guess what keys or structures to use, it simply swaps our placeholder text with its actual evaluation, improving the consistency of the output.

Drag this formula down your column. The =AI() function processes the input text and outputs structured JSON directly into the cell:

{
"rating": "Foundation",
"improvedPrompt": "You are a Senior QA Lead and Project Management Expert. Based on the UAT results in this document, draft a professional and encouraging email to my colleague who is running this UAT. Explain the next steps required to reach sign-off, specifically outlining the bug triaging process and the re-testing cycle. Format the email with a clear 'Action Items' bulleted list and a 'Timeline' section. Constraints: The email should be under 200 words and maintain a collaborative tone. Do not include technical jargon that might confuse non-technical stakeholders.",
"keyRecommendations": [
"Define a professional persona, such as a Senior QA Lead, to establish the right tone and authority for the email.",
"Specify exactly what the 'next steps' are (e.g., bug triaging or re-testing) to reduce ambiguity for the AI.",
"Incorporate structural constraints like word counts and specific section headers to ensure the output is ready to send."
],
"pillars": {
"task": {
"status": "found",
"text": [
"Draft an email to my colleague who is running this UAT and explain what they need to do next."
],
"feedback": "The task is clear and uses a strong verb, though the specific 'next steps' are left for the AI to guess."
},
"persona": {
"status": "missing",
"text": [],
"feedback": "Adding an expert persona like a QA Lead would help set a professional and authoritative tone."
},
"context": {
"status": "found",
"text": [
"this UAT"
],
"feedback": "There is a brief mention of context, but it doesn't explicitly reference the specific document or data the AI should use."
},
"format": {
"status": "missing",
"text": [],
"feedback": "Providing structural guidelines, such as using bullet points for steps, would improve the email's clarity."
}
}
}

This formula pulls out the key parts of each prompt, giving us a neat, organised set of details to display.

What the structured JSON output looks like inside your Google Sheets cell after dragging the =AI() formula down Column B.

Step 2: Create Your Sheets Canvas

Once your evaluation column is populated, open the canvas creation tool to build your custom interface. For this your account needs to be in the Gemini Alpha or if using a personal Gmail account paying for Google AI Ultra or Google AI Pro.

  1. Select Insert from the top menu bar.
  2. Click Create a canvas.

This action opens the Gemini side panel directly inside your spreadsheet.

Step 3: Link Your JSON Data to the Canvas Layout

This is where the magic happens. Since Canvas can read data directly from your active sheet tab, we can instruct Gemini to build visual dashboard elements that bind directly to our JSON column.

To help Gemini understand how to split our single-cell JSON block into distinct UI elements (like progress cards, recommendation lists and copyable text blocks), we provide it with a clear design spec. This spec defines the visual layout and includes a sample set of mock data to show Gemini exactly how our JSON keys map to each display card.

Enter this layout instruction into the side panel prompt box to construct the dashboard:

### **FRONTEND REQUIREMENTS**
The application must feature a modern, minimalist Dashboard interface. Use the **Inter** font family, soft shadows, rounded borders (rounded-2xl), and a clean layout featuring:
1. **Sidebar or Dropdown Selector:** * A select dropdown or list of prompts loaded dynamically from the Google Sheet.  
   * On change, it loads the selected prompt's diagnostic metrics into the main canvas.  
2. **Interactive Highlight Map:** * A container displaying the selected original prompt text with color-coded, underlined highlights representing found pillars.  
   * It MUST support multiple occurrences per pillar (i.e., if pillars[key].text is an array of strings, loop through and highlight every substring occurrence).  
   * If a highlighted segment is clicked, it highlights that specific pillar in the Diagnostic View below.  
3. **Pillar Diagnostic Matrix:** * A 4-column visual status strip with colored indicators showing which pillars are Found (Green), Weak (Amber), or Not Included (Red).  
   * Below the status strip, a card showing details of the active/selected pillar: its definition, the specific matched substring(s) extracted from the prompt, and actionable "Pillar Optimization Guide" feedback.  
4. **Key Recommendations &amp; Optimized Version:** * Displays the 3 top recommendations as numbered target boxes.  
   * Shows the final rewritten "Master Optimized Prompt" inside a clear, styled, copyable container with a one-click clipboard copy button.

### Sample Mock Data to pre-load as default state:
```json
{
  "rating": "Effective",
  "improvedPrompt": "As an expert copywriter, draft a warm, professional thank-you email to @Client for attending the Q3 planning workshop, highlighting our next steps to finalize the product roadmap by Friday.",
  "keyRecommendations": [
    "Specify a persona like 'expert copywriter' to give the email's tone a more distinct and polished feel.",
    "Add a constraint limiting the length of the email to keep it concise and easy to read.",
    "Clarify the format if you want specific sections or a bulleted summary of the next steps."
  ],
  "pillars": {
    "task": {
      "status": "found",
      "text": ["draft a warm, professional thank-you email"],
      "feedback": "Strong core instruction with clear action verbs."
    },
    "persona": {
      "status": "missing",
      "text": [],
      "feedback": "No explicit role or persona defined. Gemini will write from a generic baseline."
    },
    "context": {
      "status": "found",
      "text": ["@Client", "attending the workshop"],
      "feedback": "Excellent use of ambient context referencing a client and specific workshop."
    },
    "format": {
      "status": "weak",
      "text": [],
      "feedback": "You didn't specify length or structure. Consider adding constraints like 'under 150 words' or 'in 3 paragraphs'."
    }
  }
}
```

Gemini hopefully processes your prompt, compiles the interactive layout and renders the dynamic dashboard directly over your spreadsheet cells, similar to this readonly version

Summary

This workflow hopefully demonstrates an easy approach to make more powerful Google Sheets canvas apps. Instead of managing sprawling, hard-to-maintain tables with dozens of columns, or writing hundreds of lines of Google Apps Script to parse text, we can use a single, well-structured formula to run the entire analytical process. The =AI() function does not just gather raw data, it simultaneously classifies tasks, maps dynamic structures, extracts exact highlights and packages this data into a single, clean cell.

Connecting GCP Budget Alerts to AppSheet: A Step-by-Step Guide

Have you ever woken up to a massive Google Cloud bill because a developer accidentally left a cluster… Tagged with googlecloud, appsheet, finops, cloudbilling.

We’ve probably all been there, creating and forgetting a test Google Cloud project. Here is a nice solution published by Google Developer Expert Aryan Irani knitting together various Google Cloud / Workspace services. In a guide Aryan walks through how to set up an event-driven workflow, using Cloud Pub/Sub to help prevent unwanted Google Cloud spend right from your phone. Instead of waiting for an email notification, this system uses Google Sheets, AppSheet and Google Cloud Functions to monitor your budget and give you a push notification and easy way to shut down a project.

If you want to simplify this architecture further you can replace the webhook target with a Google Apps Script function. By using the auto-generated Cloud Resource Manager client library from the Google API Client Library Generator for Apps Script, you can programmatically shut down the entire target project directly from your app. Configuring the single cloud-platform OAuth scope in your script manifest allows you to delegate security control directly to Google Cloud IAM roles. Moving the project to a soft-delete state instantly stops all active billing across all services, databases and compute instances, providing a complete emergency stop-switch.

This project is a fantastic example of using low-code tools to solve real infrastructure problems. You can read the full step-by-step tutorial and learn how to configure this for your own projects.

Source: Connecting GCP Budget Alerts to AppSheet: A Step-by-Step Guide – DEV Community