Array.push.setAnyFormatting('red')?

Question

Description:

Stack Overflow user mhawksey recently did some fantastic optimization of my code, and in doing so, introduced me to super efficient array pushes. But working with arrays is kind of difficult, because I can't seem to be able to use functions I can when using the traditional .getRange/.setValue approach.

Problem:

I need to integrate .setFontColors('red') and .setBackgroundColors('white').

Code and Images:

First, I will post the code. Second, an image of what the currently code does. Third, an image of what the code needs to do.

function format() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();
  var lastRow = s.getLastRow();
  var row;

  //gets a [][] of all values in the column
  var data = s.getRange("A:A").getValues();
  //we are going to build a [][] to output result
  var output = [];

  //loop through all cells in column A
  for (row = 0; row < lastRow; row++) {
    var cellValue = data[row][0];
    var dash = false;
    if (typeof cellValue === 'string') {
      dash = cellValue.substring(0, 1);
    //if a number copy to our output array
    } else {
      output.push([cellValue]);
    }

    //if -dash
    if (dash === "-") {
      //build first + last name
      var name = (data[(row+1)][0]+" "+data[(row+2)][0]).trim();
      //add row for the -state (e.g. -MI)
      output.push([cellValue]);
      output.push([name]);
      output.push(["Order complete"]);
      //add a blank row
      output.push([""]);
      //jump an extra row to speed things up
      row++;
    }
  }
  //set the values we've made in our output [][] array
  s.getRange(1, 1, output.length).setValues(output);
}

This is what the code does:

This is what I'm trying to achieve:

Update:

I've appended a simple, working formatting loop. The problem is, when I run it on a longer column of data, it takes too long to process. From what I understand of the comments, I cannot quickly format a spreadsheet. Am I wrong?

Appended formatting code:

//other variables
var range1;

  //loop through column A
  for (var row = 0; row < lastRow; row++) {
    range1 = s.getRange(row + 1, 1);

    //define offsets for if statement
    var offset1 = range1.offset(1, 0);
    var offset2 = range1.offset(2, 0);

    //substring cannot run on numbers, so...
    cellValue = range1.getValue();
    if (typeof cellValue === 'number') {continue;};
    dash = cellValue.substring(0, 1);

    //if -
    if (dash === "-") {
       offset1.setFontColor('red');
       offset2.setBackground('green');
     };
   };

Answer 1

You can use all the various spreadsheet methods to get and set colors, font sizes, font weights, etc. to and from distinct arrays but you can not mix these "attributes" in one single item. See the doc here.

Or even handier, in your script editor write a script that defines a range and play with the auto complete to see everything you can do with it...

(control+space keys)

---

edit following your code update.

You should create a second array that holds all the background colors of your range and fill it according to your needs. In the code below I build the array in parrallels with your output array, not very elegantly but rather systematically to show how it works.

Note that null value means "no background color" while #0F0 is the hexadecimal code for green but you can also use the 'green' string if you prefer...

    ...
    var output = [];
    var backGrounds=[]
    //loop through all cells in column A
    for (row = 0; row < lastRow; row++) {
      var cellValue = data[row][0];
      var dash = false;
      if (typeof cellValue === 'string') {
        dash = cellValue.substring(0, 1);
        //if a number copy to our output array
      } else {
        output.push([cellValue]);
        backGrounds.push([null]);
      }

      //if -dash
      if (dash === "-") {
        //build first + last name
        var name = (data[(row+1)][0]+" "+data[(row+2)][0]).trim();
        //add row for the -state (e.g. -MI)
        output.push([cellValue]);
        backGrounds.push([null]);
        output.push([name]);
        backGrounds.push([null]);
        output.push(["Order complete"]);
        backGrounds.push(['#0F0']);
        //add a blank row
        output.push([""]);
        backGrounds.push([null]);
        //jump an extra row to speed things up
        row++;
      }
    }
  s.getRange(1, 1, output.length).setBackgrounds(backGrounds);
...

Answer 2

REMARK: The OP did a huge change from revision 1 to revision 3. Following is the content taken from the source of the revision 1.

---

Is it possible to push text to a spreadsheet with formatting such as .setFontColor, .setBackgroundColor or .setBorder? I've been monkeying around with code but never get an error message. It just doesn't do anything. I'm working with something like this:

    if (cell === "This should be red") {
    var redCell = (data[(row)][0]).setFontColor('red');
    array.push([redCell]);
    }

---

Following is my answer to the revision 1.

To copy the value and format from one cell or range to another use copyTo(destination).

From the above link

 // The code below will copy the first 5 columns over to the 6th column.
 var sheet = SpreadsheetApp.getActiveSheet();
 var rangeToCopy = sheet.getRange(1, 1, sheet.getMaxRows(), 5);
 rangeToCopy.copyTo(sheet.getRange(1, 6));
 }