All Completions
Search a worker's name to verify orientation status before allowing site access.
Orientation Videos — Vimeo IDs
Find your Vimeo video ID in the URL: vimeo.com/123456789 — paste just the numbers.
To permanently save these IDs, open the HTML file in Notepad and find:
const VIMEO_EN = '1148668650';
const VIMEO_ES = '1152034455';
Replace the placeholder text with your actual Vimeo IDs and save.
const VIMEO_EN = '1148668650';
const VIMEO_ES = '1152034455';
Replace the placeholder text with your actual Vimeo IDs and save.
💡 Make sure your Vimeo videos are set to Unlisted or Public — Private videos will not embed.
Step 1 — Create your Google Sheet
Go to sheets.google.com and create a new blank spreadsheet.
In Row 1, add these column headers exactly:
Name | Company | Project | Timestamp | Confirmation ID | Status | Certs Held | Certs Uploaded
Name the sheet tab: Completions
In Row 1, add these column headers exactly:
Name | Company | Project | Timestamp | Confirmation ID | Status | Certs Held | Certs Uploaded
Name the sheet tab: Completions
Step 2 — Add the Apps Script
In your Google Sheet, click Extensions → Apps Script. Delete any existing code, paste the script below, then save.
Also add a second tab to your Sheet named
Also add a second tab to your Sheet named
Projects with these column headers in Row 1:siteId | projectName | address | parking | checkin | orientationLocation | contactName | contactTitle | contactPhone | updatedAtfunction doPost(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var data = JSON.parse(e.postData.contents);
// ── Save project config (arrival info) ──
if (data.action === 'saveProject') {
var pSheet = ss.getSheetByName('Projects');
if (!pSheet) pSheet = ss.insertSheet('Projects');
var pRows = pSheet.getDataRange().getValues();
var siteId = data.siteId || '';
var found = -1;
for (var i = 1; i < pRows.length; i++) {
if (String(pRows[i][0]) === String(siteId)) { found = i + 1; break; }
}
var row = [
siteId,
data.projectName || '',
data.address || '',
data.parking || '',
data.checkin || '',
data.orientationLocation || '',
data.contactName || '',
data.contactTitle || '',
data.contactPhone || '',
new Date().toISOString()
];
if (found > 0) {
pSheet.getRange(found, 1, 1, row.length).setValues([row]);
} else {
pSheet.appendRow(row);
}
return ContentService.createTextOutput('OK').setMimeType(ContentService.MimeType.TEXT);
}
// ── Save completion record (existing behaviour) ──
var sheet = ss.getSheetByName('Completions');
sheet.appendRow([
data.name,
data.phone,
data.company,
data.project,
data.siteId,
data.timestamp,
data.id,
data.status,
data.quizScore,
data.certsHeld,
data.certsUploaded
]);
return ContentService.createTextOutput('OK').setMimeType(ContentService.MimeType.TEXT);
}
function doGet(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var callback = e.parameter.callback;
// ── Return project config for a specific siteId ──
if (e.parameter.action === 'getProject') {
var siteId = e.parameter.siteId || '';
var pSheet = ss.getSheetByName('Projects');
var config = {};
if (pSheet) {
var pRows = pSheet.getDataRange().getValues();
for (var i = 1; i < pRows.length; i++) {
if (String(pRows[i][0]) === String(siteId)) {
config = {
siteId: pRows[i][0],
projectName: pRows[i][1],
address: pRows[i][2],
parking: pRows[i][3],
checkin: pRows[i][4],
orientationLocation: pRows[i][5],
contactName: pRows[i][6],
contactTitle: pRows[i][7],
contactPhone: pRows[i][8],
updatedAt: pRows[i][9]
};
break;
}
}
}
var json = JSON.stringify({ project: config });
if (callback) return ContentService.createTextOutput(callback + '(' + json + ')').setMimeType(ContentService.MimeType.JAVASCRIPT);
return ContentService.createTextOutput(json).setMimeType(ContentService.MimeType.JSON);
}
// ── Return all completion records (existing behaviour) ──
var sheet = ss.getSheetByName('Completions');
var rows = sheet.getDataRange().getValues();
var records = [];
for (var i = 1; i < rows.length; i++) {
records.push({
name: rows[i][0],
phone: rows[i][1],
company: rows[i][2],
project: rows[i][3],
timestamp: rows[i][4],
id: rows[i][5],
status: rows[i][6],
quizScore: rows[i][7],
certsHeld: rows[i][8],
certsUploaded: rows[i][9],
sticker: rows[i][11] || ''
});
}
var json = JSON.stringify({ records: records });
if (callback) return ContentService.createTextOutput(callback + '(' + json + ')').setMimeType(ContentService.MimeType.JAVASCRIPT);
return ContentService.createTextOutput(json).setMimeType(ContentService.MimeType.JSON);
}
Step 3 — Deploy as a Web App
In Apps Script click Deploy → New deployment.
• Type: Web app
• Execute as: Me
• Who has access: Anyone
Click Deploy, authorize when prompted, and copy the Web app URL.
• Type: Web app
• Execute as: Me
• Who has access: Anyone
Click Deploy, authorize when prompted, and copy the Web app URL.
Step 4 — Paste the URL into the portal file
Open the HTML file in Notepad and search for:
const SHEETS_URL = '';
Paste your URL between the quotes:
const SHEETS_URL = 'https://script.google.com/macros/s/YOUR_ID/exec';
Save. Every completed orientation will now write a new row — including which certifications were held and which files were uploaded.
const SHEETS_URL = '';
Paste your URL between the quotes:
const SHEETS_URL = 'https://script.google.com/macros/s/YOUR_ID/exec';
Save. Every completed orientation will now write a new row — including which certifications were held and which files were uploaded.
Edit the text shown on the final "You're Cleared!" screen. Changes apply to both English and Spanish automatically and take effect immediately.
🏁 Completion Screen Text
🇺🇸 English
🇲🇽 Spanish
🇺🇸 English
🇲🇽 Spanish
🇺🇸 English
🇲🇽 Spanish
🇺🇸 English
🇲🇽 Spanish
✅ Completion Icon
The symbol shown inside the green circle. Works for both languages.
📞 Orientation Contact
Displayed on the completion screen so workers know who to call with questions.
🗺️ Site Arrival Info
Shown on the completion screen to help workers get to the site. Leave any field blank to hide it.
🗺️ Site Logistics Plan PDF
The PDF uploaded when this project was created is shown below. To replace it, paste a new Google Drive link. To remove it, click Clear.
No PDF set
Any Google Drive link works — /view, /preview, or sharing URL. It will be converted automatically.
💡 Changes are stored in this browser. To make them permanent across all devices, update the
T.en / T.es constants in the HTML file.