Write a Google Apps Script to Automate a Sheets Task with Dry-Run Mode

Get a working Google Apps Script for your Sheets task, with trigger setup, the permission screens you will see, and a dry-run mode that logs changes first.

You getWhat the script does, setup steps and permission prompts you will see

Prompt

Prompt
Act as a Google Apps Script developer who builds safe automations for Google Sheets users who are not programmers.

Your job: write a script that automates one task in my spreadsheet. Before writing anything, ask me these questions in one message and wait for my answers:
1. What should the script do, step by step?
2. What are the sheet tab names and the column headers involved?
3. Should it run on a schedule, on edit, on form submit, or only when I click Run?
4. Roughly how many rows does the sheet have?

Rules:
- Include a DRY_RUN setting at the top, set to true by default. In dry-run mode the script changes nothing and logs every action it would take, with row numbers.
- Read and write data in batches (getValues and setValues), not cell by cell.
- Put trigger creation in its own function that I run once, and explain how to remove the trigger.
- List the exact permission screens I will see, including the unverified app warning, and what each scope allows.
- Comment the code in plain English.

Output format:
1. What the script does (3-5 lines)
2. Setup steps (numbered)
3. Permission prompts you will see
4. The full script
5. Trigger setup and removal
6. Test plan: dry run, check the log, then go live

End with one line on assumptions you made and what could break, such as renamed columns.

Example output

At a glanceTest plan before automating the archive scriptFrom dry run to daily trigger for moving old Done tasks
  1. Run in dry-run modeKeep DRY_RUN true; confirm logged row numbers match the sheet
  2. Back up the fileMake a copy of the spreadsheet first
  3. Run for real onceSet DRY_RUN to false, run, then check the Archive tab
  4. Install the daily trigger6 am dailyRun installTrigger once to archive every morning

Example for: a team lead who wants tasks marked Done for over 30 days moved from the Tasks tab to an Archive tab every morning; about 800 rows.

1. What the script does

  • Reads the Tasks tab in one batch.
  • Finds rows where Status (column D) is "Done" and Completed On (column E) is more than 30 days old.
  • Copies those rows to the Archive tab, then deletes them from Tasks, bottom row first.
  • In dry-run mode it only logs what it would move.

2. Setup steps

  1. Open the sheet, then Extensions > Apps Script.
  2. Delete the sample code and paste the script below.
  3. Click Save, choose archiveDoneTasks in the Run dropdown, click Run.
  4. Open Executions to read the dry-run log.

3. Permission prompts you will see

ScreenWhat it means
Authorization requiredClick Review permissions and pick your account
Google hasn't verified this appNormal for your own scripts: Advanced > Go to project (unsafe)
See, edit, create and delete your spreadsheetsNeeded to read Tasks and write Archive
Connect to an external service / run when you are not presentAppears once you create the daily trigger

4. The full script

// Set to false only after the dry-run log looks right
var DRY_RUN = true;
var CUTOFF_DAYS = 30;

// A row qualifies if Status is Done and the date is older than the cutoff
var isOldDone_ = (row, cutoff) =>
  row[3] === 'Done' && row[4] instanceof Date && row[4] < cutoff;

var archiveDoneTasks = () => moveRows_(
  SpreadsheetApp.getActive().getSheetByName('Tasks'),
  SpreadsheetApp.getActive().getSheetByName('Archive'),
  new Date(Date.now() - CUTOFF_DAYS * 864e5));

// Pair each row with its sheet row number, skip the header
var moveRows_ = (src, arc, cutoff) => apply_(src, arc,
  src.getDataRange().getValues()
    .map((row, i) => [row, i + 1])
    .slice(1)
    .filter(p => isOldDone_(p[0], cutoff)));

var apply_ = (src, arc, hits) => DRY_RUN
  ? (hits.forEach(p => Logger.log('[DRY RUN] Would archive row ' + p[1] + ': ' + p[0][0])),
     Logger.log('[DRY RUN] Total: ' + hits.length))
  : (hits.length && arc.getRange(arc.getLastRow() + 1, 1, hits.length, hits[0][0].length)
       .setValues(hits.map(p => p[0])),
     hits.slice().reverse().forEach(p => src.deleteRow(p[1])),
     Logger.log('Archived ' + hits.length + ' rows'));

Note: if archiveDoneTasks does not show in the Run dropdown, rewrite it as a standard function declaration.

5. Trigger setup and removal

var installTrigger = () => ScriptApp.newTrigger('archiveDoneTasks')
  .timeBased().everyDays(1).atHour(6).create();

Run installTrigger once. To remove it: Triggers (clock icon) > three dots > Delete trigger.

6. Test plan

  1. Run with DRY_RUN true; confirm logged row numbers match the sheet.
  2. Make a copy of the file as a backup.
  3. Set DRY_RUN to false, run once, check Archive.
  4. Install the trigger.

Assumptions: Archive has the same columns as Tasks, and column E holds real dates; renaming tabs or moving columns will break it.

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

Tips and common edits

Why this works

  • Asking for tab names, headers and schedule first means the script fits your actual sheet instead of a guessed layout.
  • A dry-run flag that defaults to true lets you check every planned change in the log before any data is touched.
  • Listing the permission screens in advance stops users from abandoning the script at the unverified app warning.

Use in ChatGPT, Claude or Gemini when a repetitive Sheets job eats your time. Have your tab names, column headers and the schedule you want ready before you paste it.

Common edits

  • Ask for an onEdit or form-submit trigger instead of a daily schedule.
  • Ask the script to email you a summary of what it changed after each run.

Prompt details

By
Prompts-GPT editors
Published

Did this prompt work?

Keep exploring

More in Excel & Spreadsheets