#JP27 Membuat Quiz Dengan Apps Script (Soal acak)

Membuat Quiz Dengan Apps Script (Soal acak)

*Untuk Mendapatkan Full Script tanpa password silahkan Klik Disini*

1. Copy Spreadsheet (Klik disini)

2. Pada sheet Database ada beberapa tabel

Penjelasan : 

Kolom Index : Tulis nomer pertanyaan

Kolom Pertanyaan : Tulis pertanyaan sesuai jumlah jawaban

Kolom Jawaban : Tulis jawaban secara berurutan

Kolom Skor : Tulis/Sesuikan skor/nilai

Kolom Koreksi : Centang jawaban yang benar

3. Pilih menu Ekstesi lalu pilih Apps script

4. Terdapat 4 file pada lembar kerja Apps Script yaitu

• Code.gs
• index.html
• js.html
• css.html
• sidebar.html

5. Copy dan pastekan script di bawah ini ke Code.gs

Masukkan Password Untuk Melihat Script (Password ada di dalam video)

function include(filename) {
  return HtmlService.createTemplateFromFile(filename).evaluate().getContent()
}
function getQuestions(){
    var sheetName = "Database"
    var ws = SpreadsheetApp.getActive().getSheetByName(sheetName)
    var dataRange = ws.getDataRange()
    var values = dataRange.getValues()
    var questions = {}
    
    for (var i = 1; i < values.length; i ++){
        var rowData = values[i]
        var key = rowData[0]
        var title = rowData[1]
        var option = rowData[2]
        var point = rowData[3]
        var correct = rowData[4]
        if (questions[key]){
            questions[key].options.push(option)
            if (correct){
                questions[key].corrects.push(option)
            }
        }else{
            questions[key] = {
                id: key,
                title: title,
                options: [option],
                point: point,
                corrects: correct ? [option]:[],
                selected: [],
                disabled: false,
                correct: true,
            }
        } 
    }
    
    questions = Object.keys(questions).map(function(key){
        return questions[key]
    })
    Logger.log(questions)
    return questions
}


function getEmails(){
    var sheetName = "Results"
    var ws = SpreadsheetApp.getActive().getSheetByName(sheetName)
    var values = ws.getDataRange().getValues()
    var emails = []
    values.forEach(function(row, index){
        if (index > 0){
            var email = row[1].toString().toLowerCase().trim()
            if (emails.indexOf(email) === -1){
                emails.push(email)
            }
        }
    })
    return emails
}

function getData(){
    var docProps = PropertiesService.getDocumentProperties()
    var count = docProps.getProperty("count") || 10
    var time = docProps.getProperty("time") || 30
    var questions = getQuestions()
    var emails = getEmails()
    data = {
        config: {
            count: count,
            time: time
        },
        questions: questions,
        emails: emails
    }
    return data
}

function doGet(e){
  var html = HtmlService.createTemplateFromFile("index").evaluate()
  var title = "Quiz Sheet App"
  html.setTitle(title)
  html.addMetaTag("viewport", "width=device-width, initial-scale=1.0")
  html.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL)
  return html
}



function loadConfiguration(){
    var docProps = PropertiesService.getDocumentProperties()
    var count = docProps.getProperty("count")
    if (count){
        count = parseInt(count)
    }else{
        count = 10
        docProps.setProperty("count", count)
    }
    var time = docProps.getProperty("time")
    if (time){
        time = parseInt(time)
    }else{
        time = 30
        docProps.setProperty("time", time)
    }
    var config = {count: count, time: time}
    return config
}


function saveConfiguration(config){
    var docProps = PropertiesService.getDocumentProperties()
    Object.keys(config).forEach(function(key){
        docProps.setProperty(key, config[key])
    })
}


function saveData(result){
    var sheetName = "Results"
    var ws = SpreadsheetApp.getActive().getSheetByName(sheetName)
    var rowContents = [new Date()].concat(result)
    var headers = ["Timestamp", "Email", "Score"]
    ws.getRange(1, 1, 1, headers.length).setValues([headers])
    ws.appendRow(rowContents)
}

