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?

score 1 · Answer 1 · answered Aug 25 '17 at 15:44

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.

answered Aug 25 '17 at 15:44

Karl_S

3,364
2
19
33

score 1 · Answer 2 · answered Aug 25 '17 at 15:58

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

score 0 · Accepted Answer · answered Aug 25 '17 at 16:15

0

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

answered Aug 25 '17 at 16:15

Ritesh Nair

3,327
1
17
24

I added `return JSON.stringify(response)` after `Logger.log(response)`, but in the cell I can only see `{}`. – SoftTimur Aug 25 '17 at 16:50
1

Try `Logger.log(JSON.stringify(JSON.parse(response)));` – Ritesh Nair Aug 25 '17 at 16:53
indeed, `return JSON.stringify(JSON.parse(response))` works whereas `return response` does not works. Do you know why it is like this? – SoftTimur Aug 25 '17 at 16:58
But with `return response`, the cell having `=fetchURL()` has empty value. – SoftTimur Aug 25 '17 at 17:02
I found it, it is probably because [`response` is a stream object](https://stackoverflow.com/a/45175191/702977). – SoftTimur Aug 25 '17 at 17:17
1

I think you are mixing things up, you are using google spreadsheet custom function and it expects the output to be string or array. In your case the urlfetchapp returns object and the object can't be displayed in cell. You can do this `response.toString()` – Ritesh Nair Aug 25 '17 at 17:54
`response.toString()` works. I know an object can't be displayed in cell, but that does not explain why `return JSON.stringify(response)` gives `{}` in cell. – SoftTimur Aug 25 '17 at 18:43
1

[Check this](https://stackoverflow.com/questions/16196338/json-stringify-doesnt-work-with-normal-javascript-array). – Ritesh Nair Aug 25 '17 at 18:47

Add JQuery reference to custom function

3 Answers3