Reload the table along with as button get clicked

Question

I'm working on code that I got via watching YouTube, It's an HR staff clock in and Clock out records, I've added additionally a table data in the HTML but It does not reload the table as the buttons are clicked ( we have to refresh the page to get the new records in the table), I searched for some articles and fount out this was helpful but I'm having some trouble to configure it. can I get some help to get this working?

My code

function doGet(e) {
  return HtmlService.createHtmlOutputFromFile('TimeTracker');
}

function getEmployees() { 
  var ss= SpreadsheetApp.getActiveSpreadsheet();
  var employeeSheet = ss.getSheetByName("EMPLOYEES"); 
  var getLastRow = employeeSheet.getLastRow();  
  return employeeSheet.getRange(2, 1, getLastRow - 1, 1).getValues();  
}

function clockIn(employee) {
  
  //DEFINE ALL ACTIVE SHEETS
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  //DEFINE MAIN SHEET          
  var mainSheet = ss.getSheetByName("MAIN");

  //LAST ROW ON MAIN SHEET
  var lastRow = mainSheet.getLastRow();
  
  //Define Return Variables
  var return_date = '';
  var error = 'SUCCESS';
  var return_array = [];

  for (var j = 2; j <= lastRow; j++)
  {
    // CHECK CLOCK IN
    if(employee ==  mainSheet.getRange(j, 1).getValue() && mainSheet.getRange(j,3).getValue() == '')
    {
      error = 'Need to Clock Out before Clocking In';
      return_array.push([error, return_date, employee]);
      return return_array;
    }
    
  }
  
  var new_date = new Date();
  return_date = getDate(new_date);
  
  // ADD CLOCK IN RECORD
  mainSheet.getRange(lastRow+1,1).setValue(employee)
  .setFontSize(12);
  mainSheet.getRange(lastRow+1,2).setValue(new_date)
  .setNumberFormat("MM/dd/yyyy hh:mm:ss am/pm")
  .setHorizontalAlignment("left")
  .setFontSize(12);
  
  return_array.push([error, return_date, employee]);
  return return_array;
  
}

function addZero(i) {
  if (i < 10) {
    i = "0" + i;
  }
  return i;
}

function getDate(date_in)
{
  var currentDate = date_in;
  var currentMonth = currentDate.getMonth()+1;
  var currentYear = currentDate.getFullYear();
  var currentHours = (addZero(currentDate.getHours()) > 12) ? addZero(currentDate.getHours()) - 12 : addZero(currentDate.getHours());
  var currentMinutes = addZero(currentDate.getMinutes());
  var currentSeconds = addZero(currentDate.getSeconds());
  var suffix = (addZero(currentDate.getHours()) >= 12)? 'PM' : 'AM';
  var date = currentMonth.toString() + '/' + currentDate.getDate().toString() + '/' + 
             currentYear.toString() + ' ' + currentHours.toString() + ':' +
             currentMinutes.toString() + ':' + currentSeconds.toString() + ' ' + suffix;
  
  return date;
}


function clockOut(employee) {
  
  //DEFINE ALL ACTIVE SHEETS
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  //DEFINE MAIN SHEET          
  var mainSheet = ss.getSheetByName("MAIN");

  //LAST ROW ON MAIN SHEET
  var lastRow = mainSheet.getLastRow();
  
  var foundRecord = false;
  
  var new_date = new Date();
  var return_date = getDate(new_date);
  var error = 'SUCCESS';
  var return_array = [];
  
  for (var j = 2; j <= lastRow; j++)
  {
    // FIND CLOCK IN RECORD
    if(employee ==  mainSheet.getRange(j, 1).getValue() && mainSheet.getRange(j,3).getValue() == '')
    {
      // UPDATE CLOCK IN RECORD
      mainSheet.getRange(j,3)
      .setValue(new_date)
      .setNumberFormat("MM/dd/yyyy hh:mm:ss am/pm")
      .setHorizontalAlignment("left")
      .setFontSize(12);
      var totalTime = (mainSheet.getRange(j,3).getValue() - mainSheet.getRange(j,2).getValue()) /(60*60*1000);
      mainSheet.getRange(j,4).setValue(totalTime.toFixed(2))
      .setNumberFormat("#0.00")
      .setHorizontalAlignment("left")
      .setFontSize(12);  
      foundRecord = true;     
    }
    
  }
  
  // IF NO CLOCK IN RECORD
  if(foundRecord == false)
  {
    return_array.push(['Need to Clock In First', '', employee]);
    return return_array; 
  }
  
  // CALL TOTAL HOURS
  TotalHours();
  
  return_array.push([error, return_date, employee]);
  return return_array;
}

function TotalHours()
{
  
  //DEFINE ALL ACTIVE SHEETS
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  //DEFINE MAIN SHEET          
  var mainSheet = ss.getSheetByName("MAIN");

  //LAST ROW ON MAIN SHEET
  var lastRow = mainSheet.getLastRow();
  
  //DEFINE ARRAY
  var totals = [];
  
  //LOOP THROUGH ALL RATES
  for (var j = 2; j <= lastRow; j++)
  {
    var rate = mainSheet.getRange(j, 4).getValue();
    var name = mainSheet.getRange(j, 1).getValue();
    var foundRecord = false;
    
    for(var i = 0; i < totals.length; i++)
    {
       //FOUND RECORD ADD TO TOTAL
       if(name == totals[i][0] && rate != '')
       {         
         totals[i][1] =  totals[i][1] + rate;
         foundRecord = true;
       }
    }
    
    //ADD NEW RECORD, EXISTING RECORD NOT FOUND
    if(foundRecord == false && rate != '')
    {
      totals.push([name, rate]);
    }
    
  }
  
  //CLEAR DATA
  mainSheet.getRange("F5:G1000").clear();
  
  //DISPLAY TOTALS
  for(var i = 0; i < totals.length; i++)
  {
    mainSheet.getRange(2+i,6).setValue(totals[i][0]).setFontSize(12);
    mainSheet.getRange(2+i,7).setValue(totals[i][1]).setFontSize(12);  
  } 
}

//Send Table to HTML

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



//Table Data
function getData() {
  const headerColumns = 5;  // In your case, the number of header columns is 5.

  const sheet = SpreadsheetApp.openById('15Sb-g71H6-7PPey3aQROo-oCx_ULKQq2a5kIUeLEmNY').getSheetByName("TODAY");
  const values = sheet.getRange(1, 1, sheet.getLastRow(), headerColumns).getDisplayValues();
  console.log(values)
  return values;
}

MY HTML out put

<!DOCTYPE html>
<html>
  <head>

    <base target="_top">
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css" 
    integrity="sha384-JcKb8q3iqJ61gNV9KGb8thSsNjpSL0n8PARn9HuZOnIxN0hoP+VmmDGMN5t9UJ0Z" crossorigin="anonymous">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>   
    <script>
    $( document ).ready(function() {
      getEmployees();
    });
    
    function getEmployees() 
    {
    
    google.script.run.withSuccessHandler(function(ar) 
    {
    var employeeSelect = document.getElementById("employee");
    console.log(ar);
    
    let option = document.createElement("option");
    option.value = "";
    option.text = "";
    employeeSelect.appendChild(option);
    
    ar.forEach(function(item, index) 
    {    
      let option = document.createElement("option");
      var employee = item[0];
      option.value = item[0];
      option.text = item[0];
      employeeSelect.appendChild(option);    
    });
    }).getEmployees();
    
    };
    
    function ClockIn()
    {
    
    $('#message').html("");
    var employee = document.getElementById("employee").value;
    
    if(employee != '')
    {
    
    google.script.run.withSuccessHandler(function(ar) 
    {
     console.log(ar);
    ar.forEach(function(item, index) 
    {
    
    if(item[0] == 'SUCCESS')
    {
    var message = item[2] + ' Clocked in at ' + item[1];
    $('#message').html(message);
    document.getElementById("message").className = "alert alert-primary";
    }
    else
    {
    var message = item[2] + ' ' + item[0];
    $('#message').html(message);
    document.getElementById("message").className = "alert alert-warning"; 
    }
    
     
    });
    
    }).clockIn(employee);
    
    }
    }
    
    function ClockOut()
    {
    
    $('#message').html("");
    var employee = document.getElementById("employee").value;
    
    if(employee != '')
    {
    
    google.script.run.withSuccessHandler(function(ar) 
    {
    console.log(ar);
    ar.forEach(function(item, index) 
    {
    
    if(item[0] == 'SUCCESS')
    {
    var message = item[2] + ' Clocked out at ' + item[1];
    $('#message').html(message);
    document.getElementById("message").className = "alert alert-primary";
    }
    else
    {
    var message = item[2] + ' ' + item[0];
    $('#message').html(message);
    document.getElementById("message").className = "alert alert-warning"; 
    }
        
    });
    
    }).clockOut(employee);
    
    }
    }
    </script>
  </head>
  
  <body>
  <div class="container">
  <div style="padding: 10px;" >
  <h1>WORK LOG</h1><br>
  <form>
  <div class="form-row">
  <div class="form-group col-md-4">
  <label for="employee">Employee</label>
  <select class="form-control" id="employee">
  </select>
  </div>
  </div>
  <div class="form-row">
  <div class="form-group col-md-4">
  <input type="button" value="Clock In" id="clockin" class="btn btn-primary" onclick="ClockIn()" />
  <input type="button" value="Clock Out" id="clockout" class="btn btn-primary" onclick="ClockOut()" /><br><br>
  <div class="alert alert-primary" role="alert" id="message">
  </div>
  </div> 
  </div> 
  </form>
  </div>
  
 



