DEV Community: bulldo.gs

Run a script every day at a set time with Apps Script

bulldo.gs — Sat, 13 Jun 2026 00:50:58 +0000

I want to schedule a Google Apps Script function to run automatically at a specific time every day without having to trigger it manually.

// Run myDailyJob at 8 AM every day (script timezone).
// Call createDailyTrigger() once from the editor — never from a trigger.
function createDailyTrigger() {
  var triggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < triggers.length; i++) {
    if (triggers[i].getHandlerFunction() === 'myDailyJob') {
      ScriptApp.deleteTrigger(triggers[i]);
    }
  }
  ScriptApp.newTrigger('myDailyJob')
    .timeBased()
    .everyDays(1)
    .atHour(8)
    .create();
}

function myDailyJob() {
  Logger.log('Running daily job at: ' + new Date());
}

What atHour actually promises

Apps Script time-based triggers do not fire at a precise clock time. When you call atHour(8), you are telling the scheduler to run your function sometime between 8:00 and 9:00 AM in the script's timezone — not at exactly 08:00:00. Google's infrastructure batches trigger execution and spreads it across that window. If your job must run at exactly 8:05, Apps Script is the wrong tool; use Cloud Scheduler calling a Cloud Function instead.

The script's timezone is set per project, not per user. Go to Project Settings (the gear icon in the left sidebar) and check the Time zone field. If that reads America/New_York and your Sheets data is in UTC, every atHour call is offset accordingly. I've burned a morning debugging a report that always showed yesterday's data — the trigger was firing at what was effectively midnight UTC.

One practical consequence: if you chain two time-based triggers expecting a gap of exactly one hour between them, that gap can be anywhere from zero to two hours on a bad day. Build tolerance into any logic that depends on recency.

Deleting before creating: the duplicate trap

Every call to ScriptApp.newTrigger(...).create() registers a new trigger entry, regardless of whether one already exists for the same function. Re-running your setup function — say, after tweaking the hour — stacks a second trigger on top of the first. After three iterations you have three daily runs, and the Triggers dashboard in the Apps Script editor is the only place that shows you this.

The fix is to delete any existing triggers for the target function before creating a new one, which is what the loop in the snippet above does. getHandlerFunction() returns the string name you passed to newTrigger(), so matching on 'myDailyJob' is exact.

A common mistake is calling the setup function from another trigger — say, a daily cleanup job that reinitializes everything. That creates a new trigger every day while the previous one persists. createDailyTrigger() should be called exactly once, manually, from the editor's Run menu.

Authorization and the first-run prompt

Time-based triggers run as the user who authorized the script. When you run createDailyTrigger() for the first time, Apps Script will prompt for OAuth consent if the script hasn't been authorized yet. You must complete that flow in the editor; the trigger will not run until authorization is granted.

If you later revoke access (via myaccount.google.com/permissions) or the OAuth token expires due to a password change or policy rotation, the trigger silently fails. You will see a failure email from apps-scripts-notify@google.com with a vague 'Authorization is required' message. The fix is to open the script, run any function manually to re-trigger the consent flow, and re-authorize.

Scripts attached to a Workspace account (G Suite / Google Workspace) may also hit domain administrator restrictions on which OAuth scopes are allowed. If your trigger stops firing after an org policy change, check the Admin console under Security > API controls before assuming the script is broken.

FAQ

Why did my script run twice today?

You have duplicate triggers. Open the Apps Script editor, click the clock icon (Triggers) in the left sidebar, and look for multiple entries pointing to the same handler function. Delete all but one. This happens when you run the setup function more than once without deleting the existing trigger first.

Can I make atHour fire at exactly 8:15 instead of somewhere in the 8 AM window?

No. atHour(8) gives you a one-hour window; Apps Script offers no minute-level precision for time-based triggers. For exact timing you need Cloud Scheduler (cron syntax, minute precision) calling an Apps Script deployment via its web app URL, or a Cloud Function.

The trigger is listed but my function never runs — what's wrong?

Check three things in order: (1) the function name string passed to newTrigger() matches the actual function name exactly, case included; (2) the script is authorized — run it manually once to confirm no auth prompt appears; (3) look at the trigger execution log (Executions in the left sidebar) for error messages, which are often more specific than the failure email.

How do I set the timezone the trigger uses?

In the Apps Script editor, go to Project Settings (gear icon) and set the Time zone field. This is a per-project setting and affects all time-based triggers in that script. It is separate from the spreadsheet's locale timezone if this script is container-bound to a Sheet.

Want the plain-English version? Describe the automation at bulldo.gs and get working Apps Script back — free, no login.

Grade a quiz with a script in Google Forms

bulldo.gs — Sat, 13 Jun 2026 00:50:53 +0000

I want to write an Apps Script that reads quiz responses and sets scores or feedback automatically, but nothing seems to save.

// Grade all responses for a Quiz-mode Google Form
// Run manually or wire to an onFormSubmit trigger
function gradeAllResponses() {
  var form = FormApp.getActiveForm();
  var responses = form.getResponses();

  for (var i = 0; i < responses.length; i++) {
    var response = responses[i];
    var itemResponses = response.getGradableItemResponses();

    for (var j = 0; j < itemResponses.length; j++) {
      var item = itemResponses[j];
      var correct = item.getItem().asMultipleChoiceItem().getChoices();
      var points = item.getResponse() === correct[0].getValue() ? 10 : 0;
      item.setScore(points);
      item.setFeedback(
        FormApp.createFeedback().setText(
          points > 0 ? 'Correct.' : 'Review section 3.'
        ).build()
      );
    }

    form.submitGrades([response]);
  }
}

Quiz mode is a prerequisite, not an assumption

Before any grading script can write scores, the form itself must be in Quiz mode. Open the form in the editor, go to Settings, and confirm the 'Make this a quiz' toggle is on. Without it, getGradableItemResponses() returns an empty array on every response and your script exits silently with nothing to show for it.

With Quiz mode on, each item that carries a point value becomes a 'gradable' item. The script calls getGradableItemResponses() rather than getItemResponses() because only the gradable variant exposes setScore() and setFeedback(). The two methods look similar in the docs; the wrong one compiles fine and does nothing.

Why setScore() alone is not enough

This is the part that bites almost everyone the first time. Calling item.setScore(10) and item.setFeedback(...) mutates the in-memory ItemResponse object. It does not write anything to Forms' backend. You can loop through every response, set every score, log 'done', and the respondent's score panel will be completely blank.

The write happens when you call form.submitGrades([response]) with the modified FormResponse passed in as an array. That single call is what flushes all the setScore() and setFeedback() changes you accumulated on that response. I kept this in a utils file comment for months after I first hit it, because the name submitGrades implies a submit action rather than a save action and nothing in the error output tells you the data was silently dropped.

One practical consequence: call submitGrades once per response, after you have finished mutating all its item responses. Calling it inside the inner loop (once per item) works but burns quota faster and is unnecessary.

Wiring to a trigger for real-time grading

