Apps Script Code Examples

Google Apps Script Speaking Events by Laurence Svekis

by

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

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

Quotas for Google Services

by

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

How to see all Bound and Standalone Google Apps Script from an account

by

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

Hide Columns and Rows using Google Apps Script

by

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

Listing contents of paragraphs using Google Apps Script

by

To use this code: Remember to adjust the index in getChild() based on your specific requirements and the structure of your document.