how can I create script that will bold target range, but only words that are in source range? Lest say I have target range in column A, source range B

Result of this script will be that it will bold like this:

Much thanks for answers.

CodePudding user response：

The only way to accomplish this sort of formatting in a spreadsheet is with the use of

SpreadsheetApp.newTextStyle

This function is modified to search for words in column A that are selected from words in column B

function boldMywords() {
  //const red = SpreadsheetApp.newTextStyle().setForegroundColor('red').build();
  //const org = SpreadsheetApp.newTextStyle().setForegroundColor('orange').build();
  //const blu = SpreadsheetApp.newTextStyle().setForegroundColor('blue').build();
  const bold = SpreadsheetApp.newTextStyle().setBold(true).build();
  //const cA = [red,org,blu];//colors array
  //const wordA = ['coffee','phone'];
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet0');
  const rg = sh.getRange(1,1,sh.getLastRow());
  const wrg = sh.getRange(1,2,getColumnHeight(2,sh,ss));
  const wordA = wrg.getDisplayValues().flat();
  let vs = rg.getDisplayValues();
  
  vs.forEach((r, i) => {
    r.forEach((c, j) => {
      let idxObj = { pA: [] };
      wordA.forEach(w => {
        let idx = c.indexOf(w);
        if (~idx) {
          idxObj[w] = idx;
          idxObj.pA.push(w);
        }
      });
      if (idxObj.pA.length > 0) {
        let cell = sh.getRange(i   1, j   1);
        let val = SpreadsheetApp.newRichTextValue().setText(c);
        idxObj.pA.forEach((p,k) => {
          //val.setTextStyle(idxObj[p], idxObj[p]   p.length, cA[k % cA.length]);
          val.setTextStyle(idxObj[p], idxObj[p]   p.length, bold);
        });
        cell.setRichTextValue(val.build());
      }
    });
  });
}

function getColumnHeight(col, sh, ss) {
  var ss = ss || SpreadsheetApp.getActive();
  var sh = sh || ss.getActiveSheet();
  var col = col || sh.getActiveCell().getColumn();
  var rcA = [];
  if (sh.getLastRow()){ rcA = sh.getRange(1, col, sh.getLastRow(), 1).getValues().flat().reverse(); }
  let s = 0;
  for (let i = 0; i < rcA.length; i  ) {
    if (rcA[i].toString().length == 0) {
      s  ;
    } else {
      break;
    }
  }
  return rcA.length - s;
  //const h = Utilities.formatString('col: %s len: %s', col, rcA.length - s);
  //Logger.log(h);
  //SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(h).setWidth(150).setHeight(100), 'Col Length')
}

This one will provide case free matching:

function boldMywords() {
  //const red = SpreadsheetApp.newTextStyle().setForegroundColor('red').build();
  //const org = SpreadsheetApp.newTextStyle().setForegroundColor('orange').build();
  //const blu = SpreadsheetApp.newTextStyle().setForegroundColor('blue').build();
  const bold = SpreadsheetApp.newTextStyle().setBold(true).build();
  //const cA = [red,org,blu];//colors array
  //const wordA = ['coffee','phone'];
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet0');
  const rg = sh.getRange(1,1,sh.getLastRow());
  const wrg = sh.getRange(1,2,getColumnHeight(2,sh,ss));
  const wordA = wrg.getDisplayValues().flat();
  let vs = rg.getDisplayValues();
  
  vs.forEach((r, i) => {
    r.forEach((c, j) => {
      let idxObj = { pA: [] };
      wordA.forEach(w => {
        let idx = c.toLowerCase().indexOf(w.toLowerCase());
        if (~idx) {
          idxObj[w] = idx;
          idxObj.pA.push(w);
        }
      });
      if (idxObj.pA.length > 0) {
        let cell = sh.getRange(i   1, j   1);
        let val = SpreadsheetApp.newRichTextValue().setText(c);
        idxObj.pA.forEach((p,k) => {
          //val.setTextStyle(idxObj[p], idxObj[p]   p.length, cA[k % cA.length]);
          val.setTextStyle(idxObj[p], idxObj[p]   p.length, bold);
        });
        cell.setRichTextValue(val.build());
      }
    });
  });
}

This last one get's the words to search for from a prompt:

function boldMyWords() {
  const red = SpreadsheetApp.newTextStyle().setForegroundColor('red').setBold(true).build();
  const bold = SpreadsheetApp.newTextStyle().setBold(true).build();
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const rg = sh.getDataRange();
  let vs = rg.getDisplayValues();
  let r = SpreadsheetApp.getUi().prompt('Enter words to search for separated by commas', SpreadsheetApp.getUi().ButtonSet.OK_CANCEL);
  if (r.getSelectedButton() == SpreadsheetApp.getUi().Button.OK) {
    let wordA = r.getResponseText().split(',');
    vs.forEach((r, i) => {
      r.forEach((c, j) => {
        let idxObj = { pA: [] };
        wordA.forEach(w => {
          let idx = c.toLowerCase().indexOf(w.toLowerCase());
          if (~idx) {
            idxObj[w] = idx;
            idxObj.pA.push(w);
          }
        });
        if (idxObj.pA.length > 0) {
          let cell = sh.getRange(i   1, j   1);
          let val = SpreadsheetApp.newRichTextValue().setText(c);
          idxObj.pA.forEach((p, k) => {
            val.setTextStyle(idxObj[p], idxObj[p]   p.length, red);
            val.setTextStyle(idxObj[p], idxObj[p]   p.length, bold);
          });
          cell.setRichTextValue(val.build());
        }
      });
    });
  }
}