In today’s digital age, where efficiency and productivity are paramount, the ability to automate repetitive tasks can be a game-changer. Enter Google Apps Script, an incredibly versatile tool that empowers you to automate and extend the functionality of Google Workspace applications like Google Sheets, Google Docs, and Gmail. In this beginner’s guide, we’ll take a … Read more
🚀 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
Custom formula that generates a unique invoice number 📜🔢 Generating Invoice Numbers with Google Apps Script 🔢📜 In this video, we dive into a practical example of using Google Apps Script to automate the generation of invoice numbers. The GENERATE_INVOICE_NUMBER() function is a part of a Google Sheets project and serves a crucial role in … Read more
Google Apps Script is a JavaScript-based scripting language that automates tasks within Google Workspace apps. It enhances productivity, customizes app functionality, and builds simple web applications. The speaker’s experience began with a University project capturing tweets using Apps Script, which led to automating tasks like report generation. Benefits include streamlining workflows and collaboration. Common uses … 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
When running apps script try to avoid longer than 5 minutes executions, if you need more processing you should chunk the operation into more manageable pieces. Apps Script services have daily quotas and limitations on some features. If you exceed a quota or limitation, your script throws an exception and execution stops. https://developers.google.com/apps-script/guides/services/quotas Feature Consumer … Read more
