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.

This nice spreadsheet by James/mreigthies, contains a reference of all the char() numbers you might need. I am only doing one here, but you could build a custom menu with your most used ones.

1. Create the following in your script editor:

To use you simply click on a cell then run the script from your custom menu. This also demonstrates how to use apps script to enter a formula to a cell.

It may be that you want to highlight cells on your sheet that may require a checkmark, and also you may want the user to choose between a selection. However you would want these highlights to go away before printing. Use conditional formatting for this. In my example, I have three cells (B8:B10) which the user might tick. If they are all empty they are a pale yellow, If any one of them is ticked, they turn white. You use a custom formula in each cell to make this work.

=AND(B8="",B9="",B10="")

Again, James/mreigthies, has compiled a spreadsheet full of conditional formatting examples

Leave a Reply

Your email address will not be published. Required fields are marked *