Google Apps Script, Google Sheet - Template Scriplets don't work

Question

I've been reading these answers and trying out some of the code, but I could not get my code to work. These are the links I've been reading:

My Code.gs:

function onOpen() {
  SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
      .createMenu('Custom Menu')
      .addItem('Show sidebar', 'showSidebar')
      .addToUi();
}
function showSidebar() {
  var html = HtmlService.createHtmlOutputFromFile('Page')
      .setTitle('My custom sidebar');
  SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
      .showSidebar(html);
}
function testCSV2() {
  const text = SpreadsheetApp.getActiveSheet().getDataRange().getDisplayValues();
  const result = cellArraysToCsv(text);
  Logger.log(result);
  return result;
}
function cellArraysToCsv(data) {
  const regex = /"/g;
  let change = data.map(row => row.map(value => `"${value.replace(regex, '\"\"')}"`)).join('\n');
  return change;
}

My Page.html:

<!DOCTYPE html>
<script>
  function answers() {
    var data = google.script.run.testCSV2();
    document.getElementById("myTitle").innerText = data;
  }
</script>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    Hello, world! <input type="button" value="Answers" onclick="answers()" />
    <H2 id="myTitle"></H2><br><br>
    <?!= testCSV2() ?>
  </body>
</html>

I'm getting very confused. Why is it that when I click on the button "Answers", I get no output? And why is <?!= testCSV2() ?> unchanged in the <body> of Page.html?

`And why is != testCSV2() ?> unchanged in the of Page.html?` Because you didn't `createTemplateFromFile()` but created `HtmlOutput` directly. — TheMaster, Jun 22 '22 at 13:58

score 2 · Accepted Answer · edited Jun 22 '22 at 13:50

2

Description

There are two part to this. Using templated HTML in which testCSV2() is run on the server as well as passing data before the HTML is displayed and using google.script.run.testCSV3() to get data from the server.

Code.gs

function onOpen() {
  var menu = SpreadsheetApp.getUi().createMenu("Test");
  menu.addItem("Show Test", "showSidebar").addToUi();
}

function showSidebar() {
  var html = HtmlService.createTemplateFromFile("HTML_Test");
  html.data = "greetings";
  html = html.evaluate();
  SpreadsheetApp.getUi().showSidebar(html);
}

function testCSV2() {
  return "hello";
}

function testCSV3() {
  return "goodbye";
}

HTML_Test

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    Hello, world! <input type="button" value="Answers" onclick="answers()" />
    <H2 id="myTitle"></H2><br><br>
    <?!= data ?><br>
    <?!= testCSV2() ?>
    <script>
      function answers() {
        google.script.run.withSuccessHandler( 
          function (data) {
            document.getElementById("myTitle").innerText = data;
          }
        ).testCSV3();
      }
    </script>
  </body>
</html>

References

edited Jun 22 '22 at 13:50

TheMaster

45,448
6
62
85

answered Jun 22 '22 at 13:16

TheWizEd

7,517
2
11
19

You provided a working example, but you didn't describe what's wrong with OP's code. – TheMaster Jun 22 '22 at 14:04
What do you mean? He's using `createHtmlOutputFromFile` when he should be using templated HTML as I've shown. – TheWizEd Jun 22 '22 at 14:13
That part is not clear. Unless one compares code and tries to figure out the difference, that part is not obvious. Personally, I think, if you only highlighted that in plain english even without any code, the answer would be perfect. Just my two cents. – TheMaster Jun 22 '22 at 14:18
I did in my description "Using templated HTML" and by reference. – TheWizEd Jun 22 '22 at 14:20

score 1 · Answer 2 · answered Jun 22 '22 at 13:01

1

google.script.run.yourfunctionName();

Use this code in JavaScript to call your function that's defined in gs file.

Reference: Communicate with server function

answered Jun 22 '22 at 13:01

Arslan Arshad

91
2

Thanks, but now the output is `undefined`. And `!= testCSV2() ?>` is still unchanged in the `` of Page.html. Something's still wrong.. Changed code: `var data = google.script.run.testCSV2();` – maxloo Jun 22 '22 at 13:21
update answers function with this code google.script.run.withSuccessHandler( function (data) { document.getElementById("myTitle").innerText = data; } ).testCSV2(); – Arslan Arshad Jun 22 '22 at 13:28

Google Apps Script, Google Sheet - Template Scriplets don't work

2 Answers2