Coding Help Tips Resources Tutorials
Google Apps Scripts and Web Development Tutorials and Resources by Laurence Svekis
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
Google Apps Script is a scripting language that allows you to automate tasks, extend functionality, and customize Google Workspace (formerly known as G Suite) applications such as Google Sheets, Google Docs, Google Slides, and more. It provides a way to write code and create custom scripts that interact with various Google services and APIs. Within … Read more
Hides one or more consecutive columns starting at the given index. Use 1-index for this method. var ss = SpreadsheetApp.getActiveSpreadsheet();var sheet = ss.getSheets()[0];// Hides the first three columnssheet.hideColumns(1, 3); Here’s an example of how to use sheet.hideColumns in Apps Script to hide specific columns in a Google Sheets spreadsheet: function hideColumnsExample() { // Get the … Read more
To use this code: Remember to adjust the index in getChild() based on your specific requirements and the structure of your document.
o add numbered list items to a Google Docs document using Apps Script, you can use the setListId() and setNestingLevel() methods of the ParagraphHeading element. Here’s an example that demonstrates how to add numbered list items: function addNumberedListToDoc() { var doc = DocumentApp.getActiveDocument(); var body = doc.getBody(); // Create a numbered list var listItems = … Read more