google-sheetsgoogle-apps-script

Repeat a function for all selected cells in a range


I'm new to google apps script. I have a simple code to reduce the font size of the first character in a cell while keeping the rest of the cell content as is, kinda like

before after
1 ONE 1 ONE

so from a normal font size 1 to a smaller font size 1

function reducenumsize() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getActiveRange();

    var num = range.getRichTextValue().getText().charAt(0);
    var letter = range.getRichTextValue().getText().substring(2);

    range.setRichTextValue(SpreadsheetApp.newRichTextValue()
    .setText(num+" "+letter)
    .setTextStyle(0, 2, SpreadsheetApp.newTextStyle()
    .setFontSize(3)
    .build())
    .build());
};

I want to be able to use it on all cells in range, but if I used the script while highlighting the range I want it applied to, only the top-left most cell got updated.

I tried making a loop to repeat it,

function bulkreduce() {
  var spreadsheet = SpreadsheetApp.getActive();
  for (var i = 0; i <= 5; i++){
    reducenumsize();
    spreadsheet.getCurrentCell().offset(0, 1).activate();
  }
};

this helps me to apply the previous function to all 5 column in a row. But not only is this a bit slow, I haven't found how to return to the leftmost column of my selected range even if I'm able to go to the next row. With a line of

spreadsheet.getCurrentCell().offset(1, 0).activate();

after the for loop on a A1:B5 range, the iteration goes A1, A2, A3, A4, A5, B5, then stops.

Is there a better way to do this? Idk what to look for to solve this.


Solution

  • In your situation, how about the following modification?

    Modified script:

    In this modification, the rich text objects are retrieved from the active range using getRichTextValues. And, the retrieved rich text objects are modified for your goal. And, the modified rich text objects are put into the active range using setRichTextValues. By this, all cells in the active range are updated. By using getRichTextValues and setRichTextValues, the process cost will be reduced a little.

    function reducenumsize() {
      var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
      var range = sheet.getActiveRange();
    
      // --- I modified the below script.
    
      // This style is from your showing script.
      const style = SpreadsheetApp.newTextStyle().setFontSize(3).build();
    
      const richTextValues = range.getRichTextValues().map(r => r.map(c => c.copy().setTextStyle(0, 2, style).build()));
      range.setRichTextValues(richTextValues);
    }
    

    Testing:

    When this script is run, the following result is obtained.

    enter image description here

    References: