0

I have a custom function in my sheet which returns the color code for the font color of an inputted cell. It has been working fine for a few months, but today, one out of 61 cells using the custom function return an error saying "Internal error executing custom formula". I do not understand why this problem would exist only in this cell (it is the 6th of 61 cells with the function) nor how to fix it. The custom function follows here:

function FontColor(input) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var cell = sheet.getRange(input);
var testColor = cell.getFontColor();
return testColor
}

(Apologies if I formatted the code wrong) This is a relatively simple code and there have been no problems for months. Today, I added 7 new rows where this custom function was being used in one cell per row, and the error appeared back up near the top in row 6. The formula is being used as such: In cell Q3, the formula is =FontColor(W3), and in cell W3 is simply H3. H3 contains a dollar amount with a specific font color assigned. This was necessary for the custom formula as it required an input of H3 rather than H3... If that makes sense. This is just a reference sheet I use to view payroll in my business, so I'm happy to reorganize it or modify any of these formulas/functions as necessary to make this work. Thanks in advance for your assistance!

TheMaster
  • 45,448
  • 6
  • 62
  • 85
RoK Gi Yeon
  • 1
  • Hard to say what the problem is without having a look at your sheet. – ziganotschka Sep 09 '19 at 15:44
  • @ziganotschka - I'm not really comfortable sharing as is, because it has my employees' info. If I just create a copy, the issue surely won't be there since this worked fine for so long. Just hoping for ideas on why this problem would exist in one random cell only. For example, I copied the formula exactly into a neighboring cell, and there is no error. It is uniquely random to this one cell (V6 as it happens, though I doubt that can make a difference). – RoK Gi Yeon Sep 09 '19 at 18:01
  • You can create a dummy sheet with fake contents (like 1,2,3,4,5...), just to understand the structure of your sheet with all the formulas and dependencies and see what is different in cell 6 compared to the other ones – ziganotschka Sep 09 '19 at 18:13
  • 1
    Have you tried refreshing the sheet? You must use arrays to [optimize](https://developers.google.com/apps-script/guides/sheets/functions#optimization) your function if you're hoping to use it in more than 30 cells – TheMaster Sep 09 '19 at 23:08

1 Answers1

0

Thanks, the array fixed it. In case anyone runs into a similar problem, I updated the code as such:

function FontColor(input) {
 if (input.map) {
 return input.map(FontColor);
 } else{
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];
 var cell = sheet.getRange(input);
 var testColor = cell.getFontColor(); 
 return testColor
 }
 }

Which I'm now using in batches of 100 down the column (I will need to use the function over 500 times, which is too much as well, but using =FontColor(W3:W100) works quickly and error free).

Thanks!

RoK Gi Yeon
  • 1
  • Still not enough. Your script will be able to run 12k rows without glitch in a single formula call. See [this](https://stackoverflow.com/a/57836700/)(read `getValue` as `get*` or `getFontColor`) – TheMaster Sep 10 '19 at 08:12
  • Thanks for the tip, but it still crashed at about 500 cells. I'm not sure what is causing the issue, but the above code is working as a workaround that should work indefinitely. – RoK Gi Yeon Sep 11 '19 at 07:10
  • if you want to know how/ why it crashed, you can ask a new question with the new code [mre] – TheMaster Sep 11 '19 at 08:31