Introduction

Did you know you can create your own custom formulas in Google Sheets?

Google Sheets has powerful built-in functions like SUM(), VLOOKUP(), and IF(), but sometimes you need a custom formula to perform special calculations that aren’t available by default.

With Google Apps Script (GAS), you can create custom functions that work just like regular formulas in Google Sheets.

By the end of this guide, you’ll be able to:
✅ Write custom functions in Apps Script
✅ Use them in Google Sheets like built-in formulas
✅ Automate complex calculations

Let’s get started! 🚀

---

Step 1: Open Google Apps Script Editor

1️⃣ Open a Google Sheet
2️⃣ Click on Extensions > Apps Script
3️⃣ Delete any existing code
4️⃣ Now, you’re ready to write your first function!

🎉 Apps Script Editor is where the magic happens! 🎉

---

Step 2: Create Your First Custom Function

Let’s create a function that converts miles to kilometers.

Example: Convert Miles to Kilometers

function milesToKilometers(miles) {
  return miles * 1.60934;
}

📌 How to Use in Google Sheets:
Type the following formula in any cell:

= milesToKilometers(10)  

✅ This will return 16.09 km! 🚗💨

---

Step 3: Build a Custom Currency Converter

Want to convert USD to EUR inside Google Sheets? Let’s create a function that fetches the latest exchange rate!

Example: Convert USD to EUR

function convertCurrency(amount, fromCurrency, toCurrency) {
  var url = "https://api.exchangerate-api.com/v4/latest/" + fromCurrency;
  var response = UrlFetchApp.fetch(url);
  var data = JSON.parse(response.getContentText());
  
  return amount * data.rates[toCurrency];
}

📌 How to Use in Google Sheets:

=convertCurrency(100, "USD", "EUR")

✅ This will return the equivalent amount in EUR using real-time exchange rates! 💰

🎯 Tip: Replace "USD" and "EUR" with any currency codes (e.g., "GBP", "JPY", "INR").

---

Step 4: Create a Function to Extract Domain from an Email

Need to extract the domain name from an email address? Let’s automate it!

Example: Extract Domain Name

function extractDomain(email) {
  return email.split("@")[1];
}

📌 How to Use in Google Sheets:

=extractDomain("john.doe@example.com")

✅ This will return “example.com”! 📧

---

Step 5: Automate Data Cleaning with a Custom Function

Let’s build a function that removes extra spaces from text, ensuring clean data.

Example: Trim Extra Spaces

function cleanText(text) {
  return text.trim().replace(/\s+/g, " ");
}

📌 How to Use in Google Sheets:

=cleanText("   Hello   World!   ")

✅ This will return "Hello World!" (without extra spaces).

🎯 Tip: Useful for cleaning messy datasets before analysis!

---

Step 6: Create a Function to Count Specific Words

Want to count how many times a word appears in a sentence?

Example: Count Word Occurrences

function countWord(text, word) {
  var regex = new RegExp("\\b" + word + "\\b", "gi");
  var matches = text.match(regex);
  return matches ? matches.length : 0;
}

📌 How to Use in Google Sheets:

=countWord("Apple banana apple orange apple", "apple")

✅ This will return 3, since “apple” appears 3 times! 🍏🍎

---

Step 7: How to Use Your Custom Functions in Any Spreadsheet

By default, your custom functions only work in the current spreadsheet.

To use them in other Google Sheets:
1️⃣ Copy and paste the script into Apps Script Editor of a new sheet
2️⃣ Click Save and Close
3️⃣ Start using your custom formulas 🎉

---

Bonus: Debugging & Testing Your Functions

If your function isn’t working, try these troubleshooting steps:

🔹 Use Logger.log(variable) inside your script to check values
🔹 Open Apps Script Editor > View > Logs to see errors
🔹 Make sure parameters (e.g., text, amount) are entered correctly

---

Conclusion: Start Building Custom Functions Today!

Now you know how to:
✅ Create custom Google Sheets functions using Apps Script
✅ Automate conversions, data cleaning, and calculations
✅ Use custom formulas like built-in Google Sheets functions

🚀 Next Steps:
🔹 Want to automate reports? Learn about Google Sheets Triggers!
🔹 Need to connect Google Sheets with APIs? Try UrlFetchApp.fetch()!