Tag: Custom Functions

Google Apps Script – How to Get the Sheet Name and Spreadsheet Name and add to a Cell on Google Sheets with a Custom Function (Updated Feb 2022)

Google Apps Script, Custom Functions

Boy, are these titles getting longer. 

But that’s pretty much the gist of it. In this post we will look at creating a Google Apps Script Custom Function that allows you to do Three things in Google Sheets: 

  1. Get the current sheet name.  That’s the same sheet name as the cell you are working in.
  2. Get all the sheet names. A full list of all the sheet names. 
  3. Get the name of the Spreadsheet file. 
Get Sheet Names and Spreadsheet Names Only - Google Apps Script

The above picture is pretty self-explanatory. If you type in:

=SHEETNAME(#)

Where “#”  is a number 0, 1 or 2 you will get the results displayed in the picture. Any other number will display an error. 

Continue reading “Google Apps Script – How to Get the Sheet Name and Spreadsheet Name and add to a Cell on Google Sheets with a Custom Function (Updated Feb 2022)”

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)”