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

huangapple go评论64阅读模式
英文:

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

问题

<!DOCTYPE html>
<html>

<head>
  <base target="_top">
  <?!= HtmlService.createHtmlOutputFromFile('Client_side_Functions').getContent(); ?>
</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;
}
英文:

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:

&lt;!DOCTYPE html&gt;
&lt;html&gt;

&lt;head&gt;
  &lt;base target=&quot;_top&quot;&gt;
 &lt;?!= HtmlService.createHtmlOutputFromFile(&#39;Client_side_Functions&#39;).getContent(); ?&gt;
  &lt;!-- &lt;?!= &#39;Client_side_Functions&#39; ?&gt; --&gt;
 &lt;!-- &lt;?!= Client_side_Functions ?&gt; --&gt;
&lt;/head&gt;

&lt;body&gt;
  &lt;h1&gt;Importer Data&lt;/h1&gt;
  &lt;div id=&quot;importerData&quot;&gt;&lt;/div&gt;
&lt;/body&gt;

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

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

    importerDataDiv.appendChild(table);
  } else {
    importerDataDiv.textContent = &quot;No importer data found.&quot;;
  }
  }).getImports(importer);
}
&lt;/script&gt;
function openSideBar() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const painelSht = ss.getSheetByName(&#39;Painel&#39;);
  const importer = painelSht.getRange(row, 1).getValue();
  const js = &#39;Client_side_Functions&#39;;
  const html2 = HtmlService.createTemplateFromFile(js);
  html2.importer = JSON.stringify(importer);
  const html2Content = html2.evaluate().getContent();
  const htmlWindow = HtmlService.createTemplateFromFile(&quot;Sidebar&quot;);
  htmlWindow[js] = html2Content;
  const sidebarHtml = htmlWindow.evaluate().getContent();
  SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutput(sidebarHtml));
}

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

答案1

得分: 2

以下是您要翻译的内容:

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:

&lt;!DOCTYPE html&gt;
&lt;html&gt;

&lt;head&gt;
  &lt;base target=&quot;_top&quot;&gt;
  &lt;?!= Client_side_Functions ?&gt;
&lt;/head&gt;

&lt;body&gt;
  &lt;h1&gt;Importer Data&lt;/h1&gt;
  &lt;div id=&quot;importerData&quot;&gt;&lt;/div&gt;
&lt;/body&gt;

&lt;/html&gt;

Client_side_Functions.html:

&lt;script&gt;
var importer = &lt;?!= importer ?&gt;;
function displayImporterData(importer) {
  google.script.run.withSuccessHandler(function (importerData) {
    var importerDataDiv = document.getElementById(&quot;importerData&quot;);
    if (importerData &amp;&amp; importerData.length &gt; 0) {
      var table = document.createElement(&quot;table&quot;);
      var thead = document.createElement(&quot;thead&quot;);
      var tbody = document.createElement(&quot;tbody&quot;);
      // Table headers
      var headerRow = document.createElement(&quot;tr&quot;);
      var headers = [&quot;Exporter&quot;, &quot;Items&quot;, &quot;Gross Weight (Kg)&quot;, &quot;Date&quot;];
      headers.forEach(function (header) {
        var th = document.createElement(&quot;th&quot;);
        th.textContent = header;
        headerRow.appendChild(th);
      });
      thead.appendChild(headerRow);
      table.appendChild(thead);

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

      importerDataDiv.appendChild(table);
    } else {
      importerDataDiv.textContent = &quot;No importer data found.&quot;;
    }
  }).getImports(importer);
}
displayImporterData(importer);
&lt;/script&gt;

Reference:

英文:

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:

&lt;!DOCTYPE html&gt;
&lt;html&gt;

&lt;head&gt;
  &lt;base target=&quot;_top&quot;&gt;
  &lt;?!= Client_side_Functions ?&gt;
&lt;/head&gt;

&lt;body&gt;
  &lt;h1&gt;Importer Data&lt;/h1&gt;
  &lt;div id=&quot;importerData&quot;&gt;&lt;/div&gt;
&lt;/body&gt;

&lt;/html&gt;

Client_side_Functions.html:

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

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

      importerDataDiv.appendChild(table);
    } else {
      importerDataDiv.textContent = &quot;No importer data found.&quot;;
    }
  }).getImports(importer);
}
displayImporterData(importer);
&lt;/script&gt;

Reference:

huangapple
  • 本文由 发表于 2023年5月17日 07:13:13
  • 转载请务必保留本文链接:https://go.coder-hub.com/76267638.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定