Add JQuery reference to custom function

Question

I want to test calling an API in a custom function for Google Sheets. code.gs is as follows:

function TApi(input) {
  var url = "https://api.nytimes.com/svc/search/v2/articlesearch.json";
  url += '?' + $.param({
    'api-key': "cdaa59fea5f04f6f9fd8fa551e47fdc4",
    'q': "MIT"
  });
  $.ajax({
    url: url,
    method: 'GET',
  }).done(function(result) {
    return result;
    console.log(result);
  }).fail(function(err) {
    throw err;
  });
}

But when I call =TAPI() in a sheet cell, it returns an error ReferenceError: "$" is not defined. (line 22). I guess we need to add a link to JQuery. Does anyone know how to do this?

Answer 1

You can only use JQuery on client side scripts which use the HTML service. It is not available server side. There is a blurb about using it in the HTML Services Best Practices.

Answer 2

It's not possible. You must build either a web app or custom UI (sidebar or dialog) using HtmlService and do the processing on the client. Because your code runs on Google servers, there are no 'window' or 'document' objects. DOM and BOM are only accessible on the client.

In fact, feel free to do the following little experiment. Open your browser console (I'm using Chrome developer tools) and type in

console.log(this); //this logs global object

Here's the output

This is the 'window' object used by jQuery for navigating the DOM tree. jQuery is simply a JS library that builds on top of existing DOM manipulation methods and CSS selectors.

Next, open any GAS file, run the following function and check the Logs (Ctrl + Enter):

function test() {
 Logger.log(this);
}

And here's the output.

As you can see, the global object in this context consists of Google-defined pseudo classes (GAS services).

Answer 3

You can use urlFetch app. Try the below snippet

function fetchURL() {
    try {
        var url = "https://api.nytimes.com/svc/search/v2/articlesearch.json";
        url += '?api-key=cdaa59fea5f04f6f9fd8fa551e47fdc4&q=MIT';
        var params = {
            'method': 'get',
            'contentType': 'application/json',
            'muteHttpExceptions': true
        }
        var response = UrlFetchApp.fetch(url, params);
        Logger.log(response)
    } catch (e) {
        Logger.log(e)
    }
}