Google Sheets: Range Not Found

Question

I am new with Google Sheets scripts and I can't seem to figure why I am getting this error: Range not found (line 4, file "Code"). I have followed everything from https://developers.google.com/apps-script/guides/sheets/functions#creating_a_custom_function

Here is my code:

function getBgColor(input) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var range = sheet.getRange(input);
  return range.getBackground();
}

I appreciate all the assistance.

JPV JPV · Accepted Answer · 2015-03-20T08:13:17

Your code should be fine if your input range is only one cell, at least if you pass it in as a string. =getBgColor("D1"). However, if you want to pass in a range, I think you will have to loop to that range in order to get the colors of the whole range. So you'll need something like this (untested):

function getBgColor(input) {
var bgs = SpreadsheetApp.getActiveSheet().getRange(input).getBackgrounds(),
colors = [];
for(var i = 0; i < bgs.length; i++){
colors.push(bgs[i]);
}
return colors;
}

That should give you a range with all the backgroundcolors of the range that you passed it. If you would want those colors in one cell, use: return colors.join(", "). If you want to return only unique colors, there are also ways to do that..

See if this helps first ?

Google Sheets: Range Not Found

1 Answers