.png)
It is simple to configure NoahFace to sync a lookup list (eg: Projects, Customers, Jobs, or Work Types) from Google Sheets. To do this, complete the following steps:
1. Create a Google Sheet containing your lookup list and select: Extensions / Apps Script:
2. Create a script with a doGet() function which constructs and returns your list as a JSON array:
function doGet() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var rows = sheet.getDataRange().getValues();
var list = [];
for (var i = 1; i < rows.length; i++) {
var item = {};
item.List = 1;
item.Value = rows[i][0].toString();
item.Name = rows[i][1];
list.push(item);
}
var body = {Lists: list};
return ContentService.createTextOutput(JSON.stringify(body)).setMimeType(ContentService.MimeType.JSON);
}
A more complex example can be found below that returns the list of users, projects, and work types from tabs labelled "Users", "Projects", and "Work Types" respectively:
function doGet() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Users");
var rows = sheet.getDataRange().getValues();
var users = [];
for (var i = 1; i < rows.length; i++) {
var user = {};
user.SyncGuid = rows[i][0].toString();
user.UserNumber = rows[i][0].toString();
user.FirstName = rows[i][1];
user.LastName = rows[i][2];
user.UserType = rows[i][3];
user.Email = rows[i][4];
user.Site = rows[i][5];
user.Groups = rows[i][6].split(";");
users.push(user);
}
sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Projects");
rows = sheet.getDataRange().getValues();
var list = [];
for (var i = 1; i < rows.length; i++) {
var item = {};
item.List = 1;
item.Value = rows[i][0].toString();
item.Name = rows[i][1];
list.push(item);
}
sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Work Types");
rows = sheet.getDataRange().getValues();
for (var i = 1; i < rows.length; i++) {
var item = {};
item.List = 2;
item.Value = rows[i][0].toString();
item.Name = rows[i][1];
list.push(item);
}
var body = {Users: users, Lists: list};
return ContentService.createTextOutput(JSON.stringify(body)).setMimeType(ContentService.MimeType.JSON);
}
For more information see: Open APIs.
4. Select Deploy / New Deployment, provide a Description, provide Access to "Anyone", and select Deploy:
5. Copy the Web app URL:
6. Add a Synchronisation within the NoahFace Dashboard, assign a Type of "Custom", assign your User list url, and turn on the Synchronise global pick lists switch:
7. You can then reference your lookup list when you are setting up your Access Point Type:
8. When users clock in, they will then be able to select from your lookup list:
