Search code examples
javascripthtmlgoogle-apps-scriptweb-applications

Teacher Zoom Attendance Tool

I am a teacher designing a tool to take attendance from Zoom usage data (the days we live in!)

Zoom provides reports that you can copy and paste and stay formatted as a table. Like this:

Luke Name Name
lr@email.com
09/10/2020 08:22:03 AM  09/10/2020 08:33:36 AM  12
Barbara Name Name
bar@email.com
09/10/2020 08:22:12 AM  09/10/2020 08:31:57 AM  10
Joaquin Name Name Name
joa@email.com
09/10/2020 08:22:12 AM  09/10/2020 08:31:59 AM  10
Rafaella Name Name
raf@email.com
09/10/2020 08:22:18 AM  09/10/2020 08:31:55 AM  10
Andrea Name Name
and@email.com
09/10/2020 08:22:19 AM  09/10/2020 08:32:14 AM  10
Sara Name Name Name
sar@email.com
09/10/2020 08:22:20 AM  09/10/2020 08:31:56 AM  10

If this is posted into a text editor or a Google Sheet it does format into rows and columns correctly.

My code right now takes that long string of info and cuts out anything that is not an email and then checks the email against a class list in order to see who was absent. What I now want to do is to make it check for late students. Ideally the user would input the string and the time class started and it would display absent students and separately late students.

I think the part I can't figure out is how the user can copy paste the info into a prompt or into an html textarea and then correctly write it to rows and columns so I can manipulate the data.

Code:

//this function copies the info to a spreadsheet and sets formulas in order to find just emails and check them against a student list

function checkAttendance() {
 
var ss = SpreadsheetApp
var sheet = ss.getActiveSheet();
  
var response =  SpreadsheetApp.getUi().prompt("Student Attendance", "Paste emails of students who attended.", SpreadsheetApp.getUi().ButtonSet.OK_CANCEL);
  if(response.getSelectedButton() == SpreadsheetApp.getUi().Button.CANCEL) {return}
  
  var responseText = response.getResponseText();
  
  var splitText = responseText.split(" ");
  var atHandle = SpreadsheetApp.getActive().getSheetByName("Attendance Handling");
  
  atHandle.getRange(2, 1, 200, 100).clear();
  
  atHandle.getRange(2, 3).setFormula("=FILTER(B2:B, ISNA(MATCH(B2:B, A2:A, 0)))");
  
  var colNum = getColNum2("Student Email");
  var rows = sheet.getLastRow();
  
  sheet.getRange(2, colNum, rows-1, 1).copyTo(atHandle.getRange(2, 2, rows, 1));
  
  atHandle.getRange(2, 1).setFormula('=TRANSPOSE(SPLIT(E2, " "))');
  atHandle.getRange(2, 4).setFormula('=FILTER(A2:A, ISNA(MATCH(A2:A, B2:B, 0)))')
   atHandle.getRange(2, 5).setValue(responseText);
  atHandle.getRange(2, 6).setFormula('=if(AND(IFERROR(SEARCH("@", D2), "") <> "", IFERROR(SEARCH("(", D2), "") = ""), D2, "")');
   SpreadsheetApp.getActive().getSheetByName("Attendance Handling").getRange(2, 6).copyTo(SpreadsheetApp.getActive().getSheetByName("Attendance Handling").getRange(2, 6, 200, 1));
  atHandle.getRange(2, 7).setFormula('=FILTER(F2:F, F2:F <> "")');
 
  
  showAttendance()
  
}

  
function showAttendance() {
  
  var atHandle = SpreadsheetApp.getActive().getSheetByName("Attendance Handling");
   var rows1 = getLastRow(3);
  var rows2 = getLastRow(7)

  var absent = atHandle.getRange(2, 3, rows1-1, 1).getValues().join("\n");
  var present = atHandle.getRange(2, 7, rows2-1, 1).getValues().join("\n");
 
  
  var t = HtmlService.createTemplateFromFile('attendance'); // Modified
  t.Absent = absent
  t.Present = present
  
  html = t.evaluate().setWidth(600).setHeight(345); // Added

  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
    .showModalDialog(html, 'Class Attendance');
  
}

<!DOCTYPE html>
<html>

<head>
  <base target="_top">
  
 //style removed for space

</head>
<body>
<form>

  <div>
 
  Absent (in student list but not in pasted values):
    <br>
    <textarea id="tofield" name="tofield" rows="9" cols="60"><?!= Absent ?></textarea>
           <br>
  
  Guests (present in pasted values but not in student list):
    <br>
    <textarea id="tofield" name="tofield" rows="9" cols="60"><?!= Present ?></textarea>
           <br>
           
          


<p>
</p>
   </form>
   
   Note: this information is not saved. Please use or copy and paste this information as needed beforing closing.

<p>
   <button id="btn" style="margin-left: 8px; font-size: 15px; padding: 5px" onclick='google.script.host.close();' class="sbtn btn btn-secondary btn-c">Close</button>
</p>

  
</body>



</html>
Solution

  • You need to use something like google.script.run.doSomething(data) to pass values from the client-side to the server side code, where doSomething is a server side function and data is a a variable holding the value / object that you want to pass to the client side.

    Note: There are some limitations regarding what values/object types can be passed to the server-side, i.e. Date objects can't be passed but you can pase the a string o the correspoining milliseconds.

    The following code converts the sample data input into a bidimensional array that can be passed to sheet by using setValues(values).

    var data = `Luke Name Name
lr@email.com
09/10/2020 08:22:03 AM  09/10/2020 08:33:36 AM  12
Barbara Name Name
bar@email.com
09/10/2020 08:22:12 AM  09/10/2020 08:31:57 AM  10
Joaquin Name Name Name
joa@email.com
09/10/2020 08:22:12 AM  09/10/2020 08:31:59 AM  10
Rafaella Name Name
raf@email.com
09/10/2020 08:22:18 AM  09/10/2020 08:31:55 AM  10
Andrea Name Name
and@email.com
09/10/2020 08:22:19 AM  09/10/2020 08:32:14 AM  10
Sara Name Name Name
sar@email.com
09/10/2020 08:22:20 AM  09/10/2020 08:31:56 AM  10`;
const arr = data.split('\n');
const matrix = [];
for(let i = 0; i < arr.length;i = i + 3){
  var row = [arr[i],arr[i+1],
  new Date(arr[i+2].slice(0,22)),
  new Date(arr[i+2].slice(24,46)),
  arr[i+2].slice(48)]
  matrix.push(row);
}
console.info(matrix);

    I suggest you to use the above on the server side, as mentioned previously, because Date objects can't be passed from client-side to sever-side, just be sure that the Apps Script, the Spreadsheet and Zoom are using the same timezone, otherwise you will have improve it to handling the timezones differences appropiately.

    Resources