Retrieving Google Sheets cell border style program

问题:

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

I'm looking for something like:

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

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

回答1:

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.