24

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.

Bhargav Rao
  • 50,140
  • 28
  • 121
  • 140
user1747567
  • 241
  • 1
  • 2
  • 3
  • Star this issue, created to make those available in Google Apps Script: [http://code.google.com/p/google-apps-script-issues/issues/detail?id=2002](http://code.google.com/p/google-apps-script-issues/issues/detail?id=2002) – Jacob Jan Tuinstra Jan 11 '13 at 19:21
  • Correct link: http://code.google.com/p/google-apps-script-issues/issues/detail?id=2002 – Jacob Jan Tuinstra Jan 16 '13 at 20:42

3 Answers3

26

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

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

Mogsdad
  • 44,709
  • 21
  • 151
  • 275
  • Sadly the example doesn't work, 'Range.BorderStyle.DASHED' gives a 'Range undefined' error – gotofritz Apr 02 '16 at 14:16
  • 2
    Actually discovered the problem - the documentation is wrong, it should be SpreadsheetApp.BorderStyle.DASHED/DOTTED/SOLID, not Range. – gotofritz Apr 02 '16 at 14:18
  • 1
    @gotofritz It would be nice to be able to trust the docs, wouldn't it?! Updated answer, thanks. – Mogsdad Apr 02 '16 at 15:08
  • How can you get the Border or border style? I check the reference in the documentation. Apparently, this is not possible yet. – KareemJ Dec 03 '19 at 13:44
4

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

Rafael De Alemar Vidal
  • 163
  • 1
  • 12
4

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);
Rhemzo
  • 73
  • 6