Running gradeAllResponses() manually is fine for a one-off batch, but for a live quiz you want the function to fire each time a new response arrives. In the Apps Script editor, go to Triggers, add a new trigger, set the event source to 'From form' and the event type to 'On form submit'.

The trigger passes an event object with a response property, so you can skip the getResponses() loop entirely and grade only the single new submission. Replace the function signature with gradeAllResponses(e), grab the response via e.response, and call submitGrades([e.response]) after scoring. This keeps execution time well under the 30-second Apps Script trigger limit even on long quizzes.

One gotcha: the trigger runs under the account that installed it. If that account loses edit access to the form, grading stops silently. Worth noting in your team's runbook.

FAQ

My script runs without errors but scores are still blank in the response view. What am I missing?

You skipped form.submitGrades([response]). Calling setScore() and setFeedback() only modifies the in-memory object; submitGrades() is the call that actually persists the changes to Forms' backend. Add it once per response, after all item mutations.

Can I use getItemResponses() instead of getGradableItemResponses()?

No. getItemResponses() returns ItemResponse objects, which do not have setScore() or setFeedback() methods. You need getGradableItemResponses() to get GradableItemResponse objects. Both methods exist on FormResponse, which is why the confusion is easy.

Does submitGrades() send an email to the respondent?

Only if the form is configured to release scores via email and the respondent chose to receive them. submitGrades() persists the grade data; the email release is a separate form-level setting under Quiz settings > Release grade.

Can I grade open-ended or paragraph questions with a script?

Yes. getParagraphTextItem() questions are gradable if the form is in Quiz mode; you call the same setScore() and setFeedback() methods on their responses. The script has to implement its own comparison logic since there is no built-in correct-answer check for free-text items.

Want the plain-English version? Describe the automation at bulldo.gs and get working Apps Script back — free, no login.

Replace text placeholders across a deck in Google Slides

bulldo.gs — Sat, 13 Jun 2026 00:45:42 +0000

I need to fill a Google Slides template with dynamic values (names, dates, company fields) without manually editing every text box on every slide.

// Replace {{token}} placeholders across an entire Slides deck.
// matchCase = true prevents {{name}} from also clobbering {{full_name}}.
function fillDeckFromData() {
  var DECK_ID = '1aBcDeFgHiJkLmNoPqRsTuVwXyZ'; // replace with your deck ID
  var pres = SlidesApp.openById(DECK_ID);

  var replacements = {
    '{{first_name}}': 'Maria',
    '{{company}}':    'Acme Corp',
    '{{date}}':       'June 12, 2026',
    '{{tier}}':       'Professional'
  };

  for (var token in replacements) {
    pres.replaceAllText(token, replacements[token], true);
  }

  pres.saveAndClose();
}

The matchCase trap that ruins your template

Presentation.replaceAllText(searchText, replacement) accepts an optional third argument, matchCase. It defaults to false. That sounds harmless until you have both {{name}} and {{full_name}} in the same deck: a case-insensitive search for {{name}} will match inside {{full_name}} first, leaving you with a garbled token like {{fullMaria}} on slide 3.

Passing true as the third argument locks the search to an exact byte-for-byte match. Combined with a consistent delimiter convention — double curly braces, lowercase, underscores for spaces — your tokens never collide with real slide copy. The first time I hit the garbled-token bug it was in a 40-slide sales deck sent to a live prospect; the fix is one boolean.

Choose your delimiter once and enforce it in the template. {{first_name}} is unambiguous; name or FIRST_NAME without delimiters will eventually match something in a headline and you will not catch it until after the send.

How replaceAllText() reaches every shape

The method operates at the Presentation level, not the Slide level. One call walks every slide, every layout, and every master in the deck — including text inside grouped shapes, tables, and speaker notes. You do not need to loop over slides yourself.

The replacement is applied to the raw text run content. It does not touch font size, color, or paragraph style on the replaced span, which means your template's formatting survives the substitution intact. If you need to change formatting on replaced text (bold a name, colorize a tier label), you have to iterate shapes manually after the fact using Shape.getText().getRuns().

saveAndClose() at the end flushes the changes and releases the lock. If you omit it, changes are still written on script exit, but calling it explicitly is the safe pattern for any script that touches a shared file.

Driving it from a Sheet row

The common production pattern is one Sheet row per recipient: columns map to token names, and the script reads the active row, builds the replacements object, makes a copy of the master deck with DriveApp.getFileById(DECK_ID).makeCopy(recipientName), fills the copy, then exports or shares it.

DriveApp.makeCopy() returns a File object; call getId() on it to get the deck ID you pass into SlidesApp.openById(). Keep the master deck untouched by always working on the copy — replaceAllText() is destructive and there is no undo inside Apps Script.

If you are generating more than 20 or 30 decks in a single run, wrap the loop in a try/catch and log failures to a second sheet. The Slides API has a per-minute quota (currently 300 write requests per minute per project); a tight loop over a large list will hit it and the default error message does not tell you which row failed.

FAQ

Does replaceAllText() work inside tables and speaker notes?

Yes. The method scans all text content in the presentation, including table cells and speaker notes. Shapes inside groups are also included. The one place it does not reach is alt text (accessibility descriptions) — those are not text runs and require a separate API call.

My placeholder is not being replaced even though it looks right in the slide.

Google Slides sometimes splits a single visible token across multiple text runs internally, especially if you typed it character-by-character or edited it later. Open the slide, delete the token, and retype it in one pass without clicking away. You can also diagnose this by calling Shape.getText().getRuns() in a helper script and logging each run's content — you will see the token split across two or three entries.

Can I replace a placeholder with formatted text (bold, a different color)?

No, not with replaceAllText(). The method only substitutes the string content; it cannot inject a formatted span. The workaround is to replace with a unique sentinel string, then iterate shapes to find that sentinel with getText().find(), and apply formatting to the returned TextRange.

How do I target only specific slides instead of the whole deck?

replaceAllText() has no slide-range filter. To target specific slides, skip the Presentation-level method and instead call getSlides(), filter the array to the slides you want, then loop over each slide's shapes and call Shape.getText().replaceAllText() on each shape individually. The shape-level variant takes the same (searchText, replacement, matchCase) signature.

Want the plain-English version? Describe the automation at bulldo.gs and get working Apps Script back — free, no login.

Convert getValues for loops to map and filter

bulldo.gs — Sat, 13 Jun 2026 00:45:36 +0000

I want to rewrite my getValues for loop using map and filter so the code is shorter, but I keep getting a parameter mismatch error when I try to write results back with setValues.

// Keep only rows where column A > 100, then write column B values
function filterAndRemap() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getRange(2, 1, sheet.getLastRow() - 1, 2).getValues();

  // filter keeps the full row -- 2D shape intact
  var filtered = data.filter(function(row) {
    return row[0] > 100;
  });

  // map over filtered rows to extract column B, wrapped back into arrays
  var colB = filtered.map(function(row) {
    return [row[1]];
  });

  if (colB.length > 0) {
    sheet.getRange(2, 4, colB.length, 1).setValues(colB);
  }
}

