Trouble creating detail view tab in appscript

user_14942515 0
Viewed 54

Actually I am trying to create information Modal in google sheets which extract data which matched the code, get the data from master sheet and it should display the data in Modal, but somehow I am having issue in passing the value extracted from master sheet to html output file and display it in modal. It is showing like this in sample modal, can someone help me understand, what am I doing wrong? I am not much familiar with code though, so spare me if I am doing things entirely wrong :P

Link to sample spreadsheet :- https://docs.google.com/spreadsheets/d/1f5dGCTzMMCRqZB5hDVTbtHaxS1VNuz0IJOlCXjSNHyI/edit?usp=sharing

When user clicks the checkbox on sheet1 script runs and show the msgbox. enter image description here

function onEdit() {
  
    var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
    var Deleg = SpreadsheetApp.openById('1f5dGCTzMMCRqZB5hDVTbtHaxS1VNuz0IJOlCXjSNHyI').getSheetByName('Delegation Merged Data');
    var aRow =  ss.getActiveCell().getRow();
    var code =  ss.getRange(aRow, 1, 1, 1).getValue();
    ss.getRange(1, 3).setValue(code);
    
    var DelegData = Deleg.getRange(2, 1, Deleg.getLastRow()-1, 7).getDisplayValues();
    Logger.log(DelegData);
    
    var DelegTDetails = DelegData.filter(function(Details){
                    return Details[0] == code   ;
    });

  const htmlTemplate = HtmlService.createTemplateFromFile("Template");
  htmlTemplate.DelegTDetails = DelegTDetails;
  
  var html = HtmlService.createHtmlOutputFromFile('Template')
     .setWidth(1000)
     .setHeight(800);    
      
 SpreadsheetApp.getUi()
    .showModalDialog(html, 'Detail View'+'Test');
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
<style>
.collapsible {
  background-color: #777;
  color: white;
  cursor: pointer;
  padding: 18px;
  width: 100%;
  border: none;
  text-align: left;
  outline: none;
  font-size: 15px;
}

.active, .collapsible:hover {
  background-color: #555;
}

.collapsible:after {
  content: '\002B';
  color: white;
  font-weight: bold;
  float: right;
  margin-left: 5px;
}

.active:after {
  content: "\2212";
}

.content {
  padding: 0 18px;
  max-height: 0;
  overflow: hidden;
  transition: max-height 0.2s ease-out;
  background-color: #f1f1f1;
}
</style>
</head>
<body>

<h2>Details View for Firmware Data</h2>
<p>Collapsible Set:</p>
<button class="collapsible">Open Section 1</button>
<div class="content">
  <table style="border-collapse: collapse;
    border: 1px solid black;text-align: center;
     font-family: Arial, Helvetica, sans-serif;
  width: 100%">
        <thead>
             <tr >
                 <th style = "padding-top: 12px;
  padding-bottom: 12px;
    border: 1px solid black;
  text-align: center;
  background-color: #4CAF50;
  color: white">Delegation Code</th>
                 <th style = "padding-top: 12px;
  padding-bottom: 12px;
    border: 1px solid black;
  text-align: center;
  background-color: #4CAF50;
  color: white">Assignee</th><th style = "padding-top: 12px;
  padding-bottom: 12px;
    border: 1px solid black;
  text-align: center;
  background-color: #4CAF50;
  color: white">Assigned by</th><th style = "padding-top: 12px;
  padding-bottom: 12px;
    border: 1px solid black;
  text-align: center;
  background-color: #4CAF50;
  color: white">Task Name</th><th style = "padding-top: 12px;
  padding-bottom: 12px;
    border: 1px solid black;
  text-align: center;
  background-color: #4CAF50;
  color: white">Raised on</th><th style = "padding-top: 12px;
  padding-bottom: 12px;
    border: 1px solid black;
  text-align: center;
  background-color: #4CAF50;
  color: white">Completed on</th><th style = "padding-top: 12px;
  padding-bottom: 12px;
    border: 1px solid black;
  text-align: center;
  background-color: #4CAF50;
  color: white">Status</th>
             </tr>      
       </thead>
       <tbody>
             <? var a = 1 ;
             DelegTDetails.forEach((r,i) => { ?>
              <tr><td style = "border: 1px solid black;
  border-collapse: collapse"><?= a ?></td><td style = "border: 1px solid black;
  border-collapse: collapse"><?= r[0]?></td><td style = "border: 1px solid black;
  border-collapse: collapse"><?= r[1]?></td><td style = "border: 1px solid black;
  border-collapse: collapse"><?= r[2]?></td><td style = "border: 1px solid black;
  border-collapse: collapse"><?= r[3]?></td><td style = "border: 1px solid black;
  border-collapse: collapse"><?= r[4]?></td><td style = "border: 1px solid black;
  border-collapse: collapse"><?= r[5]?></td><td style = "border: 1px solid black;
  border-collapse: collapse"><?= r[6]?></td>
              </tr>
             <?
             a = a+1;
             }) ?>
       </tbody>
    
    </table>
</div>
<button class="collapsible">Open Section 2</button>
<div class="content">
  <p>Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat.</p>
</div>
<button class="collapsible">Open Section 3</button>
<div class="content">
  <p>Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat.</p>
</div>

<script>
var coll = document.getElementsByClassName("collapsible");
var i;

for (i = 0; i < coll.length; i++) {
  coll[i].addEventListener("click", function() {
    this.classList.toggle("active");
    var content = this.nextElementSibling;
    if (content.style.maxHeight){
      content.style.maxHeight = null;
    } else {
      content.style.maxHeight = content.scrollHeight + "px";
    } 
  });
}
</script>

</body>
</html>

google-apps-script google-sheets html javascript
1 Answers

Modification points:

  • In your script, HTML template is used. But html of SpreadsheetApp.getUi().showModalDialog(html, 'Detail View'+'Test') doesn't include the value of DelegTDetails. I thought that this might be the reason of your issue.
  • When I saw your script, const htmlTemplate = HtmlService.createTemplateFromFile("Template") is declared. But, it seems that this is not used.

In order to remove this issue, how about the following modification?

Modified script:

Please modify your Google Apps Script as follows.

From: 
 const htmlTemplate = HtmlService.createTemplateFromFile("Template");
 htmlTemplate.DelegTDetails = DelegTDetails;
 
 var html = HtmlService.createHtmlOutputFromFile('Template')
    .setWidth(1000)
    .setHeight(800);    
     
SpreadsheetApp.getUi()
   .showModalDialog(html, 'Detail View'+'Test');
To: 
const htmlTemplate = HtmlService.createTemplateFromFile("Template");
htmlTemplate.DelegTDetails = DelegTDetails;
SpreadsheetApp.getUi().showModalDialog(htmlTemplate.evaluate().setWidth(1000).setHeight(800), 'Detail View'+'Test');

Note:

  • In this modification, it supposes that the value of DelegTDetails is the value you expected. Please be careful this.

Reference:

user_71086530
Related