How to set the number of rows of rows and columns during creation? (And a word about Google’s “advanced options”)

Question

When creating an ex novo spreadsheet, Google SpreadsheetApp allows you to specify the number of rows and columns of the specified spreadsheet:

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

This applies only to the first sheet that is included in the newly created spreadsheet.

What I need to do is repeat this behavior when inserting a new sheet into an existing table. There seems to be no explicit way to do this.

The only key is the function that creates the new sheet.

var sheet = spreadsheet.insertSheet(options);

Optionally accepts this mysterious parameter options, which is never described anywhere, except here , where Google states that these are "advanced parameters", the object can be used to duplicate a sheet from a template.

Apparently, documentation about this object is missing. These advanced options for creating a sheet are mentioned only there.

To summarize the questions:

• How to set the number of rows and columns of a new worksheet created inside an existing spreadsheet , programmatically, without creating and cloning other spreadsheets or using a template, etc.
• ? : , -! (, , ...)

Answer 1

( " .." ) . , , , , .

, , , , .

/**
 * 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 rows
      newSheet.deleteColumns(rows+1, newSheetColumns-columns);
    }
    else if (columns > newSheetColumns) {
      // add rows
      newSheet.insertColumnsAfter(newSheetColumns,columns-newSheetColumns);
    }
  }

  // Return new Sheet object
  return newSheet;
}

; . , , , .

:

insertSheet();

script 200 ( 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]

10 10 (, ):

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

325 :

[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]

, , 2000 52 ( ):

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

240 :

[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

- , : x y / sheet.deleteColumns(columnPosition, howMany) sheet.deleteRows(rowPosition, howMany) .

:

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);

, , , , , , .

Answer 3

, , . , . GetLastColumn , . . GetMaxColumns. , .

- :

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

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

deleteColumns , . sheet.deleteColumns(sart column, ).

, , .deleteColumn(columnPosition) .