This is a script that I’m workshopping to change the colors of any given data range’s links. It’s meant to re-analyze on open so that there aren’t any “slips” of unstyled links where someone can see.

For now, I’m working on a version that styles all the text based on specified ranges. The first row has some cells that’s merged, but afaik, that’s not a problem, since it works on the next row that also has some merged cells.

I’ll try to describe some things that may or may not contribute to the problem, so looking at the spreadsheet might not be necessary, though I do have a copy of the current version available here. You’re gonna have to approve importing some ranges if you make copies, but otherwise you should have no issues (“jump to” links, however, will be broken.)

Here is also the code, in case looking at it’s all you need. Code also spoilered below (since, apparently, I need it.)

Guesses on what might be problematic:

• The first row is meant to have white text
• The links in the first 2 rows are meant to override the “normal” color
• The original array was sliced and concatenated to the array that sets the text color. Without those 2 arrays (and other code that deletes the first 2 rows of the original text color array), the text color would just be normal text black and link blue.
• The top colors array is in a separate loop than the initial text color setting loop. The top colors array, afaik, still should work because 1. 2. of top colors is setting the color perfectly.
• There was mixed usage of rich text value and font Color. RTF “found” the links, font color changed the… font’s color.
• The raw data from getting the data range is what was edited. I didn’t use display value. That, I plan to use for a variation of this script.

Tried:

As you can see from the code, a lot of logs to the console were made. I tried workshopping as much as possible. I tried seeing if the length of the arrays were okay, and made sure the dimensions lined up. That seems fine. Each subarray is 15 elements long. I made sure the first and second lines (the “problem areas”) both have the same dimensions and colors.

I was having issues doing some things, like making a true/false array for the links and making a loop to check all the text. I did those in roundabout ways just so I could “read” what was going on and not get confused by someone else’s code. I made 2 separate loops to make “if/else” blocks that were “legible” to me.

A lot of the old variations of code were “lost” because I didn’t record them in the draft, to make it legible. But, for things like the loops and the “if/else” blocks had several drafts before I ended up with something that worked.

Code:

Looks like you just get your page stretched.

/* @OnlyCurrentDoc */
/**
* A function to style links. WIP.
**/
function styleLinks(arg1="Neopets Blueprints Wishlist",arg2="#ffffff",arg3="#000000",arg4="#ffffff",arg5="#0b5394") {
var sheet = SS.getSheetByName("Neopets Blueprint Wishlist" || arg1);
var data1 = sheet.getDataRange();
var dataWord1 = data1.getDisplayValues();
var linkColor = '#3c78d8' || arg2;
//textColor1 is the "normal", general text color.
var textColor1 = '#000000' || arg3;
//textColor2 is the first row color.
var textColor2 = '#ffffff' || arg4;
//textColor3 is the second row color.
var textColor3 = '#0b5394' || arg5;
var data1LC = sheet.getLastColumn();
var data1LR = sheet.getLastRow();
var links1 = [];

        var inherit1 = data1.getRichTextValue();
        var inheritStyle = inherit1.getTextStyle();
        var inheritColor = inheritStyle.getForegroundColorObject();



var textColors = [];
//console.log(dataWord1.length);
//console.log(data1LC);
//console.log(data1LR);
//console.log(dataWord1[1]);
//console.log(dataWord1)
//console.log(dataWords1.length);
//console.log(dataWord1[2]);

//Below makes array of the Display Values.
var dataWords1a = dataWord1.join().split(',');
//Below makes an array of words from the previous array (Currently unused, meant for another draft of the code)
//var dataWords1b = dataWords1a.join().split(" ");

//dataWord1 is what the individual cells show (on each row), the outer array broken up by columns. 
//console.log(dataWord1);
//console.log(dataWord1[1]);

var dataRTVLinks = data1.getRichTextValues().map(r => r.map(c => {
    const link = c.getLinkUrl();
    return link;
}));

function linkBool(item) {
  if(item === null) { return false } else { return true };
}

//console.log(dataRTVLinks[1]);
//console.log(dataRTVLinks[5]);


let dataLinksBool = dataRTVLinks.map(r => r.map(linkBool));
var links2Rows = dataLinksBool.slice(0,2);
//console.log(links2Rows);
//console.log(dataLinksBool[1]);
//console.log(dataWord1[1]);

for (let i = 0; i < dataLinksBool.length; i++) {
textColors[i] = [];
for (let j = 0; j < data1LC; j++)
{if(dataLinksBool[i][j] == true){
  textColors[i][j]= linkColor} else {textColors[i][j] = textColor1;}
  }
}

//console.log(textColors.length);
//console.log(textColors[0].length);
//console.log(textColors[2].length);
//console.log(textColors[15].length);

var topColors = [];
for (let k=0; k < links2Rows.length; k++) {
topColors[k] = [];
for (let l = 0; l < data1LC; l++) {
  if(links2Rows[k][l] == true) {
    topColors[k][l] = linkColor} 
    else if (links2Rows[k][l] == false && k < 1) {
      topColors[k][l] = textColor2 } else 
      {topColors[k][l] = textColor3 }
}

}
console.log(topColors);
console.log(topColors[0].length);

topColors.flat();

let topArr1 = topColors.splice(0, 1).flat();
let topArr2 = topColors.splice(0, 1).flat();

//console.log("topArr1:");
//console.log(topArr1);
//console.log("topArr2:");
//console.log(topArr2);

textColors.splice(0,2,topArr1,topArr2);

//console.log(textColors[1]);

//data1.setFontColors(textColors);

}

//console.log(dataLinksBool[1]);
//console.log(dataLinksBool[5]);