Unlock the Power of Google Sheets: 100 Custom Formulas You Can’t Live Without

Google Sheets is a remarkable tool for everything from personal budgeting to enterprise-level data analysis. But the built-in functions sometimes leave gaps when you need a specialized calculation, custom text manipulation, or seamless API integration. That’s where Google Apps Script comes in—it lets you craft your own formulas (called custom functions) to extend Sheets beyond its native capabilities.

In this post, you’ll discover:

• Why you should build custom formulas
• How to set up your Apps Script environment
• 10 key categories of custom functions with real-world examples
• Bonus tips for optimizing performance and shareability

Why Custom Formulas?

1. Tailored to Your Workflow: Need a case-insensitive lookup? A currency converter? A binomial distribution calculator? Write it once and reuse it across spreadsheets.
2. Maintainability: Encapsulate complex logic in a single function, making your sheets cleaner and formulas easier to audit.
3. Integration: Pull data from external APIs (crypto prices, weather forecasts, Analytics metrics) with a simple formula call.
4. Redistribution: Share your custom functions with teammates or publish them as an add-on for broader use.

Getting Started: Your First Custom Function

1. Open a Google Sheet.
2. Go to Extensions ▶ Apps Script.
3. Rename Code.gs (optional) and paste:function ADD_TWO(a, b) { return a + b; }
4. Save the project.
5. Back in your sheet, type =ADD_TWO(3,5) and watch it return 8.

Congrats—you’ve just created and used your first custom formula!

10 Categories of Custom Formulas (Highlights)

Below are the ten main categories featured in our full library of 100 examples. Scroll down to download the complete list or jump to a specific section.

Category	Examples
1. Simple Math Operations	ADD_TWO, SUBTRACT, MULTIPLY, DIVIDE, POWER
2. Text Manipulation	REVERSE_TEXT, TO_UPPER, PROPER_CASE
3. Date & Time	DAYS_BETWEEN, IS_WEEKEND, WEEKNUM_ISO
4. Lookup & Reference	VLOOKUP_CASE_INSENSITIVE, INDEX_MATCH
5. Array & Range Processing	FLATTEN_RANGE, TRANSPOSE_CUSTOM
6. Financial Calculations	COMPOUND_INTEREST, NPV_SIMPLE, IRR_SIMPLE
7. Statistics & Aggregation	MEDIAN_CUSTOM, CORRELATION, Z_SCORE
8. Conditional Logic	IF_ELSE, IFS_CUSTOM, XLOOKUP_BASIC
9. External APIs & Integration	GET_JSON_FIELD, GET_CRYPTO_PRICE
10. Utilities & Helpers	RANDARRAY_CUSTOM, UUID_GENERATE, LOG_TO_SHEET

Deep Dive: Three Favorites

1. VLOOKUP_CASE_INSENSITIVE

function VLOOKUP_CASE_INSENSITIVE(searchKey, range, index) {
  searchKey = String(searchKey).toLowerCase();
  var values = range.getValues();
  for (var i = 0; i < values.length; i++) {
    if (String(values[i][0]).toLowerCase() === searchKey) {
      return values[i][index-1];
    }
  }
  return '#N/A';
}

Why it matters: Standard VLOOKUP is case-sensitive. This version ensures you never miss a match due to letter casing.

2. GET_CRYPTO_PRICE

function GET_CRYPTO_PRICE(sym) {
  var url = 'https://api.coingecko.com/api/v3/simple/price?ids='+sym+'&vs_currencies=usd';
  var data = JSON.parse(UrlFetchApp.fetch(url).getContentText());
  return data[sym].usd;
}

Why it matters: Instantly pull live cryptocurrency prices into your financial model or dashboard.

3. NETWORKDAYS

function NETWORKDAYS(start, end) {
  var s = new Date(start), e = new Date(end), count = 0;
  while (s <= e) {
    if (s.getDay() > 0 && s.getDay() < 6) count++;
    s.setDate(s.getDate() + 1);
  }
  return count;
}

Why it matters: Calculate business days between dates without relying on external add-ons.

Tips for Success

• Cache strategically: Use CacheService for expensive API calls.
• Batch calls: Retrieve ranges once instead of calling getValues() in loops.
• Error handling: Wrap risky logic in try/catch and return meaningful errors with IFERROR_CUSTOM.
• Documentation: Add JSDoc comments above functions so collaborators know inputs/outputs.

---