Creating add ons in Google Apps Script

Question

I have put together some code which I would like to access in other Google sheets, however, as it uses SpreadsheetApp.getUi the code has to be bound to a sheet. I have therefore decided to create the code as an add on.

Unfortunately, the add ons don't appear in other spreadsheets and disappear from the spreadsheet where the add on was created unless I open up the apps script page. Where am I going wrong?

var ui = SpreadsheetApp.getUi();
var ss = SpreadsheetApp.getActiveSpreadsheet();

function onOpen(e) {
  SpreadsheetApp.getUi().createAddonMenu()
  .addItem("Delete Columns", "delCols")
  .addItem("Insert Columns", "insCols")
  .addItem("Subjects Sheet", "deptNamesKS4")
  .addItem("Subjects Sheet", "deptNamesKS3")
  .addToUi();

};

function onInstall(e) {
  onOpen(e);
};

function delCols(e) {

  var lastColumn = ss.getLastColumn();
  var headers = ss.getRange('1:1').getValues();

  var searchVal = ui.prompt("Enter name of column to be deleted").getResponseText()

  var names = headers[0];
  var loopCounter = names.length - 1

  for (var i = loopCounter; i >= 1; i--) {
    if(names[i].indexOf(searchVal) > -1) {
      ss.deleteColumn(i + 1);
    }DE
  }
}

function insCols(e) {

  var lastColumn = ss.getLastColumn();
  var headers = ss.getRange('1:1').getValues();

  var searchVal = ui.prompt("Enter name of column to be deleted").getResponseText();
  var noCols = ui.prompt("Number of columns to be inserted").getResponseText();

  var names = headers[0];
  var loopCounter = names.length - 1

  for (var i = loopCounter; i >= 1; i--) {
    if(names[i].indexOf(searchVal) > -1) {
      ss.insertColumnsBefore(i + 1, noCols);
    }
  }
}

Any help would be appreciated.

Thanks

Review the documentation; it takes a fair bit to generate an add-on — tehhowch

Iamblichus Iamblichus · Accepted Answer · 2020-01-09T12:09:25

In order to use the add-on in other files, you would have to do one of the following:

(1) Publish the add-on, as explained here.

(2) Test the add-on via Run > Test as add-on.... I wouldn't recommend this, since you would have to add each file you want to use the add-on with first, and open the file from there.

Workaround (use a library):

A possible workaround to reach your purpose would be, considering what you want the add-on for, instead of using your code as an add-on, save it in a library and then include it in each file you want it to run (you would have to change createAddonMenu to createMenu).

Creating add ons in Google Apps Script

1 Answers

Workaround (use a library):