Search code examples
javascripthtmlgoogle-apps-scriptgoogle-sheets

Share data from .gs (Google Apps Script) to <script> variable in html

[
  {"id":1,"label":"Node 2"},
  {"id":2,"label":"Node 3"},
  {"id":3,"label":"Node 4"},
  {"id":4,"label":"Node 5"}
]

Hi! In the following code, the function getArray() returns this string ↑. Do you know how to connect it with the variable nodes in the .html . I pasted the codes, Thanks!

function getArray(){
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = ss.getRange(1,1,ss.getLastRow()-1,2).getValues();

  let objArray = []
  data.forEach(element => {
    let obj = {}
    obj.id = element[0]
    obj.label = element[1]
    objArray.push(obj)
    return element
    }
  )
var obj = JSON.stringify(objArray);
//Logger.log(obj);
return obj;
}

<!doctype html>
<html>
<head>
  <title>Network</title>
</head>
<body>
<div id="mynetwork"></div>
<script type="text/javascript">

var nodes = [
  {"id":1,"label":"Node 2"},
  {"id":2,"label":"Node 3"},
  {"id":3,"label":"Node 4"},
  {"id":4,"label":"Node 5"}
];

</script>
</body>
</html>

Recap: My goal is use the returned "obj" in .gs as var "nodes" in .html. I don't know if the programming speech its correct. Thanks!

If some can share me the code, im going to thanks you a lot thanks!

Solution

  • Modification points:

    • In this answer, the value of nodes are retrieved by google.script.run.
    • At getArray() in GAS side, the value is returned as an object.
    • At JavaScript side, the object of nodes is used for vis.js.

    Modified script

    Google Apps Script side:

    function showBox() {
   var template = HtmlService.createTemplateFromFile("Map");
   var html = template.evaluate();
   html.setHeight(450).setWidth(650);
   SpreadsheetApp.getUi().showModalDialog(html, "New Title");
}

function getArray(){
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = ss.getRange(1,1,ss.getLastRow()-1,2).getValues();
  
  let objArray = []
  data.forEach(element => {
    let obj = {}
    obj.id = element[0]
    obj.label = element[1]
    objArray.push(obj)
    return element
    }
  )
  return objArray;  // Modified
}

    HTML and Javascript side:

    <!doctype html>
<html>
<head>
  <title>Network</title>
  <script type="text/javascript" src="https://unpkg.com/vis-network/standalone/umd/vis-network.min.js"></script>
  <style type="text/css">
    #mynetwork {
      width: 600px;
      height: 400px;
      border: 1px solid lightgray;
      margin-left: auto;
      margin-right: auto;
    }
  </style>
</head>
<body>
<div id="text"></div>
<div id="mynetwork"></div>
<script type="text/javascript">

// Modified
google.script.run.withSuccessHandler(nodes => {
  var edges = [{from: 1, to: 3}, {from: 1, to: 2}, {from: 2, to: 4}, {from: 2, to: 5}, {from: 3, to: 3}];
  var container = document.getElementById('mynetwork');
  var data = {nodes: nodes, edges: edges};
  var options = {edges: {smooth: false}, physics: {enabled: false}};
  var network = new vis.Network(container, data, options);
}).getArray();

</script>
</body>
</html>

    Reference: