Turn Gen AI Prompts into Google App Script Code

Google Apps Script (GAS) is a powerful tool that allows you to automate tasks across Google Workspace (GWS) applications such as Gmail, Google Sheets, Google Docs, and more.

With the advent of Generative AI (Gen AI), even those with minimal coding experience can create useful scripts.

This article will guide you through the process of crafting simple prompts for Gen AI to generate the code you need, while emphasizing the importance of clear communication in script design. One effective tool for this purpose is ChatGPT.

The quality of the output you receive from Gen AI is directly related to the clarity of your prompt.

By asking clear, straightforward questions and providing necessary context, you can guide the AI to produce accurate and functional scripts.

Here’s how you can master the basics of prompting Gen AI for GAS:

1. Identify the Task and Application:

• Clearly define what you want to achieve with your script.
• Specify the Google Workspace applications involved (e.g., Gmail, Google Sheets).

2. Break Down the Task:

• List the specific steps or components required to complete the task.

3. Frame Your Prompt:

• Write a clear and concise prompt that outlines the task.
• Include relevant details such as the desired functionality and the applications used.

4. Provide Context and Constraints:

• Mention any specific constraints or requirements (e.g., trigger conditions, formatting rules).
• Include example data or scenarios if applicable.

5. Request Error Handling:

• Ask the AI, such as ChatGPT, to include error handling in the script.
• Specify how you want to handle common issues (e.g., invalid input, missing data).

6. Review and Refine:

• Review the generated code critically.
• Refine your prompt if the output is not as expected, providing additional details or clarifications.

7. Troubleshoot Errors:

• If you encounter errors, prompt the AI, such as ChatGPT, to help troubleshoot by pasting the error code or providing a screenshot.
• Ask for specific fixes or explanations to understand and resolve the issue.

Let’s walk through some example prompts for different Google Workspace applications, try them out yourself by copy and pasting the prompts below into your preferred Gen AI tool like ChatGPT:

Task: Send an email notification when a new row is added to a Google Sheets document.

Prompt (copy this): I need a Google Apps Script that sends an email notification via Gmail whenever a new row is added to my Google Sheets document. The email should include the data from the new row. Please include error handling for invalid email addresses.

Task: Create a monthly report by summarising data from multiple sheets.

Prompt (copy this): I need a Google Apps Script that generates a monthly report in Google Sheets. The script should summarise data from multiple sheets within the document, calculate totals and averages, and output the results in a new sheet named “Monthly Report”. Include error handling for missing or incomplete data.

Task: Schedule events based on form responses in Google Forms.

Prompt (copy this): I need a Google Apps Script that creates events in Google Calendar based on responses from a Google Forms survey. The script should extract the event details (date, time, description) from the form responses and schedule the events accordingly. Please handle errors such as invalid dates or duplicate events.

Errors are a natural part of the coding process. When you encounter an error, you can leverage Gen AI to help troubleshoot and fix the issue, ChatGPT can be particularly helpful in these situations:

• Describe the Error: Provide a detailed description of the error you encountered.
• Paste Error Code: Copy and paste the error code or message into your prompt.
• Request a Fix: Ask the AI, such as ChatGPT, to help resolve the error, providing context and any relevant code snippets.

I encountered the following error while running my Google Apps Script: “TypeError: Cannot read property ‘getValues’ of null”. Please help me troubleshoot and fix this error. Here is the relevant part of my code:

Mastering the art of prompting Gen AI for Google Apps Script requires clear communication, critical thinking, and iterative refinement.

By following the steps outlined in this article, you can effectively guide the AI to generate accurate and functional scripts that automate your workflow in Google Workspace.

Remember, the more specific and detailed your prompts, the better the output you’ll receive. Your boss will be amazed at how quickly and efficiently you can automate tasks. Happy scripting!