function showSidebar(){
    var title = "Quiz Sheet App Configuration"
    var userInterface = HtmlService.createTemplateFromFile("sidebar").evaluate().setTitle(title)
    SpreadsheetApp.getUi().showSidebar(userInterface)
}

function openQuizSheetApp(){
    var url = ScriptApp.getService().getUrl()
    var html = "<script>window.open('https://script.google.com/macros/s/AKfycbyhOxGZVkzdh3gxRKG_gebdEHeKireMHTcns1PEiVytCIqypCD_WUta51BtI71JxSfs/exec');google.script.host.close();</script>"
    var userInterface = HtmlService.createTemplate(html).evaluate().setTitle("Opening...")
    SpreadsheetApp.getUi().showSidebar(userInterface)
}


function onOpen(){
    SpreadsheetApp.getUi().createMenu("App")
        .addItem("Open quizz sheet app", "openQuizSheetApp")
        .addItem("Configuration", "showSidebar")
        .addToUi()
}

6. Copy dan pastekan script di bawah ini ke index.html

<!DOCTYPE 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">
    <script src="https://cdn.jsdelivr.net/npm/vue/dist/vue.js"></script>
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
    <!-- <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css"> -->
    <link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
    <link rel="stylesheet" href="https://www.w3schools.com/lib/w3-theme-blue.css">

    <?!= include("css") ?>
</head>
<body>
    <div id="app" v-cloak>
        <header class="w3-card w3-theme">
            <div class="w3-bar">
                <p class="w3-bar-item w3-mobile"><b>{{title}}</b></p>
                <p class="w3-bar-item w3-tag w3-round w3-theme-l5 w3-margin-right w3-mobile">
                    <input
                        class="w3-mobile w3-center"
                        type="email" 
                        style="border: none; background: transparent; outline: none; padding: 0px;" 
                        v-model="email" 
                        placeholder="Your email address"
                        :disabled="start"
                        required
                    >
                </p>
                <template v-if="!start">
                    <p class="w3-bar-item w3-button w3-round w3-theme-d4 w3-margin-right w3-mobile" 
                        @click="startQuiz"
                    >Start Quiz</p>
                </template>
                <template v-else>
                    <p class="w3-bar-item w3-button w3-round w3-theme-d4 w3-margin-right w3-mobile"
                        @click="endQuiz"
                    >End Quiz</p>
                    <p class="w3-bar-item w3-tag w3-round w3-theme-l4 w3-margin-right w3-mobile" >{{config.count}} Questions</p>
                    <p class="w3-bar-item w3-tag w3-round w3-theme-l4 w3-margin-right w3-mobile">{{timer}}</p>
                </template>
            </div>

        </header>
        
        <main v-if="selectedQuestions">
            <div class="w3-card w3-container w3-padding" 
                v-for="(q, qIndex) in selectedQuestions" :key="q.id">
                <label class="w3-block w3-mobile w3-container w3-padding w3-round" :class="q.correct ? 'w3-theme-l3': 'w3-pale-red'">
                    <span class="w3-tag w3-theme w3-round">{{qIndex + 1}} </span>
                    <b>{{q.title}}</b>
                    <span class="w3-tag w3-theme-l4 w3-round">{{q.point}}</span>
                    <span v-if="q.corrects.length > 1" class="w3-tag w3-red w3-round">Bisa Pilih lebih dari 1 Jawaban</span>
                    <span class="w3-tag w3-theme-l4 w3-round" v-else>Pilih 1 Jawaban</span>
                </label>
                <template v-if="q.corrects.length > 1">
                    <template v-for="(option, i) in q.options">
                        <label class="w3-block w3-mobile">
                            <input class="w3-check" type="checkbox" v-model="q.selected" :name="q.id" :value="option" :disabled="q.disabled">
                        {{option}}</label>
                    </template>
                </template>
                <template v-else>
                    <template v-for="(option, i) in q.options">
                        <label class="w3-block w3-mobile">
                            <input class="w3-radio" type="radio" v-model="q.selected" :name="q.id" :value="option" :disabled="q.disabled">
                        {{option}}</label>
                    </template>
                </template>
            </div>
        </main>
        
        <footer v-if="selectedQuestions">
            <div class="w3-container w3-theme">
                <p class="w3-center w3-mobile">End of the quizz</p>
                <p class="w3-small w3-mobile">Powered by Google Apps Script and wwwjavabitpro.com</p>
            </div>
        </footer>
        
        <div class="message" v-if="message">
            <div class="w3-card">
                <div class="w3-padding" :class="message.type=='Error' ? 'w3-red': 'w3-theme'">
                    {{message.type}}
                </div>
                <div class="w3-padding">
                    <p class="w3-small">{{message.message}}</p>
                    <button class="w3-button w3-round" 
                        :class="message.type=='Error' ? 'w3-red': 'w3-theme'" 
                        @click="closeMessageBox">OK</button>
                </div>
            </div>
        </div>
        
        <div class="message loading" v-if="submitting">
            
        </div>
        
    </div>
    <?!= include("js") ?>
