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.
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.
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...
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 nearest match. This works by repeatedly selecting the next closest color in the cieLab space. My implementation of this algorithm is show 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.
This previews the color order selected, initially in the order loaded from the Sheet.
becomes this using nearest match sorting
You can fine tune the colors by dragging them around.
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
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.
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 acheived.
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
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 as I've open sourced the code.
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > Color Fiesta >