Write a Google Apps Script to Automate a Repetitive Google Sheets Task

You get a commented Google Apps Script built around your sheet names, columns and timing, plus install steps, a test plan on a copy, and undo steps.

Prompt

Prompt
Act as a Google Apps Script developer who builds automations for people who have never coded.

Your job: write a Google Apps Script that automates one repetitive task in my Google Sheet. Before writing anything, ask me these questions in one numbered message, then wait for my answers:
1. What task do you repeat, step by step, and how often?
2. What are the exact tab names involved, and which columns hold what (column letter plus header)?
3. When should it run: from a custom menu, when a cell is edited, or on a schedule (day and time)?
4. Is there any data the script must never change or delete?

Rules:
- Put every tab name, column letter and time in a labeled settings section at the top, so I can change them without touching the logic.
- Comment each section in plain English.
- Never delete a row without first copying it elsewhere, and write each change to a Log tab with a timestamp.
- Install steps must be click-by-click, naming exact menus and buttons, including the Google permission screen.
- Use only built-in Apps Script services.

Output format:
1. What the script will do (3 bullets)
2. The full script in one code block
3. Install steps (numbered)
4. Test run on a copy of the sheet: what to check
5. How to undo: remove the trigger, delete the script, restore data
6. Limits and risks

End with one line naming any assumption you made.

Example output

Example for: a dog-grooming studio that moves paid bookings from the Bookings tab to Archive every Monday at 6am.

1. What the script will do

  • Every Monday between 6 and 7am, find rows in Bookings where column F (Status) says Paid.
  • Copy each row to the bottom of Archive, then remove it from Bookings.
  • Write one line per moved row to a Log tab with the time and client name (column B).

2. The full script

The script arrives as one code block with three parts:

  • Settings: SOURCE_TAB = 'Bookings', ARCHIVE_TAB = 'Archive', LOG_TAB = 'Log', STATUS_COLUMN = 6, MATCH_TEXT = 'Paid', RUN_DAY = Monday, RUN_HOUR = 6.
  • archivePaidBookings: reads all rows, copies matches to Archive first, logs them, then deletes them from the bottom up so row numbers don't shift.
  • installWeeklyTrigger: creates the Monday trigger once and skips it if one already exists.

3. Install steps

  1. Open the sheet, click Extensions > Apps Script.
  2. Delete the sample code in Code.gs and paste the script.
  3. Click the Save icon.
  4. In the function dropdown, pick installWeeklyTrigger, click Run.
  5. On the permission screen click Review permissions, choose your account, click Advanced > Go to project (unsafe), then Allow. This warning appears for all personal scripts.
  6. Click the clock icon (Triggers) and confirm one weekly trigger is listed.

4. Test run on a copy

  1. File > Make a copy and install the script in the copy only.
  2. Mark 3 test rows as Paid and one as Pending.
  3. Run archivePaidBookings manually.
  4. Check: 3 rows in Archive with all columns intact, Pending row still in Bookings, 3 lines in Log.

5. How to undo

  • Stop it: Triggers page, three-dot menu on the trigger, Delete trigger.
  • Remove it: delete the code in Code.gs and save.
  • Restore data: copy rows back from Archive, or use File > Version history to restore a pre-run version.

6. Limits and risks

  • If someone types "paid" in lowercase, it won't match; the script trims spaces but is case-sensitive.
  • Free Google accounts allow 90 minutes of trigger run time per day; this job takes seconds.
  • Edits made during the run may be lost.

Assumption: row 1 is a header row and Archive uses the same column order as Bookings.

Written by AI from this prompt. Your results will vary.

Tips and common edits

Why this works

  • Asking for exact tab names, columns and timing first means the script runs on the real sheet instead of a guessed layout.
  • The copy-before-delete rule and Log tab make mistakes recoverable, which matters most for first-time script users.
  • Click-by-click install, a test on a copy and undo steps cover the parts non-developers usually get stuck on.

Use when you repeat the same copy, move or cleanup job in Google Sheets every week. Have your tab names and column headers open. Works in ChatGPT, Claude or Gemini; paste the script into Extensions > Apps Script.

Common edits

  • Ask for a custom menu button instead of a timed trigger so you control when it runs.
  • Add an email summary to yourself after each run listing what changed.

Prompt details

By
Prompts-GPT editors
Published

Did this prompt work?

Keep exploring

More in Coding