ChatGPT writing Google Apps Script code for a Google Sheets automation

How to Use ChatGPT to Write Google Apps Script for Sheets

by

ChatGPT will happily write you a Google Apps Script. The problem is what happens next: you paste it into the editor, click Run, and get an error you don’t understand — or worse, it runs fine and silently emails you garbage.

This guide takes a different approach. Below is a complete, working script that emails you a daily summary of new rows in a Google Sheet. I ran its logic through a test suite before publishing it, and I’ll show you exactly which parts I verified and which parts I couldn’t. Then I’ll show you how to get ChatGPT to modify it for your own sheet without breaking it.

What you’ll end up with

A Google Sheet that emails you once a day with a plain-text list of every row added in the last 24 hours. If nothing was added, it stays quiet instead of sending an empty email. The output looks like this:

You have 2 new row(s):

1. Timestamp: 2026-07-28 08:00 | Name: Alice | Amount: 120
2. Timestamp: 2026-07-27 23:00 | Name: Bob | Amount: (empty)

That’s the actual output from running the code, not a mock-up.

The script

Paste this whole thing. It assumes your sheet has a header row, and that column A holds a timestamp (which is what Google Forms writes by default). Change the three values at the top and nothing else.

const SHEET_NAME = 'Sheet1';          // tab name, bottom-left of your sheet
const RECIPIENT   = 'you@example.com'; // where the digest goes
const DATE_COLUMN = 1;                 // 1 = column A

function sendDailyDigest() {
  const sheet = SpreadsheetApp.getActive().getSheetByName(SHEET_NAME);
  if (!sheet) throw new Error('No tab named ' + SHEET_NAME);

  const data = sheet.getDataRange().getValues();
  const headers = data.shift();

  const recent = selectRecent(data, DATE_COLUMN - 1, new Date(), 24);
  if (recent.length === 0) {
    console.log('No new rows in the last 24 hours. No email sent.');
    return;
  }

  GmailApp.sendEmail(
    RECIPIENT,
    'Daily digest: ' + recent.length + ' new row(s)',
    buildDigest(headers, recent)
  );
}

function selectRecent(data, dateColIndex, now, hours) {
  const cutoff = new Date(now.getTime() - hours * 60 * 60 * 1000);
  return data.filter(function (row) {
    const cell = row[dateColIndex];
    if (!(cell instanceof Date)) return false;
    if (isNaN(cell.getTime())) return false;
    return cell >= cutoff;
  });
}

function buildDigest(headers, rows) {
  const lines = rows.map(function (row, i) {
    return (i + 1) + '. ' + headers.map(function (h, c) {
      return h + ': ' + formatCell(row[c]);
    }).join(' | ');
  });
  return 'You have ' + rows.length + ' new row(s):\n\n' + lines.join('\n');
}

function formatCell(value) {
  if (value instanceof Date && !isNaN(value.getTime())) {
    const p = function (n) { return String(n).padStart(2, '0'); };
    return value.getFullYear() + '-' + p(value.getMonth() + 1) + '-' + p(value.getDate()) +
           ' ' + p(value.getHours()) + ':' + p(value.getMinutes());
  }
  if (value === null || value === undefined || value === '') return '(empty)';
  return String(value);
}

Installing it (6 steps)

  1. Open your Google Sheet. In the menu bar: Extensions → Apps Script. A new tab opens with a file called Code.gs.
  2. Delete everything in that file and paste the script above.
  3. Change SHEET_NAME, RECIPIENT, and DATE_COLUMN at the top to match your sheet.
  4. Click the save icon, then pick sendDailyDigest in the function dropdown and click Run. Google will ask for permission the first time — it will warn that the app isn’t verified. That’s normal for your own scripts: click Advanced → Go to (project name), then Allow.
  5. Check the execution log at the bottom. Either an email arrived, or you’ll see No new rows in the last 24 hours. Both mean it worked.
  6. To make it run daily: click the clock icon (Triggers) in the left sidebar → Add Trigger → function sendDailyDigest, event source Time-driven, type Day timer, and pick an hour. Save.

