Call bound script from external application - Google spreadsheet

Question

I want to integrate a Chrome Extension with a Google Spreadsheet.

In simple words: I want my Chrome extension to open a Spreadsheet and call one of the scripts contained in the spreadsheet. Especially JSON data (see update at the end).

• My knowledge in dealing with an extension is fine.
• Also, my knowledge with creating bound scripts in a spreadsheet.
• But, my knowledge with Google web apps, doGets and things like that is very very confused, their documentation start from a point where I'm supposed to know what they are talking about, but I don't. So I would need details about how things flow. Talking about authentication and stuff without knowing what and where the codes should be doesn't seem to bring any light.

So I need a detailed answer. And the where part is absolutely necessary.

So far, I have a very nice script that runs (the script is not the scope of this question), bound to the Spreadsheet. (The script should run only for this sheet, so it's not a problem for it to remain bound, unless for technical reasons).

The sheet needs to get some external data (which I do not control) to run the scripts with that data. If I try it from the spreadsheet using UrlFetch, I get cross domain issues and Google blocks me (if you can help me in bypassing this crossdomain issue, it would be great as well).

In a Chrome extension, attached to the page from where the data is retrieved, I can do all the requests I need and actually get the data, but then, I don't know how to send this data to the sheet.

So, I want the extension and the sheet to talk to each other. It doesn't need to be a two way talk. If just the extension can input the data in the sheet without getting an answer it works.

Is that achievable?

Is there another way of doing this?

---

Update:

With the help from @Peter Herrmann, I was able to get out of the zero. doGet and doPost are set (as shown in the end of this quesiton) and now I'm struggling to send "JSON" data to the sheet. I created both the "JSON" and "JSONP" versions in separate sheets to test.

If I type the exec link in the browser's navigation bar, it works fine (but I believe this is only a "GET", is that right? Can I send JSON via the navigation bar?).

If I use the browser's console to send a XMLHttpRequest it brings the following errors, being it the "JSON" or the "JSONP" versions of the script:

• Chrome's console: "XMLHttpRequest cannot load https://script.google.com/macros/s/xxxx/exec. Response to preflight request doesn't pass access control check: No 'Access-Control-Allow-Origin' header is present on the requested resource. Origin https://xxxxxxxxx.com is therefore not allowed access. The response had HTTP status code 405."
• Mozilla's console--- "405 Method not Allowed".

The code used for the requests is this:

var req = new XMLHttpRequest();     
req.open("POST", "https://script.google.com/macros/.../exec", true);
req.setRequestHeader("Content-Type", "application/json;charset=UTF-8");
req.send(JSON.stringify({d1:"hey", d2:"there"}));

However, using a standard "JSONP" request in a script tag like this works (but again this doesn't send JSON to the sheet, does it?):

 $("<script src='https://script.google.com/macros/s/AKfycbza11ABUxtxn-rcv-1v2ZM3uCzpARx1-t6KkPJk4rtAta_4SQc/exec?prefix=window.alert'></script>").appendTo($(document.head)).remove();

If I try to put the request code inside the script tag, it also causes the "not allowed" error.

In the sheet code, the relevant part is:

function doGet(request) {

   var result = JSON.stringify({data: 'Thanks, I received the request'});

   //JSON
   return ContentService.createTextOutput(result).setMimeType(ContentService.MimeType.JSON);

   //JSONP
   result = request.parameters.prefix + "(" + result + ");";
   return ContentService.createTextOutput(result).setMimeType(ContentService.MimeType.JAVASCRIPT);
}


function doPost(request) {

   var result = JSON.stringify({data: 'Thanks, I received the request'});

   //JSON 
   return ContentService.createTextOutput(result).setMimeType(ContentService.MimeType.JSON);

   //JSONP
   result = request.parameters.prefix + "(" + result + ");";
   return ContentService.createTextOutput(result).setMimeType(ContentService.MimeType.JAVASCRIPT);
}

Answer 1

When a Google App script is published as a web app, the special callback functions doGet() and doPost() are invoked automatically depending upon whether a GET or POST request is made to the script's URL.

So if you want an external application to interact with your google app script, you make http requests to the script's url.

For example if you write in code.gs the following and then publish it as a webapp with correct settings, anyone who hits the script's url will end up getting message Hello World because it will be a GET request to the script's url and doGet() is invoked automatically which in this case is programmed to return a string

function doGet() {
  return ContentService.createTextOutput('Hello, world!');
}

Try this url for example.

Similarly you can write code to return other mime types viz. ATOM, CSV, iCal, JavaScript, JSON, RSS, vCard, XML

Let's say if you want to return json data just change the mime type. For example if you change the code like this :

function doGet(request) {

 var employees = {"employees": [  { "firstName": "John", "lastName": "Doe"}, { "firstName": "Anna", "lastName": "Smith"} ] }

  return ContentService.createTextOutput(JSON.stringify(employees))
    .setMimeType(ContentService.MimeType.JSON);
}

After you publish it as a webapp and visit the url you will get a json output.

try this url

Output looks like {"employees":[{"firstName":"John","lastName":"Doe"},{"firstName":"Anna","lastName":"Smith"}]} which is in json format now.

So the if your chrome extension needs to communicate it will happen by making http request to the script's url.

Now coming to authentication part, you can set the required authentication while publishing the script as a web app and remember for outside apps to communicate with your app script you have to publish it.

If you want to send data to the Google App Script then publish your script as webapp and then you can send the data by making GET or POST request to the url of the published webapp.

For example you can write in code.gs like this :

function doGet(e) 
{
  Logger.log( Utilities.jsonStringify(e) );

  if (e.parameter.data) 
  {
    var dataInQueryString = e.parameter['data']

    // now we can write dataInQueryString to a spreadhseet using spreadhsheet api

    return ContentService.createTextOutput('Content received : '+ dataInQueryString);

  }
}

Now publish it as usual and invoke the url with query parameter data to see the result.

I mean : https://*published_url*?data='Test Data'

When you invoke this url in the browser which will make a GET request to the url and doGet() will be called automatically.

Try this url :

https://script.google.com/macros/s/AKfycbzl-LnNIYtOCs8iEB9FjrCSUl9cS0iy7a8JmnG-RfpOfIf7m98/exec?data=I am great

In order to send json data in querystring refer to this answer

Hope this helps!

Answer 2

Yes of course its possible. But your question as it stands, is too open ended. You need to break it down into 2 or 3 different questions, accompanied by code that you've written and tried out. Then someone could help you out, rather than them doing the entire writing themselves.

The easiest way for you to communicate with the script is to use doGet() or doPost(). It may be a little confusing, by try reading up more and doing the example shown in google's documentation. Once you get the hang of it, it'll be easy. URLFetch is designed for making requests to arbitrary URLs, so the cross domain shouldn't be because of that. Again, unless you write some code yourself, try it out and then post it here, its difficult to help out.

Answer 3

You say you want to update the spreadsheet from say a chrome extension. You can expose your update function using ContentService from within your script and then call that URL from your extension. Here's an example that sets data in a spreadsheet cell and returns a message:

function doGet(request) {

  //get the data from the request's "somedata" querystring parameter ..../exec?somedata=mydata
  var data = request.parameters.somedata;

  //update the bound spreadsheet (workaround https://code.google.com/p/google-apps-script-issues/issues/detail?id=5734)
  SpreadsheetApp.openById('1cm6tK0Io4lnbRZ0OhlvZijhrQoqCt01adyYlUbZgUZY')
  .getSheetByName('Sheet1')
  .getRange('B1')
  .setValue(data);

  //send some data back as a response
  var result = {
    data: 'Thanks, I received: ' + data,
    error: null
  };
  return ContentService.createTextOutput(JSON.stringify(result))
    .setMimeType(ContentService.MimeType.JSON);
}

To test:

1. In the code editor, Publish > Deploy as Web App
2. Choose Execute the app as me, Allow access: anyone, even anonymous
3. Click Deploy, then Authorise and Allow.
4. Copy and note the URL that is given. I got https: //script.google.com/macros/s/AKfycbyTi0NOZdTm5J_tZnUXw9skWfYlLADvkDDdCd593XC-H6LN4A/exec
5. Add ?somedata=ABC123 to the URL and hit the full URL from an incognito browser window. https://script.google.com/macros/s/AKfycbyTi0NOZdTm5J_tZnUXw9skWfYlLADvkDDdCd593XC-H6LN4A/exec?somedata=ABC123

This results in "ABC123" being set in cell B1 and the browser message: {"data":"Thanks, I received: ABC123","error":null}

Here's a link to the source spreadsheet from which you can File > Make a copy.