Google Scripts Sheets hide/ unhide

Question

In writing a 'custom function' Google Script for my particular sheet, I simply want to hide a column:

function hideColumn(index) {
  // get active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // get first sheet
  var sheet = ss.getSheets()[0];
  sheet.hideColumns(index);
}

This code works fine when I run it from within the Script editor but if I try to run it from inside a cell "=hideColumn(2)", I get the following error:

"You do not have permission to call hideColumns (line 48)."

From the same sheet/ script I'm able to run other custom functions such as:

function metersToMiles(meters) {
  if (typeof meters != 'number') {
    return null;
  }
  return meters / 1000 * 0.621371;
}

This seems to be some issue with the hideColumns function being run from inside a sheet? (ie. custom function?)

JPV JPV · Accepted Answer · 2014-12-15T08:12:31

Your script 'hideColumn' is not a custom function, but a 'normal script'. Also it does not return anything (whereas the second function does). Only custom functions can be entered like formulas in the spreadsheet. See here for more info. My advice would be to create an extra menu-item using an onOpen trigger so you can run the function from the (spreadsheet)menu.

Hope that helps ?

Google Scripts Sheets hide/ unhide

1 Answers