</body>
</html>

<style>.footer,.generic-footer{margin-bottom:98px}@media (min-width:52px){.footer,.generic-footer{margin-bottom:78px}}@media (min-width:52px){.footer,.generic-footer{margin-bottom:56px}}@media (min-width:52px){.footer,.generic-footer{margin-bottom:0}}.disclaimer{position:fixed;z-index:9999999;bottom:0;right:0;border-top:2px solid #ff5c62;text-align:center;font-size:14px;font-weight:400;background-color:#fff;padding:5px 10px 5px 10px}.disclaimer a:hover{text-decoration:underline}@media (min-width:52px){.disclaimer{text-align:right;border-left:2px solid red;border-top-left-radius:10px}}@media (min-width:1920px){.disclaimer{width:20%}}</style><div class="disclaimer">Version.01.05.22 @Copyright <a title="https://www.javabitpro.com/" target="_blank" href="https://www.javabitpro.com/" style="color: black;"><b>www.javabitpro.com</b></a></div>

7. Copy dan pastekan script di bawah ini ke js.html

<script>
    function randomSelectArray(arr, count){
        if (count > arr.length){
            count = arr.length
        }
        
        let data = arr.slice()
        
        let newArr = []
        for (let i = 0; i < count; i ++) {
            let newItem = data.splice(Math.floor(Math.random() * data.length), 1)
            newArr = newArr.concat(newItem)
        }
        return newArr
    }
    
    
    function formatTime(seconds){
        let h = Math.floor(seconds / 3600)
        if (h < 10){
          h = "0" + h
        }
        let m = Math.floor((seconds % 3600) / 60)
        if (m < 10){
          m = "0" + m
        }
        let s = (seconds % 3600) % 60
        if (s < 10){
          s = "0" + s
        }
        return [h, m, s].join(":")
    }
    
    let data = {
        message: null,
        submitting: false,
        start: false,
        title: "Quiz Sheet",
        countDown: null,
        counter: 0,
        email: "",
        config: {},
        questions: null,
        emails: [],
        selectedQuestions: null
    }
    
    let computed = {
        timer: function(){
            return formatTime(this.counter)
        }
    }
    
    
    let methods = {
        startQuiz: function(){
            if (this.email === ""){
                this.message = {
                    type: "Error",
                    message: "Email Wajib di isi"
                }
            }else if(this.emails.includes(this.email.toString().trim().toLowerCase())){
                this.message = {
                    type: "Error",
                    message: `Alamat Email ${this.email} sudah digunakan, Silahkan menggunakan email yang lain.`
                }
            }else{
                this.start = true
                this.counter = this.config.time * 60
                let selectedQuestions = randomSelectArray(this.questions, this.config.count)
                selectedQuestions.forEach(function(item){
                    item.disabled = false
                    item.selected = []
                    item.correct = true
                })
                
                this.selectedQuestions = selectedQuestions
                
                this.countDown = setInterval(function(){
                    this.counter -= 1 
                    if (this.counter === 0){
                        clearInterval(this.countDown)
                        this.endQuiz()
                    }
                }.bind(this), 1000)
            }
        },
        endQuiz: function(){ 
            this.start = false
            this.submitting = true
            clearInterval(this.countDown)
            let totalScore = 0
            let tempArray
            this.selectedQuestions.forEach((q)=>{
                if (typeof(q.selected) !== "object"){
                    tempArray = [q.selected]
                }else{
                    tempArray =  [... q.selected]
                }

                if (tempArray.join() == q.corrects.join()){
                    console.log(true)
                    q.correct = true
                    totalScore += q.point
                }else{
                    console.log(false)
                    q.correct = false
                }
                
                q.disabled = true
            })
            let result = [this.email,totalScore]
            google.script.run
                .withSuccessHandler(function(){
                    this.submitting = false
                    this.message = {
                        type: "Confirmation",
                        message: `Thank you, ${this.email}, your total score is ${totalScore}. The wrong items are highlighted in red`
                    }
                    this.emails = [...this.emails, this.email]
                    this.email = ""
                }.bind(this))
                .withFailureHandler(function(e){
                    this.submitting = false
                    this.message = {
                        type: "Error",
                        message: e.message
                    }
                }.bind(this))
                .saveData(result)
            
        },
        closeMessageBox: function(){
            this.message = null
        }
    }
    
    
    
    google.script.run
        .withSuccessHandler(function(appData){
            data.config = appData.config
            data.questions = appData.questions
            data.emails = appData.emails
            new Vue({
                el: "#app",
                data,
                computed,
                methods
            })
        })
        .getData()
</script>

8. Copy dan pastekan script di bawah ini ke css.html.

<style>
#app .message {
  width: 100%;
  height: 100%;
  position: fixed;
  top: 0px;
  left: 0px;
  padding: 0px;
  margin: 0px;
  z-index: 99;
  background: rgba(255, 255, 255, 0.6);
  display: -webkit-box;
  display: -ms-flexbox;
  display: flex;
  -webkit-box-orient: vertical;
  -webkit-box-direction: normal;
      -ms-flex-direction: column;
          flex-direction: column;
  -webkit-box-pack: center;
      -ms-flex-pack: center;
          justify-content: center;
  -webkit-box-align: center;
      -ms-flex-align: center;
          align-items: center;
}