What getValues actually gives you

getValues returns a 2D array: an array of rows, where each row is itself an array of cell values. A three-row, two-column range comes back as [[A1,B1],[A2,B2],[A3,B3]]. That 2D shape is not optional -- setValues requires it, and flattening it (even accidentally) triggers the parameter mismatch error: 'The number of rows in the data does not match the number of rows in the range.'

The classic for loop sidesteps this by never changing the shape at all; it just reads and writes by index. When you switch to map and filter you start transforming the array, which is where the shape problem surfaces.

filter rows first, then map values -- in that order

filter is safe because it returns a subset of the original rows unchanged. Each surviving element is still a full array, so the outer array remains 2D. You lose nothing by running filter before any other transformation.

map is where people get burned. Calling data.map(function(row) { return row[0]; }) produces a 1D array of scalars -- fine for reading, fatal for setValues. The fix is to wrap the extracted value back into a one-element array: return [row[0]]. That single extra pair of brackets restores the 2D shape setValues needs.

The first time I hit this I spent twenty minutes staring at what looked like a perfectly correct range reference before realizing the column count was 1 but the data depth was 0. The guard at the end (checking colB.length before calling setValues) is also worth keeping: calling setValues on a zero-row range throws a separate error that is even harder to read.

Chaining without a temp variable

Once the filter-then-map order is clear, you can chain both calls directly off getValues and skip the intermediate variable. That cuts a five-to-eight line for loop down to three lines of actual logic, with no index arithmetic and no off-by-one risk on the loop bound.

One real tradeoff: chaining obscures the intermediate shape for anyone reading the code later. If the filter condition is at all complicated, keeping filter and map as separate named variables (as in the snippet above) makes the intent obvious at a glance. For a trivial condition like row[0] > 100, the chain is fine. Use judgment rather than a rule.

FAQ

Why does map(function(row) { return row[0]; }) cause a setValues error?

That map produces a 1D array of scalars, like [10, 20, 30]. setValues requires a 2D array where every element is itself an array. Wrap the value: return [row[0]], which gives [[10],[20],[30]] -- the shape setValues accepts.

Can I filter and map in a single pass instead of two steps?

You can use reduce to do both at once, but Apps Script does not have flatMap and reduce with conditional push is harder to read than a chained filter().map(). Two steps is idiomatic and the performance difference over a sheet-sized dataset (a few thousand rows) is not measurable.

Does this work if I need to keep multiple columns, not just one?

Yes. In the map callback, return all the columns you want as an array literal: return [row[1], row[2], row[4]]. The length of that inner array must match the column count you pass to getRange when you call setValues.

What happens if filter removes all rows and colB is empty?

setValues on an empty array throws 'Incorrect range height'. Guard with if (colB.length > 0) before the write, as shown in the snippet. Alternatively, clear the destination range first so no stale values remain when the filter result is zero rows.

Want the plain-English version? Describe the automation at bulldo.gs and get working Apps Script back — free, no login.

Switch an old script from Rhino to the V8 runtime

bulldo.gs — Sat, 13 Jun 2026 00:40:25 +0000

I want to enable the V8 runtime on an existing Apps Script project so I can use modern JavaScript, but I am worried about breaking things that are already working.

// appsscript.json — set runtimeVersion to enable V8
// Rollback: change V8 back to DEPRECATED_ES5
{
  "timeZone": "America/New_York",
  "dependencies": {},
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8"
}

// Code.gs — safe V8-compatible replacement for a common Rhino pattern
// Rhino allowed: for each (var item in collection) {}
// V8 requires standard for...of instead
function listSheetNames() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var names = [];
  var sheets = ss.getSheets();
  for (var i = 0; i < sheets.length; i++) {
    names.push(sheets[i].getName());
  }
  Logger.log(names.join(', '));
}

The one-line change and why it is a project-wide bomb

Open the Apps Script editor, click Project Settings (the gear icon), and check "Show appsscript.json manifest file in editor." Then open that file and change "runtimeVersion": "DEPRECATED_ES5" to "runtimeVersion": "V8". Save. That is the entire migration from a settings standpoint.

What catches people off guard is the failure mode. V8 parses every .gs file in the project as a unit before running anything. One Rhino-only statement — a for each loop, a __iterator__ method, a Date.prototype.getYear call in an otherwise untouched utility file — causes a syntax or runtime error that prevents the whole script from initializing. Not just the file that contains the bad line. Every function, every trigger, the entire project goes dark.

The first time I hit this it took me twenty minutes to figure out why a completely unrelated trigger had stopped firing. The error message pointed at the Rhino syntax in a helper file I had not touched in two years. V8 does not isolate the damage; it fails at parse time, before any execution happens.

Finding Rhino-only syntax before you flip the switch

The three patterns that break most often under V8 are: for each (var x in obj) (a Mozilla-specific extension Rhino supported, not standard ES), XML literals like var x = <tag/>, and __iterator__ or __defineGetter__ on prototypes. Do a text search across all .gs files for those strings before you change runtimeVersion.

The Apps Script editor has no project-wide search, so either use the browser's find-in-page on each file, or copy all your .gs files into a local editor with multi-file search. Any hit needs to be rewritten: for each becomes a standard for...of or indexed for loop; XML literals need to be replaced with XmlService calls or plain strings; the prototype methods have no direct replacement and usually need a logic rewrite.

After fixing the files, change runtimeVersion to V8, save, and immediately run one function manually from the editor toolbar. If the Execution Log shows the function completing, your triggers are also alive. If you see a red syntax error, V8 caught something the search missed — read the error, fix the line, repeat.

Instant rollback while you keep fixing files

If production triggers are firing and you need to un-break them immediately, set "runtimeVersion": "DEPRECATED_ES5" in appsscript.json and save. Rhino resumes within seconds. You do not need to revert any .gs file changes you already made — DEPRECATED_ES5 tolerates both old and new syntax.

This lets you migrate file by file. Fix one .gs file, flip to V8, test, flip back to DEPRECATED_ES5, fix the next file. Tedious for large projects, but it keeps triggers running throughout. Once every file passes a V8 test run, leave runtimeVersion set to V8 permanently.

One practical note: the DEPRECATED_ES5 value is exactly that — deprecated. Google has not announced a hard removal date as of mid-2026, but they have signaled V8 is the intended path. Old scripts will keep running on Rhino for now, but new V8-only APIs (like globalThis or native Promise support in triggers) are not back-ported, so staying on Rhino indefinitely means foregoing those.

FAQ

After I set runtimeVersion to V8, all my triggers stopped running with no error in the dashboard. What happened?

A parse-time error in any .gs file silently kills initialization. Open the editor, run any function manually, and look at the Execution Log — it will show the actual syntax error and the file name. Fix that line, then triggers resume automatically without redeployment.

Can I use arrow functions and const/let after switching to V8?

Yes. V8 supports ES2019 and most ES2020 syntax: arrow functions, const/let, destructuring, spread, async/await, optional chaining, and nullish coalescing. The one thing that does not work the same way is top-level await — Apps Script still requires async functions to be called from a synchronous trigger wrapper.

My script uses XmlService and it works fine on Rhino. Will it break on V8?

XmlService itself is a Google service and is runtime-agnostic — it works on both. What breaks under V8 is E4X, the old Rhino XML literal syntax (angle-bracket XML directly in JavaScript code). If your script uses XmlService.parse() and XmlService.getContentType() style calls rather than inline XML literals, it will migrate without changes.

Is there a way to see which runtime a deployed script is currently using without opening the manifest?

Not from outside the editor. You have to open the project, enable manifest display in Project Settings, and read appsscript.json. The Apps Script API (the REST API for managing scripts) does expose the runtimeVersion field in the project Content resource if you need to audit it programmatically across many projects.

Want the plain-English version? Describe the automation at bulldo.gs and get working Apps Script back — free, no login.

Fix "Exceeded maximum execution time" in Apps Script

bulldo.gs — Sat, 13 Jun 2026 00:40:19 +0000

I'm running a script that processes a large spreadsheet and it keeps dying with "Exceeded maximum execution time" before it finishes.

// Checkpoint-resume pattern for long-running sheet jobs
function processInBatches() {
  var props = PropertiesService.getScriptProperties();
  var startRow = parseInt(props.getProperty('lastRow') || '2', 10);
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastDataRow = sheet.getLastRow();
  var BATCH = 200;
  var SAFE_MS = 5 * 60 * 1000;
  var started = Date.now();

  var endRow = Math.min(startRow + BATCH - 1, lastDataRow);
  var data = sheet.getRange(startRow, 1, endRow - startRow + 1, 5).getValues();

  for (var i = 0; i < data.length; i++) {
    if (Date.now() - started > SAFE_MS) {
      props.setProperty('lastRow', String(startRow + i));
      return;
    }
    // process data[i] here
  }

  if (endRow >= lastDataRow) {
    props.deleteProperty('lastRow');
    deleteTrigger_();
  } else {
    props.setProperty('lastRow', String(endRow + 1));
  }
}

The 6-minute wall is per-execution, not per-task

Apps Script enforces a hard 6-minute execution time limit per run, regardless of whether you're on a free account or a Workspace account (which bumps the limit to 30 minutes, but the same cliff exists). The error doesn't mean your logic is wrong; it means one continuous call to your function took too long. The fix is to stop thinking of your job as a single execution and start thinking of it as a pipeline of short runs.

The first time I hit this, I wasted an afternoon trying to speed up the loop. Marginal gains didn't move the needle because the data volume was the real problem — 4,000 rows at one Sheets API call per row will always breach 6 minutes. The correct frame is: how do I save where I stopped and pick up there next run?

Saving and restoring a cursor with PropertiesService

PropertiesService.getScriptProperties() gives you a small key-value store that persists between executions (up to 9 KB total, 500 bytes per value). Store the last-processed row number there before you return. On the next run, read it back and start from that row. When the job finishes, delete the property so subsequent manual runs start from row 2 again.

The safety check in the snippet above — comparing Date.now() minus the start time against 5 minutes (SAFE_MS) — gives the function 60 seconds of headroom before the hard kill. Without that buffer, the process can be mid-write when the runtime terminates it, leaving a corrupt checkpoint. Set SAFE_MS to 5 * 60 * 1000 and you will never lose a checkpoint to a race condition.

To keep the job moving automatically, install a time-driven trigger pointing at the same function: ScriptApp.newTrigger('processInBatches').timeBased().everyMinutes(1).create(). The function runs every minute, advances the cursor, and returns early when time is short. Once the last row is reached, the cleanup branch calls deleteTrigger_() — a small helper that finds and deletes the trigger by handler name — so the job doesn't loop forever.

Batch your reads first — it often eliminates the problem

Before wiring up checkpointing, check whether your script is calling getValue() or getRange().getValue() inside a loop. Each individual call to the Sheets API costs roughly 50–100 ms in round-trip overhead. 500 rows at one call each is already 25–50 seconds; 3,000 rows blows past 6 minutes without a single line of real work done.

The fix is one getValues() call outside the loop: sheet.getRange(startRow, 1, rowCount, columnCount).getValues() returns a 2D array you can iterate at memory speed. In practice, switching from per-row getValue() to a single getValues() on 2,000 rows drops execution time from 3–4 minutes to under 10 seconds. That alone closes the issue for most scripts.

The snippet batches reads to 200 rows at a time partly as a defensive ceiling — even with getValues(), very wide sheets with complex data can be slow — and partly to keep each execution well inside the safe zone. Tune BATCH upward if your sheet is narrow; 500 is a safe ceiling for a 10-column sheet.

FAQ

Does the 6-minute limit reset if I call Utilities.sleep()?

No. sleep() counts against your execution time the same as any other instruction. Sleeping inside a single execution to avoid rate limits still burns the clock. The only way to get more wall-clock time is to end the execution and schedule a new one via a time-driven trigger.

Will a Workspace (paid) account give me more than 6 minutes?

Yes — Workspace accounts get a 30-minute limit per execution. But the same cliff exists; a job processing tens of thousands of rows will still hit it. The checkpoint pattern works identically at 30 minutes; just set SAFE_MS to 28 * 60 * 1000.

How do I delete the trigger when the job finishes?

Iterate ScriptApp.getProjectTriggers(), check trigger.getHandlerFunction() === 'processInBatches', and call ScriptApp.deleteTrigger(trigger). Wrap this in a private helper function named deleteTrigger_ (the underscore makes it uncallable from the UI). Failing to delete it means the function keeps firing every minute indefinitely, which burns your daily trigger quota (20 trigger-hours/day for free accounts).

My script writes results back to the sheet — how do I avoid writing duplicates on resume?

Write to the same row index you read from. Because startRow is restored from the checkpoint before any reads or writes happen, the output range for a given run starts exactly where the last run stopped. There is no window where a row gets processed twice, as long as you update the checkpoint before returning and never update it after a write failure.

Want the plain-English version? Describe the automation at bulldo.gs and get working Apps Script back — free, no login.

Merge multiple docs into one in Google Docs

bulldo.gs — Sat, 13 Jun 2026 00:35:08 +0000

I want to programmatically combine several Google Docs into one file without losing tables or list formatting.

