Color Arranger


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.


Main features


Where to get it

The Color Arranger Sheets add-on is now available (free) in 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.

color arranger add-on promotion

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. 



Sorting columns

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 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.


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 acheived.


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




For help and more information join our forum,follow the blog or follow me on twitter .


Comments