Google Sheet Data as JSON for AJAX data

Google Sheet Data as JSON for AJAX data

  1. Set up your spreadsheet with questions, answers and options for the player.
QuestionCorrectAnswer 1Answer 2Answer 3Answer 4
What color is the skyblueredgreenpurple
What color is the grassgreenbluepinkorangepurple
What is 5 + 71268023
What is 5 + 91468023
What is 5 + 51068023
  1. Get your spreadsheet ID to use in a Google Script
  1. Create a New Google Apps Script Project https://script.google.com/
  2. Get the data from the sheet and output it as an object in apps script.

function outputData() {

 const id = ‘1zhez9FBfG****1A_TntCc’;

 const sheetData = SpreadsheetApp.openById(id).getSheetByName(‘quiz’).getDataRange().getValues();

 const rows = sheetData.slice(1);

 const data = rows.map((row)=>{

   const myObj = {question:row[0],answer:row[1]};

   myObj.arr = [];

   for(let i=1;i<6;i++){

     const val = row[i];

     if(val){

       myObj.arr.push(val);

     }

   }

   return myObj;

 });

 return (data);

}

  1. Create Your WebApp to output Sheet data as a JSON object

function doGet(e){

 const output = JSON.stringify({

   status:’success’,

   data:outputData()

 })

 return ContentService.createTextOutput(output).setMimeType(ContentService.MimeType.JSON);

}

  1. Create your HTML file to host the JavaScript AJAX request to the Web App Endpoint

<!DOCTYPE html>

<html>

   <head>

       <title>Sheet Quiz</title>

       <style>

           *{

               box-sizing:border-box;

           }

           .btn{

               display:block;

               margin:10px auto;

               padding:10px;

           }

           .message{

               width:80%;

               border:1px solid #ddd;

               padding:5px;

               margin:auto;

           }

           .box{

               display:inline-block;

               border:1px solid #ddd;

               padding:5px;

               width:25%;

               text-align:center;

               text-transform:capitalize;

           }

           .box1:hover{

               background-color:black;

               color:white;

               cursor:pointer;

           }

       </style>

   </head>

   <body>

       <div class=”output”></div>

       <script src=”app2.js”></script>

   </body>

</html>

  1. Create your JavaScript code to load the JSON data into an object which can be used within the JavaScript Code.   Create page elements using JavaScript DOM element create and add event listeners to create page interactions with the game data.

const url = ‘https://script.google.com/macros/s/AKfycbxWcrVQhM0jX3bypudDtbquimzCceSWr3Ma4kwNKpHENA0EOXwSqvgQVpMXdVBDce7wQQ/exec’;

const output = document.querySelector(‘.output’);

const game = {question:0,total:0,data:[],score:0};

document.addEventListener(‘DOMContentLoaded’,init);

function init(){

   console.log(‘ready’);

   output.innerHTML = ”;

   const btn = document.createElement(‘button’);

   btn.disabled = true;

   start(btn);

   game.question = 0;

   game.total = 0;

   game.score = 0;

   game.data =[];

   fetch(url)

       .then(res => res.json())

       .then((data)=>{

           console.log(data);

           game.total = data.data.length;

           game.data = data.data;

           btn.disabled = false;

       })

}

function start(btn){

   const html = `Welcome to the quiz.  Press the button below to start the QUIZ.`;

   const div = maker(‘div’,html,’message’,output);

   btn.textContent = ‘Start Game’;

   btn.classList.add(‘btn’);

   div.append(btn);

   btn.addEventListener(‘click’,loadQuestion);

}

function loadQuestion(){

   output.innerHTML = ”;

   if(game.question >= game.total){

       const html = `<h1>Game Over</h1><div>You got ${game.score} out of ${game.total} correct.</div>`;

       const div = maker(‘div’,html,’message’,output);

       const btn3 = maker(‘button’,’Play Again’,’btn’, div);

       btn3.addEventListener(‘click’,init);

   }else{

   const div = maker(‘div’,”,’message’,output);

   const val = game.data[game.question];

   //console.log(val.question);

   const question = maker(‘div’,`${val.question}?`,’question’,div);

   const optList = maker(‘div’,”,’opts’,div);

   val.arr.forEach((opt)=>{

       ///console.log(opt);

       const temp = maker(‘div’,opt,’box’,optList);

       temp.classList.add(‘box1’);

       temp.myObj = {

           opt:opt,

           answer:val.answer

       };

       temp.addEventListener(‘click’,checker);

   })

}

   //console.log(‘question’);

}

function checker(e){

   const val = e.target.myObj;

   //console.log(val.opt);

   //console.log(val.answer);

   removeClicks();

   e.target.style.color = ‘white’;

   let html = ”;

   if(val.opt == val.answer){

       game.score++;

       e.target.style.backgroundColor  = ‘green’;

       html = `Correct!`;

   }else{

       e.target.style.backgroundColor = ‘red’;

       html = `Wrong!`;

   }

   const parent = e.target.parentElement;

   console.log(parent);

   game.question++;

   const rep = game.question == game.total ? ‘End Game’ : ‘Next Question’;

   const feedback = maker(‘div’,html,’message’,parent);

   const btn2 = maker(‘button’,rep,’btn’,parent);

   btn2.addEventListener(‘click’,loadQuestion);

}

function removeClicks(){

   const boxes = document.querySelectorAll(‘.box’);

   boxes.forEach((ele)=>{

       ele.removeEventListener(‘click’,checker);

       ele.style.color = ‘#ddd’;

       ele.classList.remove(‘box1’);

   })

}

function maker(eleType,html,cla,parent){

   const ele = document.createElement(eleType);

   ele.innerHTML = html;

   ele.classList.add(cla);

   return parent.appendChild(ele);

}

Leave a Comment