// Merges all source docs into destDocId, in order.
// Run from the Apps Script editor; no triggers needed.
function mergeDocs() {
  var sourceIds = [
    'DOC_ID_ONE',
    'DOC_ID_TWO',
    'DOC_ID_THREE'
  ];
  var dest = DocumentApp.openById('DEST_DOC_ID').getBody();
  for (var i = 0; i < sourceIds.length; i++) {
    var srcBody = DocumentApp.openById(sourceIds[i]).getBody();
    var total = srcBody.getNumChildren();
    for (var j = 0; j < total; j++) {
      var el = srcBody.getChild(j);
      var type = el.getType();
      if (type === DocumentApp.ElementType.PARAGRAPH) {
        dest.appendParagraph(el.asParagraph().copy());
      } else if (type === DocumentApp.ElementType.TABLE) {
        dest.appendTable(el.asTable().copy());
      } else if (type === DocumentApp.ElementType.LIST_ITEM) {
        dest.appendListItem(el.asListItem().copy());
      }
    }
  }
}

Why there is no single appendElement call

The Document service in Apps Script does not expose a generic appendElement method on Body. Every element type has its own typed append method: appendParagraph, appendTable, appendListItem, and so on. That means a merge loop that ignores element types will throw TypeError: el.copy is not a function the moment it hits a table, because you would be passing an Element where the API expects a Table.

The fix is to call getType() on each child element and switch on DocumentApp.ElementType. The type enum values are strings like PARAGRAPH, TABLE, LIST_ITEM, INLINE_IMAGE, and HORIZONTAL_RULE. In practice the first three account for almost all real document content. The code above handles those three and silently skips anything else (images, rules) rather than crashing the entire merge.

Getting the doc IDs and running the script

The ID for any Google Doc is the long string between /d/ and /edit in its URL. Paste those into the sourceIds array and put the destination doc's ID in 'DEST_DOC_ID'. The destination doc must already exist; the script does not create it.

Open script.google.com, create a new project, paste the function, save, and click Run. The first run will trigger an OAuth consent screen asking for permission to read and modify your Docs. Accept it. Subsequent runs execute without prompting. I keep this script bound to the destination doc (via Extensions > Apps Script from within that doc) so the project stays easy to find later.

One quota note: DocumentApp.openById counts against the Apps Script read quota, which is 50 opens per execution for consumer accounts and higher for Workspace. For more than roughly 40 source docs, batch across multiple executions or use a time-driven trigger that processes a slice at a time.

Preserving list grouping across source documents

List items in Google Docs carry a list ID that ties them to a specific list group. When you copy a ListItem from one document into another, Apps Script assigns it a new list ID automatically, so items from different source docs will not accidentally merge into the same numbered list. That behavior is what you want.

Where it surprises people: if a single source doc has two separate bullet lists separated by a paragraph, and that paragraph is a heading (which Apps Script still sees as PARAGRAPH), the heading copies correctly and the two lists stay separate. The problem only appears if you skip paragraph elements, which drops the visual boundary and makes two distinct lists appear to run together in the output. Copy everything in child order and this does not happen.

FAQ

Does this preserve bold, italic, and heading styles?

Yes. The .copy() call on a paragraph or list item carries over all inline text attributes (bold, italic, underline, font size) and the paragraph style (Heading 1, Normal, etc.). What it does not carry over is named styles you defined via a custom Style; those stay tied to the source document's stylesheet.

Why does my table appear empty after the merge?

You likely called appendTable with the raw Element object rather than casting it first with el.asTable(). The cast returns a proper Table instance whose .copy() includes all rows and cells. Skipping the cast passes an untyped element and the API creates an empty table shell.

Can I merge docs from a shared drive I do not own?

Yes, as long as the account running the script has at least Viewer access on each source doc. The OAuth scope https://www.googleapis.com/auth/documents covers cross-ownership reads. If a source doc is restricted and the script account lacks access, openById throws a GoogleJsonResponseException with a 403; fix it by sharing the source doc with the script-runner's account.

The script stops after a few docs with a 'Service invoked too many times' error.

That is the Apps Script DocumentService quota cap. For consumer (free) Google accounts, the daily limit is 250 document opens. For Google Workspace accounts it is higher but still finite. Split your source IDs into batches of 20-30 and run the function multiple times, or set a time-driven trigger that processes one batch per minute.

Want the plain-English version? Describe the automation at bulldo.gs and get working Apps Script back — free, no login.

Import JSON from an API in Google Sheets

bulldo.gs — Sat, 13 Jun 2026 00:35:02 +0000

I want to pull live JSON data from an API endpoint directly into a Google Sheet without installing an add-on.

// Fetch JSON from an API and write it to the active sheet
// Adjust API_URL and the field list to match your endpoint
function importJsonFromApi() {
  var API_URL = 'https://jsonplaceholder.typicode.com/users';
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var response = UrlFetchApp.fetch(API_URL);
  var raw = response.getContentText();
  var data = JSON.parse(raw);

  var headers = ['id', 'name', 'username', 'email', 'phone'];
  var rows = data.map(function(obj) {
    return headers.map(function(key) { return obj[key] || ''; });
  });

  sheet.clearContents();
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);
}

Why getContentText() comes before JSON.parse

UrlFetchApp.fetch() returns an HTTPResponse object, not a string. The first time I skipped getContentText() and passed the response object directly to JSON.parse(), it silently parsed to null and the sheet wrote nothing. You need raw = response.getContentText() to get the actual body as a string, then JSON.parse(raw) turns it into a JavaScript object or array.

The URL must be publicly accessible or accept an API key via a query parameter or Authorization header. Add headers like this: UrlFetchApp.fetch(url, { headers: { Authorization: 'Bearer ' + token } }). Apps Script's UrlFetchApp quota is 20,000 calls per day on a free Google account, 100,000 on Workspace.

The rectangular array constraint — why setValues fails without mapping

setValues() is strict: it requires a 2D array where every row has the same number of columns. If you hand it an array of plain JSON objects, it throws 'The number of rows or columns in the range does not match the number of rows or columns in the data.' The fix is to map each object to an array of values in a consistent column order, which is exactly what the headers.map() call inside data.map() does.

Picking a fixed headers array also protects you when the API adds new fields — the sheet schema stays stable instead of silently growing extra columns. If a field is missing from a particular object, the fallback || '' keeps every row the same width so the range and the data never disagree.

One practical note: if your API returns a single object rather than an array, wrap it: var data = [JSON.parse(raw)]. The map chain works identically.

Running it on a schedule without touching the sheet manually

Open Extensions > Apps Script, paste the function, and save. To run it once, hit the play button (you'll be prompted to grant UrlFetchApp and SpreadsheetApp permissions on first run — both are required). To run it automatically, go to Triggers (the clock icon in the left sidebar), add a new trigger, choose importJsonFromApi, and set a time-based interval. Hourly or daily covers most data-freshness needs and stays well within the free quota.

If the API requires OAuth rather than a static key, Apps Script's OAuth2 library (GitHub: googleworkspace/apps-script-oauth2) handles the token dance. That's a separate setup, but the fetch-parse-setValues pipeline here doesn't change.

FAQ

Why do I get 'Exception: Request failed' when fetching the URL?

Apps Script blocks URLs that return non-2xx status codes by default. Either the endpoint is down, requires authentication, or is returning a 4xx. Add { muteHttpExceptions: true } as the second argument to UrlFetchApp.fetch() to get the raw response body and status code back so you can see the actual error message.

Can I import JSON that has nested objects, like address.city?

Yes, but you have to flatten it manually. In the headers.map() callback, instead of obj[key], use a resolver like function getNestedValue(obj, path) that splits on '.' and walks the object. There is no built-in flattening — you decide which leaf values become columns.

The sheet only shows the first row of data. What went wrong?

The API probably returned a single object, not an array. JSON.parse on { ... } gives an object, and calling .map() on an object returns undefined. Wrap the parsed result: var data = Array.isArray(parsed) ? parsed : [parsed]. That normalizes both shapes.

How do I pass an API key without hardcoding it in the script?

Store it with PropertiesService: PropertiesService.getScriptProperties().setProperty('API_KEY', 'yourkey'). Read it back with PropertiesService.getScriptProperties().getProperty('API_KEY'). Properties are scoped to the script and not visible in version history, so they don't leak through shared spreadsheets or accidental screenshots of the editor.

Want the plain-English version? Describe the automation at bulldo.gs and get working Apps Script back — free, no login.

Send email reminders based on dates in Google Sheets

bulldo.gs — Sat, 13 Jun 2026 00:29:50 +0000

I want to automatically send email reminders when a date in my Google Sheet matches today, but my comparisons keep firing a day early or not at all.

// Run daily via time-driven trigger; col A = email, col B = due date, col C = task name
function sendDateReminders() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Tasks');
  var data = sheet.getDataRange().getValues();

  var today = new Date();
  today.setHours(0, 0, 0, 0);

  for (var i = 1; i < data.length; i++) {
    var email = data[i][0];
    var dueDate = new Date(data[i][1]);
    dueDate.setHours(0, 0, 0, 0);

    if (dueDate.getTime() === today.getTime()) {
      var subject = 'Reminder: ' + data[i][2] + ' is due today';
      var body = 'This is your reminder that "' + data[i][2] + '" is due today.';
      MailApp.sendEmail(email, subject, body);
    }
  }
}

Why date comparison almost always fires wrong

When Apps Script reads a date cell via getValues(), it returns a JavaScript Date object. That object carries the full timestamp — including a time component that reflects the spreadsheet's timezone offset. A cell you formatted as '2026-06-10' arrives in your script as something like 'Wed Jun 10 2026 00:00:00 GMT-0500', and new Date() for the current moment might be 'Wed Jun 10 2026 09:14:33 GMT-0500'. Comparing those two with === or getTime() will always return false, so your reminder never fires.

The fix is one line per date: call setHours(0, 0, 0, 0) on both the cell value and today's date before comparing. That zeroes out hours, minutes, seconds, and milliseconds, leaving only the calendar date. I keep a small normalizeDate() helper at the top of every sheet-automation file I write; re-typing setHours four times per comparison is where bugs hide.

Once both sides are normalized, use getTime() to compare — it returns an integer (milliseconds since epoch) and === works correctly. Direct Date object comparison with == does not, because two different Date objects are different references even when they represent the same moment.

Sheet layout and reading the data

The script assumes a sheet named 'Tasks' with three columns: column A is the recipient email, column B is the due date (formatted as a date in Sheets, not plain text), and column C is a task name used in the email subject and body.

getDataRange().getValues() returns a two-dimensional array. Row 0 is your header row, so the loop starts at i = 1. Each data[i] is one row; data[i][0] is the email, data[i][1] is the date object, data[i][2] is the task name. If your sheet has columns in a different order, adjust the index numbers accordingly.

One practical note: if column B contains a plain text string ('June 10') instead of a real Sheets date, wrapping it in new Date() will still parse it, but you lose timezone-safe behavior and cross-locale reliability. Format the column as Date in Sheets and let the serialization handle it cleanly.

Setting up the daily trigger

The script does nothing on its own. You need a time-driven trigger to run sendDateReminders() once per day. In the Apps Script editor, go to Triggers (the clock icon), add a new trigger, set the function to sendDateReminders, choose Time-driven, Day timer, and pick a time window like 7am–8am.

Pick a time window that's early enough to be useful but after the spreadsheet's timezone midnight. If your sheet is set to US/Eastern and you run the trigger at 1am UTC (which is 8pm Eastern the previous day), today inside the script will be one calendar day behind what your users expect. Match the trigger's timezone — set under Project Settings — to the sheet's timezone.

MailApp.sendEmail() counts against your daily Gmail quota: 100 emails per day for personal accounts, 1,500 for Google Workspace. If you have more than a few hundred rows, batch sends or switch to GmailApp.sendEmail() with an alias. For most reminder sheets, MailApp is fine.

FAQ

Why does my reminder send a day early?

The date cell arrives as a Date object with a non-zero time component. When you compare it to today without zeroing both sides with setHours(0,0,0,0), a cell storing midnight of the target date is numerically less than today's timestamp mid-morning — so the comparison passes a day before it should.

Can I send reminders X days before the due date instead of on the day itself?

Yes. After normalizing today with setHours(0,0,0,0), add the offset: var target = new Date(today.getTime()); target.setDate(target.getDate() + 3); then compare dueDate.getTime() === target.getTime(). Adjust the number to however many days ahead you want the reminder.

My script runs but no emails arrive — what should I check first?

Run sendDateReminders() manually from the editor and watch the execution log. The most common causes are: (1) the sheet name in getSheetByName() doesn't match exactly (case-sensitive), (2) column B contains text strings instead of real date values, or (3) the script hasn't been granted Gmail permission yet — the first manual run prompts for OAuth authorization that a trigger won't prompt for on its own.

Does this work if recipients are different people per row?

Yes. MailApp.sendEmail() accepts any valid address in column A — it sends a separate email per matching row. Each call is independent, so different rows can have different recipients, subjects, and body text.

Want the plain-English version? Describe the automation at bulldo.gs and get working Apps Script back — free, no login.

Save Gmail attachments to a Drive folder in Gmail

bulldo.gs — Sat, 13 Jun 2026 00:29:43 +0000

I need to automatically pull file attachments out of Gmail and store them in a Drive folder without manually saving each one.

// saveAttachments.gs — run on a time trigger or manually
// Saves qualifying Gmail attachments to a named Drive folder.
function saveAttachmentsToDrive() {
  var folderName = 'Gmail Attachments';
  var searchQuery = 'has:attachment newer_than:1d';

  var folders = DriveApp.getFoldersByName(folderName);
  var folder = folders.hasNext()
    ? folders.next()
    : DriveApp.createFolder(folderName);

  var threads = GmailApp.search(searchQuery);
  for (var i = 0; i < threads.length; i++) {
    var messages = threads[i].getMessages();
    for (var j = 0; j < messages.length; j++) {
      var attachments = messages[j].getAttachments();
      for (var k = 0; k < attachments.length; k++) {
        var att = attachments[k];
        var ct = att.getContentType();
        if (ct === 'application/ics') continue;
        if (ct.indexOf('image/') === 0 && att.getSize() < 20000) continue;
        folder.createFile(att);
      }
    }
  }
}

