Is it possible to add formatting (shading) to rows being appended in Google Sheets (by Google Apps S-CodePudding

I've got a Google App Script which is copying rows from one sheet to another, performing various transformations. This logic ultimately gets rows onto the new sheet using sheet.appendRow(row detail). I would like these newly created rows to have a background colour (my intention is to hold a 'latestColour' so I can alternate the shading).

So, is there anyway to add shading within the appendRow method itself, or easily determine the range that the appendRow method processed, such that I can apply additional logic to add the shading.

CodePudding user response：

You can use conditional formatting

=and(A1<>"",A2="")

CodePudding user response：

Although I'm not sure whether I could correctly understand your situation, from your question, I thought that you might be using [Format] --> [Alternating colors] in Google Spreadsheet. And, when a new row is appended by putting the values, you might want to reflect "Alternating colors" in the appended row. If my guess is correct, how about the following sample script?

Sample script:

function myFunction() {
  const addValues = ["sample1", "sample2", "sample3"]; // This is a sample appending value. Please replace this for your value.
  const sheetName = "Sheet1"; // Please set the sheet name.

  // Retrieve banding object from the data range.
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const b = sheet.getDataRange().getBandings();
  if (b.length == 0) {
    console.log("Bandings are not used.");
    return;
  }

  // Append the value.
  sheet.appendRow(addValues);

  // Expand the range of banding.
  b[0].setRange(sheet.getDataRange());
}

When this script is run, the current banding is retrieved. And, after the value was appended, the banding is updated by including the appended row. In this sample, even when the multiple rows are appended, this script can be used.

Note:

From your question, I guessed that there is one banding in the data range in your sheet. Please be careful this.

References:

CodePudding user response：

Unfortunately the method appendRow() does not receive formatting settings as input, only an array of values.
However, here is a suggestion if you want to implement your own logic:

Sample code:

function applyColorLastRow() {
  var ss = SpreadsheetApp.getActive(); //get active sheets file 
  var range = ss.getDataRange(); //get populated range, you may want to set a range manually if needed.

  var lastRowNum = range.getLastRow(); //getting the last row index of the range.
  var lastRowRange = ss.getRange(`${lastRowNum}:${lastRowNum}`); //narrowing the range (using A1 notation) to the last row only to apply color

  var lastRowColor = lastRowRange.getCell(1,1).getBackgroundObject().asRgbColor().asHexString(); 

  //Your row coloring logic here...
  if (lastRowColor === '#ffffff'){ //toggling white/grey color as an example...
    lastRowRange.setBackground('#cccccc'); //apply grey color to all cells in the last row range
  } else {
    lastRowRange.setBackground('#ffffff'); //apply white color to all cells in the last row range
  };
}