How to refresh tag in javascript using google app script

Question

How can i refresh my table tag without reloading my page? In my current code, it refreshing but i notice that its like refreshing the whole document. I am trying to make a filter function where it filter the date only. So when i check 1 radio button and then fetch it, the radio button i checked will refresh also(became unchecked). So how i can refresh my table only? Help me with this please. I am doing this project since January 2018.

Code.gs

function doGet() {
  return HtmlService
      .createTemplateFromFile('Index')
      .evaluate();

}

function getData() {
   return JSON.stringify(SpreadsheetApp.openById('17lKIhONfEeNogsBhKtGr4zVaLgH0_199-3J3-0tAdcE')
  .getActiveSheet()
  .getDataRange()
  .getValues());
}

function include(JavaScript) {
  return HtmlService.createHtmlOutputFromFile(JavaScript)
      .getContent();
}

function setApproved(a, b) {
  var html = HtmlService.createTemplateFromFile('Index').evaluate().getContent();
  var ss = SpreadsheetApp.openById('17lKIhONfEeNogsBhKtGr4zVaLgH0_199-3J3-0tAdcE');
  var sheet = ss.getActiveSheet();
  var dataRange = sheet.getDataRange();
  var values = dataRange.getValues();

  var headers = values[0];
  var ldapIndex = headers.indexOf('dbName');
  var idIndex = headers.indexOf('dbID');
  var statusIndex = headers.indexOf('dbStatus');
  var sheetRow;

  for( var i = 1 ; i < values.length; i++ ) {
    var row = values[i];
    if(row[idIndex] == a && row[ldapIndex] == b) { 
      sheetRow = i +1;
      break;
    }
  }

  ++statusIndex;
  sheet.getRange(sheetRow, statusIndex).setValue('Approved');

  return html;
}
function fetchData() {
var html = HtmlService.createTemplateFromFile('Index').evaluate().getContent();
return html;
}

Index.html

    <!DOCTYPE html>
    <html>
      <head>
        <base target="_top">

      </head>

      <body>
      <? var data = getData(); ?>
      <div id="Options">
        <label><b>Month</b></label>
        <select id="selectMonth">
          <option value="0">-Select Month-</option>
          <option value="1">January</option>       
          <option value="2">February</option>       
          <option value="3">March</option>       
          <option value="4">April</option>       
          <option value="5">May</option>       
          <option value="6">June</option>       
          <option value="7">July</option>       
          <option value="8">August</option>       
          <option value="9">September</option>       
          <option value="10">October</option>       
          <option value="11">November</option>       
          <option value="12">December</option>
        </select> - 
      <input type="radio" name="radioYear" id="radioYear" value="<?= new Date().getYear(); ?>"> <?= new Date().getYear(); ?>
      <input type="radio" name="radioYear" id="radioYear2" value="<?= new Date().getYear()+1; ?>"> <?= new Date().getYear()+1; ?>
      <button id="btnFetch" onclick="google.script.run.withSuccessHandler(fetchRecord).getData()">Fetch</button>
      </div>
      <div  id="tables">
            <table id="tableShift2">
            <caption>Team unknown</caption>
              <th>   Date and Time Plotted   </th>
              <th>   Name   </th>
              <th>   Date of VL   </th>
              <th>   HD/WD   </th>
              <th>   Action   </th>
            </table>
      </div>
      <div id="date"></div>
    <script
src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js">
</script>
<script>
$(function() {
  google.script.run.withSuccessHandler(showData)
      .getData();
});

function showData(things1) {
  var things = JSON.parse(things1);
  var table = $('#tableShift2');

    for (var i = 1; i < things.length; i++) {
      var monthNames = [
        "January", "February", "March",
        "April", "May", "June", "July",
        "August", "September", "October", 
        "November", "December"
      ];

      var date = new Date(things[i][1]);
      var day = date.getDate();
      var monthIndex = date.getMonth();
      var year = date.getFullYear();
      var hour = date.getHours();
      var minute = date.getMinutes();
      var second = date.getSeconds();

      if (things[i][6] == '') {
        table.append('<tr> <td>' + monthNames[monthIndex] + ' ' + day + ', ' + year + ' ' + hour + ':' + minute + ':' + second +
                 '</td> <td>' + things[i][2] +
                 '</td> <td>' + things[i][3] +
                 '</td> <td>' + things[i][4] +
                 '</td> <td ><button onclick=\'ApproveAndRefresh("' + things[i][0] + '","' + things[i][2] +
                 '")\' id="btnApprove">Approve</button> <button onclick=\'DeclineAndRefresh("' + things[i][0] + '","' + things[i][2] + 
                 '")\' id="btnDecline">Decline</button></td></tr>');

      }

  }
}
function ApproveAndRefresh(data, data1){
  google.script.run
  .withSuccessHandler(refreshData)
  .setApproved(data, data1);
}

function DeclineAndRefresh(data, data1){
  google.script.run
  .withSuccessHandler(refreshData)
  .setDeclined(data, data1);
}

function refreshData(hl){
   document.open();
   document.write(hl);
   document.close();
}

</script>

      </body>
    </html>

This is (kind of) a big question. It requires some refactoring of your current code (to make it look/work nicely). If you want to just make it work you need to use `google.script.run.withSuccessHandler().callFunc()` to get data from the server then update your HTML code with the data returned. Right now in your code `setApproved()` and `setDeclined()` both call HTMLService and reload your page. Instead, you want to return data back to the page and update the HTML using javascript. Read this [Communicate with Server Functions](https://developers.google.com/apps-script/guides/html/communication) — Chris, Feb 20 '18 at 17:29

score 1 · Accepted Answer · answered Feb 20 '18 at 17:59

You already have a showData() function that refreshes the data but doesn't refresh the page. You can use that to update your table. Currently, you have it setup to append row to your table table.append(). However, you will need to clear the table first without removing your headers as explained here.So the function would be modified like so:

function showData(things1) {
  var things = JSON.parse(things1);
  $('#tableShift2 tbody').remove(); //Remove Previous table, except headers
  var table = $('#tableShift2');
  ... //Remaining code remains the same
}

Or you can create the headers also programmatically, with table.append()
Next, you will need to modify the ApproveAndRefresh() and DeclineAndRefresh() functions so that showData() function is called on Success, like so:

function ApproveAndRefresh(data, data1){
  google.script.run
  .withSuccessHandler(showData)
  .setApproved(data, data1);
}

Finally, the setApproved and setDecline() currently return HTML content. However, they only need to return the getData() values like so:

function setApproved(a, b) {
  // You dont need the recreate the page 
  var ss = SpreadsheetApp.openById('17lKIhONfEeNogsBhKtGr4zVaLgH0_199-3J3-0tAdcE');
  var sheet = ss.getActiveSheet();
  ... // The rest of the code remains same here
  ++statusIndex;
  sheet.getRange(sheetRow, statusIndex).setValue('Approved');

  return getData()  //You just return values generated from getData()
}

Equivalently, you can just return spreadsheet data like so

 return JSON.stringfy(ss.getActiveSheet().getDataRange().getValues())

How to refresh tag in javascript using google app script

1 Answers1

Linked