0

I'm attempting to create a HIDE() function, which will hide either the current row, column or both based on another (true/false) value.

I've already created the full function, but unfortunately it's only working from the editor, and not from the sheet. When attempting to use it from the sheet, I get an error, such as You do not have permission to call unhideRow.

Below is a simple function to replicate this problem:

/**
 * Hides the current row based on a boolean switch.
 * @customfunction
 */
function Hide(hidden) {
  // hidden = true

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet()

  var range = sheet.getRange("A1");
  // real function uses sheet.getActiveCell()

  if (hidden) {
    sheet.hideRow(range);
  } else {
    sheet.unhideRow(range);
  }

  return
}

I've read how-to-grant-permission-to-self-defined-functions, and understand it's not possible to use custom functions for this purpose.

I then attempted to test the function as an add-on from the editor, but get the same error.

I've also been reading up on the Authorization model used for sheets, but I fail to understand what I need to do in order to allow a function such as this to run.

So my question simply is, what are the steps needed to create an add-on that will allow me to include a function such as the suggested HIDE()?

0

Convert your custom function to an add-on will not solve the problem because custom functions only can return values. They can't make changes to the spreadsheet, they can't call services that require authorization to be executed.

References

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.