How to Use Gemini for Google Apps Script Automation: Complete Workspace Task Automation Guide

Why Google Apps Script + Gemini Is the Most Underrated Automation Stack

Every office worker spends 5-15 hours per week on repetitive Google Workspace tasks: copying data between spreadsheets, sending follow-up emails, creating calendar events from form submissions, generating weekly reports from raw data. These tasks are too small to justify a dedicated automation platform (Zapier, Make) but too frequent to ignore.

Google Apps Script is the native automation layer for Google Workspace. It can automate anything in Sheets, Docs, Gmail, Calendar, Drive, and Forms — with no external tools, no API keys, and no monthly subscription. The problem: Apps Script is JavaScript-based, and most non-developers cannot write it.

Gemini closes this gap. Describe what you want to automate in plain language, and Gemini generates working Apps Script code. You paste it into the script editor, run it, and the task is automated. No coding experience required — just the ability to describe your workflow clearly.

This guide covers the most common automation patterns, with real scripts you can copy and modify.

Getting Started

Opening the Apps Script Editor

From Google Sheets: Extensions > Apps Script

From Google Docs: Extensions > Apps Script

Standalone script: Go to script.google.com > New Project

Your First Automation with Gemini

Open Gemini and describe your task:

"Write a Google Apps Script that reads all rows in a Google
Sheets spreadsheet, finds rows where column D (Status) is
'Overdue', and sends an email to the address in column B
(Email) with a reminder that their task in column A (Task)
is overdue."

Gemini generates the script. Copy it into the Apps Script editor and run.

Automation Pattern 1: Spreadsheet Data Processing

Auto-Sort and Clean Data

"Write a Google Apps Script that:
1. Opens the active spreadsheet
2. Selects the 'Raw Data' sheet
3. Removes any rows where column A is empty
4. Removes duplicate rows (based on column C - Email)
5. Sorts all data by column D (Date) in descending order
6. Logs how many rows were removed and how many remain"

Gemini output (typical):

function cleanAndSortData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('Raw Data');
  var data = sheet.getDataRange().getValues();
  var header = data.shift();

  // Remove empty rows
  var filtered = data.filter(function(row) {
    return row[0] !== '';
  });

  // Remove duplicates by email (column C)
  var seen = {};
  var unique = filtered.filter(function(row) {
    var email = row[2].toString().toLowerCase();
    if (seen[email]) return false;
    seen[email] = true;
    return true;
  });

  // Sort by date (column D) descending
  unique.sort(function(a, b) {
    return new Date(b[3]) - new Date(a[3]);
  });

  // Clear and rewrite
  sheet.clearContents();
  sheet.getRange(1, 1, 1, header.length).setValues([header]);
  if (unique.length > 0) {
    sheet.getRange(2, 1, unique.length, header.length)
      .setValues(unique);
  }

  Logger.log('Removed: ' + (data.length - unique.length)
    + ' rows. Remaining: ' + unique.length);
}

Auto-Generate Summary Rows

"Write a Google Apps Script that reads the 'Sales' sheet,
groups rows by column B (Region), calculates the sum of
column E (Revenue) for each region, and writes the summary
to a new sheet called 'Regional Summary' with columns:
Region, Total Revenue, Number of Transactions, Average
Transaction Value."

Cross-Spreadsheet Data Sync

"Write a Google Apps Script that:
1. Opens the source spreadsheet by ID: [source_id]
2. Reads all data from 'Master List' sheet
3. Opens the destination spreadsheet by ID: [dest_id]
4. Clears the 'Synced Data' sheet in the destination
5. Copies all data from source to destination
6. Adds a timestamp in cell A1 of a 'Sync Log' sheet:
   'Last synced: [date and time]'

This should run automatically every day at 6 AM."

Automation Pattern 2: Gmail Automation

Auto-Reply to Specific Email Patterns

"Write a Google Apps Script that:
1. Searches Gmail for unread emails with subject containing
   'Quote Request'
2. For each matching email:
   a. Extracts the sender's name and email
   b. Sends an auto-reply: 'Hi [Name], thanks for your quote
      request. Our team will review your requirements and
      respond within 24 hours. - Sales Team'
   c. Labels the email 'Quote Requests'
   d. Marks it as read
3. Runs every 15 minutes via trigger"

Email Digest Generator

"Write a Google Apps Script that every Monday at 8 AM:
1. Searches Gmail for emails received in the last 7 days
   with label 'Important Updates'
