Apps Script. Google Sheets — get all sheets info

Max Makhrov
2 min readJun 1, 2023

--

In Google Apps Script use this code to fetch information about sheets of a given Spreadsheet:


/**
* @param {String} options.ssID - spreadsheet Id
* @param {Array[String]} options.header - what columns to return
*/
function getSheetsInfo(options) {
options = options || getConfig_();
var ssId = options.ssId;
var ss;
if (ssId) {
ss = SpreadsheetApp.openById(ssId);
} else {
ss = SpreadsheetApp.getActive();
}
var sheets = ss.getSheets();
var header = options.header || [
'id',
'name',
'color',
'rows',
'columns',
'lastrow',
'lastcolumn',
'ishidden'
];
var result = [header];
var addInfo_ = function(sheet) {
var infoRow = getSheetInfoRow_(sheet, header);
result.push(infoRow)
}
sheets.forEach(addInfo_);

return result;

}

function getSheetInfoRow_(sheet, header) {
var self = this;
self.id = function() {
return sheet.getSheetId()
}
self.name = function() {
return sheet.getName();
}
self.color = function() {
var colorObject = sheet.getTabColorObject();
var theme = sheet.getParent().getSpreadsheetTheme();
return convertColorObjectToHexColor_(colorObject, theme);
}
self.rows = function() {
return sheet.getMaxRows();
}
self.columns = function() {
return sheet.getMaxColumns();
}
self.lastrow = function() {
return sheet.getLastRow();
}
self.lastcolumn = function() {
return sheet.getLastColumn();
}
self.ishidden = function() {
return sheet.isSheetHidden();
}
var result = [], key, value;
for (var i = 0; i < header.length; i++) {
key = header[i];
if (!self[key]) {
value = null;
} else {
value = self[key]();
}
result.push(value);
}
return result;
}


/**
* Get the HEX-color of sheet
*
* @param {colorObject} colorObject
* https://developers.google.com/apps-script/reference/spreadsheet/color
* Examples of getting color object:
* `range.getBackgroundObjects()`
* `sheet.getTabColorObject()`
*
* @param {theme} theme
* ## Need to pass theme in order to optimize calls to server
* `ss.getSpreadsheetTheme()`
*/
function convertColorObjectToHexColor_(colorObject, theme) {
// https://developers.google.com/apps-script/reference/base/color-type
var colorTypeEnum = colorObject.getColorType().toString();
switch (colorTypeEnum) {
case 'UNSUPPORTED':
return '';
case 'RGB':
return colorObject.asRgbColor().asHexString()
case 'THEME':
theme = theme || SpreadsheetApp.getActive().getSpreadsheetTheme();
// https://developers.google.com/apps-script/reference/base/color-type.html
var colorType = colorObject.asThemeColor().getThemeColorType();
return theme.getConcreteColor(colorType).asRgbColor().asHexString();
default:
return 'Unknown';
}
}

Test this code as follows:

function test_getSheetsInfo() {
var optios = {
ssId: '1HwUaZk86BtrPdQ1RYILwTcRwJUUClgqtAPEpMAsX0y8' // change to your Spreadsheet Id
}
var sheetInfo = getSheetsInfo(optios);
console.log(sheetInfo);
}

The result is information about each sheet:

  • id
  • name
  • color
  • rows
  • columns
  • lastrow—the last row with data
  • lastcolumn — the last column with data
  • ishidden

Here are my test results:

Interesting fact, colors are added in 2 ways: as usual hex-color, and as theme color. To test it, create a new Spreadsheet, and run this:

function bug_test() {
var ss = SpreadsheetApp.getActive();
var s = ss.getSheetByName('Sheet1');
// 🤓LOL🤓. Setting color to tab and it does not paint tab
var color = SpreadsheetApp.newColor()
.setThemeColor(SpreadsheetApp.ThemeColorType.ACCENT1)
.build();
sheet.setTabColorObject(color);
}

The actual sheet’s color will not change, while you’ve just set it to ACCENT1. This bug is relevant for the date, 06/2023.

--

--