Tutorial: Create and Distribute Automated Mailing List with Google Sheets & Google Apps Script

Use As Library

Access to the sheet file

To share the Apps Script code from a sheet as a library is quite easy. We will call the sheet with the code inside that we want to share as a library the Library Sheet.

A user needs to have access to the Library Sheet in order to be able to import the library. In Google Drive, you have to right click the sheet file, click on Share in the menu, then on Share again in the sub-menu. Only users with access to the sheet can use the library.

If you want to give anyone access, without them being able to edit the your file, you can select “Anyone with the link” under General Access. Set role as Viewer.

Share Script ID

Go to the Apps Script environment of the Library Sheet and click on Project Settings in the menu on the left, the cog symbol. Here you can find the Script ID, which is the only ID you need to share to another sheet if that needs/wants to import the library.

Import Library

If we open another sheet that wants to use the library from the Library Sheet, go to the Apps Script environment of that other sheet and click on the + symbol behind Libraries. Paste the Script ID from the Library Sheet and press the Look up button.

Here you can select a version and give the library an identifier. If the version says HEAD, it means it directly uses the latest saved version of the actual scripts from the Library Sheet. Any other version number means that it refers to an immutable version (snapshot) of the library.

Create Version

Back to the Library Sheet, go to the Apps Script environment. At the top of the page you see a deploy button. Click on it and select new Deployment.

On the cog symbol after Select type, select Library, give a description and click deploy.

Select Version

Back to the sheet that wants to import the library. Under libraries (in Apps Script environment), click on the imported library name from before to be able to select the newly created version from the step before.

Create another Version

Back to the library file. The first time that we created a snapshot, we created a new library deployment. For a version update, do not make a new deployment, but rather click Manage Deployments. Select the one we just made under Active. Click on the Edit button, the pencil symbol. Select New Version from the dropdown menu there and deploy.

Deployments and Versions Explained

As far as I know, a Version cannot exist without a Deployment pointing to it. A Deployment is a reference for the outside world that points to a Version. A Version is an immutable snapshot of the scripts. If you would create more than one Deployment, they can both “see” all Versions there are, and point to one. In the context of sharing and importing a library as we discussed, it does not regard Deployments, it only considers Versions inside the file.

A Deployment is for example needed for an API Executable type of deployment, where the address of the deployment points to an Version. This address is a URL with a specific deployment ID inside.

Furthermore, creating a another new library deployment is not necessary in this context. You do want to create at least one version snapshot though, so the importing sheets can point to a safe and immutable version. If something changes in the HEAD, it won’t affect or break anything.

Calling Library Functions From The Library Importing Sheet

This is from the perspective of the library importing sheet.

// Facade to librart functions
function CreateMailingList() {
  try {
    // MailingListScript is the exact identifier (name) of the imported Library
    MailingListScript.CreateMailingList();
  }
  catch(error) {
    SpreadsheetApp.getUi().alert(`Error ${error}`);
  }
}

// Facade to librart functions
function CreateMailingList() {
  try {
    // MailingListScript is the exact identifier (name) of the imported Library
    MailingListScript.CreateMailingList();
  }
  catch(error) {
    SpreadsheetApp.getUi().alert(`Error ${error}`);
  }
}