CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Kieran
Kieran

Reputation: 324

Apps Script: Construct range of rows from array of row numbers

I have a list of row numbers in a spreadsheet which I need to change the background colour of. As the spreadsheet is quite large (10+ sheets, each with almost 5000 rows), I am trying to construct a range so I can batch set the background, as doing each row individually was taking over the max time of 6 minutes.

Here's the code I have:

// highlight required rows
var first = -1, last = -1;
for(var j = 0; j < rowNumsToHighlight.length; j++) {
  if(first == -1) {
    first = rowNumsToHighlight[j];
    continue;
  }

  // if the current row number is one more than the previous, update last to be the current row number
  if(rowNumsToHighlight[j] - 1 == rowNumsToHighlight[j - 1]) {
    last = rowNumsToHighlight[j];
    continue;
  }
  // otherwise the last row should be the previous one
  else {
    last = rowNumsToHighlight[j - 1];
  }

  var numRows = (last - first) + 1;
  var range = sheet.getRange(first, 1, numRows, 4);
  if(range.getBackground().toUpperCase() != highlightColour.toUpperCase()) {
    range.setBackground(highlightColour);
  }

  first = -1; 
  last = -1;
}

rowNumsToHighlight is just an array that looks like: [205,270,271,272,278,279]. So, with that as an example, setBackground should be ran on row 205, on rows 270-272, and on 278-279.

I'm fairly sure the solution is simple, but just can't see it. Thanks for any help.

==== Updated Code ====

Based on Serge's code below, I made it more efficient again by reducing the number of getRange() calls made. Time is down from 78 to 54 seconds.

function updateColours(sheet, array, colour){
  var columns = sheet.getLastColumn();
  var rows = sheet.getLastRow();
  var range = sheet.getRange(1, 1, rows, columns);

  Logger.log("Resetting highlight on all rows...");
  range.setBackground(null);

  var backgrounds = range.getBackgrounds();
  for(var n = 0; n < backgrounds.length; n++){
    var rowIdx = n + 1;
    if(array.indexOf(rowIdx) > -1){
      for(var c = 0; c < columns; c++){
        backgrounds[n][c] = colour;
      }
    }
  }
  Logger.log("Highlighting non-translated rows...");
  range.setBackgrounds(backgrounds);
}

Upvotes: 2

Views: 1450

Answers (2)

Serge insas
Serge insas

Reputation: 46794

Maybe this one is faster(?) and built in a way that will make your work easier (function with arguments).

It writes only once to the sheet (or 2 if you clear colors before writing)...

use like below :

function testBG(){
  updateColors(0,[7,8,9,18,19,23]);
}

function updateColors(sheetNum,array){
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheets()[sheetNum];
  var columns = sh.getMaxColumns();
  var range = sh.getRange(1,1,sh.getMaxRows(),columns);
  sh.getRange(1,1,sh.getMaxRows(),columns).setBackground(null);// use this if you want to clear all colors before setting them
  var backGrounds = range.getBackgrounds();// get all cells BG
  for(var n=0;n<backGrounds.length;n++){
    var rowIdx = n+1;
    if(array.indexOf(rowIdx)>-1){
      for(c=0;c<columns;c++){
        backGrounds[n][c]="#F00";// if row number is in the array fill in red
      }
    }
  }
  sh.getRange(1,1,sh.getMaxRows(),columns).setBackgrounds(backGrounds);//update sheet in one call
}

test sheet in view only, make a copy to test.

Upvotes: 3

Alan Wells
Alan Wells

Reputation: 31300

This is how I would do it:

function createRanges() {
  var rowNumsToHighlight = [5,7,8,9,18,19];
  var arrayLength = rowNumsToHighlight.length;
  var loopCounter = 0, thisNumberInArray=0, nextNumberInArray=0, crrentNmbrPlusOne=0;
  var currentRangeBegin=0, numberOfRowsInRange=1;

  currentRangeBegin = rowNumsToHighlight[0];

  for(loopCounter=0; loopCounter < arrayLength; loopCounter+=1) {
    thisNumberInArray = rowNumsToHighlight[loopCounter];
    nextNumberInArray = rowNumsToHighlight[loopCounter+1];
    crrentNmbrPlusOne = thisNumberInArray+1;

    if (nextNumberInArray===undefined) {
      workOnTheRange(currentRangeBegin, numberOfRowsInRange);
      return;
    };

    if (nextNumberInArray!==crrentNmbrPlusOne) {
      workOnTheRange(currentRangeBegin, numberOfRowsInRange);
      numberOfRowsInRange = 1; //Reset to 1
      currentRangeBegin = nextNumberInArray;
    } else {
      numberOfRowsInRange+=1;
    };
  };

};

function workOnTheRange(first,numRows) {
  var range = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet11').getRange(first, 1, numRows, 4);
  range.setBackground("red");
};

I've tested the code and it works.

Upvotes: 1

Related Questions