app.js

const express = require('express'),
    app = express(),
    mysql = require('mysql'),
    joi = require('joi'),
    bodyparser = require('body-parser'),
    {google} = require('googleapis'),
    keys = require('./keys.json'),
    client = new google.auth.JWT(
        keys.client_email,
        null,
        keys.private_key,
        ['https://www.googleapis.com/auth/spreadsheets'],
    ),
    swig  = require('swig'),
    nodeMailer = require('nodemailer'),
    transporter = nodeMailer.createTransport({
        service: 'gmail',
        auth: {
            user: 'YourEmail',
            pass: 'YourPassword'
        }
    });

app.engine('html', swig.renderFile);
app.set('view engine', 'html');
app.set('views', __dirname + '/form');
app.use(bodyparser.json());
app.use(express.urlencoded());
app.use(express.json());
app.use('/css',express.static(__dirname +'/form/css'));
app.use('/js',express.static(__dirname +'/form/js'));
app.use('/images',express.static(__dirname +'/form/images'));
app.use('/webfonts',express.static(__dirname +'/form/webfonts'));
app.use('/fonts',express.static(__dirname +'/form/fonts'));

var pool = mysql.createPool({
    connectionLimit : 60,
    host:'localhost',
    user: 'root',
    password: '',
    database: 'ieee',
    multipleStatements: true
});

client.authorize(function(err, tokens) {
    if(err) {
        console.log('Google Sheets API Failed \n Error: ' + JSON.stringify(err,undefined,2));
        return;
    } else {
        console.log('Google Sheets API Connected!');
    }
});

const gsapi = google.sheets({
    version:'v4',
    auth: client
});

// app.use('/', express.static('form'));

app.get('/', (req,res) => {
    res.render('index');
});

app.post('/', function (req, res) {
    const SchemaValidation = {
        name: joi.string().min(4)
            .required()
            .error(() => {
                return {
                    message: 'Name is required. (min:4 chars)',
                };
            }),
        email: joi.string()
            .email()
            .error(() => {
                return {
                    message: 'Email field can\'t be Empty',
                };
            }),
        phone: joi.string()
            .min(8)
            .max(14)
            .required()
            .error(() => {
                return {
                    message: 'Phone Number field is Required (min: 8 characters - max: 14 characters)',
                };
            }),
        university: joi.string()
            .required()
            .error(() => {
                return {
                    message: 'University field is Required',
                };
            }),
        faculty: joi.string()
            .required()
            .error(() => {
                return {
                    message: 'Faculty field is Required',
                };
            }),
        academicyear: joi.string()
            .required()
            .error(() => {
                return {
                    message: 'Academic Year field is Required and should range from 1-6',
                };
            }),
        workshop: joi.array()
            .items(joi.string().error(() => {
                return {
                    message: 'You must pickup 2 Committees',
                };
            })),
        first_choice: joi.string()
            .required()
            .error(() => {
                return {
                    message: 'You should pickup first choice',
                };
            }),
        second_choice: joi.string()
            .required()
            .error(() => {
                return {
                    message: 'You should pickup second choice',
                };
            }),
    };

    joi.validate(req.body,SchemaValidation,(err, result) => {
        if(err) {
            res.render('index', {output: `<div class="alert alert-danger" role="alert">${err.details[0].message}</div>`});
            return; // don't try saving to db if the schema isnt valid
        }
        pool.getConnection(function(err, connection) {
            if(err) {
                console.log('Can\'t connect to MySQL Database \n Errors: ' + err);
                res.render('index', {output: `<div class="alert alert-danger" role="alert">Can't Connect to MySQL Database</div>`});
            } else {
                connection.query('INSERT INTO `form` (`name`,`email`,`phone`,`university`,`faculty`,`academic_year`,`first_choice`,`second_choice`) VALUES ("'+req.body.name+'","'+req.body.email+'","'+req.body.phone+'","'+req.body.university+'","'+req.body.faculty+'","'+req.body.academicyear+'","'+req.body.first_choice+'","'+req.body.second_choice+'")', function(err, users) {
                    if(err){
                        res.render('index', {output: `<div class="alert alert-danger" role="alert">Make sure you entered Correct Data</div>`});
                        connection.release();
                    }
                    else
                    {
                        connection.query(`SELECT id FROM form WHERE phone = '${req.body.phone}' LIMIT 1;`, function(err, rows) {
                            if(err) {
                                console.log(`MYSQL DB ERROR: Can't Find User with Phone: ${req.body.phone} \n Error: ${err}`)
                                connection.release();
                            } else {
                                let ID = rows[0].id,
                                    dataArray = [ID, req.body.name, req.body.email, req.body.phone, req.body.university, req.body.faculty, req.body.academicyear, req.body.first_choice, req.body.second_choice];
                                const insertData = {
                                    spreadsheetId: '1AoTk7I1iWiNJJojK4YNBM4A2XwpwYaMgjnUESFSc4pA',
                                    range: 'A2',
                                    valueInputOption: 'USER_ENTERED',
                                    resource: { values: [dataArray]}
                                };
                                gsapi.spreadsheets.values.append(insertData);
                                var mailOptions = {
                                    from: 'midomailer@gmail.com',
                                    to: req.body.email,
                                    subject: 'IEEE Application Submitted',
                                    html: `<html lang="en">
                                    <head>
                                        <meta charset="UTF-8">
                                        <meta name="viewport" content="width=device-width, initial-scale=1.0">
                                        <meta http-equiv="X-UA-Compatible" content="ie=edge">
                                    </head>
                                    <body>
                                        <div style='font-size:18px;'>Hello ${req.body.name} <br/>
                                            Thank you for registering, You've selected <b>${req.body.first_choice}</b> as your first preference and <b>${req.body.second_choice}</b> as your second preference, you'll be contacted soon for your interview.</div>
                                    </body>
                                    </html>`
                                };
                                transporter.sendMail(mailOptions, (error, info) => {
                                    if(error) {
                                        console.log(error);
                                    } else {
                                        console.log('Email sent: ' + info.response);
                                    }
                                });
                                res.render('success');
                                connection.release();
                            }
                        });
                    }
                });
            }
        });
    })
});

app.listen(3000);