What getAttachments() actually returns

The Gmail API's getAttachments() method gives you everything attached to the MIME envelope: PDFs, spreadsheets, Word docs, but also every inline image in every HTML signature, and the .ics calendar invite that Google Calendar automatically appends to meeting emails. The first time I ran a naive version of this script against a busy inbox I ended up with 400 tiny PNG files of company logos, an .ics for every calendar event in the last week, and exactly zero of the invoices I was trying to collect.

The fix is to filter on content type before writing to Drive. The script above skips application/ics unconditionally. For images it applies a size heuristic: anything under 20,000 bytes is almost certainly a signature icon or a spacer pixel, not a real attachment. That threshold holds well in practice; a genuine photograph or scanned document is rarely under 20 KB. Adjust the number if your domain sends small image attachments legitimately.

Note that getAttachments() does not expose inline images that are referenced via a Content-ID header but hosted remotely (tracked pixel style). Those never appear in the list, so you don't need to filter them. The ones you do need to filter are locally-attached inline parts, which Gmail encodes as standard MIME attachments with an image/* content type.

Targeting the right messages with search operators

GmailApp.search() accepts the same operators as the Gmail search box: has:attachment newer_than:1d, label:invoices has:attachment, from:vendor@example.com has:attachment. The 100-thread limit per search call is the main operational constraint — if you're processing more than 100 threads per day, you need to paginate using the two-argument form GmailApp.search(query, start, max) and loop until the result set is empty.

Using newer_than:1d on a daily time trigger means you'll reprocess the same messages that arrived in the last 24 hours on every run, which creates duplicate files in Drive. The cleaner pattern is to apply a Gmail label after processing a thread (thread.addLabel()) and exclude that label from the search query with -label:saved-to-drive. That way each message is processed exactly once regardless of trigger timing.

The search operator is:unread is tempting as a proxy for new messages, but it breaks the moment someone reads a thread before the trigger fires. Label-based deduplication is more reliable.

Setting up a time trigger without touching the UI

You can register the trigger programmatically so anyone who copies the script doesn't have to click through the Apps Script editor triggers panel:

Add a one-time setup function: function createTrigger() { ScriptApp.newTrigger('saveAttachmentsToDrive').timeBased().everyDays(1).atHour(7).create(); }. Run it once, then delete it or leave it — calling it again creates a duplicate trigger, which doubles your Drive writes.

The script requires two OAuth scopes: https://www.googleapis.com/auth/gmail.readonly and https://www.googleapis.com/auth/drive. Apps Script infers these from the service calls you make, so you don't declare them manually. On first run the authorization dialog will ask you to approve both. If you later add GmailApp.search() with a modify operator (like addLabel), the scope widens to gmail.modify and the authorization prompt fires again for any existing users of the script.

FAQ

Why are duplicate files appearing in my Drive folder?

The script re-runs against the same messages each time unless you mark processed threads. Add a Gmail label after saving (thread.addLabel(GmailApp.getUserLabelByName('saved-to-drive'))) and include -label:saved-to-drive in your search query. Create the label in Gmail first or use GmailApp.createLabel() once in a setup function.

Can I save attachments to a subfolder organized by sender or date?

Yes. Replace the single folder.createFile(att) call with logic that creates or fetches a subfolder first: var sub = getOrCreateSubfolder(folder, messages[j].getFrom()). DriveApp.getFoldersByName() searches across all of Drive, not just children of a specific folder, so use folder.getFoldersByName() on the parent folder object to scope the lookup correctly.

The script times out before finishing. How do I handle a large backlog?

Apps Script has a 6-minute execution limit. For large backlogs, process one thread per run and store the last-processed thread index in PropertiesService (PropertiesService.getScriptProperties().setProperty('lastIndex', i)). Read it back at the start of each run and resume from there. Combine this with a short trigger interval (every 5 minutes) to drain the backlog incrementally.

How do I skip Google Workspace native files like Docs or Sheets that show up as attachments?

Call att.isGoogleType() before saving. It returns true for Docs, Sheets, Slides, and Forms attached as Drive links. Those can't be written to Drive via createFile() anyway — the call throws — so checking isGoogleType() and skipping is both the correct filter and the error prevention.

Want the plain-English version? Describe the automation at bulldo.gs and get working Apps Script back — free, no login.

Send personalized emails from a sheet in Gmail

bulldo.gs — Sat, 13 Jun 2026 00:22:40 +0000

I have a spreadsheet of names and email addresses and I want to send each person a personalized message from my Gmail account without copy-pasting or using a paid tool.

// Mail merge: Sheet cols A=Name, B=Email, C=Sent
// Run from Apps Script; authorize Gmail + Sheets scopes
function sendMerge() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange().getValues();
  var quota = MailApp.getRemainingDailyQuota();
  var sent = 0;
  for (var i = 1; i < rows.length; i++) {
    if (rows[i][2] === 'Sent') continue;
    if (sent >= quota) {
      Logger.log('Quota reached at row ' + (i + 1));
      break;
    }
    var name = rows[i][0];
    var email = rows[i][1];
    var subject = 'Hey ' + name + ', here is your update';
    var body = 'Hi ' + name + ',\n\nYour personalized content goes here.\n\nThanks';
    MailApp.sendEmail(email, subject, body);
    sheet.getRange(i + 1, 3).setValue('Sent');
    sent++;
  }
}

Set up your sheet and open the script editor

Put names in column A, email addresses in column B, and leave column C blank — the script writes 'Sent' there as it goes. Header row in row 1 is assumed; the loop starts at index 1 (row 2) to skip it. Open the script editor from Extensions > Apps Script, paste the function, and save.

The first time you run sendMerge() Google will ask you to authorize two scopes: Sheets (read/write the active spreadsheet) and Gmail (send mail on your behalf). Both are required. If you only see a Sheets prompt, delete the file and re-paste — a cached partial authorization sometimes skips the Gmail scope on older script files.

Why the Sent column is the whole point

Consumer Google accounts cap at roughly 100 outgoing recipients per 24-hour rolling window via MailApp. If your list has 200 rows and you run the script at 11 pm, it will send 100 and log 'Quota reached at row 101'. Without the Sent check, a second run the next morning resends rows 1-100 to people who already heard from you.

The guard is two lines: skip rows already marked Sent, and check getRemainingDailyQuota() before each send rather than once at the top. Quota resets at midnight Pacific, not at a fixed offset from your first send, so a pre-loop check can be stale by the time you reach row 80. Checking inside the loop costs nothing and catches the edge.

I keep a copy of this pattern in a utils file I paste into every client sheet project — the first time I forgot the Sent column I sent a 'welcome' message to a 40-person list twice in one afternoon. Nobody complained loudly, but it was avoidable.

Personalize beyond name substitution

The body string uses plain concatenation rather than a template, which makes the highlighting cleaner but also makes it easy to pull additional columns. Add a column D for a custom note, read it as rows[i][3], and concatenate it into the body the same way. You can also pass an options object as a fourth argument to MailApp.sendEmail() to set a replyTo address, cc, or an HTML body — the signature is sendEmail(recipient, subject, body, options).

For HTML email, set options.htmlBody to a string of markup. The plain body argument still needs to be present as a fallback for mail clients that strip HTML. Keep both in sync or you will confuse recipients whose clients show the plain version.

Workspace (paid Google) accounts get a higher quota — 1,500 recipients per day — and can send as an alias using GmailApp.sendEmail() with a 'from' field. MailApp does not support alias sending; if that matters, swap the send call to GmailApp.sendEmail() with the same argument order.

FAQ

What is the daily email limit for Apps Script MailApp?

Consumer (free) accounts: approximately 100 recipients per 24-hour window. Google Workspace accounts: 1,500 per day. getRemainingDailyQuota() returns the live number so you can check mid-loop rather than guessing.

Can I send HTML email with this script?

Yes. Pass a fourth argument to MailApp.sendEmail(): an options object with an htmlBody property set to your HTML string. Keep the plain-text body argument in place as a fallback — it is required even when htmlBody is set.

The script sent duplicates. What happened?

Most likely the Sent column check was missing or the column index was wrong (columns are zero-indexed in getValues(), so column C is index 2, not 3). Confirm sheet.getRange(i + 1, 3).setValue('Sent') writes to the correct column before your next run.

How do I run this on a schedule instead of manually?

In the Apps Script editor, open Triggers (the clock icon), add a time-driven trigger pointing to sendMerge, and set your interval. The Sent column makes repeated scheduled runs safe — already-sent rows are skipped automatically.

Want the plain-English version? Describe the automation at bulldo.gs and get working Apps Script back — free, no login.

Remove duplicate rows in Google Sheets

bulldo.gs — Sat, 13 Jun 2026 00:22:22 +0000

I have a Google Sheet with duplicate rows and I want to remove them programmatically, either on demand or on a schedule, without destroying the rest of my data.

// removeDuplicates.gs — dedup active sheet, keep first occurrence
// Run from Extensions > Apps Script, or bind to a trigger.
function removeDuplicateRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var seen = new Set();
  var unique = [];
  for (var i = 0; i < data.length; i++) {
    var key = data[i].join('|');
    if (!seen.has(key)) {
      seen.add(key);
      unique.push(data[i]);
    }
  }
  sheet.clearContents();
  sheet.getRange(1, 1, unique.length, unique[0].length).setValues(unique);
}

Why rewrite instead of delete

The instinct when deduplicating is to loop through the sheet and call deleteRow on each duplicate. That works, but it has a sharp edge: every call to deleteRow shifts all rows below it up by one. If you delete row 3, what was row 4 is now row 3, and your loop index is already pointing at the new row 4. The safe workaround people reach for is iterating bottom-to-top, which works but means holding the full duplicate set in memory anyway, making one API call per deleted row.

The approach here sidesteps the problem entirely. Read everything once with getDataRange().getValues() — a single API call that returns a 2D array. Build the deduplicated array in JavaScript using a Set to track which row fingerprints you have already seen. Then clear the sheet and write the result back with one setValues call. Two API calls total, regardless of how many duplicates you had. For a 10,000-row sheet, this is the difference between a script that finishes in two seconds and one that times out at the six-minute Apps Script execution limit.

The row key is built with data[i].join('|'). The pipe character works as a separator in practice; if your data actually contains pipe characters in every column, use a multi-character sentinel like '||~||' to eliminate false collisions. I have watched this bite people on CRM exports where one column was a phone number formatted as +1|555|0100 — the joined key collides across rows that are genuinely distinct.

Wiring it to a trigger or a menu item

Running removeDuplicateRows manually from the Apps Script editor is fine for one-off cleanup. For anything recurring — a sheet fed by a form, a nightly import, a webhook dump — you want a trigger.

In the Apps Script editor, open the clock icon (Triggers), add a new trigger, point it at removeDuplicateRows, and set it to time-driven on whatever cadence matches your ingest rate. The script runs as you, under your quota, so it counts against your daily Apps Script runtime allowance (6 minutes per execution, 90 minutes per day on the free tier).

If you want a button in the sheet UI instead, add this alongside the existing function: function onOpen() { SpreadsheetApp.getActiveSpreadsheet().addMenu('Tools', [{name: 'Remove duplicates', functionName: 'removeDuplicateRows'}]); }. The onOpen trigger fires automatically each time the spreadsheet opens and puts a menu entry under a custom Tools header. The first time I set this up for a client, I forgot that onOpen only runs for the user who opens the file — shared sheets where a non-owner triggers it still work, because the menu calls the bound script, which runs as the script owner.

Deduping on a subset of columns, not the whole row

Sometimes two rows are duplicates for your purposes even if one column differs — a timestamp column, an auto-incremented ID, a status field that got updated. In that case, building the key from the entire row gives you false negatives (rows that should be collapsed but aren't, because column G differs by a second).

To key on specific columns only, replace the join line. If columns A, B, and C (indexes 0, 1, 2) define uniqueness: var key = [data[i][0], data[i][1], data[i][2]].join('|'). Everything else in the script stays the same. The unique array still keeps the full row — you are only changing what counts as a duplicate, not what gets written back.

One practical note: this keeps the first occurrence. If the column you excluded (say, a timestamp) is meaningful and you want the most recent occurrence rather than the earliest, sort the data descending by that column before the dedup loop. getValues returns rows in sheet order, so pre-sort in the sheet or add a JavaScript sort step before the Set iteration.

FAQ

Will this delete my header row?

No, as long as your header is row 1 and is unique (which it almost always is — column names do not repeat). The Set sees it first and keeps it. If you have a pathological case where two header rows are identical, add an if (i === 0) { unique.push(data[i]); continue; } guard at the top of the loop.

The script runs but then the sheet is blank — what happened?

Almost certainly clearContents fired and then setValues failed silently because unique was empty or unique[0] was undefined. This happens when getDataRange returns a sheet with no data (e.g., a freshly created tab) or when all rows were considered duplicates of each other — check that your separator character does not appear in every cell of a column. Add a guard: if (unique.length === 0) return; before the clearContents call.

Can I run this on a specific named sheet instead of the active one?

Replace SpreadsheetApp.getActiveSheet() with SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Your Sheet Name'). getSheetByName returns null if the name does not match exactly (case-sensitive, leading/trailing spaces count), so add a null check if you are running this from a trigger where the active sheet might not be the one you intend.

How do I dedup across multiple sheets at once?

Wrap the body of removeDuplicateRows in a helper that accepts a sheet argument, then iterate: var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets(); for (var s = 0; s < sheets.length; s++) { dedupSheet(sheets[s]); }. Each sheet gets its own isolated Set, so duplicates are evaluated per-sheet, not across sheets.

Want the plain-English version? Describe the automation at bulldo.gs and get working Apps Script back — free, no login.