How to set a sheet's number of rows and columns at creation time? (And a word about Google's "advanced parameters")

Question

When creating a spreadsheet ex novo, Google's SpreadsheetApp allows one to specify the number of rows and columns of said spreadsheet:

var spreadsheet = SpreadsheetApp.create(name, rows, columns);

This only applies to the first sheet that comes within the newly created spreadsheet.

What I need to do is to replicate this behaviour when inserting a new sheet inside an existing spreadsheet. Apparently there is no explicit way to do this.

The only clue is, the function that creates a new sheet

var sheet = spreadsheet.insertSheet(options);

Optionally takes this mysterious parameter, options, which is not ever described anywhere except here, where Google states that this "advanced parameters" object can be use to duplicate the sheet from a template.

There appears to be no documentation about this object anywhere else. These advanced parameters for sheet creation are only mentioned there.

Summing up, the questions are:

1. How to set the number of rows and columns of a new sheet, created inside of an existing spreadsheet, programmatically, without creating and cloning other spreadsheets or using a template, and so on.
2. What on earth are those advanced parameters? I'd really like to read any documentation about these: if they exist, they may even be useful for something! (Or at least usable...)

Answer 1

With the restrictions you've imposed ("and so on") it's not possible to answer your question. The options are to start with a template spreadsheet that has all the sheets you want, clone existing sheets already in the spreadsheet, or add new sheets then expand or trim them to the size you want.

Utility function

Assuming it's OK to use the last option, here's a utility function that handles a variety of ways to insert a new sheet.

/**
 * Wrapper for Spreadsheet.insertSheet() method to support customization.
 * All parameters are optional & positional.
 *
 * @param {String}  sheetName     Name of new sheet (defaults to "Sheet #")
 * @param {Number}  sheetIndex    Position for new sheet (default 0 means "end")
 * @param {Number}  rows          Vertical dimension of new sheet (default 0 means "system default", 1000)
 * @param {Number}  columns       Horizontal dimension of new sheet (default 0 means "system default", 26)
 * @param {String}  template      Name of existing sheet to copy (default "" means none)
 *
 * @returns {Sheet}               Sheet object for chaining.
 */
function insertSheet( sheetName, sheetIndex, rows, columns, template ) {
  
  // Check parameters, set defaults
  var ss = SpreadsheetApp.getActive();
  var numSheets = ss.getSheets().length;
  sheetIndex = sheetIndex || (numSheets + 1);
  sheetName = sheetName || "Sheet " + sheetIndex;
  var options = template ? { 'template' : ss.getSheetByName(template) } : {};
  
  // Will throw an exception if sheetName already exists
  var newSheet = ss.insertSheet(sheetName, sheetIndex, options);
 
  if (rows !== 0) {
    // Adjust dimension: rows
    var newSheetRows = newSheet.getMaxRows();
    
    if (rows < newSheetRows) {
      // trim rows
      newSheet.deleteRows(rows+1, newSheetRows-rows);
    }
    else if (rows > newSheetRows) {
      // add rows
      newSheet.insertRowsAfter(newSheetRows, rows-newSheetRows);
    }
  }
  
  if (columns !== 0) {
    // Adjust dimension: columns
    var newSheetColumns = newSheet.getMaxColumns();
    
    if (columns < newSheetColumns) {
      // trim columns
      newSheet.deleteColumns(columns+1, newSheetColumns-columns);
    }
    else if (columns > newSheetColumns) {
      // add columns
      newSheet.insertColumnsAfter(newSheetColumns,columns-newSheetColumns);
    }
  }
  
  // Return new Sheet object
  return newSheet;
}

Examples & Performance

You expressed concern about slow performance; below are some observations from the Execution Transcript of single scenarios. The time needed to tweak dimensions is not significantly more than the time for inserting a default sheet.

Insertion of a default sheet:

insertSheet();

This script takes no more than 200ms (and most of that is just Spreadsheet.insertSheet()):

[15-11-18 15:56:25:365 EST] Starting execution
[15-11-18 15:56:25:369 EST] SpreadsheetApp.getActive() [0 seconds]
[15-11-18 15:56:25:390 EST] Spreadsheet.getSheets() [0.02 seconds]
[15-11-18 15:56:25:544 EST] Spreadsheet.insertSheet([Sheet 2, 2, {}]) [0.153 seconds]
[15-11-18 15:56:25:565 EST] Sheet.getMaxRows() [0.02 seconds]
[15-11-18 15:56:25:565 EST] Sheet.getMaxColumns() [0 seconds]
[15-11-18 15:56:25:597 EST] Execution succeeded [0.196 seconds total runtime]

Inserting a sheet at a specific index with 10 rows and 10 columns (smaller than default):

insertSheet("New sheet",1, 10, 10);

This takes about 325ms:

[15-11-18 15:59:54:476 EST] Starting execution
[15-11-18 15:59:54:481 EST] SpreadsheetApp.getActive() [0 seconds]
[15-11-18 15:59:54:520 EST] Spreadsheet.getSheets() [0.038 seconds]
[15-11-18 15:59:54:595 EST] Spreadsheet.insertSheet([New sheet, 1, {}]) [0.075 seconds]
[15-11-18 15:59:54:629 EST] Sheet.getMaxRows() [0.033 seconds]
[15-11-18 15:59:54:699 EST] Sheet.deleteRows([11, 990]) [0.069 seconds]
[15-11-18 15:59:54:746 EST] Sheet.getMaxColumns() [0.046 seconds]
[15-11-18 15:59:54:809 EST] Sheet.deleteColumns([11, 16]) [0.062 seconds]
[15-11-18 15:59:54:810 EST] Execution succeeded [0.329 seconds total runtime]

And finally, inserting a sheet at a specific index with 2000 rows and 52 columns (both twice default):

insertSheet("Big Sheet",1, 2000, 52);

Which takes about 240ms:

[15-11-18 16:09:36:393 EST] Starting execution
[15-11-18 16:09:36:398 EST] SpreadsheetApp.getActive() [0 seconds]
[15-11-18 16:09:36:419 EST] Spreadsheet.getSheets() [0.02 seconds]
[15-11-18 16:09:36:491 EST] Spreadsheet.insertSheet([Big Sheet, 1, {}]) [0.071 seconds]
[15-11-18 16:09:36:518 EST] Sheet.getMaxRows() [0.027 seconds]
[15-11-18 16:09:36:563 EST] Sheet.insertRowsAfter([1000, 1000]) [0.044 seconds]
[15-11-18 16:09:36:585 EST] Sheet.getMaxColumns() [0.021 seconds]
[15-11-18 16:09:36:645 EST] Sheet.insertColumnsAfter([26, 26]) [0.059 seconds]
[15-11-18 16:09:36:646 EST] Execution succeeded [0.247 seconds total runtime]

Answer 2

One option to consider would be to think about it from the opposite direction as you are proposing: Instead of creating a sheet with x columns and y rows, create a sheet with the default number of columns/rows and use the sheet.deleteColumns(columnPosition, howMany) and sheet.deleteRows(rowPosition, howMany) methods to define the sheet size.

Your code would be something like:

var ss = SpreadsheetApp.create(name, rows, columns);
var myNumColumns = ____; //or defined in the function call
var newSheet = ss.insertSheet(sheetName, sheetIndex, options);
var columnsToDelete = newSheet.getLastColumn() - myNumColumns;
newSheet.deleteColumns((myNumColumns-1), columnsToDelete);

You would probably have to create a custom menu function or add on interface to do this if you want the end-users to create it for themselves since you can't really create a trigger off of adding a new sheet.

Answer 3

Unfortunately, getLastColumn will not work as this just gets the last column with content in it. You want getMaxColumns instead. You can follow the links to get more detailed info on the difference.

Something like:

var doc = SpreadsheetApp.openByID(FILEID_HERE);
var sheet = doc.getSheetByName(SHEETNAMEHERE);
var deleteColumns = sheet.getMaxColumns - sheet.getLastColumn; 

sheet.deleteColumns((sheet.getLastColumn() +1),deleteColumns); 

In this sample, deleteColumns would be equal to the total number of columns minus the number of columns that have content in them. Then, in the next line, you set sheet.deleteColumns(start column, how many columns).

It looks like this might be sunset soon though, in which case you could use the .deleteColumn(columnPosition) command and a loop.

Answer 4

You can add the Google Sheets API service and then use the code below to resize the sheet in one simple API call.

 const sheetId = {your sheet id}
 const spreadsheetId = {your spreadsheet id}

 Sheets.Spreadsheets?.batchUpdate(
      {
        requests: [
          {
            updateSheetProperties: {
              fields: 'gridProperties',
              properties: {
                sheetId: sheetId,
                gridProperties: {
                  columnCount: 40, // required column count
                  rowCount: 20, // required row count
                },
              },
            },
          },
        ],
      },
      spreadsheetId
    )