Custom Function to Change the Font Size of a Specified Cell-CodePudding

I am trying to create a custom function with Google apps script which achieves the following goal:

To do this I have created the following Apps Script function:

function changeFontSize(cell,size) {
  var spreadsheet = SpreadsheetApp.getActive().getSheetByName("Sheet1");
  var range = spreadsheet.getRange(cell)
  range.setFontSize(size);
}

And combined it with the following formula:

=IF(B2="Heading",changeFontSize(B3,18),changeFontSize(B3,12))

This returns the following error:

Exception: Range not found (line 3).

Why is this occuring and how can I adapt my function to correctly achieve my desired result?

CodePudding user response：

Modification points:

I think that the reason of your issue of Exception: Range not found is due to B3 of =IF(B2="Heading",changeFontSize(B3,18),changeFontSize(B3,12)). In this case, the value of cell "B3" is given. If the value of cell "B3" is the A1Notation, no error might occur. But, from your current error message, I thought that the value might not be the A1Notation.
And also, even when B3 of =IF(B2="Heading",changeFontSize(B3,18),changeFontSize(B3,12)) is the A1Notation, I think that an error occurs. Because in the current stage, setFontSize cannot be used with the custom function. Unfortunately, it seems that this is the current specification.

From the above situation, in order to achieve your goal, how about using a simple trigger of OnEdit? The sample script is as follows.

Sample script:

function onEdit(e) {
  const sheetName = "Sheet1"; // Please set the sheet name.
  const fontSize1 = 18; // Please set the font size.
  const fontSize2 = 12; // Please set the font size.

  const { range } = e;
  const sheet = range.getSheet();
  if (sheet.getSheetName() != sheetName || range.getA1Notation() != "B2") return;
  range.offset(1, 0).setFontSize(range.getValue() == "Heading" ? fontSize1 : fontSize2);
}

When you use this script, please set the value of Heading to the cell "B2". By this, the font size of the cell "B3" is changed to 18. And, when the cell "B2" is not Heading, the font size is changed to 12.

Note:

This is a simple sample script. So, please modify this for your actual situation.

Reference:

Simple Triggers