<table class="table">
  <? var [header, ...data] = getData(); ?>
  <thead class="thead-dark"><tr>
  <? for (var j = 0; j < header.length; j++) { ?>
    <th><?= header[j] ?></th>
  <? } ?>
  </tr></thead>

  <? for (var i = 0; i < data.length; i++) { ?>
  <tr>
    <? for (var j = 0; j < data[i].length; j++) { ?>
    <td><?= data[i][j] ?></td>
    <? } ?>
  </tr>
  <? } ?>
</table>


  </div>
  
  </body>
</html>




  [1]: https://stackoverflow.com/questions/45864415/button-action-to-retrieve-data-from-spreadsheet-using-google-app-script

Every function in a project must have a unique name and there can only be one doGet(); — Cooper, Oct 13 '20 at 05:24
@Cooper I think I've named all functions correctly, where did I go wrong? — Miyer, Oct 13 '20 at 05:39
You have two (2) `doGet()` functions defined in your script. You can only have one, remove one of them. — Aerials, Oct 13 '20 at 10:20
@Aerials when I do that my table is getting messed up. I'm getting the following message var [header, ...data] = getData(); ?> for (var j = 0; j < header.length; j++) { ?> } ?> for (var i = 0; i < data.length; i++) { ?> for (var j = 0; j < data[i].length; j++) { ?> } ?> } ?> — Miyer, Oct 13 '20 at 10:27
So which one did you remove? Did you notice they had different content? — Aerials, Oct 13 '20 at 10:39
@Aerials I removed the bottom one (which I think it connects to the table) when I did that my table got messed up. I move the getData function under the doGet function (which is right on the top) but it didn't work either. also, I tried to work on the HTML part by moving some sections but that also didn't work. — Miyer, Oct 13 '20 at 10:42
Read through this https://developers.google.com/apps-script/guides/html/templates documentation about serving templated HTML. Why did you choose to remove the second `doGet()` instead of the first? — Aerials, Oct 13 '20 at 12:57
@Aerials I chose to remove the second one because the first one had the (e) in it (which i didn't understand it's function. I thought it can mess up the entire code. — Miyer, Oct 13 '20 at 14:42
The doGet() is useful to display the html page or dialog and then if you wish to add content to the page dynamically you can use [google.script.run](https://developers.google.com/apps-script/guides/web) or if you wish to go to another page then you can use a query string and determine which page to display by looking at the [request parameters](https://developers.google.com/apps-script/guides/web#request_parameters) from within your single doGet() command they will be found in e.parameters — Cooper, Oct 13 '20 at 16:23
Here's an example of a [multipage webapp](https://stackoverflow.com/a/55770563/7215091) — Cooper, Oct 13 '20 at 16:26
@Cooper I'm not a coding expert, Yes I did read those links and scripts and also tried applying to the above code in whatever way I understood but it's not working. I'm not sure where I'm going wrong. So far I've must have edited the code more than 50 times by studying the contents... still no luck. :( — Miyer, Oct 13 '20 at 16:33
Well, If I were you I'd go back to a simpler version of the code and create a simple example adding a little bit more as I get each phase to function that way I know that the problem is always somewhere within the new stuff. Or I'd hire someone else who already knows how to do it. — Cooper, Oct 13 '20 at 16:50
@Cooper I've got what I wanted already, all works fine, but I just wanted to give that additional push to reduce one step of refreshing the entire page to get the new data in the table but to refresh itself when the data is entered in the table by clicking the clock in or Clock out button. If I were to hire a coder, how can I study coding and did all that I've done by myself? So far I've learned a lot by trying to get things to work by myself and this forum has helped me a lot. So far so good :) If I want to climb higher then I need to try harder, at this moment I've stuck a little bit here. — Miyer, Oct 13 '20 at 17:16
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/222977/discussion-between-cooper-and-muhammed-aadhil). — Cooper, Oct 13 '20 at 17:30

score 3 · Accepted Answer · answered Oct 13 '20 at 18:11

3

So the reason that you code is not working is because your not actually calling getData() on server side when either clockin or clockout buttons are clicked.

answered Oct 13 '20 at 18:11

Cooper

59,616
6
23
54

Reload the table along with as button get clicked

1 Answers1