Google Apps Script – How to make a Custom Function to Use in Google Sheets

Google Apps Script: Custom Functions, Google Sheets

Google Sheets has a vast library of functions (or formulas) you can use to get your Spreadsheet tasks done. However, there are some instances when you want a specific function that is not available in Google Sheets or wants to make a simplified version of some combined function to make life easier for your users. Fortunately, Google has your back with Google Apps Script.

Before we start, Google does a pretty great job explaining the basics of creating a custom function. However, when it comes to explaining how to add all that information that goes into a function when you type it into Sheets it is a bit vague.

Custom Function Decorators Google Apps Script

Let’s look at the creation process through a recent example I created:

Continue reading “Google Apps Script – How to make a Custom Function to Use in Google Sheets”

Google Sheets – Random Alphabetic, Random Alphanumeric and Random Alphanumeric + Character Custom Functions (Updated January 2022)

Google Sheets, Google Apps Script

I was working on a Spreadsheet in Google Sheets a few days ago and needed to generate some random codes for my users. To do this, I just used the RANDBETWEEN(start val, end val) function built into Google Sheets.

It goes a little like this. If I want to build a 5 digit random number I would:

RANDBETWEEN Google Sheets

But this got me wondering. Is there a function for random Alphabetic strings or Alphanumeric strings or Alphanumeric with Character strings. The answer?

via GIPHY

 

No…

So instead I decided to make a custom function with Google Apps Script to do this job for me…

Continue reading “Google Sheets – Random Alphabetic, Random Alphanumeric and Random Alphanumeric + Character Custom Functions (Updated January 2022)”

Google Apps Script – How to Automatically Generate a Time From a Four Digit 24 hour Time in Google Sheets.

Google Apps Script, Google Sheets

On a recent board post, a Google Sheets user wanted to change a four-digit number (for example, 1230) to a time, like 12:30, in the same cell that the item was entered.

Unfortunately, the user was not in a position to change the starting values, so they were left with the 4 digits.

There are two ways of doing this with varying levels of complexity:

 

  1. The Google Sheets Formula Approach
  2. The Google Apps Script onEdit Approach

Continue reading “Google Apps Script – How to Automatically Generate a Time From a Four Digit 24 hour Time in Google Sheets.”

How to Hide a Row based on a Cell Value in Google Sheets with Filter or Google Apps Script.

Google Sheets, Google Apps Script, onEdit

Sometimes, when you are working on a shared Google Sheet you might want to hide a row based on a cell value.

For example, perhaps we don’t want to see row information of orders that have been paid for. Take a look at my D&D miniatures wholesale orders sheet (image below). I know I don’t need to follow up the orders that have been paid, marked with a ‘Yes’ in column F, so I don’t want to see them on my sheet.

We want to hide the "Yes" - Google Sheets

We can hide these two ways:

  1. Create a filter.
  2. Google Apps Script onEdit() trigger.

Continue reading “How to Hide a Row based on a Cell Value in Google Sheets with Filter or Google Apps Script.”

Sections to Sheets – Free Google Add-on That Separates Column Sections Into Individual Sheets for Page-by-Page Export or Printing (Updated January 2022)

What is Sections to Sheets?

Sections to Sheets is a free Google Sheets Add-on that separates rows by column sections and puts them in individual pages or sheets in a spreadsheet in preparation for export and printing.

You can find Sections to Sheets at the Google Workspace Marketplace.


Google Workspace Marketplace badge

Example 1

Let’s say you want to print out a list of expenditures by department and you want each department to start on a new page. You’ll also want to keep the same header for each page.

Example 2

You might want to export a list of grades by class number. You sort the grades by class and then export the list with each class starting on a new page with a header and footer.

Sections to Sheets can help you achieve this quickly by creating a new Google Spreadsheet and separating each selection and putting it into a new Sheet(tab) with or without headers and footer. This will enable you to quickly export or print your sheets.

Continue reading “Sections to Sheets – Free Google Add-on That Separates Column Sections Into Individual Sheets for Page-by-Page Export or Printing (Updated January 2022)”

%d bloggers like this: