Apps Script. Google Sheets — get all sheets info

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.