If you’re just getting started with Google Apps Script, don’t worry—each function is explained clearly, with details on how to use it in your Google Sheets to make your life easier. So, let’s dive into it!
How to Add These Custom Formulas
If you missed the steps on how to add custom formulas to your Google Sheets, here’s a quick recap:
- Open Google Sheets and select Extensions > Apps Script.
- Copy and paste the function code into the script editor.
- Save your script, and then start using the functions directly within your Google Sheet.
1. Concatenate Strings with Space
Formula: =CONCATENATE_WITH_SPACE(str1, str2)
Have you ever needed to combine text in Google Sheets, but want it to look clean with spaces between the words? This custom formula is exactly what you need.
function CONCATENATE_WITH_SPACE(str1, str2) {
return str1 + ' ' + str2;
}Usage example: Enter =CONCATENATE_WITH_SPACE("Hello", "World") and you’ll get Hello World.
Explanation: This function takes two text strings as arguments and simply concatenates them with a space in between. It’s a small but very useful function to help keep your text clean and readable.
2. Convert String to Title Case
Formula: =TO_TITLE_CASE(str)
If you want to make your text more presentable, like converting names to title case, this function can be a game changer.
function TO_TITLE_CASE(str) {
return str.replace(/\w\S*/g, (txt) => txt.charAt(0).toUpperCase() + txt.substr(1).toLowerCase());
}Usage example: Enter =TO_TITLE_CASE("hello world") and you’ll get Hello World.
Explanation: This function uses regular expressions to convert each word in a string to title case, where the first letter is capitalized and the rest are in lowercase. This is perfect for making data presentable in reports.
3. Get Current Date and Time
Formula: =CURRENT_DATETIME()
Sometimes you just need to know the current date and time without manually entering it each time. This custom function helps you with that.
function CURRENT_DATETIME() {
return new Date();
}Usage example: Enter =CURRENT_DATETIME() to get the current date and time.
Explanation: This function returns the current date and time using JavaScript’s Date object. It’s a simple, yet useful way to automatically capture timestamps in your sheet.
4. Calculate Compound Interest
Formula: =COMPOUND_INTEREST(principal, rate, time)
Managing finances in Google Sheets? This formula makes calculating compound interest a breeze.
function COMPOUND_INTEREST(principal, rate, time) {
return principal * Math.pow((1 + rate / 100), time);
}Usage example: Enter =COMPOUND_INTEREST(1000, 5, 2) to find out how much your initial investment will grow after 2 years at 5% interest.
Explanation: This function calculates compound interest based on the principal amount, interest rate, and time period. It’s especially helpful if you’re tracking financial growth or planning investments.
5. Convert Kilometers to Miles
Formula: =KM_TO_MILES(km)
If you often deal with units of measurement, like converting distances, this formula is a must-have.
function KM_TO_MILES(km) {
return km * 0.621371;
}Usage example: Enter =KM_TO_MILES(10) and you’ll get 6.21371.
Explanation: This function converts kilometers into miles by multiplying the input value by 0.621371, which is the conversion factor. It’s perfect for people who work with international data or need to switch between metric and imperial systems.
Wrapping Up
These five custom formulas will help you manipulate and analyze your data more efficiently, making your Google Sheets experience a lot more productive and enjoyable. Whether you’re calculating compound interest, converting text to title case, or working with unit conversions, these functions are here to make your life easier.
If you haven’t tried creating custom functions in Google Sheets before, these examples are a great place to start. They’re simple enough for beginners but powerful enough to solve real-world problems.
Ready to Try Them Out?
Go ahead and add these custom functions to your Google Sheets to see them in action! Feel free to share which functions you find most useful, or let me know if you have ideas for more custom formulas you’d like to see.