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:
Simple Single List Example
The following example assumes the sheet contains a single list where the first column contains the value for each item and the second column contains the name for each item. The first row in the sheet is assumed to contain labels and is ignored.
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);
}
Multiple List Example
The following example assumes that there are three tabs labelled "Users", "Projects", and "Work Types", each formatted in the same way as the simple single list example:
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);
}
Hierarchical List (Horizontal) Example
The following example assumes that each row contains a site name in the first column, and a list of work types for each site in subsequent columns. The number of work types may vary for each site.
This will create a hierarchical list in NoahFace, which is appropriate for use with Site, Nest, or Tree Data Entry.
function doGet() {
var list = [];
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var rows = sheet.getDataRange().getValues();
var root = {};
root.List = 1000;
root.Value = "1000";
root.Name = "Work Types";
list.push(root);
for (var i = 0; i < rows.length; i++) {
var parent = {};
parent.List = 1000;
parent.Value = (i+1).toString();
parent.Name = rows[i][0].toString();
parent.Parent = "1000";
list.push(parent);
for (var j = 1; j < rows[i].length; j++) {
var item = {};
item.List = 1000;
item.Value = rows[i][j].toString();
item.Name = rows[i][j].toString();
item.Parent = (i+1).toString();
list.push(item);
}
}
var body = {Lists: list};
return ContentService.createTextOutput(JSON.stringify(body)).setMimeType(ContentService.MimeType.JSON);
}
Hierarchical List (Vertical) Example
The following example assumes that each column contains a site name in the first row, and a list of work types for each site in subsequent rows. The number of work types may vary for each site.
This will create a hierarchical list in NoahFace, which is appropriate for use with Site, Nest, or Tree Data Entry.
function doGet() {
var list = [];
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var rows = sheet.getDataRange().getValues();
var root = {};
root.List = 1000;
root.Value = "1000";
root.Name = "Work Types";
list.push(root);
for (var j = 0; j < rows[0].length; j++) {
var parent = {};
parent.List = 1000;
parent.Value = (j+1).toString();
parent.Name = rows[0][j].toString();
parent.Parent = "1000";
list.push(parent);
for (var i = 1; i < rows.length; i++) {
if (rows[i][j] === "" || rows[i][j] === null) continue; // skip empty cells
var item = {};
item.List = 1000;
item.Value = rows[i][j].toString();
item.Name = rows[i][j].toString();
item.Parent = (j+1).toString();
list.push(item);
}
}
For more information see: Open APIs.
4. Select Deploy / New Deployment, select a type of Web App, provide a Description, provide Access to "Anyone", and select Deploy:
5. Copy the Web app URL:
Test this URL from a Web Browser to make sure it is accessible and that the returned JSON is properly formed.
6. Add a Synchronization within the NoahFace Dashboard, assign a Type of "Custom", assign your User list url, and turn on the Synchronize 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:
