Connect and use an Apps Script Library ID in another Google Sheets Document

What are Google Sheets App Script Library ?

A App Script library is a App Script project whose functions can be reused in other scripts and other Google Sheets App Script Code Editors.


Steps to Publish the App Script Library

  1. Open the Google Sheets document that contains the Apps Script library you want to use
  2. Go to “Extensions” > “Apps Script” to open the Apps Script editor
  3. In the Apps Script editor, click on the menu “File” > “Manage versions
  4. Click on “Save new version” and then “OK
  5. After saving a new version, click on “Publish” > “Deploy as web app
  6. In the dialog box, choose the version you just saved, and under “Who has access“, select “Anyone, even anonymous
  7. Click “Deploy
  8. Copy the URL provided in the “Current web app URL” field

Sample Code for the External App Script Library


function getContents(uri) {
var result = UrlFetchApp.fetch(uri);
var contents = result.getContentText();
return contents;
}

function Response(uri)
{
try {
var options = {
followRedirects : true
};
var response = UrlFetchApp.fetch(uri, options);
return response.getResponseCode() ;
}
catch (error) {
return "Error";
}
}

Function to get the Page URL MetaTT or Meta Title Tag from Google Sheet Cell with Page URL Value in Another Cell Running the Formula =callMetaTT(A1) for example.

function MetaTT(uri) {
var html = getContents(uri);
const regexp = RegExp('','g');
var MetaTT = html.matchAll(regexp);
var resultsArr = []
for (const match of MetaTT) {
var clean = match[1].replace(/<[^>]+>/g, "").replace(/&/g, '&').replace(/ /g, ' ').replace(/©/g, '©');
resultsArr.push(clean);
}

return resultsArr;
}


Use The Functions from the Apps Script Library in Your Script

  1. Create an intermediary function in the new Sheet Extension’s Apps Script
  2. In your Apps Script editor, create a new function that calls the MetaTT function with the provided URL
  3. Save the script.
  4. Use the custom function in a Google Sheets cell
  5. =callMetaTT(Cell Range)

function callMetaTT() {
var uri = “https://example.com”; // Replace with the URL you want to fetch
var results = MyLibrary.MetaTT(uri);
// Logger.log(results); // Log the results to the Apps Script logger
}

This workaround allows you to indirectly call the MetaTT function from a cell in Google Sheets. However, keep in mind that there might be limitations on the number of API calls you can make, depending on your Google Workspace plan.

If you want to call the MetaTT function directly from a cell in Google Sheets and pass a URL as an argument to fetch data, you can use a custom function. Unfortunately, custom functions in Google Sheets can’t directly call Apps Script functions that use the UrlFetchApp service due to restrictions on external API calls. However, you can create an intermediary function in the Apps Script editor that calls the MetaTT function and is then called from the cell.


Set up the Apps Script

Here’s how you can set it up the Apps Script on the New Sheet to use main Library Functions

Create an intermediary function in Apps Script

In your Apps Script editor, create a new function that calls the MetaTT function with the provided URL

function callMetaTT(uri) {
return MyLibrary.MetaTT(uri);
}

  • Lets say you have the sheet ready in Column A and A1 Cell you have Page URL link which want to get the MetaTT in Column B1
  • Replace “https://example.com” with the URL you want to fetch data from with Cell Name like
  • RUn the Formula Function Script in B1 as =callMetaTT(A1) to Get the A1 Page URL MetaTitle Tag

When you enter this formula in a cell, it will call the callMetaTT function, which in turn calls the MetaTT function from your library with the provided URL.

This workaround allows you to indirectly call the MetaTT function from a cell in Google Sheets. However, keep in mind that there might be limitations on the number of API calls you can make, depending on your Google Workspace plan.


Popular Articles