Forwarding Events to Google Sheets

It is simple to configure NoahFace to forward user events to Google Sheets. To do this, complete the following steps:
‍

1. From within your Google Sheet, select: Extensions / Apps Script:

‍

2. Create a script with a doPost() function which extracts and processes the event properties.

Simple Example

This simple example adds a new row to your Google Sheet for each event received:

function doPost(http) {
       var event = JSON.parse(http.postData.contents);
       var sheet = SpreadsheetApp.getActiveSheet();
       var lastRow = Math.max(sheet.getLastRow(),1);
       sheet.insertRowAfter(lastRow);
       sheet.getRange(lastRow + 1, 1).setValue(event.time);
       sheet.getRange(lastRow + 1, 2).setValue(event.number);
       sheet.getRange(lastRow + 1, 3).setValue(event.firstname);
       sheet.getRange(lastRow + 1, 4).setValue(event.lastname);
       sheet.getRange(lastRow + 1, 5).setValue(event.org);
       sheet.getRange(lastRow + 1, 6).setValue(event.site);
       sheet.getRange(lastRow + 1, 7).setValue(event.device);
       sheet.getRange(lastRow + 1, 8).setValue(event.type);
       sheet.getRange(lastRow + 1, 9).setValue(event.detail);
       SpreadsheetApp.flush();
       return HtmlService.createHtmlOutput("Success");
}

Advanced Example

This advanced example adds a new Google Sheet for each day, and adds a new row for each period of work for each user:

function doPost(http) {
        var event = JSON.parse(http.postData.contents);
        var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
        var sheet = activeSpreadsheet.getSheetByName(event.time.substring(0, 10));
        if (sheet == null) {
                // Create new sheet for today if it does not already exist
               sheet = activeSpreadsheet.insertSheet();
               sheet.setName(sheetname);
               sheet.appendRow(["Employee Number", "First name", "Last Name", "Task", "Start Time", "End Time", "Hours"]);
       } else {
                // Find current task for this user
                for (var i=sheet.getLastRow(); i>1; i--) {
                        if (sheet.getRange(i, 1).getValue() == event.number) {
                               if (sheet.getRange(i, 6).getValue() == "") {
                                        // Save end time for the task and calculate the hours
                                       sheet.getRange(i, 6).setValue(event.time);
                                        sheet.getRange(i, 7).setValue("=(F"+i+"-E"+i+")*24");
                                }
                               break;
                        }
                }
       }
        if (event.type != "clockout" && event.type != "startbreak") {
                // Record start of new task
                sheet.appendRow([event.number, event.firstname, event.lastname, event.detail, event.time]);
        }
        SpreadsheetApp.flush();
        return HtmlService.createHtmlOutput("Success");
}

For more information see: Open APIs.

‍

3. Select Deploy / New Deployment, provide a Description, provide Access to "Anyone", and select Deploy:

‍

4. Copy the Web app URL:

‍

5. Add a Notification within the NoahFace Dashboard, assign a Method of "POST" and assign your Notification url:

‍

6. Assign this Notification to your Access Point Type:

‍

That's it! Once you have completed these steps, NoahFace will automatically add all user events to your Google Sheet.