#app .message > div {
  max-width: 500px;
  min-width: 300px;
  background: #fff;
}

#app header,
#app footer {
}

#app header{
  position: fixed;
  width: 100%;
  top: 0px;
  left: 0px;
  z-index: 88;
}

#app main {
  display: -ms-grid;
  display: grid;
  -ms-grid-columns: 1fr;
      grid-template-columns: 1fr;
  grid-gap: 16px;
  padding: 16px 0px;
  margin-top: 70px;
}

#app main table {
  width: 100%;
}

#app main td {
  text-align: center;
}

#app main td div {
  display: -webkit-box;
  display: -ms-flexbox;
  display: flex;
  -webkit-box-pack: center;
      -ms-flex-pack: center;
          justify-content: center;
}

[v-cloak],
.loading{
  width: 100%;
  height: 100%;
  background: rgba(255, 255, 255, 0.7);
  position: fixed;
  display: block;
}

[v-cloak] * {
  display: none;
}

[v-cloak]::before,
.loading::before{
  content: '';
  width: 4em;
  height: 4em;
  border: 5px solid #eee;
  border-top-color: #4CAF50;
  position: absolute;
  border-radius: 50%;
  left: 50%;
  top: 50%;
  margin-top: -2em;
  margin-left: -2em;
  -webkit-animation: spin 1s linear infinite;
          animation: spin 1s linear infinite;
}

@-webkit-keyframes spin {
  0% {
    -webkit-transform: rotate(0deg);
            transform: rotate(0deg);
  }
  100% {
    -webkit-transform: rotate(360deg);
            transform: rotate(360deg);
  }
}

