Coding Help Tips Resources Tutorials
Google Apps Scripts and Web Development Tutorials and Resources by Laurence Svekis
🚀 Unlock the Power of Compound Interest: Calculate Your Future Investment Value with Custom Formulas! Ever wondered how your investments will grow over time with compound interest? In this tutorial, we’ll show you how to create a custom formula using Google Sheets to calculate the future value of an investment based on key factors like … Read more
How to get the total amount of items including the tax rate CALC_TOTAL_AMOUNT Product Price Quantity Tax Rate Subtotal Total Amount Product A 10 5 0.08 4 54 Product B 20 3 0.05 3 63 Product C 15 2 0.1 3 33 Total 150 In this example, we’ll create a custom formula that calculates the … Read more
How to use data from two sheets to create a custom Apps Script function CALC_TOTAL_COST_WITH_TAX function getTaxRate(category) { const sheets = SpreadsheetApp.getActive().getSheetByName(‘TaxTable’); const taxTable = sheets.getDataRange().getValues(); let tax = 0 taxTable.forEach(val =>{ if (val[0] == category) { tax = val[1]; } }) return tax; } function test(){ Logger.log(getTaxRate(‘Category 1’)) } Category Tax Rate Category 1 … Read more
In this tutorial, we walk you through the creation of a custom formula using Google Apps Script in Google Sheets, allowing you to automatically calculate total costs for items and generate an overall expenditure. Summary: Managing calculations in spreadsheets becomes a breeze with this custom formula: Step 1 – Setup: Step 2 – Writing Code: … Read more
WEIGHTED_AVERAGE Value Weight 10 0.1 15 0.1 5 1 Result 6.25 Scenario: You want to create a custom Google Sheets formula that calculates the weighted average of values in a specified range, where each value is multiplied by its corresponding weight. Data Table: A B C 1 Value Weight 2 10 0.3 3 15 0.5 … Read more
AVERAGE_ABOVE_THRESHOLD 7 4 9.333333333 6 10.66666667 15 11.33333333 11 8 In this example, we’ll create a custom formula that calculates the average of a range of numbers, excluding any values that are less than a specified threshold. Scenario: You have a range of numbers in column A and a threshold value in cell B1. You … Read more
Google Sheets Formulas Capitalize the first letter of each word in a given string function CAPITALIZE_WORDS(str) { const words = str.split(‘ ‘); for(let i=0;i<words.length;i++){ words[i] = words[i].charAt(0).toUpperCase() + words[i].slice(1).toLowerCase(); } return words.join(‘ ‘); } The given code defines a function called CAPITALIZE_WORDS that takes a string str as an argument. The purpose of the function … Read more
Ultimate Apps Script Questions with Solutions Guide How can I use Google Apps Script to send emails from a Google Sheet?How can I add a timestamp to a Google Sheet when a cell is edited?How can I retrieve data from an external API using Google Apps Script?How can I delete rows from a Google Sheet … Read more
Copy Data with function to a New Sheet The purpose of this code is to add a custom menu to a Google Sheet that allows the user to copy the values of the currently selected range and paste them into a new sheet with a unique name. When the user clicks on the custom menu … Read more
Table of Sample DataFormula to calculate the average of the values in a given rangeSample table for the code with the given input values:Concatenate two strings and capitalize the first letterTable of sample data for the CONCAT_CAPITALIZE_FIRST function:Calculate the factorial of a given numberTable of sample data for the code:Count the number of occurrences of … Read more