Retrieving Google Sheets cell border style programmatically

Asked 12/2, 2018 at 19:41 Answered 8/8 at 17:55

Solved google-apps-script google-sheets border

It is possible to set the range border style and color. Now the question is: how can we get the border style programmatically?

I'm looking for something like:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var b2 = sheet.getRange("B2");

var border = b2.getBorder(); // getBorder does not exist, but b2.setBorder does :-/

Urethroscope answered 12/2, 2018 at 19:41 Comment(1)

"star" this feature request: issuetracker.google.com/issues/36760052 it's 8 years old! – Pisarik 15/11, 2021 at 21:25

I'm not sure whether this is the best way. I always retrieve the border style using Sheets API. The sample script is as follows.

When you use this sample script, please enable Sheets API.

How to enable API:

From April 8, 2019, the specification for enabling API has been changed for the GAS project created after April 8, 2019. Ref

GAS project created after April 8, 2019

In this case, when the API is enabled at Advanced Google services, the API is automatically enabled at API console. So you can use the API by only enabling it at Advanced Google services.

Enable Sheets API v4 at Advanced Google Services

On script editor
- Resources -> Advanced Google Services
- Turn on Google Sheets API v4

GAS project created before April 8, 2019

In this case, the API is required to be enabled at Advanced Google services and API console.

Enable Sheets API v4 at Advanced Google Services

On script editor
- Resources -> Advanced Google Services
- Turn on Google Sheets API v4

Enable Sheets API v4 at API console

On script editor
- Resources -> Cloud Platform project
- View API console
- At Getting started, click "Explore and enable APIs".
- At left side, click Library.
- At Search for APIs & services, input "sheets". And click Google Sheets API.
- Click Enable button.
- If API has already been enabled, please don't turn off.

If now you are opening the script editor with the script for using Sheets API, you can enable Sheets API for the project by accessing this URL https://console.cloud.google.com/apis/library/sheets.googleapis.com/

Other case

If you want to use the API which is not included in the Advanced Google services at GAS project created after April 8, 2019, it is required to link Cloud Platform Project to Google Apps Script Project. And the API is required to be enabled at API console. You can see the detail flow of this at here.

Sample script :

var spreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId();
var res = Sheets.Spreadsheets.get(spreadsheetId, {ranges: "Sheet1!B2", fields: "sheets/data/rowData/values/userEnteredFormat/borders"});

References :

Advanced Google Services : https://developers.google.com/apps-script/guides/services/advanced
Sheets API v4: https://developers.google.com/sheets/api/
Linking Cloud Platform Project to Google Apps Script Project

If this was not useful for you, I'm sorry.

Lakshmi answered 12/2, 2018 at 22:22 Comment(2)

Very well explained, thanks. I can see by logging JSON.stringify(res) the resulting structure is pretty deep in terms of nodes, but at least the information is there :-) – Urethroscope 12/2, 2018 at 23:5

@A. Masson I'm glad this was useful for you. Thank you, too. – Lakshmi 12/2, 2018 at 23:7

As of August 8th, 2024, SpreadsheetApp.Range.getBorder is not included in https://developers.google.com/apps-script/reference/spreadsheet/range, but it's shown in the Google Apps Script Editor autocomplete feature and it works.

The Editor shows the following methods for Class Border, which also is not documented in the official reference of Spreadsheet Service.

getBorderStyle(): Returns a Enum BorderStyle instance.
getColor(): Returns a Class Color instance.

Newspaper answered 8/8 at 17:55 Comment(0)