Google Apps Script – Using Google Apps Script Add-On Without Publishing

google docsgoogle-appsgoogle-apps-scriptgoogle-apps-script-add-on

I've just written my first Apps Script add-on for showing some links in a sidebar of a Docs document.

It looks like the way I created it made it bound to a specific document (the one from which I clicked Tools > Script Editor). When I open this document, the script shows up under the Add-ons menu item.

I would like the add-on to be available in my other documents as well, but I don't feel like publishing it: It requires me to add screenshots, icons, and other stuff that prevents me from being able to flesh out a script without too much fuzz.

I tried to publish it with "Private visibility" but that didn't make it available to my other documents (tried searching in Add-ons > Get add-ons).

So how do I make the script available to use in all my documents? Can I detach the script from the original document and would that help?

Best Answer

I tried to publish it with "Private visibility" but that didn't make it available to my other documents (tried searching in Add-ons > Get add-ons).

...

So how do I make the script available to use in all my documents?

Publishing and add-on as privately (unlisted or limited to a group of users) will not display the add-on on the add-ons store, except for those that use Google Apps / G Suite account.

To install the add-on, the publisher should grab the add-on listing URL and share it with the users in order to make them be able to install it.

To grab the add-on listing URL, on the Chrome Web Store dashboard, click on the name of the published add-on.

That will open the add-on listing.

Grab the URL shown on the web browser address bar (Chrome call it omnibox). If it contains ?authuser=n or &authuser=n, where n will be an integer, remove that part and share the URL with the add-on users.

To install the add-on for yourself, click the Free button.

Can I detach the script from the original document and would that help?

The way to "detach" a script from a document, is to create an standalone Apps Script project but it could involve to make changes to the code as there some features that can't be tested directly like methods that get the active document and that are blocked when the script is run from Run > Test as add-on.... This rather than making the things easier could be a bit harder and if the add-on is intended your use only, there is not an important gain unless you will use a development process that requires a stand-alone Apps Script project.

https://developers.google.com/apps-script/add-ons/publish
Publish an add-on privately (Stack Overflow Q&A)

Short answer

The link points to a Q&A that has two answers but non-of them looks to me to be appropiate. I.E. the code of one of answers is incomplete and both of them send emails, so the adoption is too complex to be used as a starting point.

Below is a script that is easier to adopt.

Instructions

Create a test form and note the form ID (between ../d/ and /edit in the url).
Set the form to send responses to a new spreadsheet.
You'll be redirected to the new spreadsheet. Note the active sheet name (rename it if you want) and add a header to the column to be used to hold the response edit URLs, i.e. Edit Url. NOTE: Capitalization is very important, so be very careful on how do you write it.
Go to Tools > Script editor to add a Google Apps Script project bounded to the spreadsheet.
Replace the default code with the code below (remember to edit the global variables according to your case).
Save the project, and then add an installable form summit trigger (if you get an "Authorization required" dialog, click "Review Permissions" and then "Allow").
Submit some sample responses to test the solution.

Code

/*
 * Global Variables
 */

// Form URL
var formURL = 'https://docs.google.com/forms/d/form-id/viewform';
// Sheet name used as destination of the form responses
var sheetName = 'Form Responses 1';
/*
 * Name of the column to be used to hold the response edit URLs 
 * It should match exactly the header of the related column, 
 * otherwise it will do nothing.
 */
var columnName = 'Edit Url' ;
// Responses starting row
var startRow = 2;

function getEditResponseUrls(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues(); 
  var columnIndex = headers[0].indexOf(columnName);
  var data = sheet.getDataRange().getValues();
  var form = FormApp.openByUrl(formURL);
  for(var i = startRow-1; i < data.length; i++) {
    if(data[i][0] != '' && data[i][columnIndex] == '') {
      var timestamp = data[i][0];
      var formSubmitted = form.getResponses(timestamp);
      if(formSubmitted.length < 1) continue;
      var editResponseUrl = formSubmitted[0].getEditResponseUrl();
      sheet.getRange(i+1, columnIndex+1).setValue(editResponseUrl);
    }
  }
}

Additional resources

I created a gist with the content from the question and answer. this b/c I'm exploring ways to better collaborate between end-users who write code.

Best Answer

Related Solutions

Google-apps-script – Create help pop-up in Google Document using Google Apps Script

Google Sheets – Show URL to Edit Google Form Responses

Short answer

Instructions

Code

Additional resources

Related Topic