google-sheetsgoogle-apps-scriptweb-applications

How to import the number from google sheet in HTML

How to import the number from google sheet ...?

I have some number in google sheet and wants to populate in HTML web app. below is the HTML code, help with some tips

I am sharing the google sheet for reference and also attached the HTML page view (image) for better understanding.

Google Sheet

Screenshot

<!DOCTYPE html>
<html>
<title>W3.CSS Template</title>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Raleway">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
<style>
html,body,h1,h2,h3,h4,h5 {font-family: "Raleway", sans-serif}
</style>
<body class="w3-light-grey">

<!-- Top container -->
<div class="w3-bar w3-top w3-black w3-large" style="z-index:4">
  <button class="w3-bar-item w3-button w3-hide-large w3-hover-none w3-hover-text-light-grey" onclick="w3_open();"><i class="fa fa-bars"></i>  Menu</button>
  <span class="w3-bar-item w3-right">Supplier Management Team</span>
</div>

<!-- Sidebar/menu -->
<nav class="w3-sidebar w3-collapse w3-white w3-animate-left" style="z-index:3;width:300px;" id="mySidebar"><br>
  <div class="w3-container w3-row">
    <div class="w3-col s4">
    </div>
      <span>Welcome, <strong><h5 id="email"></h5></strong></span><br>
      <head>
    <base target="_top">
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
    <script>
    $(function() {

    google.script.run
      .withSuccessHandler(dispEmail)
      .getCurrentUserEmail();
     });//runs after dom is loaded
     function dispEmail(data)
     {
       $('#email').text(data.email);//put's email into h1 tag
     }
     console.log('My Code');
    </script>
  </head>
  <body>
    <h5 id="email"></h5>
      <hr>
      <a href="#" class="w3-bar-item w3-button"><i class="fa fa-envelope"></i></a>
      <a href="#" class="w3-bar-item w3-button"><i class="fa fa-user"></i></a>
      <a href="#" class="w3-bar-item w3-button"><i class="fa fa-cog"></i></a>
    </div>
  <hr>
  <div class="w3-container">
    <h5>Dashboard</h5>
  </div>
  <div class="w3-bar-block">
    <a href="#" class="w3-bar-item w3-button w3-padding-16 w3-hide-large w3-dark-grey w3-hover-black" onclick="w3_close()" title="close menu"><i class="fa fa-remove fa-fw"></i>  Close Menu</a>
    <a href="#" class="w3-bar-item w3-button w3-padding w3-blue"><i class="fa fa-users fa-fw"></i>  Overview</a>
  </div>
</nav>


<!-- Overlay effect when opening sidebar on small screens -->
<div class="w3-overlay w3-hide-large w3-animate-opacity" onclick="w3_close()" style="cursor:pointer" title="close side menu" id="myOverlay"></div>

<!-- !PAGE CONTENT! -->
<div class="w3-main" style="margin-left:300px;margin-top:43px;">

  <!-- Header -->
  <header class="w3-container" style="padding-top:22px">
    <h5><b><i class="fa fa-dashboard"></i> My Dashboard</b></h5>
  </header>

  <div class="w3-row-padding w3-margin-bottom">
    <div class="w3-quarter">
      <div class="w3-container w3-red w3-padding-16">
        <div class="w3-left"><i class=""></i></div>
        <div class="w3-right">
          <h3>52</h3>
        </div>
        <div class="w3-clear"></div>
        <h4>Open</h4>
      </div>
    </div>
    <div class="w3-quarter">
      <div class="w3-container w3-blue w3-padding-16">
        <div class="w3-left"><i class=""></i></div>
        <div class="w3-right">
          <h3>99</h3>
        </div>
        <div class="w3-clear"></div>
        <h4>Hold</h4>
      </div>
    </div>
    <div class="w3-quarter">
      <div class="w3-container w3-teal w3-padding-16">
        <div class="w3-left"><i class=""></i></div>
        <div class="w3-right">
          <h3>23</h3>
        </div>
        <div class="w3-clear"></div>
        <h4>WIP</h4>
      </div>
    </div>
    <div class="w3-quarter">
      <div class="w3-container w3-orange w3-text-white w3-padding-16">
        <div class="w3-left"><i class=""></i></div>
        <div class="w3-right">
          <h3>50</h3>
        </div>
        <div class="w3-clear"></div>
        <h4>Closed</h4>
      </div>
    </div>
  </div>



 
  <!-- Footer -->
  <footer class="w3-container w3-padding-16 w3-light-grey">
    <h6> Internal Application</h6>
  </footer>

  <!-- End page content -->
</div>

<script>
// Get the Sidebar
var mySidebar = document.getElementById("mySidebar");

// Get the DIV with overlay effect
var overlayBg = document.getElementById("myOverlay");

// Toggle between showing and hiding the sidebar, and add overlay effect
function w3_open() {
    if (mySidebar.style.display === 'block') {
        mySidebar.style.display = 'none';
        overlayBg.style.display = "none";
    } else {
        mySidebar.style.display = 'block';
        overlayBg.style.display = "block";
    }
}

// Close the sidebar with the close button
function w3_close() {
    mySidebar.style.display = "none";
    overlayBg.style.display = "none";
}
</script>

</body>
</html>
Solution

  • This is a simple example of how to get data in the spreadsheet into a simple html file. I used an object to move the data from the server to the client. I've also included the doGet if you wish to make it a web app.

    Code.gs file:

    function getVals(range)
{
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sht=ss.getActiveSheet();
  var rng=sht.getRange(range);
  var rngA=rng.getValues();
  var data = {};
  for(var i=0;i<rngA.length;i++)
  {
    data[rngA[i][0]]=rngA[i][1];
  }
  return data;
}

function displayThisDialog()
{
  var userInterface=HtmlService.createHtmlOutputFromFile('index10').setWidth(400).setHeight(450);
  SpreadsheetApp.getUi().showModelessDialog(userInterface, 'Get Vals');
}

function doGet()
{
  var html = HtmlService.createHtmlOutputFromFile('index10');
  return html.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL)
}

    index10.html file:

    <!DOCTYPE html>
<html>
  <head>
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
    <script>
    $(function() {
    google.script.run
      .withSuccessHandler(dispVals)
      .getVals('A1:B4');
     });
     function dispVals(data)
     {
         $('#val0').text('WIP= ' + data.WIP);
         $('#val1').text('Open= ' + data.Open);
         $('#val2').text('Closed= ' + data.Closed);
         $('#val3').text('Hold= ' + data.Hold);
     }
     console.log('My Code');
    </script>
  </head>
  <body>
    <h1 id="val0"></h1>
    <h1 id="val1"></h1>
    <h1 id="val2"></h1>
    <h1 id="val3"></h1>
  </body>
</html>