2. Creates a summary list with: sender, subject, date, and
   first 100 characters of the body
3. Compiles this into a formatted HTML email
4. Sends the digest to team-leads@company.com with subject
   'Weekly Important Updates Digest - [date range]'"

Auto-Archive Old Emails

"Write a Google Apps Script that:
1. Finds all emails in the inbox older than 30 days
2. Excludes emails with labels: 'Keep', 'Action Required',
   or 'Starred'
3. Archives them (removes from inbox but keeps in All Mail)
4. Logs the count of archived emails
5. Runs daily at midnight"

Automation Pattern 3: Calendar Integration

Create Events from Spreadsheet

"Write a Google Apps Script that reads the 'Events' sheet
and creates Google Calendar events. The sheet has columns:
A: Event Name, B: Date (YYYY-MM-DD), C: Start Time (HH:MM),
D: End Time (HH:MM), E: Location, F: Description,
G: Attendees (comma-separated emails), H: Created (checkbox)

For each row where column H is not checked:
1. Create a calendar event with all the details
2. Add attendees and send invitations
3. Check the box in column H
4. Add the calendar event ID in column I"

Auto-Block Focus Time

"Write a Google Apps Script that runs every Sunday at 8 PM
and creates 'Focus Time' blocks on my calendar for the
upcoming week:
- Monday, Wednesday, Friday: 9:00-11:00 AM
- Tuesday, Thursday: 2:00-4:00 PM
Only create the block if the time slot is currently free.
Mark the events as 'busy' and add the description:
'Protected focus time - no meetings please.'"

Meeting Prep Reminders

"Write a Google Apps Script that runs every morning at 7 AM:
1. Gets all calendar events for today
2. For each event with at least one attendee:
   a. Creates a task in a Google Sheet ('Meeting Prep') with:
      Meeting name, time, attendees, and a 'Prepared?' checkbox
3. Sends me an email with today's meeting list and a reminder
   to review the prep sheet"

Automation Pattern 4: Cross-Service Workflows

Form Submission to Full Workflow

"Write a Google Apps Script that triggers when a Google Form
is submitted. The form collects: Name, Email, Company,
Service Interest, Budget Range, and Message.

When a submission arrives:
1. Add the data to the 'Leads' sheet in the CRM spreadsheet
2. Send a confirmation email to the submitter:
   'Hi [Name], thanks for your interest in [Service].
   We'll reach out within 24 hours.'
3. Send a notification email to sales@company.com:
   'New lead: [Name] from [Company], interested in [Service],
   budget: [Budget Range]'
4. Create a follow-up event on the sales calendar for
   tomorrow at 10 AM: 'Follow up with [Name] - [Company]'"

Weekly Report Auto-Generation

"Write a Google Apps Script that runs every Friday at 5 PM:

1. Read this week's data from the 'Weekly Metrics' sheet
   (columns: Metric, This Week, Last Week)
2. Calculate week-over-week change for each metric
3. Create a new Google Doc titled 'Weekly Report - [date]'
   in the 'Reports' Drive folder
4. Format the doc with:
   - Header: Weekly Performance Report
   - Table: Metric | This Week | Last Week | Change %
   - Highlight any metric with >10% change (positive in green,
     negative in red)
5. Share the doc with the team distribution list
6. Send an email with a link to the doc"

Invoice Processing Pipeline

"Write a Google Apps Script for invoice processing:

1. Monitor a specific Gmail label 'Invoices'
2. When a new email arrives with a PDF attachment:
   a. Save the PDF to a Drive folder 'Incoming Invoices'
   b. Extract the filename and date
   c. Add a row to the 'Invoice Log' spreadsheet:
      Date Received, Sender, Filename, Drive Link, Status='New'
3. Send a Slack webhook notification (URL: [webhook_url]):
   'New invoice received from [sender]. Review at [sheet_link]'
4. Run every 30 minutes"

Testing and Debugging with Gemini

When Scripts Fail

When a generated script throws an error, copy the error message back to Gemini:

"This Google Apps Script is throwing the following error:

TypeError: Cannot read properties of undefined (reading 'getValues')
at cleanData (Code.gs:5:28)

Here is the script:
[paste the script]

Fix the error and explain what was wrong."

Gemini will typically identify the issue (wrong sheet name, missing permissions, null reference) and provide a corrected version.

Common Errors and Fixes

Testing Best Practices