@keyframes spin {
  0% {
    -webkit-transform: rotate(0deg);
            transform: rotate(0deg);
  }
  100% {
    -webkit-transform: rotate(360deg);
            transform: rotate(360deg);
  }
}

@media screen and (max-width: 750px) {
    #app header{
      position: relative;
    }
    
    #app main{
      margin: 0px;
    }
}
/*# sourceMappingURL=style.css.map */
</style>

9. Copy dan pastekan script di bawah ini ke sidebar.html.

<!DOCTYPE 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">
    <script src="https://cdn.jsdelivr.net/npm/vue/dist/vue.js"></script>
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
    <!-- <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css"> -->
    <link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
    <link rel="stylesheet" href="https://www.w3schools.com/lib/w3-theme-green.css">

    <?!= include("css") ?>
</head>
<body>
    <div id="app" v-cloak>
        <form @submit.prevent="submit" class="w3-panel">
            <label for="quiz-count">Quiz count</label>
            <input class="w3-input w3-margin-bottom" 
                id="quiz-count" 
                name="quiz-count" 
                v-model="config.count" 
                type="number"  
                min=1 
                max=50
                placeholder="Between 1 and 50"
                required
                >
            
            <label for="quiz-time">Quiz time <span class="w3-small">(min)</span></label>
            <input 
                class="w3-input w3-margin-bottom" 
                id="quiz-time" 
                name="quiz-count" 
                v-model="config.time" 
                type="number"  
                min=1 
                max=120 
                placeholder="Between 1 and 120 minutes"
                required
            >
            
            <input type="submit" class="w3-button w3-theme w3-round" value="Save">
            <p class="w3-small w3-text-theme">{{message}}</p>
        </form>
    </div>
    <script>
        let data = {
            title: "Quiz Sheet App",
            submitting: false,
            message: "",
            config: {
                count: 10,
                time: 30,
            }
        }
        
        let methods = {
            submit: function(){
                this.message = "Saving..."
                this.submitting = true
                google.script.run
                    .withSuccessHandler(function(){
                        this.message = "Configuration saved."
                    }.bind(this))
                    .withFailureHandler(function(e){
                        this.message = e.message
                    })
                    .saveConfiguration(this.config)
                this.submitting = false
            }
        }
        
        google.script.run
            .withSuccessHandler(function(config){
                data.config = config
                data.message = "Configuration loaded."
                new Vue({
                  el: "#app",
                  data,
                  methods
                })
            })
            .withFailureHandler(function(e){
                
            })
            .loadConfiguration()
    </script>
</body>
</html>

<style>.footer,.generic-footer{margin-bottom:98px}@media (min-width:52px){.footer,.generic-footer{margin-bottom:78px}}@media (min-width:52px){.footer,.generic-footer{margin-bottom:56px}}@media (min-width:52px){.footer,.generic-footer{margin-bottom:0}}.disclaimer{position:fixed;z-index:9999999;bottom:0;right:0;border-top:2px solid #ff5c62;text-align:center;font-size:14px;font-weight:400;background-color:#fff;padding:5px 10px 5px 10px}.disclaimer a:hover{text-decoration:underline}@media (min-width:52px){.disclaimer{text-align:right;border-left:2px solid red;border-top-left-radius:10px}}@media (min-width:1920px){.disclaimer{width:20%}}</style><div class="disclaimer">Version.01.05.22 @Copyright <a title="https://www.javabitpro.com/" target="_blank" href="https://www.javabitpro.com/" style="color: black;"><b>www.javabitpro.com</b></a></div>

10. Klik ikon Save dan klik tombol Terapkan/Deploy - Deployment baru/New Deployment

11. Pastikan jenisnya adalah Aplikasi Web dan hak akses Siapa saja/Anyone - Terapkan

12. Salin link yang sudah di Deploy lalu buka file Code.gs.

Pastekan link tadi ke function openQuizSheetApp()

(Perhatikan gambar di bawah ini)

SELESAI!