Modifying the color of multiple cells simultaneously

Question

Modifying the color of multiple cells simultaneously

I'm currently facing an issue where I need to change the cell color of multiple cells at once, all stored within a specific range. However, I only want certain cells to change based on a particular condition. While I can manually change each cell, it's time-consuming and inefficient. I am thinking that if I can construct a range of cells, I could use the setBackground() command for better efficiency.

Therefore, I'm seeking advice on the most effective way to tackle this problem?

The approach I am considering involves:

Utilizing a range.
Recognizing that ranges contain a "block" of cells, including some that may not require color changes.
Exploring the possibility of removing unwanted cells from a range. If achievable, I could iterate through and exclude those unnecessary cells before applying changes to the rest.
Potentially storing cells in an array and creating a range from that. However, this idea is hindered by my understanding that ranges typically consist of a rectangular block with two corner cells and contents in between.
Investigating alternative constructs that can handle multiple cells simultaneously, akin to a range’s capabilities.

Here is my current loop logic:

  for(var i=1; i<=numRows; i++){
    for(var j=1; j<=numColumns; j++){
      var mCell = monthRange.getCell(i, j);
      var mValue = mCell.getValue();
      if(mValue != "")
      {
        var eCell = exerciseRange.getCell(1, mValue);
        var eValue = eCell.getValue();
      }
      
      if (mValue != "" && eValue == true) {
        mCell.setBackground('green');
      } 
    }
  }

I would greatly appreciate any assistance as I navigate this challenge, especially since I am relatively new to this concept.

Thanks! TM

javascript google-sheets google-apps-script

Answer 1

Answer №1

Optimize your code by fetching all the ranges, values, and backgrounds at once and storing them as arrays. Then iterate through the arrays to make comparisons and store the backgrounds in a separate array. Set the backgrounds only once after completing all comparisons:

function optimizeFunction() {
  var sheet = SpreadsheetApp.getActive();
  var monthRange = sheet.getRange("A1:C50000");
  var monthValues = monthRange.getValues(); // retrieve range values as an array

  var exerciseRange = sheet.getRange("D1:K1");
  var exerciseValues = exerciseRange.getValues(); //fetch exercise range values as an array

  var numColumns = monthRange.getNumColumns();
  var numRows = monthRange.getNumRows();
  var backgrounds = monthRange.getBackgrounds(); // fetch backgrounds as an array

  for (var i = 0; i < numRows; i++) {
    for (var j = 0; j < numColumns; j++) {
      var mValue = monthValues[i][j]; // access value from values array
      if (mValue != "") {
        var eValue = exerciseValues[0][mValue-1]; // access corresponding exercise value
        if (eValue == true) {
          backgrounds[i][j] = "green"; // update background color in array
        }
      }
    }
  }

  monthRange.setBackgrounds(backgrounds); // set all backgrounds at once

}

Try this optimized version which processed 150,000 cells (50,000 rows * 3 columns) in just 15 seconds!

https://i.sstatic.net/9K87L.png

Answer 2

Optimize your code by fetching all the ranges, values, and backgrounds at once and storing them as arrays. Then iterate through the arrays to make comparisons and store the backgrounds in a separate array. Set the backgrounds only once after completing all comparisons:

function optimizeFunction() {
  var sheet = SpreadsheetApp.getActive();
  var monthRange = sheet.getRange("A1:C50000");
  var monthValues = monthRange.getValues(); // retrieve range values as an array

  var exerciseRange = sheet.getRange("D1:K1");
  var exerciseValues = exerciseRange.getValues(); //fetch exercise range values as an array

  var numColumns = monthRange.getNumColumns();
  var numRows = monthRange.getNumRows();
  var backgrounds = monthRange.getBackgrounds(); // fetch backgrounds as an array

  for (var i = 0; i < numRows; i++) {
    for (var j = 0; j < numColumns; j++) {
      var mValue = monthValues[i][j]; // access value from values array
      if (mValue != "") {
        var eValue = exerciseValues[0][mValue-1]; // access corresponding exercise value
        if (eValue == true) {
          backgrounds[i][j] = "green"; // update background color in array
        }
      }
    }
  }

  monthRange.setBackgrounds(backgrounds); // set all backgrounds at once

}

Try this optimized version which processed 150,000 cells (50,000 rows * 3 columns) in just 15 seconds!

https://i.sstatic.net/9K87L.png

Modifying the color of multiple cells simultaneously

Answer №1

Similar questions

Transfer information from .gs (Google Apps Script) to a variable inside a <script> tag in an HTML document

The initial JavaScript load shared by all users is quite large in the next.js framework

The error message "MVC JS deletethisproduct is not defined at HTMLAnchorElement.onclick (VM457 Index:40)" indicates that there

issues with jasmine angularjs tests exhibiting erratic outcomes

Searching for particular information within an array of objects

Experiencing issues with autoungrabify or autolock in cytoscape.js?

Managing file sizes on the client side prior to transferring them to the server side

utilize console.log within the <ErrorMessage> element

increasing the size of a picture without resorting to a pop-up window

Running res.render in an asynchronous manner within an express application

Navigating Time Constraints and Error Management in the World of Facebook Graph API

Keep track of open tabs and their content by utilizing sessions

Assigning a value to a variable can prevent the occurrence of an

Tacking the progress bar onto an array of $.ajax() calls within a Promise.all

What is the best way to enlarge text size with jquery?

Tips for executing getJSON requests one after the other

What is the best way to implement a dropdown menu with JavaScript or jQuery?

"Update your Chart.js to version 3.7.1 to eliminate the vertical scale displaying values on the left

What could be causing the issue with the JS function that is preventing the button from

The HTML document requests information from a JSP file