Google Sheets

Mastering Google Apps Script: SpreadsheetApp Class

by

Google Apps Script SpreadSheetsApp class Google Apps Script – sheetsapp Class The spreadsheetsapp class is specifically designed to work with Google Sheets. It offers a range of methods that allow developers to perform operations such as reading and writing data, formatting cells, creating charts, and more. Basic Example: // Open the active spreadsheet var spreadsheet … Read more

Future value of an investment using compound interest Google Apps Script Custom Formula

by

🚀 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

Create Apps Script custom functions How to get the total amount of items including the tax rate

by

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

Google Sheets get Data from two sheets to use within a custom function

by

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

Get the total cost custom sheets formula calculate values across ranges

by

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

Calculate a weighted Average from a range of values

by

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

Calculate average from range above a threshold

by

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 Examples and source code

by

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

by

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