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, Jan 09 '20 at 10:26

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

0

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).

edited Jun 20 '20 at 09:12

Community

1
1

answered Jan 09 '20 at 12:09

Iamblichus

18,540
2
11
27

Thanks lamblichus I'll look into the above methods. I did try the code out in this link and it worked without having to publish as an add-on https://developers.google.com/gsuite/add-ons/editors/docs/quickstart/translate – Fazila Jan 10 '20 at 10:20
@Fazila I assume you mean it worked in the Doc you have the script bound to right? – Iamblichus Jan 10 '20 at 10:23
Yes it worked in the document and in other documents I opened as well as in the add on was available in other documents. – Fazila Jan 10 '20 at 11:32
@Fazila the add-on cannot be available in other documents if you don't do anything else than what is specified in the quickstart. What steps are you following in order to use the add-on in other documents? – Iamblichus Jan 10 '20 at 12:10
I must have been mistaken I've tried copying and running the code again and you're right it isn't available in other documents not sure how I managed to get it before unless I was looking at the same document and didn't realise. I'll look at creating either a library or publishing as an add on although I am quite a novice and have not used either of the methods of any hints/tips would be welcome or if you know anyone who can provide a step by step guide. Thanks again for your time lamblichus I really appreciate it. – Fazila Jan 10 '20 at 14:15
@Fazila for the library, you can refer to [my answer to this question](https://stackoverflow.com/questions/59392780/is-there-any-way-to-trigger-functions-of-google-sheet-1-from-google-sheet-2/59393514#59393514). Regarding publishing, I'd suggest you to follow the steps indicated in the [link I provided](https://developers.google.com/gsuite/add-ons/how-tos/publishing-editor-addons#publishing_instructions). – Iamblichus Jan 10 '20 at 14:37

Creating add ons in Google Apps Script

1 Answers1

Workaround (use a library):

Linked