skip to Main Content

I have an onEdit trigger set up for this script:

function ifBold() {

var cell = SpreadsheetApp.getActiveSpreadsheet().getRange("'Prospect List'!A:A");

  if(cell.getFontWeight() == 'bold')
      
return true;
      
  else
      
return false;
} 

I have one sheet that I enter data into and it creates a unique ID on the sheet "Prospect List". I am trying to get my script to constantly update every time I update the other sheet and constantly check if there is a bold cell. Right now, it works the first time, the trigger says it works, but the TRUE/FALSE value in the cell does not update. I would also rather type the a1Notations into the ifBold() function but that didn’t seem to work. Any help is appreciated.

I tried to run the script and it did not update the cell value.

2

Answers


  1. This is about the same thing:

    function ifBold() {
      const ss = SpreadsheetApp.getActiveSpreadsheet();
      const sh = ss.getSheetByName("Sheet0");
      const cell = sh.getRange("A1")
      if (cell.getFontWeight() == 'bold') Logger.log("bold")
    } 
    

    Whenever you treat a range of cells like you did it returns the value of the upper left corner cell

    You could run it like this and check every cell in the range looking for at least one bold:

    function ifBold() {
      const ss = SpreadsheetApp.getActiveSpreadsheet();
      const sh = ss.getSheetByName("Sheet0");
      if (sh.getRange("A1:A" + sh.getLastRow()).getValues().flat().some((e,i) => sh.getRange(i+1,1).getFontWeight() == "bold")) Logger.log("bold")
    } 
    
    Login or Signup to reply.
  2. ALTERNATE SOLUTION

    You can try running a script with the installable onChange trigger to detect the change from your sheet and display the correct value.

    Installing the onChange Trigger

    To setup the onChange trigger on your spreadsheet:

    1. On your Apps Script editor, click the Triggers tab on the left menu (the clock/alarm icon)

    2. At the bottom right part of the page, click "Add Trigger"

    3. Select and configure the type of trigger you want to create, and then click Save.

    Your setup should look like this: image

    The Script

    Notice on the screenshot that there is a function that needs to be run in order for the onChange trigger to work. As such, you can refer to this sample script for your reference:

    function checkBold(e){
      const sheetName = "Sheet1"; // sheet where the changes are to be detected
      var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
      var changetype = e.changeType;
      var cell = sheet.getActiveCell();
      if (cell.getColumn() == 1) // restricts the script to only check the row where the changes are made (if the text is bold or not)
        if((cell.getFontWeight() == 'bold')*(changetype == 'FORMAT'))
          sheet.getRange(cell.getRow(), cell.getColumn()+1).setValue("TRUE");
        else
          sheet.getRange(cell.getRow(), cell.getColumn()+1).setValue("FALSE");
    }
    

    This script works similarly to the one you have, but with a key change. A changeType event object trigger is added to check if there were some changes done on a cell or text; for this case, we are tracking if there is a format change done on the cell or text, which for this case is changing the text to bold.

    OUTPUT

    image

    RESOURCES

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search