I would like to make the formula of RICHTEXT_TO_HTML dynamic, meaning that when i autofill a whole row it should go from A2 to A3 to A4, ....

1. I use the formula as RICHTEXT_TO_HTML(A1) ; the cell changes so it is dynamic, the formula doesnt work.
2. I use the formula as RICHTEXT_TO_HTML('A1'); the cell is not changing dynamically when going down, formula is working.

Problem is occurring when using the following script;

function RICHTEXT_TO_HTML(qRange) {
  var indexBool = false;
  var indexItalic = false;
  var indexUnderline = false;
  var indexStrikethrough = false;

  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var range = sheet.getRange(qRange);
  var cell = range;
  var cellValue = cell.getRichTextValue();
  var txt = String(cell.getDisplayValue());
  var styles = cell.getTextStyles();
  var result = '';

  for (var i = 0; i < txt.length; i  ) {
    var style = cellValue.getTextStyle(i, i   1);
    if (!indexStrikethrough && style.isStrikethrough()) {
      indexStrikethrough = true;
      result  = '<strike>';
    } else if (indexStrikethrough && !style.isStrikethrough()) {
      indexStrikethrough = false;
      result  = '</strike>';
    }

    if (!indexUnderline && style.isUnderline()) {
      indexUnderline = true;
      result  = '<u>';
    } else if (indexUnderline && !style.isUnderline()) {
      indexUnderline = false;
      result  = '</u>';
    }

    if (!indexBool && style.isBold()) {
      indexBool = true;
      result  = '<strong>';
    } else if (indexBool && !style.isBold()) {
      indexBool = false;
      result  = '</strong>';
    }

    if (!indexItalic && style.isItalic()) {
      indexItalic = true;
      result  = '<i>';
    } else if (indexItalic && !style.isItalic()) {
      indexItalic = false;
      result  = '</i>';
    }

    result  = txt[i];
  }

  if (indexStrikethrough) {
    result  = '</strike>';
  }

  if (indexUnderline) {
    result  = '</u>';
  }

  if (indexBool) {
    result  = '</strong>';
  }

  if (indexItalic) {
    result  = '</i>';
  }

  return result;
}

Anyone has an idea how to get around this so the cellrange changes dynamically like with any other regular formula if you dont use a '$'? Thank you very much in advance!

CodePudding user response：

Thanks a lot! I also found that =RICHTEXT_TO_HTML(ADDRESS(ROW(A1);COLUMN(A1);4)) also works, i don't think there is a lot of workload difference between the 2 of them but will keep both in mind in case 1 gives more problems than the other one.

Once again thank you :) This problem is solved

CodePudding user response：

The problem is that if you use RICHTEXT_TO_HTML(A1), qRange won't be 'A1' but instead the value in that cell, rich text. I propose rewriting formula like this:

Call: (in sheet)

=RICHTEXT_TO_HTML(row(A1),column(a1))

Formula

function RICHTEXT_TO_HTML(row, column) {
  ...
  var range = sheet.getRange(row, column);
  ...

In this case formula will be updated correctly upon copy, and will work the same

CodePudding user response：

@Roma is correct. The parameter A1 passed to the function is not the range but the value of the range. A solution is to get the formula and extract the text between the parenthesis. This example assumes there is only one parameter passed to the function.

Since the parameter is in fact a range instead of a string it will be updated if the formula is copied to other cells.

=TestFormula(G99) will return G99

function TestFormula(dummy) {
  let formula = SpreadsheetApp.getActiveRange().getFormula();
  let range = formula.match(/\((.*)\)/);
  return range[1];
}