Set border color & style in spreadsheet programmatically

Question

Google Spreadsheet has in the toolbar under the border button also a button to change the color and change the border style.

How can these be accessed within a Google Apps Script?

The setBorderColor function which is described for documents seems unavailable for spreadsheets.

Answer 1

The reported issue has been fixed, as of 12 Jan 2016. Range now has these methods:

• setBorder(top, left, bottom, right, vertical, horizontal), as before.
• setBorder(top, left, bottom, right, vertical, horizontal, color, style), NEW!

Examples are provided in the documentation; here's how to set a dashed red border^*:

var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets()[0];  var cell = sheet.getRange("B2"); // Sets borders on the top and bottom, but leaves the left and right unchanged // Also sets the color to "red", and the border to "DASHED". cell.setBorder(true, null, true, null, false, false, "red", SpreadsheetApp.BorderStyle.DASHED); 

---

^*Corrected, as per comment: the documentation is wrong, it should be SpreadsheetApp.BorderStyle.DASHED/DOTTED/SOLID, not Range. – gotofritz

Answer 2

Currently the setBorder() properties do not allow us to provide color and style. There is an open issue you can follow here.

Answer 3

You can do a little trick. Copy the formatting in a coloured border cell to where ever you want.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var source = ss.getSheets()[0];
var destination = ss.getSheets()[1];

var blueBorderRange = source.getRange("B2:D4");

// This copies the formatting in B2:D4 from the source sheet to
// D4:F6 in the second sheet
blueBorderRange.copyFormatToRange(destination, 4, 6, 4, 6);