GAS – Insert Checkmark to Cell and Conditional Formatting

A little timesaver. Google Sheets doesn’t have a “Special Characters” selector, so if you want to use one you have to open up a Google Doc, copy the character you want, then back to Sheets to use it. This example shows a simple apps script for inserting a checkmark to a cell. We will then look at conditional formatting of the cell/cells. Continue reading

GAS – Get Array from Data Validated Range

Been happily grabbing data ranges from cell ranges in google sheets for ages, but never spotted that if you have a range where all the cells have data validation in them (e.g. a list of values or list from range), then even if they are blank, e.g. no selection has been made, GAS still counts them if you get the “length” of the array. This little snippet deals with that! Continue reading

GAS – Restore Data Validation

A downside with GAS spreadsheets is the inability to share them and properly protect them at the same time, whilst remaining usable by an end user. So if I want an “unknown” and “untrusted” user to access the sheet and select from dropdowns etc., i must give them editing rights.

This example shows how to check and restore a cell validation each time the sheet is opened. Continue reading