Fix 429 Errors in Google Sheets MAP and UrlFetchApp

A user on the Google Apps Script community recently posted: “I am using the MAP function to run a custom script that fetches crypto prices for 300 rows. It works for a minute, then suddenly every cell returns a 429 error. How can I slow down the execution so I don’t hit the API limit?”

This is a classic issue. When you nest a custom function (using UrlFetchApp) inside a Lambda function like MAP or SCAN, Google Sheets attempts to calculate those cells virtually simultaneously. This triggers a “Too Many Requests” (429) error from the external API, or even from Google’s own internal service throttler.

Comparison of Methods to Fix 429 Errors

Method	Best For	Pros	Cons
Row-by-Row (Current)	Very small datasets (<10 rows)	Simple to write.	Almost guaranteed to hit 429 errors on large sets.
Utilities.sleep()	Medium datasets	Throttles execution manually.	Slows down the sheet; often hits the 30-second execution limit.
Batch Processing (Recommended)	Large datasets (100+ rows)	Sends 1 request for many rows; highly efficient.	Requires rewriting the script to handle arrays.

---

The Recommended Solution: Batching your API Calls

Instead of calling the script 500 times for 500 rows, you should pass the entire range to the script once. This allows the script to handle the logic, manage any necessary delays, or use a batch-endpoint provided by the API.

Step 1: Rewrite your Apps Script for Arrays

Most users write scripts to handle a single value. To fix the 429 error, we must rewrite the function to accept an array (a range of cells).

Tested on Google Sheets/Apps Script as of 2024.

/**
 * Fetches data in bulk to avoid 429 errors.
 * @param {A1:A500} inputRange The range of data to process.
 * @customfunction
 */
function BATCH_FETCH_DATA(inputRange) {
  // If a single cell is passed, wrap it in an array to maintain consistency
  if (!Array.isArray(inputRange)) {
    inputRange = [[inputRange]];
  }

  // Map over the 2D array provided by Google Sheets
  return inputRange.map(row => {
    const cellValue = row[0];
    if (!cellValue) return "";

    try {
      // Logic: If your API supports batching, call it once OUTSIDE this map.
      // If not, we add a small delay here to prevent 429 errors.
      Utilities.sleep(100); // 100ms delay between fetches

      const response = UrlFetchApp.fetch("https://api.example.com/data/" + cellValue);
      const json = JSON.parse(response.getContentText());
      return json.price; // Adjust based on your API structure
    } catch (e) {
      return "Error: " + e.message;
    }
  });
}

Step 2: Apply the formula to your Sheet

Stop using =MAP(A1:A500, LAMBDA(row, MY_FUNCTION(row))). Instead, call your new batch function once in the top cell of your results column.

1. Go to your sheet.
2. In cell B1, enter: =BATCH_FETCH_DATA(A1:A500)
3. Press Enter.

The script will now run as a single process. Because it is one execution, Utilities.sleep() will actually work to throttle the requests, and the API is much less likely to flag your traffic as a Denial of Service (DoS) attack.

---

Troubleshooting and Common Traps

• The 30-Second Limit: Google Sheets custom functions have a strict 30-second execution limit. If you have 500 rows and a 100ms sleep timer, you will hit 50 seconds and the function will time out.
  • The Fix: If your dataset is huge, don’t use a custom function. Use a standard script assigned to a button (Insert > Drawing) or a custom menu that writes the values directly to the cells.
• The “Loading…” State: Large batch operations can make a sheet feel “stuck” in a loading state.
  • The Fix: Break your data into smaller chunks (e.g., call the function for rows 1-100, then 101-200).
• API Batch Endpoints: Check if the service you are calling (e.g., CoinGecko, OpenAI, etc.) has a “bulk” or “batch” endpoint. This is always superior to a loop. Instead of fetching 100 times, you send 100 IDs in one request.

---

Frequently Asked Questions

What if I need to apply this to filtered rows? If you use the formula =BATCH_FETCH_DATA(A1:A500), it will process everything in that range regardless of filters. To respect filters, you would typically use the SUBTOTAL or AGGREGATE functions to identify visible rows, but it is often easier to simply let the script run and ignore the hidden rows manually in your data logic.

Can I automate this with Apps Script triggers? Yes. If your data updates frequently and you keep hitting 429 errors, navigate to Extensions > Apps Script, click the Triggers (clock icon), and set your function to run every hour. This “pushes” the data to the sheet rather than “pulling” it via a formula, which is much more stable for large datasets.

Does Google have its own limit on UrlFetchApp? Yes. Consumer accounts (Gmail) are generally limited to 20,000 calls per day, while Google Workspace accounts have higher limits (100,000+). However, the 429 error is almost always the receiving API telling you to slow down, not Google.