1

I'm creating color palettes with Google Sheets as a basis for a color reference book: You can see a sheetproof here. I wanted to batch fill the cells' background in Column C according to the HEX/RGB Codes. Manipulating a script I found elsewhere, I got this:

function Colors_in_RGB_from_HEX()  {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
 
  for (var i = 1; i <= lastRow; i++) {
    var valRgb = sheet.getRange(i, 2).getValue();
    sheet.getRange(i, 3).setBackgroundRGB(
      parseInt(valRgb.substr(1, 2), 16),
      parseInt(valRgb.substr(3, 2), 16),
      parseInt(valRgb.substr(5, 2), 16)
    );
  }
}

The script works but only fills 1,096 cells out of 65,536, due to exceeded maximum execution time error (6 minutes). I asked for help on Reddit, 'cause Idk anything about Apps Script: I limited to copying, correcting and doing various tests with the code above (I'm more familiar with Excel, but it has a limit of colors-per-sheet that strangles me).

They answered me that

the trick, unlike Excel, is that I should never read one cell at a time: instead I should call getRange once on the whole range, then call getValues and obtain an array with the colors, finally use the setBakgrounds to set the backgrounds passing the array of colors obtained as parameters

I've been studying the answer (a brain teaser for me) for about a month, but I've to surrender in front of the evidence that I need someone to write the Apps Script for me.

1
  • Welcome to Web Applications. I'm sorry if we have given you the wrong idea about this community. Questions centered on programming problems are a better fit for Stack Overflow. Let me explain this. While many posts have scripts, this is not a place to ask others to write scripts or ask to teach you about programming. However, you might make this question a good fit for this site if you tell us about similar questions posted here, like webapps.stackexchange.com/q/59683/88163, and why they don't meet your needs, while you keep the question focused on using the web apps rather than on programming. Jul 29, 2023 at 16:24

1 Answer 1

0

Use batching to reduce the number of API calls. In this instance, you can use Range.getValues(), Range.setBackgrounds() and Array.map(), like this:

function setFillColors() {
  const range = SpreadsheetApp.getActiveSheet().getRange('B3:B');
  const fillColors = range.getValues()
    .map(row => row.map(color => color || null));
  range.offset(0, 1).setBackgrounds(fillColors);
}

See Range.getValues, Range.setBackgrounds() and Array.map().

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.