1. Test on a copy. Before running any script that modifies data, make a copy of the spreadsheet and test on the copy.
2. Start with Logger.log(). Add logging statements to verify each step executes correctly before adding the actual automation actions.
3. Test triggers manually first. Before setting a trigger, run the function manually and verify the output.
4. Use try/catch. Wrap risky operations in try/catch blocks so one failure does not stop the entire script.

Setting Up Triggers

Time-Based Triggers

function createTimeTrigger() {
  // Run every day at 8 AM
  ScriptApp.newTrigger('myFunction')
    .timeBased()
    .atHour(8)
    .everyDays(1)
    .create();
}

// Other options:
// .everyMinutes(15)  — every 15 minutes
// .everyHours(1)     — every hour
// .onWeekDay(ScriptApp.WeekDay.MONDAY) — specific day

Event-Based Triggers

function createFormTrigger() {
  // Run when a form is submitted
  ScriptApp.newTrigger('onFormSubmit')
    .forSpreadsheet(SpreadsheetApp.getActive())
    .onFormSubmit()
    .create();
}

function createEditTrigger() {
  // Run when the spreadsheet is edited
  ScriptApp.newTrigger('onEdit')
    .forSpreadsheet(SpreadsheetApp.getActive())
    .onEdit()
    .create();
}

Trigger Limits

Monitoring and Error Handling

Error Notification Script

Ask Gemini to add error handling to any script:

"Wrap this script in error handling that:
1. Catches any error
2. Logs the error details to a 'Error Log' sheet
   (timestamp, function name, error message, stack trace)
3. Sends me an email notification if the script fails
4. Does NOT stop the entire script if one row fails —
   continue processing remaining rows"

Execution Log Dashboard

"Create a Google Apps Script that maintains an execution log.
Every time any of my automation scripts runs:
1. Log to 'Execution Log' sheet: timestamp, script name,
   status (success/error), duration, rows processed
2. The sheet auto-calculates: success rate (last 7 days),
   average duration, total rows processed this week
3. If success rate drops below 90%, send an alert email"

10 Ready-to-Use Automation Ideas

1. Auto-deduplicate contact list — remove duplicate entries in a CRM spreadsheet based on email address
2. Birthday reminder — scan employee list, send email reminders 3 days before each birthday
3. Expense report compiler — gather data from multiple department sheets into a master report
4. Meeting notes distributor — after updating a meeting notes doc, auto-email a summary to attendees
5. Stale task detector — find tasks in a project tracker sheet that have not been updated in 14+ days and flag them
6. Client onboarding checklist — when a new client row is added, auto-create a Google Doc from a template with their details filled in
7. Auto-rename Drive files — rename files in a specific folder based on a naming convention from a spreadsheet
8. Daily metrics snapshot — copy current dashboard values to a daily log for historical tracking
9. Auto-RSVP collector — scan calendar events for upcoming team meetings and create an RSVP tracker spreadsheet
10. Content calendar publisher — read blog post schedule from a sheet and create draft documents from a template on the scheduled date

For each of these, describe the automation to Gemini and it will generate the working script.

Frequently Asked Questions

Do I need to know JavaScript to use this?

No. Gemini writes the code for you. You need to understand what you want to automate (the workflow) and be able to describe it clearly. Debugging may occasionally require looking at code, but Gemini can help with that too.

Is Google Apps Script free?

Yes. Apps Script is included with any Google account (free Gmail or Google Workspace). There are quota limits on execution time and email sending, but they are generous enough for most automations.

Can Apps Script access external APIs?

Yes. Apps Script can make HTTP requests to any external API using UrlFetchApp. Ask Gemini: “Write an Apps Script that calls [API] and processes the response.”

How secure are my scripts?

Scripts run under your Google account’s permissions. They can access any Google service you have access to. Be careful with scripts that process sensitive data — do not share script projects with people who should not have access to the underlying data.

Can I schedule a script to run at a specific time?

Yes. Use time-based triggers. You can schedule scripts to run: every minute, every hour, every day at a specific hour, every week on a specific day, or every month on a specific date.

What happens if my script breaks?

Failed triggers send an error notification to the script owner’s email. The script will continue to attempt execution on the next trigger interval. Fix the error and the next execution will succeed.

Can multiple people use the same automation?

Yes. Share the spreadsheet (or Doc) containing the script. Anyone with edit access can run the script manually. Triggers run under the account that created them, regardless of who edits the spreadsheet.