"You do not have permission to call ..." Steps to convert a custom function it into an add-on that will have permission?

Question

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()?

Answer 1

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

• https://developers.google.com/apps-script/guides/sheets/functions