Important note on my add-ons
I’ve (reluctantly) decided to stop supporting all my Apps Script add-ons, as I no longer have the time or inclination to put them through the various verification processes and upcoming add-on store process changes.
I realize that many of you may be using some of them. In order to provide some continuity, since they are open source, you are welcome to make your own copy and use them how you wish. Any developers who want to repackage and republish are also welcome to do so.
Here’s the repos. You’ll also find the link to the scripts if you prefer to take a copy of already built scripts.
https://github.com/brucemcpherson/ChordSnip
https://github.com/brucemcpherson/ColorArranger
https://github.com/brucemcpherson/SankeySnip
https://github.com/brucemcpherson/SlidesMerge
https://github.com/brucemcpherson/Dicers
Sankeysnip
http://Slidesmerge
Dicers
What is the Color Arranger
 |
An Apps Script add-on that allows you to arrange, merge and sort a Google Sheet using the background color properties. You work on a sidebar preview of the colors present in the selected column, sorting, moving around manually and merging. Once you’re happy with the order and the final color palette, Apply the changes to sort your data and recolor your column cells. |
Where to get it
The Color Arranger Sheets add-on is now available (free) in the add-ons store.
Example of what it’s for
Let’s say you have a sheet with a column of plain text values that you want to order. Fill the cells with color shades to represent sentiment, pick the color property you want to sort on (for example saturation, hue etc), and apply to sort your sheet. You can also use the dropper to merge colors automatically.
On loading
The add-on displays ‘Sort column’ which are the sheet headings of the current sheet. Each unique color in the selected column is shown in the Arranger area. Selecting a different column will repopulate the Arranger with the colors from that column.
If you check Headings row, any arranging will not include the headings row in the sort.
If there is not a heading row, the first row color is included in the Arranger and the labels are A,B,C,D etc…
Sorting properties
Once the column colors are loaded you can sort them on a variety of color properties. These are values from a number of different color space measurements.
The initial order is the same order as on the sheet.
In addition to various color space value sorts, and the original order, it also provides a sorting algorithm that is unique to the Color Arranger, known as the nearest match. This works by repeatedly selecting the next closest color in the cieLab space. My implementation of this algorithm is shown in Find nearest color match.
At this stage you are not sorting the sheet – just the list of colors, so you can play around trying different orders.
The Arranger
This previews the color order selected, initially in the order loaded from the Sheet.
becomes this using nearest match sorting
Fine tuning
You can fine-tune the colors by dragging them around.
Changing and merging colors
In some cases, you may want to reduce the number of unique colors by merging similar colors. You can do that using the eyedropper tool. Click the eyedropper, select a color and drop it on other colors. In this case I wanted to reduce the number of brown variants.
First I’ll sort by nearest match to get colors like each other together
Now I’ll click on the dropper, the cursor changes to the dropper and I’ll position it over the brown color I like and want to keep to select it
The cursor changes to a bucket fill, which I click on each color I want to change to that selected brown, and when done, hit escape or click back on the bucket home.
And that’s it – I’ve now configured the preview so that the various old browns are going to be replaced by the single brown I selected
Contrasting text color
If you are changing the background color of cells, it’s possible that the font color of the text in that cell will no longer be appropriate. This option will redo all the text colors of the sorted column so that the best contrast ratio of color to textColor is achieved.
Applying changes to sheet
Up till now, all the changes have been locally in the Arranger, with no changes made to the Sheet.
Apply will sort the sheet in the final color order selected, and apply any color merging, Close will abandon the whole thing and exit the add-on.
I did a few more color merges, so my final palette looked like this
I apply the changes, so now I have reduced the palette to this
And my sheet is now nicely sorted and recolored like this
Availability
|
The Color Arranger Sheets add-on is available (free) in add-ons store. If you are interested in building add-ons you might find some useful stuff here. |