HomeCasa Prompts

Automate Google Sheets and Gmail with Google Apps Script

Generate a ready-to-paste Google Apps Script: it reads your sheet, sends emails or creates events, runs on a trigger, avoids duplicates and includes a test mode and setup steps. Designed for ChatGPT, Claude, Gemini and Microsoft Copilot. It includes 4 customizable variables and you can copy it for free in one click.

Intermediate · Code · Pending editorial review

Designed for:

Prompt

Act as an expert Google Apps Script and Google Workspace developer who writes robust automations for non-technical teams.

Context:
- What I want to automate: {{task}}
- Sheet structure (tab name and columns with a sample row): {{sheet}}
- When it should run: {{trigger}}
- Services involved (Gmail, Calendar, Drive, Docs, Forms): {{services}}

Deliverables:
1. A 4-6 step summary of the flow and the assumptions you make about the data.
2. Complete Apps Script code (JavaScript V8) with:
   - A CONFIG block at the top (tab name, columns, sender, subject) so it can be changed without touching the logic.
   - Bulk reads with getValues() and bulk writes, not cell by cell.
   - A status column to mark processed rows and prevent duplicate sends if the script runs twice.
   - A test mode (DRY_RUN) that logs what it would do without sending anything.
   - Per-row error handling, logging with console.log and a summary at the end.
   - LockService if concurrent runs are possible.
3. A function that creates the trigger in code, plus the manual alternative from the Triggers panel.
4. Step-by-step setup: Extensions > Apps Script, paste, save, authorize permissions (explain which permissions it will request and why).
5. Limits to keep in mind: daily email quotas and maximum execution time by account type; write [VERIFY in Google's quotas documentation] instead of exact numbers.
6. A test plan with 3 sample rows, including one with empty or invalid data.

Constraints:
- No external libraries or paid services.
- Never ask for or store passwords in the code.
- If the sheet structure is unclear, ask me before writing the code.

Format: numbered sections, the code in a single block and comments in English.

How to use it

Describe your columns with one real row (no personal data). Run the script with DRY_RUN on first, check the log, and only then switch it off and create the trigger.

Usage example

Task: email reminder 3 days before each invoice is due · Sheet: "Invoices" tab with client, email, amount in USD, due date, status · Trigger: daily at 8 a.m. · Services: Gmail.

Sample output

2. Code (Apps Script, V8)

const CONFIG = {
  SHEET_NAME: 'Enrollments',
  STATUS_COL: 5,            // A=name, B=email, C=course, D=start date, E=status
  SUBJECT: 'Welcome to your course',
  DRY_RUN: true,            // set to false once the test run looks right
};

function sendWelcomeEmails() {
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(30 * 1000)) return;           // another run is in progress
  try {
    const sheet = SpreadsheetApp.getActive().getSheetByName(CONFIG.SHEET_NAME);
    const lastRow = sheet.getLastRow();
    if (lastRow < 2) return;
    const rows = sheet.getRange(2, 1, lastRow - 1, CONFIG.STATUS_COL).getValues();
    let sent = 0;
    const statuses = rows.map(([name, email, course, start, status], i) => {
      if (status) return [status];                // already processed: skip
      try {
        if (!/^[^@\s]+@[^@\s]+\.[^@\s]+$/.test(email)) throw new Error('invalid email');
        const date = Utilities.formatDate(new Date(start), Session.getScriptTimeZone(), 'MMM d, yyyy');
        const body = `Hi ${name},\n\nWelcome to "${course}"! We start on ${date}.`;
        if (CONFIG.DRY_RUN) { console.log(`[DRY_RUN] would email ${email}`); return ['']; }
        GmailApp.sendEmail(email, CONFIG.SUBJECT, body);
        sent++;
        return ['SENT ' + new Date().toISOString()];
      } catch (e) {
        console.log(`Row ${i + 2}: ${e.message}`);
        return ['ERROR: ' + e.message];
      }
    });
    sheet.getRange(2, CONFIG.STATUS_COL, statuses.length, 1).setValues(statuses); // one bulk write
    console.log(`Done: ${sent} sent out of ${rows.length} rows`);
  } finally {
    lock.releaseLock();
  }
}

function createTrigger() {
  ScriptApp.newTrigger('sendWelcomeEmails')
    .forSpreadsheet(SpreadsheetApp.getActive()).onFormSubmit().create();
}

The status column prevents duplicate sends if the script runs twice. The Google Calendar invite goes in the same loop once the course calendar is confirmed → [FILL IN]…

Key facts

Category
Programming & development · Automations
Output type
Code
Level
Intermediate
Designed for
ChatGPT, Claude, Gemini and Microsoft Copilot
Verified in
Pending editorial verification
Variables
What to automate, Sheet structure, Trigger and Services involved
Language
English
Price
Free, no sign-up
Updated
Nov 7, 2026

Frequently asked questions

Do I need to know how to code to use the script?

Not much: you paste the code, adjust the CONFIG block and authorize permissions. Still, test it on a copy of the sheet before using it with real data.

Why does the script ask for so many permissions?

Apps Script requests access to every service the code uses, such as Gmail or Calendar. Review the list: if it asks for something the script does not need, the code can be adjusted to narrow the permissions.

What is this prompt for?

Generate a ready-to-paste Google Apps Script: it reads your sheet, sends emails or creates events, runs on a trigger, avoids duplicates and includes a test mode and setup steps.

Which AI tools does it work with?

It is designed for ChatGPT, Claude, Gemini and Microsoft Copilot. We have not verified it in these tools yet, so results may vary between them.

How do I customize the prompt?

Click “Customize” and fill in the variables (What to automate, Sheet structure, Trigger and Services involved). The text updates instantly and you can then copy it in one click. Values are stored only in your browser.

Do I need experience to use it?

It is intermediate: it helps to know the topic so you can provide good context and review the result critically.

Is this prompt free?

Yes. Every HomeCasa Prompts prompt is free, with no sign-up or subscription. You can also use them in professional projects.

Can I trust the AI output?

Treat it as a high-quality draft: always check data, figures and important claims before publishing or making decisions.

Related prompts