Script : set in bold highest value

Question

I got an array in Google Sheet that looks something like this :

+-----+---------+------------+------------+------------+-----+
|     |    A    |      B     |      C     |      D     | ... |
+-----+---------+------------+------------+------------+-----+
|  0  |         | Mission #1 | Mission #2 | Mission #3 | ... |
+-----+---------+------------+------------+------------+-----+
|  1  | Item #1 |     100    |     250    |     250    | ... |
+-----+---------+------------+------------+------------+-----+
|  2  | Item #2 |     200    |    1000    |     500    | ... |
+-----+---------+------------+------------+------------+-----+
|  3  | Item #3 |     300    |    3000    |     800    | ... |
+-----+---------+------------+------------+------------+-----+
| ... |   ...   |     ...    |     ...    |     ...    | ... |
+-----+---------+------------+------------+------------+-----+

I want to create a script that bold the highest value for each item. But there is a problem, as you can see, some items get the same value in different missions, what I need is in this case, only the "most right" needs to be bold.

So what I expect to do :

+-----+---------+------------+------------+------------+-----+
|     |    A    |      B     |      C     |      D     | ... |
+-----+---------+------------+------------+------------+-----+
|  0  |         | Mission #1 | Mission #2 | Mission #3 | ... |
+-----+---------+------------+------------+------------+-----+
|  1  | Item #1 |     100    |     250    |   **250**  | ... |
+-----+---------+------------+------------+------------+-----+
|  2  | Item #2 |     200    |  **1000**  |     500    | ... |
+-----+---------+------------+------------+------------+-----+
|  3  | Item #3 |     300    |  **3000**  |     800    | ... |
+-----+---------+------------+------------+------------+-----+
| ... |   ...   |     ...    |     ...    |     ...    | ... |
+-----+---------+------------+------------+------------+-----+
** = is bold

So I started to think :

• var Item1High = max(b1;d1);
• For each row (X1) if value == Item1High then set to bold

But at this state the most difficult part is to set in bold only the most right value (So the highest value of row) And I'm stuck at this state.

Could you help me with that ? I'm using Google Sheet Script in JavaScript.

Thanks !

Answer 1

• You want to set the bold type to the maximum value in each row of Google Spreadsheet.
• For example, when the column "B" and "C" is the same value and the maximum value, you want to set the bold to the column "C".
• You want to achieve this using Google Apps Script.

The flow of this sample script is as follows.

Flow:

1. Retrieve all values from the sheet.
2. Create text styles.
3. Set the created text styles.

Sample script:

Before you run the script, please set the sheet name.

function myFunction() {
  const sheetName = "Sheet1";

  // 1. Retrieve all values from the sheet.
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const range = sheet.getDataRange();
  const values = range.getValues();
  values.shift();

  // 2. Create text styles.
  const tempStyle = SpreadsheetApp.newTextStyle().setBold(false).build();
  const bold = SpreadsheetApp.newTextStyle().setBold(true).build();
  const styles = values.map(r => {
    r.shift();
    let ar = Array(r.length).fill(tempStyle);
    ar[r.lastIndexOf(Math.max(...r))] = bold;
    return ar;
  });

  // 3. Set the created text styles.
  range.offset(1, 1, styles.length, styles[0].length).setTextStyles(styles);
}

References:

• newTextStyle()
• setTextStyles()

Added:

In this sample script, the selected range is used.

function myFunction() {
  const sheetName = "Sheet1";
  const selectRange = "B2:D4";  // Please set the range you want to use.

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const tempStyle = SpreadsheetApp.newTextStyle().setBold(false).build();
  const bold = SpreadsheetApp.newTextStyle().setBold(true).build();
  const range = sheet.getRange(selectRange);
  const values = range.getValues();
  const styles = values.map(r => {
    let ar = Array(r.length).fill(tempStyle);
    ar[r.lastIndexOf(Math.max(...r))] = bold;
    return ar;
  });
  range.setTextStyles(styles);
}