Is it possible to insert a custom formula in conditional format dynamically by searching for a column name?

Question

I'm attempting to build a function in google script that would allow me to code in certain column names (as these will remain constant), and to have said function find that column by the name, and create/insert a custom formula via conditional formatting to highlight duplicates in that column. The formula to find duplicates is =COUNTIF(E:E,E1)>1 (using column E as an example).

So to re-state, what I'm trying to do is:

1. Have a function that inserts a new conditional format rule to highlight duplicates in a row
2. Have the formula for the new conditional format be dynamic in case the column moves between data sets (although the column name will be the same)
3. Have this function insert the conditional format into a column by name, instead of the number

I've tried to find something similar to this on stackoverflow but not finding much luck, just a few posts that are:

Custom Formula in Condition Format Rule Builder Script

Get column values by column name not column index

So this sheet would find the column "WOW":

Desired outcome/Would look like this after the function has ran (Highlight E2 and E2 in the WOW column):

So my attempt at making this code is below, however I'm hoping someone has a better idea as it's not quite working and this seems to be advanced coding (and I'm a newbie)

function conditionalformat1(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheet1');//need to call the tab name as I have many tabs
  var colName = 'WOW' //specific column name to have this function search for
  var sheetName = 'sheet1'

  var newRule = SpreadsheetApp.newConditionalFormatRule()
                    .whenFormulaSatisfied('=COUNTIF(E:E,E1)>1')//this should be dynamic instead what it is now...not sure if we can use R1C1 notation?
                    .setBackground('red')
                    .setRanges(getColByName)
                    .build()//similar setup to (https://stackoverflow.com/questions/50911538/custom-formula-in-condition-format-rule-builder-script) @Patrick Hanover & https://developers.google.com/apps-script/reference/spreadsheet/conditional-format-rule-builder#whenFormulaSatisfied(String)

  var rules = sheet.getConditionalFormatRules();
  rules.push(conditionalformat1);
  sheet.setConditionalForatRules(rules);

function getColByName(colName) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheet1');
  var colName = 'WOW'
  var data = sheet.getRange("1:1000").getValues();//have it search the entire sheet?
  var col = data[0].indexOf(colName);
  if (col != -1) {
    return data[row-1][col];
  }
}// via the question & author https://stackoverflow.com/questions/36346918/get-column-values-by-column-name-not-column-index @Leonardo Pina 



}//end of conditionalformat1 conditional formatting

Thanks for the help in advance, this will be great to learn how to have functions find columns by name and execute items.

Answer 1

You do not need any code, just use the conditional formatting formula

=AND(a$1="WOW";countif(A$2:A;a2)>1)

Answer 2

• Get column number using your current function getColumnByName
• Get column letter from the column number
• Create a range string from column number
• Use the range string in your custom formula and the range variable in conditional formatting

function conditionalformat0() {
  const sheetName = 'sheet1';
  const colName = 'WOW'; //specific column name to have this function search for
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName); //need to call the tab name as I have many tabs
  const c2l = (n) =>
    /*Column to Letter: 0 => A*/
    n >= 26
      ? String.fromCharCode(64 + n / 26) + c2l(n % 26)
      : String.fromCharCode(65 + n);
  const getColByName = (colName, sheet) => {
    const data = sheet.getRange(`1:${sheet.getLastColumn()}`).getValues();
    const col = data[0].indexOf(colName);
    if (col !== -1) return col;
    throw new Error('Column not found');
  };
  const colLetr = c2l(getColByName(colName, sheet));
  const rangeA1 = `${colLetr}:${colLetr}`;
  const newRule = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied(`=COUNTIF(${rangeA1},${colLetr}1)>1`)
    .setBackground('red')
    .setRanges([sheet.getRange(rangeA1)])
    .build();
  const rules = sheet.getConditionalFormatRules();
  rules.push(newRule);
  sheet.setConditionalFormatRules(rules);
}

Answer 3

I've fixed your code a bit. It should work to some extent. Try it:

function conditionalformat() {
  var sheetName = 'Sheet1';
  var sheet     = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var colName   = 'WOW';
  var range     = sheet.getRange(1,getColByName(colName,sheet),sheet.getLastRow());
  var formula   = '=COUNTIF(E:E,E1)>1';

  var newRule = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied(formula)
    .setBackground('red')
    .setRanges([range])
    .build();

  var rules = sheet.getConditionalFormatRules();
  rules.push(newRule);
  sheet.setConditionalFormatRules(rules);

}

function getColByName(colName,sheet) {
  var data = sheet.getDataRange().getValues();
  var col  = data[0].indexOf(colName);
  if (col != -1) return col+1;
}

But I don't understand what do you want to do with the formula =COUNTIF(E:E,E1)>1. Should it changing somehow? How?

And just in case, here is the pure JS function (based on Amit Agarwal's solution) to get column letter(s) from a number:

function get_col_letter(column) {
  var col_letter = '';
  let block = column;
  while (block >= 0) {
    col_letter = String.fromCharCode((block % 26) + 65) + col_letter;
    block = Math.floor(block / 26) - 1;
  }
  return col_letter;
};

console.log(get_col_letter(0));  // 'A'
console.log(get_col_letter(1));  // 'B'
console.log(get_col_letter(25)); // 'Z'
console.log(get_col_letter(26)); // 'AA'

Update

I've added the option to change letters in formula by given column:

function conditionalformat() {
  var sheetName = 'Sheet1';
  var sheet     = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var colName   = 'WOW';
  var colNum    = getColNum(colName,sheet);
  var colLetter = getColLetter(colNum);
  var range     = sheet.getRange(`${colLetter}1:${colLetter}`);
  var formula   = `=COUNTIF(${colLetter}:${colLetter},${colLetter}1)>1`;

  var newRule = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied(formula)
    .setBackground('red')
    .setRanges([range])
    .build();

  // sheet.clearConditionalFormatRules(); // remove old formatting
  var rules = sheet.getConditionalFormatRules();
  rules.push(newRule);
  sheet.setConditionalFormatRules(rules);

}

// get number column from its name
function getColNum(colName,sheet) {
  var data = sheet.getDataRange().getValues();
  var col  = data[0].indexOf(colName);
  if (col != -1) return col+1;
}

// get letter column from its number
function getColLetter(column) {
  var col_letter = '';
  let block = column - 1;
  while (block >= 0) {
    col_letter = String.fromCharCode((block % 26) + 65) + col_letter;
    block = Math.floor(block / 26) - 1;
  }
  return col_letter;
};

Answer 4

/*
If you want to find columns by name the best thing to do is to build an object that does it for you so that you don't have to call and additional function every time you need the column name. 

First of all you have to know the row that the column names are on.  I call this the header row
*/

function getColumnHeaders() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('SheetName');
  const hr = 1;//header row
  const hA = sh.getRange(hr, 1, 1, sh.getLastColumn()).getValues().flat();
  const col = {};//the object that converts column names to numbers
  hA.forEach((h, i) => col[h] = i + 1);

  /* this gives you column numbers however often what you really want are the column indices so that you can use them to access data in rows of the 2 dimensional arrays like the ones returned by getValues() function in this case you would do it the following way
  */
  const idx = {}; //the object that does the conversion for you from column names to indices

  hA.forEach((h, i) => idx[h] = i);

  /* if you want both then you can do it this way */

  hA.forEach((h, i) => col[j] = i + 1; idx[h] = i;);

  /*  Now if you wish to access data from a spreadsheet with a header row by using header names instead of indexes you can */

  const data = sh.getRange(startRow, startCol, sh.getLastRow() - startRow + 1, sh.getLastColumn() - sc + 1).getValues();

  data.forEach(r =>
    r[idx["column name"]];//this accesses the value in the current row for the column name that you enter and it does not require you to do an additional function call to do it because it's already in the object and most like the object is located in the cpu's cache so this is much faster
  )



}

I don't consider this to be a complete answer to the question I just wanted to point out a way to do this that doesn't require function calls because unnecessary function calls inside of your data loops will greatly decrease performance.