Javascript - Trying to get my custom google sheet script to update the value in the cell every time it is triggered

EthanBrenes
February 16, 2024
121 views
0 votes
2 Answers

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.

Answers

- Cooper
- February 16, 2024 at 12:29 am
- 0 votes
0
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.

- MicTest
- February 16, 2024 at 2:14 am
- 0 votes
0
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:

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

RESOURCES
- Installable Triggers
- Event Objects
Login or Signup to reply.