performance google-sheets google-apps-script google-sheets-api border

What is the most efficient way to get borders in Google Apps Script

What is the best way to get border details in Google Apps Script?

I cannot see anything with borders in the documentation for GAS, so I have had to resort to getting borders through the Spreadsheet API.

This works ok, other than when the number of borders gets large where it will take a long time to return, or not return at all.

Is there a better way to do this?

var fieldsBorders = 'sheets(data(rowData/values/userEnteredFormat/borders))';

var currSsId = SpreadsheetApp.getActiveSpreadsheet().getId();
var activeSheet = SpreadsheetApp.getActiveSheet();
var name = activeSheet.getName();

var data = Sheets.Spreadsheets.get(currSsId, {
    ranges: name,
    fields: fieldsBorders
});

Solution

You want to reduce the process cost for retrieving the borders from a sheet in Spreadsheet.
When I set the borders for 26 x 1000 cells and run your script, the process time was about 50 s in my environment.
- For this situation, you want to reduce the cost more.
- Your reply comment is perhaps it was a bigger sheet it was on, either way 50s is a long time to get the borders. The other calls to GAS take a very small amount of time to complete. Can you confirm this is the only way to get borders?

If my understanding is correct, how about this workaround? In this workaround, I request directly to the endpoint of Sheets API for retrieving the borders.

Workaround:

Sample situation

In this sample script, as a sample situation, I supposes the default sheet which has 26 columns x 1000 rows, and the borders are set to all cells.

Sample script 1:

In this sample script, the borders are retrieved by one API call.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var fileId = ss.getId();
var sheetName = ss.getActiveSheet().getName();
var token = ScriptApp.getOAuthToken();
var fields = "sheets/data/rowData/values/userEnteredFormat/borders";
var params = {
    method: "get",
    headers: {Authorization: "Bearer " +  token},
    muteHttpExceptions: true,
};
var range = sheetName + "!A1:Z1000";
var url = "https://sheets.googleapis.com/v4/spreadsheets/" + fileId + "?ranges=" + encodeURIComponent(range) + "&fields=" + encodeURIComponent(fields); 
var res = UrlFetchApp.fetch(url, params);
var result = JSON.parse(res.getContentText());

Result:

When the sample script 1 was used, the average process time was 2.2 seconds.
- Although I'm not sure about the internal process of Sheets API of Advanced Google Service, it was found that when it directly requests to the endpoint, the process cost can be reduced.

Sample script 2:

In this sample script, the borders are retrieved with the asynchronous process by several API calls.

var sep = 500; // Rows retrieving by 1 request.
var ss = SpreadsheetApp.getActiveSpreadsheet();
var fileId = ss.getId();
var sheetName = ss.getActiveSheet().getName();
var token = ScriptApp.getOAuthToken();
var fields = "sheets/data/rowData/values/userEnteredFormat/borders";
var requests = [];
var maxRows = 1000;
var row = 1;
for (var i = 0; i < maxRows / sep; i++) {
  var range = sheetName + "!A" + row + ":Z" + (row + sep - 1);
  requests.push({
    method: "get",
    url: "https://sheets.googleapis.com/v4/spreadsheets/" + fileId + "?ranges=" + encodeURIComponent(range) + "&fields=" + encodeURIComponent(fields),
    headers: {Authorization: "Bearer " +  token},
  });
  row += sep;
}
var response = UrlFetchApp.fetchAll(requests);
var result = response.reduce(function(ar, e) {
  var obj = JSON.parse(e.getContentText());
  Array.prototype.push.apply(ar.sheets[0].data[0].rowData, obj.sheets[0].data[0].rowData);
  return ar;
}, {sheets: [{data: [{rowData: []}]}]});

Result:

When the sample script 2 was used, the following results were obtained.

When sep is 500 (in this case, 2 API calls are run.), the average process time was 1.9 seconds.
When sep is 200 (in this case, 5 API calls are run.), the average process time was 1.3 seconds.

But if the number of requests in one run are increased, the error related to the over of quotas occurs. Please be careaful this.

Note:

This is a simple sample for testing above situation. So I think that above script cannot be used for all situations. If you use above sample script, please modify it for your situation.