The geeky things that keep me happy.
Google Apps Script / Javascript
I just had a recent email from a reader who asked how to tidy up a user’s inputted name from say, a Google Form so that all the first letters of each work in the name are capitalised in the same way that the Google Sheets Proper function does.
I thought it would be a good idea to provide a quick reference for the reader and myself for future projects.
Continue reading “Google Apps Script – Javascript: Emulate the “Proper” Google Sheets Function”
Google Apps Script, Google Sheets, Javascript: Spread operator, fill, new Array, forEach, isArray, flat, every, slice, length, typeOf
Imagine if you have a set of data, say, Cat, Dog, Goat and you want to create a new column where each item is repeated 3 times.
Essentially what we are doing here is spreading out our original list of numbers in multiples of 3.
What if we wanted to specify how many times each item will be spread out into our new column? For example, maybe we want cat to repeat 2 times, dog to repeat 4 times and goat to repeat 3 times. Something like this:
Finally, what if we want to evenly assign our list of cat, dog and goat to another list? Imagine we have a bunch of people wanting to pet an animal. We need to evenly assign our animals to the list of people.
There is no real easy approach to do these things with the standard Google Sheets Formulas (Known as Functions in Google Sheets). Fortunately, we can create custom functions with Google Apps Script to do the job.
In this post, I will go over the 3 custom functions that I created to solve these three problems. I’ll then provide a quick-use guide if you just want to dive in and get started using them. Finally, I go over some of the interesting parts of the Google Apps Script code for those of you who wish to learn more about creating your own custom functions.
