Search code examples
google-apps-scriptsidebar

How to open a sidebar with data from a sheet, passing a variable from server to client-side in Google Apps Script?

The page is to be open and a function should build a table with the data obtained from a sheet. The variable value doesn't seem to get through, nor does this give me any error:

<!DOCTYPE html>
<html>

<head>
  <base target="_top">
 <?!= HtmlService.createHtmlOutputFromFile('Client_side_Functions').getContent(); ?>
  <!-- <?!= 'Client_side_Functions' ?> -->
 <!-- <?!= Client_side_Functions ?> -->
</head>

<body>
  <h1>Importer Data</h1>
  <div id="importerData"></div>
</body>

</html>

<script>
var importer = <?= JSON.stringify(importer) ?>;
function displayImporterData(importer) {
  google.script.run.withSuccessHandler(function(data){
  var importerDataDiv = document.getElementById("importerData");
   if (importerData && importerData.length > 0) {
    var table = document.createElement("table");
    var thead = document.createElement("thead");
    var tbody = document.createElement("tbody");
    // Table headers
    var headerRow = document.createElement("tr");
    var headers = ["Exportador", "Items", "Peso Bruto (Kg)", "Data"];
    headers.forEach(function(header) {
      var th = document.createElement("th");
      th.textContent = header;
      headerRow.appendChild(th);
    });
    thead.appendChild(headerRow);
    table.appendChild(thead);

    //Table body - importer data
    importerData.forEach(function(rowData) {
      var row = document.createElement("tr");
      rowData.forEach(function(cellData) {
        var td = document.createElement("td");
        td.textContent = cellData;
        row.appendChild(td);
      });
      tbody.appendChild(row);
    });
    table.appendChild(tbody);

    importerDataDiv.appendChild(table);
  } else {
    importerDataDiv.textContent = "No importer data found.";
  }
  }).getImports(importer);
}
</script>

function openSideBar() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const painelSht = ss.getSheetByName('Painel');
  const importer = painelSht.getRange(row, 1).getValue();
  const js = 'Client_side_Functions';
  const html2 = HtmlService.createTemplateFromFile(js);
  html2.importer = JSON.stringify(importer);
  const html2Content = html2.evaluate().getContent();
  const htmlWindow = HtmlService.createTemplateFromFile("Sidebar");
  htmlWindow[js] = html2Content;
  const sidebarHtml = htmlWindow.evaluate().getContent();
  SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutput(sidebarHtml));
}


function getImports(importer) {
  const exportsFileId = 'fileID';
  const exportsFile = SpreadsheetApp.openById(exportsFileId);
  const exportsSht = exportsFile.getSheetByName('Sheet1');
  const exportsData = exportsSht.getDataRange().getValues().filter(e => e[1].indexOf(importer) !== -1);
  return exportsData;
}
Solution

  • Modification points:

    • In your HTML template, Client_side_Functions is not used from htmlWindow[js] = html2Content; of Google Apps Script side.
    • In your Javascript, displayImporterData is not run.
    • data is not used.

    When these points are reflected in your script, how about the following simple modification? I thought that in this case, your Google Apps Script can be used.

    Sidebar.html:

    <!DOCTYPE html>
<html>

<head>
  <base target="_top">
  <?!= Client_side_Functions ?>
</head>

<body>
  <h1>Importer Data</h1>
  <div id="importerData"></div>
</body>

</html>

    Client_side_Functions.html:

    <script>
var importer = <?!= importer ?>;
function displayImporterData(importer) {
  google.script.run.withSuccessHandler(function (importerData) {
    var importerDataDiv = document.getElementById("importerData");
    if (importerData && importerData.length > 0) {
      var table = document.createElement("table");
      var thead = document.createElement("thead");
      var tbody = document.createElement("tbody");
      // Table headers
      var headerRow = document.createElement("tr");
      var headers = ["Exportador", "Items", "Peso Bruto (Kg)", "Data"];
      headers.forEach(function (header) {
        var th = document.createElement("th");
        th.textContent = header;
        headerRow.appendChild(th);
      });
      thead.appendChild(headerRow);
      table.appendChild(thead);

      //Table body - importer data
      importerData.forEach(function (rowData) {
        var row = document.createElement("tr");
        rowData.forEach(function (cellData) {
          var td = document.createElement("td");
          td.textContent = cellData;
          row.appendChild(td);
        });
        tbody.appendChild(row);
      });
      table.appendChild(tbody);

      importerDataDiv.appendChild(table);
    } else {
      importerDataDiv.textContent = "No importer data found.";
    }
  }).getImports(importer);
}
displayImporterData(importer);
</script>

    Reference: