Questions on extending GAS spreadsheet usefulness

Question

I would like to offer the opportunity to view output from the same data, in a spreadsheet, TBA sidebar and, ideally another type of HTML window for output created, for example, with a JavaScript Library like THREE. The non Google version I made is a web page with iframes that can be resized, dragged and opened/closed and, most importantly, their content shares the same record object in the top window. So, I believe, perhaps naively, something similar could be made an option inside this established and popular application. At the very least, the TBA trial has shown me it useful to view and manipulate information from either sheet or TBA. The facility to navigate large building projects, clone rooms and floors, and combine JSON records (stored in depositories like myjson) for collaborative work is particularly inspiring for me.

I have tried using the sidebar for different HTML files, but the fact only one stays open is not very useful, and frankly, sharing record objects is still beyond me. So that is the main question. Whether Google people would consider an extra window type is probably a bit ambitious, but I think worth asking.

Answer 1

You can't maintain a global variable across calls to HtmlService. When you fire off an HtmlService instance, which runs in the browser, the server side code that launched it exits.

From that point control is client side, in the HtmlService code. If you then launch a server side function (using google.script.run from client side), a new instance of the server side script is launched, with no memory of the previous instance - which means that any global variables are re-initialized.

There are a number of techniques for peristing values across calls.

• The simplest one of course is to pass it to the htmlservice in the first place, then to pass it back to server side as an argument to google.script.run.
• Another is to use property service to hold your values, and they will still be there when you go back, but there is a 9k maximum entry size
• If you need more space, then the cache service can hold 100k in a single entry and you can use that in the same way (although there is a slight chance it will be cleaned away -- although it's never happened for me)
• If you need even more space, there are techniques for compressing and/or spreading a single object across several cache entries - as documented here http://ramblings.mcpher.com/Home/excelquirks/gassnips/squuezer. This same method supports Google Drive, or Google cloud storage if you need to persist data even longer

Of course you can't pass non-stringifiable objects like functions and so on, but you can postpone their evaluation and allow the initialized server side script to evaulate them, and even share the same code between server, client or across projects.

Some techniques for that are described in these articles http://ramblings.mcpher.com/Home/excelquirks/gassnips/nonstringify http://ramblings.mcpher.com/Home/excelquirks/gassnips/htmltemplateresuse

However in your specific example, it seems that the global data you want is fetched from an external api call. Why not just retrieve it client side in any case ? If you need to do something with it server side, then pass it to the server using google.script.run.

Answer 2

window.open and window.postMessage() solved both the problems I described above.

I hope you will be assured from the screenshot and code that the usefulness of Google sheets can be extended for the common good. At the core is the two methods for inputting, copying and reviewing textual data - spreadsheet for a slice through a set of data, and TBA for navigation of associations in the Trail (x axis) and Branches (y axis), and for working on Aspects (z axis) of the current selection that require attention, in collaborations, from different interests.

So, for example, a nurse would find TBA useful for recording many aspects of an examination of a patient, whereas a pharmacist might find a spreadsheet more useful for stock control. Both record their data in a common object I call 'nset' (hierarchy of named sets), saved in the cloud and available for distribution in collaborative activities.

TBA is also useful for cloning large sets of records. For example, one room, complete with furniture can be replicated on one floor, then that floor, complete with rooms can be replicated for a complete tower.

Being able to maintain parallel nset objects in multiple monitor windows by postMessage means unrivalled opportunities to display the same data in different forms of multimedia, including interactive animation, augmented reality, CNC machine instruction, IOT controls ...

Here is the related code:

From the TBA in sidebar:

 window.addEventListener("message", receiveMessage, false);

    function openMonitor(nset){
       var params = [
          'height=400',
          'width=400'
       ].join(',');
        let file = 'http://glasier.hk/blazer/model.html';
        popup = window.open(file,'popup_window', params); 
        popup.moveTo(100,100);
      }

    var popup;

    function receiveMessage(event) {
      let ed,nb;
      ed = event.data;
      nb = typeof ed === "string"? ed : nb[0];
      switch(nb){
        case "Post":
          console.log("Post");
          popup.postMessage(["Refreshing nset",nset], "http:glasier.hk"); 
          break;
        }
    }

        function importNset(){
          google.script.run
          .withSuccessHandler(function (code) {
           root = '1grsin';
           trial = 'msm4r';
           orig = 'ozs29';
           code = orig;
            path = "https://api.myjson.com/bins/"+code;
            $.get(path)
            .done((data, textStatus, jqXHR) => {
              nset = data;
              openMonitor(nset);
              cfig = nset.cfig;
              start();
              })
          })
          .sendCode();
        }

From the popup window:

     $(document).ready(function(){
        name = $(window).attr("name");
        if(name === "workshop"){
          tgt = opener.location.href;
        }
        else{
          tgt = "https://n-rxnikgfd6bqtnglngjmbaz3j2p7cbcqce3dihry-0lu-script.googleusercontent.com"
        }
        $("#notice").html(tgt);
        opener.postMessage("Post",tgt);
        $(window).on("resize",function(){
          location.reload();
        })
      })
    }

    window.addEventListener("message", receiveMessage, false);

    function receiveMessage(event) {
      let ed,nb;
      ed = event.data;
      nb = typeof ed === "string"? ed : ed[0];
      switch(nb){
        case "Post": popup.postMessage(["nset" +nset], "*"); break;
        default :
        src = event.origin;
        notice = [ed[0]," from ",src ];
        console.log(notice);
       // $("#notice").html(notice).show();
        nset = ed[1];
        cfig = nset.cfig;
        reloader(src);
      }
    }

I should explain that the html part of the sidebar was built on a localhost workshop, with all styles and scripts compiled into a single file for pasting in a sidebar html file. The workshop also is available online. The Google target is provided by event.origin in postMessage. This would have to be issued to anyone wishing to make different monitors. For now I have just made the 3D modelling monitor with Three.js.

I think, after much research and questioning around here, this should be the proper answer.

Answer 3

The best way to implement global variables in GAS is through userproperties or script properties.https://developers.google.com/apps-script/reference/properties/properties-service. If you'd rather deal with just one, write them to an object and then json.stringify the object (and json.parse to get it back).