About GRESB

/**

* GOOGLE APPS SCRIPT BACKEND

* Paste this into a Google Apps Script project attached to your Spreadsheet.

const SPREADSHEET_ID = ‘REPLACE_WITH_YOUR_SPREADSHEET_ID’;

const DRIVE_FOLDER_ID = ‘REPLACE_WITH_YOUR_FOLDER_ID’;

function doGet(e) {

const action = e.parameter.action;

if (action === ‘getDropdowns’) {

return ContentService.createTextOutput(JSON.stringify(getDropdownData()))

.setMimeType(ContentService.MimeType.JSON);

}

return HtmlService.createHtmlOutput(“NHT Group Service API Active”);

}

function doPost(e) {

try {

const data = JSON.parse(e.postData.contents);

const result = processRegistration(data);

return ContentService.createTextOutput(JSON.stringify(result))

.setMimeType(ContentService.MimeType.JSON);

} catch (error) {

return ContentService.createTextOutput(JSON.stringify({ status: ‘error’, message: error.toString() }))

.setMimeType(ContentService.MimeType.JSON);

}

function getDropdownData() {

const ss = SpreadsheetApp.openById(SPREADSHEET_ID);

const sheet = ss.getSheetByName(‘Dropdow’);

const rows = sheet.getDataRange().getValues();

const categories = [];

const providers = {};

const plans = {};

const productMap = {};

// Skip header

for (let i = 1; i < rows.length; i++) {

const [id, cat, prov, plan] = rows[i];

if (!cat || !prov || !plan) continue;

if (!categories.includes(cat)) categories.push(cat);

if (!providers[cat]) providers[cat] = [];

if (!providers[cat].includes(prov)) providers[cat].push(prov);

const key = cat + ‘|’ + prov;

if (!plans[key]) plans[key] = [];

if (!plans[key].includes(plan)) plans[key].push(plan);

productMap[cat + ‘-‘ + prov + ‘-‘ + plan] = id;

}

return { categories, providers, plans, productMap };

}

function processRegistration(data) {

const ss = SpreadsheetApp.openById(SPREADSHEET_ID);

const leadSheet = ss.getSheetByName(‘Lead’);

const folder = DriveApp.getFolderById(DRIVE_FOLDER_ID);

const timestamp = new Date();

const dateStr = Utilities.formatDate(timestamp, “GMT+7”, “yyyy-MM-dd_HH-mm-ss”);

// 1. Upload Images

const frontUrl = data.frontCard ? uploadToDrive(data.frontCard, `Front_${data.fullName}_${dateStr}.png`, folder) : “”;

const backUrl = data.backCard ? uploadToDrive(data.backCard, `Back_${data.fullName}_${dateStr}.png`, folder) : “”;

const selfieUrl = data.selfie ? uploadToDrive(data.selfie, `Selfie_${data.fullName}_${dateStr}.png`, folder) : “”;

const signatureUrl = data.signature ? uploadToDrive(data.signature, `Sig_${data.fullName}_${dateStr}.png`, folder) : “”;

// 2. Prepare Row Data

// Product logic: Try to find original Product ID from Dropdow

const productID = data.category; // Or custom mapping

const servicePlan = `${data.provider} – ${data.plan}`;

const newRow = [

timestamp,

productID,

servicePlan,

data.fullName,

data.katakana,

data.dob,

data.gender,

data.address,

data.phone,

data.email,

frontUrl,

backUrl,

selfieUrl,

signatureUrl

];

leadSheet.appendRow(newRow);

// 3. Send Email

sendConfirmationEmail(data, servicePlan);

return { status: ‘success’ };

}

function uploadToDrive(base64Data, filename, folder) {

const content = base64Data.split(‘,’)[1];

const decoded = Utilities.base64Decode(content);

const blob = Utilities.newBlob(decoded, ‘image/png’, filename);

const file = folder.createFile(blob);

// Set public permission for AppSheet reading

file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);

// Return lh3 format link

return “https://lh3.googleusercontent.com/d/” + file.getId() + “=s3000”;

}

function sendConfirmationEmail(data, planInfo) {

const subject = “[NHT Group] Xác nhận đăng ký dịch vụ thành công – ” + data.fullName;

const htmlBody = `

<h1 style=”margin: 0; font-size: 24px;”>NHT GROUP</h1>

</div>

<p>Kính gửi Anh/Chị <strong>${data.fullName}</strong>,</p>

<p>NHT Group xin chân thành cảm ơn Anh/Chị đã tin tưởng và lựa chọn dịch vụ của chúng tôi. Hệ thống đã tiếp nhận yêu cầu đăng ký của Anh/Chị với thông tin như sau:</p>

<tr>

<td style=”padding: 8px 0; border-bottom: 1px solid #f1f5f9;”>${data.category}</td>

</tr>

<tr>

<td style=”padding: 8px 0; border-bottom: 1px solid #f1f5f9;”>${planInfo}</td>

</tr>

<tr>

<td style=”padding: 8px 0; border-bottom: 1px solid #f1f5f9;”>${data.fullName}</td>

</tr>

<tr>

<td style=”padding: 8px 0; border-bottom: 1px solid #f1f5f9; font-weight: bold; color: #64748b;”>Số điện thoại:</td>

<td style=”padding: 8px 0; border-bottom: 1px solid #f1f5f9;”>${data.phone}</td>

</tr>

</table>

<p style=”margin-top: 25px;”>Nhân viên CSKH của chúng tôi sẽ liên hệ lại với Anh/Chị trong vòng 24h để hoàn tất thủ tục.</p>

</div>

<p style=”margin: 0; font-weight: bold; color: #1e293b;”>NHT Group</p>

<p style=”margin: 4px 0; font-size: 13px; color: #64748b;”>Kết Nối & Phát Triển</p>

<p style=”margin: 2px 0; font-size: 12px;”>📞 Hotline: <strong>098.888.888</strong></p>

<p style=”margin: 2px 0; font-size: 12px;”>📧 Email: <a href=”mailto:nguyenhongtuyen2512@gmail.com” style=”color: #2563eb; text-decoration: none;”>nguyenhongtuyen2512@gmail.com</a></p>

<p style=”margin: 2px 0; font-size: 12px;”>🌐 Website: nhtgroup.vn (Comming Soon)</p>

</div>

MailApp.sendEmail({

to: data.email,

subject: subject,

htmlBody: htmlBody

});

}