Step 4 is where most people stop. The “unverified app” screen looks alarming but it’s just Google telling you that you, personally, wrote this and nobody at Google reviewed it.

What I actually tested — and what I couldn’t

This matters, because most script tutorials never tell you whether the code was run at all.

Verified. I extracted the pure logic — selectRecent, buildDigest, formatCell — and ran it against a test suite in Node. Seven checks, all passing: the 24-hour window keeps only the right rows; blank cells, text-that-looks-like-a-date, and invalid dates are all excluded rather than crashing; single-digit months and days are zero-padded; empty cells render as (empty); and no raw JavaScript date string leaks into the email body.

Not verified. The three lines that touch Google’s own services — SpreadsheetApp.getActive(), sheet.getDataRange(), and GmailApp.sendEmail() — only exist inside Google’s runtime. They cannot be executed outside it, so I did not test them and I’m not going to pretend otherwise. They follow the documented API, but the first real run happens on your machine, which is exactly why step 5 above tells you to check the log.

One thing the testing actually caught. My first version passed every logic check but produced this in the email body:

1. Timestamp: Tue Jul 28 2026 17:00:00 GMT+0900 (Korean Standard Time) | Name: Alice

That’s what you get when a spreadsheet Date lands in string concatenation untouched. It’s not a bug that throws an error — the script “works” and quietly sends you that. The formatCell function exists solely to prevent it. If you ask ChatGPT for a digest script, this is the class of problem you should expect to find yourself.

Errors you will probably hit

What you see What it means Fix
No tab named Sheet1 Your tab is called something else Look at the tab name in the bottom-left of the sheet and copy it exactly — it is case-sensitive and trailing spaces count
TypeError: Cannot read properties of null (reading 'getDataRange') Same problem, but you removed the guard clause Keep the if (!sheet) throw line — it turns a confusing error into a readable one
Email never arrives, log says “No new rows” Column A isn’t a real Date — it’s text Select column A → Format → Number → Date time. Text that looks like a date is deliberately excluded
Exception: Service invoked too many times for one day: email Gmail daily send quota Consumer Gmail accounts get a much smaller daily quota than Workspace accounts. Check Google’s current quota page — and don’t put this on an hourly trigger
Authorization is required to perform that action You skipped the permission screen Run the function manually once from the editor and complete the Advanced → Allow flow
Times in the email are off by hours Script timezone differs from your sheet’s Project Settings in Apps Script → set the timezone, then re-run

Getting ChatGPT to modify it safely

The script above is a base. When you want it to do something else, give ChatGPT the whole script plus your actual column layout, and ask for a specific change — not a rewrite. Vague prompts produce vague code:

Here is a working Google Apps Script. Column A is a timestamp, column B is
a customer name, column C is an order total in numbers.

[paste the whole script]

Change ONLY the buildDigest function so the email also shows the sum of
column C at the bottom. Keep every other function exactly as it is, and
explain what you changed in one sentence.

Two habits make the difference. First, constrain the blast radius — “change only this function” prevents ChatGPT from quietly rewriting logic you already tested. Second, ask it to explain the change in one line; if the explanation doesn’t match what you asked for, the code won’t either.

Once you get a modified version back, don’t trust it because it looks right. Run it from the editor and read the log. The date-formatting problem above passed every visual inspection and still would have emailed you nonsense.

If you’re new to writing prompts for spreadsheet work, our guide on writing Google Sheets formulas with ChatGPT covers the same principle at formula level, and automating Google Sheets with Apps Script goes wider on trigger types.

Where to take it next

The same skeleton — read the sheet, filter rows, do something — covers most spreadsheet automation. Swap the filter and you get an alert when a value crosses a threshold. Swap GmailApp for a different service and it posts to chat instead. The part worth keeping is the discipline: change one function at a time, run it, read the log.

Leave a Comment