Set border color & style in spreadsheet programmatically

border, colors, google-apps-script, google-sheets

Solution

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

Problem

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.

Original source