CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetscenteringuppercase
Lod
Lod

Reputation: 769

How To Horizontally Center Align Only The Uppercase Cells from a Range with Google Apps Script in Google Sheets?

I have a question similar to this one.

Change Google Spreadsheets cell horizontal alignment using python

My sample sheet

The sample sheet was inspired by this Youtube Tutorial

uses a QUERY formula (in the 'Report' sheetA5) dependant on 2 validation dropdowns (in cells A2 and B2).

I'd need the script to horizontally center align all the Uppercase cells and only the Uppercase cells in the range A5:B.

And it needs to do it dynamically, on the change event (when I switch the dropdown selection in A1 and/or B1).

I tested this from gathered other solutions and documentation below but it's not working.

function onEdit(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetByName = e.sheet.getSheetByName("Report");
  var range = e.sheetByName.getRange('A5:B');
  var value = e.range.getValue();

  const isUpperCase = (string) => /^[A-Z]*$/.test(string)

  if (sheetByName === 'Report' && 
      typeof value === 'string' &&
      isUpperCase(value)) {
        e.range.setHorizontalAlignment("center");
  }
}

I also tested with this other script but not success:

function onEdit(e){
  const sh = SpreadsheetApp.getActiveSheet();
  const isUpperCase = (string) => /^[A-Z]*$/.test(string)
  e.sh.getRange(5, 1, sh.getLastRow()-1, 2).getValues().flat().forEach((v, i) => {
    if (isUpperCase(v)) {
      e.sh.getRange(1, i + 1).setHorizontalAlignment("center");
    }
  });
}

My test was from insights from those other answers.

Google Sheets macro to center align all dashes in top row

How can I test if a letter in a string is uppercase or lowercase using JavaScript?

How to uppercase a cell range even if user type or paste lowercase with no warning in Google Sheet

Script to automatically capitalize contents of a cell in Google Sheets?

Many thanks in advance for your help!

Upvotes: 1

Views: 108

Answers (2)

Daniel
Daniel

Reputation: 3725

One of the problems you're facing is that you're trying to setHorizontalAlignment on the entire range object. Since you're testing each cell you need to do a getCell() every time you get a match to refer specifically to that cell. Also, keep in mind that after a getValues() you get a regular array with just the raw values, and it no longer references any of the cells in the range, so you need to keep calling the original range.

Here's a sample that worked for me with your test sheet:

function onEdit(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetByName = sheet.getSheetByName("Report");

  var lastrow = sheetByName.getLastRow()
  var range = sheetByName.getRange(5, 1, lastrow, 2); // Narrowing down the range since
                                                     //  "A5:B" gets too many blank rows

  var editedrange = e.range.getA1Notation() // gets the A1 notation of the cell edited
                                            // to make sure that it only fires when the dropdowns 
                                            // change
  var values = range.getValues();

  const isUpperCase = (string) => /^[A-Z]*$/.test(string)

  if (editedrange == "A2" || editedrange == "B2") {
    values.forEach(function (value, i) {

      if (isUpperCase(value[0])) {
        range.getCell(i + 1, 1).setHorizontalAlignment("center")
      } else {
        range.getCell(i + 1, 1).setHorizontalAlignment("left")
      }

      if (isUpperCase(value[1])) {
        range.getCell(i + 1, 2).setHorizontalAlignment("center")
      } else {
        range.getCell(i + 1, 2).setHorizontalAlignment("left")
      }
    })
  }
}

I'm pretty much just looping through the values to find the uppercase matches, then using their array positions to refer to their cells, then setting the alignment accordingly. Note that the alignment remains on the cell so you have to reset it to "left" every time that you don't get a match. The result looks like this:

enter image description here

Upvotes: 1

Lod
Lod

Reputation: 769

I revisited @Tanaike's answer from earlier post.

And after much trial and error I got to this partial solution and not yet OnEdit event ready.

function horizontalCenterAlignUppercase() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetByName = sheet.getSheetByName("Report");
  var range = sheetByName.getRange('A5:B');
  var values = range.getValues();


  const isUpperCase = (string) => /^[A-Z]*$/.test(string)

  for (let i = 0; i < values.length; i++) {

    if (typeof value === 'string' &&
        isUpperCase(values[i])) {
          range.push(values[i]);
    }
  }
  range.setHorizontalAlignment("center");
}

It does Horizontally align all the cells though,

not just the Uppercase ones.

Screenshot of range A5:B:

ds

Cells A5, B5, and A8 for example should have remained left aligned.

Any insights on why the script doesn't restricts itself to the Uppercase cells input only is very much welcome and appreciated. Thank you.

Upvotes: 0

Related Questions