Something Fun From My Son

In today’s email:

1. A fun project inspired by my son

2. An overview of the Apps Script that powers it

3. A preview of the nitty-gritty image generating code

TRIVIA

MAIN ARTICLE

Video walkthrough:

My oldest son loves coloring pixel art grids. There are a bunch of these available online, but we’ve gone through most of the interesting ones.

Nothing says Spreadsheet Project like a grid of squares, so I figured I could make him a generator that would spit out a color by number grid based on an image we upload.

And yes, that’s exactly what we’ve done!

🤔Let me know if you’re interested in the more complicated coding behind getting the image into Excel as background colors. I’ll do another write-up on that next time if so.

VBA vs Apps Script

As with most good things in life, you can do all of this in either Excel or Google Sheets.

I'll walk through the Sheets version here, but I'll give you the VBA down at the bottom too...

Here’s the Google Sheet that you can copy and play with.

Apps Script Color by Number Generator

You can follow along in the Apps Script file if you make a copy of the template.

All we’re doing is looping through each cell in a 30×30 grid, seeing what the background color is and then writing a number corresponding to that color in a new sheet.

I used this little dinosaur that my youngest son will enjoy coloring for the example.

In the first part of our Apps Script, we set our constants for the sheets we’ll use, the background colors and the color index:

function colorByNumber() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = ss.getSheetByName('art'); // First sheet with pixel art
  const targetSheet = ss.insertSheet('Color by Number Grid'); // New sheet for output
  const range = sourceSheet.getRange('A1:AN30'); // Pixel art range
  const values = range.getValues();
  const backgrounds = range.getBackgrounds();
  
  const colorDict = {}; // Dictionary to map colors to numbers
  let colorIndex = 1;   // Start numbering colors from 1

Then, we’ll loop through the 30×30 range and map the colors into our color dictionary:

  // Loop through the range and map colors
  for (let row = 0; row < backgrounds.length; row++) {
    for (let col = 0; col < backgrounds[row].length; col++) {
      const bgColor = backgrounds[row][col]; // Get the background color
      
      if (bgColor !== '#ffffff' && bgColor !== '#000000' && bgColor !== '') { // Ignore white/black/empty
        if (!colorDict[bgColor]) {
          colorDict[bgColor] = colorIndex; // Assign a new number to the color
          colorIndex++;
        }
      }
    }
  }

We’ll write the numbers into our new sheet:

  // Write the "Color by Number" grid
  for (let row = 0; row < backgrounds.length; row++) {
    for (let col = 0; col < backgrounds[row].length; col++) {
      const bgColor = backgrounds[row][col];
      if (colorDict[bgColor]) {
        targetSheet.getRange(row + 1, col + 1).setValue(colorDict[bgColor]); // Set the number
      }
    }
  }

And finally, we’ll write a color legend so the kids know which colors to actually color. At the end of the function, we’ll display an alert that the grid was successfully created:

  // Write the color mapping
  let legendRow = 1;
  targetSheet.getRange(1, backgrounds[0].length + 2).setValue('Color Mapping');
  for (const [color, number] of Object.entries(colorDict)) {
    targetSheet.getRange(legendRow + 1, backgrounds[0].length + 1).setBackground(color); // Set color
    targetSheet.getRange(legendRow + 1, backgrounds[0].length + 2).setValue(number);     // Set number
    legendRow++;
  }
  
  SpreadsheetApp.getUi().alert('Color by Number grid created successfully!');
}

Some tweaking remains to be done, but I was very excited to get this working prototype up and running.

Are you interested in seeing how I generated the original pixel background colors from an image into Excel?

Reply to this and let me know!

The VBA Script:

Sub ColorByNumber()
    Dim ws As Worksheet, newWs As Worksheet
    Dim cell As Range, rng As Range
    Dim colorDict As Object
    Dim colorIndex As Long
    Dim rowNum As Long, colNum As Long

    ' Set the range with the colored cells
    Set ws = ThisWorkbook.Sheets(1) ' Assumes the pixel art is on the first sheet
    Set rng = ws.Range("A1:AN30")
    
    ' Create a dictionary to map colors to numbers
    Set colorDict = CreateObject("Scripting.Dictionary")
    colorIndex = 1

    ' Add a new sheet for the "Color by Number" grid
    Set newWs = ThisWorkbook.Sheets.Add
    newWs.Name = "Color by Number Grid"
    
    ' Loop through the cells in the range
    For Each cell In rng
        If cell.Interior.Color <> xlNone Then
            ' Get the cell's background color
            Dim cellColor As Long
            cellColor = cell.Interior.Color
            
            ' Map the color to a number if it's not already mapped
            If Not colorDict.exists(cellColor) Then
                colorDict.Add cellColor, colorIndex
                colorIndex = colorIndex + 1
            End If
            
            ' Determine the row and column in the new sheet
            rowNum = cell.Row - rng.Row + 1
            colNum = cell.Column - rng.Column + 1
            
            ' Write the corresponding number to the new sheet
            newWs.Cells(rowNum, colNum).Value = colorDict(cellColor)
        End If
    Next cell

    ' Output the color-to-number mapping
    Dim mapRow As Long
    mapRow = 1
    newWs.Cells(1, rng.Columns.Count + 2).Value = "Color Mapping"
    newWs.Cells(2, rng.Columns.Count + 1).Value = "Color"
    newWs.Cells(2, rng.Columns.Count + 2).Value = "Number"

    Dim colorKey As Variant
    For Each colorKey In colorDict.Keys
        newWs.Cells(mapRow + 2, rng.Columns.Count + 1).Interior.Color = colorKey
        newWs.Cells(mapRow + 2, rng.Columns.Count + 2).Value = colorDict(colorKey)
        mapRow = mapRow + 1
    Next colorKey

    MsgBox "Color by Number grid created successfully!", vbInformation
End Sub

Learn without limits on Coursera.
Google’s latest course is Prompting Essentials. Learn to give clear and specific instructions to generative AI in 5 easy steps.
Enroll here.

NEXT STEPS

Whenever you’re ready, here’s how I can help:

1. Business tech stack (FREE)
   My recommendations for software and tools I use to create content and run my online business.

2. Sponsor Got Sheet
   Got Sheet teaches business operators, teachers and entrepreneurs how to get good at spreadsheets. Interested in sponsoring an issue or a series of Got Sheet newsletters? Reach out to get more information.

3. Personal budget tool
   As a Got Sheet subscriber, I want you to have a 75% discount on the personal budget I built 10+ years ago and am still using to this day.

HAPPY SPREADSHEETING!

Enjoyed this issue?
Subscribe below or Leave a testimonial

Cheers, Eamonn
Connect on LinkedIn, YouTube & Twitter.