SpreadsheetApp.getActiveSpreadSheet suddenly stops working

March 11, 2022

At first I had the error of my rows being out of bounds? Then I get this error with "TypeError: SpreadsheetApp.getActiveSpreadSheet is not a function" after I tampered a bit.

I’m not sure why my script isn’t working. I have one function to just autoresize all my rows and another function to resize my rows a specific amount (this one keeps saying my rows are out of bounds) Both of these functions use to work for my rows, so I’m not sure what changed.

function onOpen() {
    SpreadsheetApp.getUi().createMenu('Scripts')
        .addItem('Hide columns', 'hideCols')
        .addItem('Show Columns', 'show')
        .addItem('Auto Resize Rows', 'autoResizeRows')
        .addItem('Resize Rows', 'resizeRows')
        .addToUi()
}

function hideCols() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  var cells = sheet.createTextFinder("Pictures").matchEntireCell(true).findAll();

  cells.map(x => sheet.hideColumns(x.getColumn()));
}

function show() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  var cells = sheet.createTextFinder("Pictures").matchEntireCell(true).findAll();

  cells.map(x => sheet.showColumns(x.getColumn()));
}

function autoResizeRows() {
  var ss = SpreadsheetApp.getActiveSpreadSheet();
  var dataRange = ss.getDataRange();
  var lastRow = dataRange.getLastRow();
  ss.autoResizeRows(2, lastRow);
}

function resizeRows() {
  var sss = SpreadsheetApp.getActiveSpreadsheet();
  var cellss = sss.getSheetByName('Sheet1');
  var responseData = cellss.getDataRange().getValues();
  cellss.setRowHeights(2,responseData.length, 300);
}

>Solution :

Because you’ve a typo in this function:-

function autoResizeRows() {
  var ss = SpreadsheetApp.getActiveSpreadSheet();
  var ssname = ss.getActiveSheet()
  var lastRow = ssname.getLastRow();
  ssname.autoResizeRows(1, lastRow);
}

It’s getActiveSpreadsheet not getActiveSpreadSheet that’s why your code is breaking, change that S to s.

Reference:-

getActiveSpreadsheet