CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
jackratt
jackratt

Reputation: 13

Changing the font size of a specified cell

Problem

I have a spreadsheet with a number of headings in a column B which I am attempting to change the font size of.

If a cell in column B contains the text "Heading" I would like the cell directly below it to increase to size 18 font.

All other text in column B without "Heading" in the cell directly above should be size 12, including the cell containing the word "Heading".

For example - if cell B5 contains "Heading" then cell B6 should be size 18 font. The rest of column B should remain as size 12 font including cell B5. If cell B5 no longer contains "Heading" then cell B6 should revert to size 12 font.

Current Progress

With the help of user Tanaike on this post I now have the below script. This script will find any cell within column B that contains "Heading" and increase the font size of the cell directly below it to size 18 font. It will not however revert the cell back to size 12 font when "Heading" is removed from the cell directly above and is now empty or contains some other text.

Question

How can this script be expanded upon to not only increase the font size of a cell to 18 when "Heading" is present in the cell directly above but also reduce the font size back to 12 when "Heading" is no longer present in the cell directly above?

function onEdit() {
  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 column = 2; // Please set the column number.
  const headerTitle = "Heading";

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const range = sheet.getRange(1, column, sheet.getLastRow());
  const { s1, s2 } = range.createTextFinder(headerTitle).matchEntireCell(true).findAll().reduce((o, r) => {
    o.s1.push(r.offset(1, 0).getA1Notation());
    o.s2.push(r.getA1Notation());
    return o;
  }, { s1: [], s2: [] });
  if (s2.length == 0) return;
  [[s1, fontSize1], [s2, fontSize2]].forEach(([r, s]) => sheet.getRangeList(r).setFontSize(s));
}

Upvotes: 1

Views: 834

Answers (1)

Yuri Khristich
Yuri Khristich

Reputation: 14537

You can try to implement this functionality from the code bellow:

function onEdit(e) {
  const fontSize1 = 18;
  const fontSize2 = 12;
  const headerTitle = "Heading";

  const value = e.value.toString();
  const oldValue = e.oldValue.toString();

  if (!oldValue.match(headerTitle) && value.match(headerTitle)) {
    e.source.toast(e.range.getA1Notation() + ' --> font size: ' + fontSize1);
    e.range.offset(1, 0).setFontSize(fontSize1);
    return;
  }

  if (oldValue.match(headerTitle) && !value.match(headerTitle)) {
    e.source.toast(e.range.getA1Notation() + ' --> font size: ' + fontSize2);
    e.range.offset(1, 0).setFontSize(fontSize2)
    return;
  }
}

But it has a big flaw. It works only when you're editing the cells manually. It wont work when you're pasting a text into a cell via Cmd+V or changing several cells at once. It doesn't even work when you're removing a cell content with Delete button. Etc

Update

Here is the simple static function that sets font size 18 for the cells that have the title 'Heading' in the cell above and sets font size 12 for the rest cells in column B:

function change_font_sizes() {
  const size1  = 18;
  const size2  = 12;
  const title  = 'Heading';
  const sheet  = SpreadsheetApp.getActiveSheet();
  const range  = sheet.getRange('B1:B' + sheet.getLastRow());
  const sizes  = range.getFontSizes();
  const values = range.getDisplayValues().flat().slice(0,-1); // remove the last cell

  values.forEach((value,i) => sizes[i+1][0] = value.match(title) ? size1 : size2);
  range.setFontSizes(sizes);
}

You can run this function manually every time you feel your sheet needs it.

Upvotes: 1

Related Questions