Change cell value based on its background color in google sheets

Question

Trying to ditch Excel for Google sheets. I have this empty table with some colored cells that I need to fill with symbols. Presently I use this VBA script to do the job:

Sub mark()
    Dim r      As Range
    Dim rCell  As Range

    Set r = Selection.Cells

    For Each rCell In r
        If rCell.Interior.ColorIndex = 10 Then rCell.Value = "×"
        If rCell.Interior.ColorIndex = 3 Then rCell.Value = "×"
        If rCell.Interior.ColorIndex = 45 Then rCell.Value = "×"
        If rCell.Interior.ColorIndex = 1 Then rCell.Value = "×"
        If rCell.Interior.ColorIndex = 15 Then rCell.Value = "×"
    Next

End Sub

Is there a way to accomplish same thing using google sheets?

Alessandro Alessandro · Accepted Answer · 2020-11-05T13:19:48

Solution

In order to achieve this you will have to use Google Apps Script. You can attach an Apps Script project to your Google Spreadsheet by navigating Tools > Script Editor.

You should find a template function called myFunction, a perfect starting point for your script.

Here you can start translating your VBA script to Apps Script which is very similar to Javascript.

First you should define some constants for your script:

  // An array containing the color codes you want to check
  const colors = ['#00ff00']; // Watch out, it's case sensitive 
  // A reference to the attached Spreadsheet
  const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SheetName'); // Selecting the Worksheet we want to work with by name
  // Here we retrieve the color codes of the backgrounds of the range we want to check
  const range = ss.getDataRange().getBackgrounds(); // Here I select all the cells with data in them

Let's now loop through our range rows and columns to apply the logic:

The .getBackgrounds() method returns a multidimensional array in the form array[row][column] -> "background-color-code".

  for (let i = 0; i<range.length; i++) {
    let row = range[i];
    // Let's loop through the row now
    for (let j = 0; j< row.length; j++) {
      let color = row[j];
      // If the background color code is among the ones we are checking we set the cell value to "x"
      if(colors.includes(color)) {
        // Javascript index notation is 0 based, Spreadsheet one though, starts from 1
        ss.getRange(i+1, j+1).setValue("x"); // Let's add 1 to our indexes to reference the correct cell with the .getRange(row, column) function
      }
    }
  }

Reference

Please take a look at the documentation for further reading and method specifications

Google Apps Script Spreadsheet Service

Range Class

.getBackgrounds()

.getRange(row,column)

Change cell value based on its background color in google sheets

1 Answers

Solution

Reference