Apply a "row banding" theme to a range

I am a beginner to Google Apps Script but use it to automate some simple repeating tasks. I have several spreadsheets I am copying content on a weekly basis and export them as an .xls file that I send to my client.

I am trying to apply alternating colors to a range I copy from another sheet but I completely got stuck. How to correctly set bandingTheme with the applyRowBanding method? What is the right syntax I should use in the last line of my code?

My code:

function copyRange (SourceSSID, SourceRange, TargetSheetName, bandingTheme) {
  var sheetSource = SpreadsheetApp.openById(SourceSSID);
  var sheetTarget = SpreadsheetApp.openById("bla-bla");
  var source =  sheetSource.getRange(SourceRange);
  var target_ss = sheetTarget.getSheetByName(TargetSheetName);
  var values = source.getValues();
  var target = target_ss.getRange(1, 1, values.length, values[0].length);
  target.clear();

  target.setValues(values);

  target.applyRowBanding ();
}

Solution 1:

If your method argument bandingTheme is one of the enums listed here, you can simply apply it, using the apply___Banding(BandingTheme theme) method signature:

target.applyRowBanding(bandingTheme);

The above is equivalent to this line, per documentation:

target.applyRowBanding(bandingTheme, true, false);

(In other words, the default behavior is to color the header but not the footer, in addition to alternating row colors.)

You can ensure no existing themes were previously present (only a single kind of alternating colors - be it from columns OR rows - can be present at any given time, else an error is thrown).

target.getBandings().forEach(function (banding) {
  banding.remove();
});
/**
 * set the new banding theme
 * ....
 */

If you wanted to set a custom banding theme, you can do so by starting from one of the theme designs. Note that the apply___Banding methods return the Banding object that they applied. If you bind this return value (or chain the methods), then you can modify it using its class methods.

const newBanding = target.applyRowBanding(SpreadsheetApp.BandingTheme.BLUE);
// newBanding is now a Banding that was instantiated with the "Blue" template.
// Color the header column:
newBanding.setHeaderColumnColor('teal');

// Equivalent:
target.applyRowBanding(SpreadsheetApp.BandingTheme.BLUE).setHeaderColumnColor('teal');

Note that setting colors for non-header columns in a row-banding theme doesn't work. Likewise for setting non-header row colors in a column-banding theme.

If your bandingTheme argument isn't one of the theme enums, then you will have to provide more details about what it is in order to get answers that help you convert it into the available Spreadsheet Service methods.