Dynamic Sheet Data with JavaScript and Google Sheets with Apps Script GET and POST

Complete Quiz with Score Submission to Google Sheets

Using the quiz code and the score submission for the previous lessons, this lesson will finalize the building of the interactive dynamic quiz application adding the ability for the user to submit their score into the Spreadsheet.  

Once the quiz is complete, the user will be provided an input field asking for their name, and a button to submit the score.  The score and number of questions which are in the global game object can be used to pass those values to the Google Apps Script endpoint URL with a POST request.  

The Google Apps Script Web App can use both doGet() and doPost() and depending on the method, that will invoke the corresponding function and run its code.  This way we can use just the one endpoint for both retrieving the question list as JSON, and sending the score details.

The quiz code will remain the same, the end of game with results should have the new input field added like in the example below.

The doPost() from the Google Apps Script will automatically loop through all the sheet first row column values, so you can add a new value like total and then add to the formData on the submission the same property name and it will work.  This is one of the benefits if the code is written to be dynamic as changes to the resources and updates to the data should be accommodated with little to no change in the final code.

Exercise : Create a way for users to submit their score at the end of the quiz.

  1. Use the previous quiz code and copy in the post submission code.
  2. Add to the end game screen, the input field and a button to submit the score.
  3. Once the button is pressed invoke a function named sendeData().  Create the function with two parameters, the user name from the input field and the main element that contains both the input field and the button.   This will allow the  sendeData() to clear the contents of the element.  Once the button is pressed, update the main field to remove the button and the input and provide a message to the user.
  4. In the  sendeData() function, create a new formData object new FormData()
  5. Append the desired value using the sheet column heading names as the key value, and the value that you want added into that column.  
  6. Using fetch, add the options to send the formData in the body and the method as POST
  7. Once the response is received from the WebApp endpoint, you can provide feedback to the user.  The response will contain the row which can then be used as an id confirmation that the score was submitted for the user if needed.

HTML

<!doctype html>

<html>

<head>

   <title>JavaScript JSON AJAX</title>

   <style>

       .question {

           padding: 5px;

           font-size: 1.5em;

       }

       .main {

           border: 1px solid #ddd;

           padding: 10px;

           width: 90%;

           margin: auto;

       }

       .box {

           padding: 5px;

           border-bottom: 1px solid #333;

           margin: 5px;

       }

       .box1:hover {

           background-color: black;

           color: white;

           cursor: pointer;

       }

       .btn,

       .btn1 {

           display: block;

           margin: auto;

           width: 200px;

           margin: 5px;

           padding: 5px;

           color: white;

           background-color: black;

           font-size: 1.5em;

       }

   </style>

</head>

<body>

   <div>

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

       <button class=”btn”>Click Me</button>

   </div>

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

</body>

</html>

JAVASCRIPT

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

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

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

btn.textContent = ‘Start Game’;

btn.onclick = startGame;

const game = {

   data: {},

   que: 0,

   score: 0,

   ans: []

};

function startGame() {

   btn.style.display = ‘none’;

   output.textContent = ‘Starting game, loading data….’;

   fetch(url)

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

       .then(data => {

           game.data = data;

           buildGame();

       })

}

function buildGame() {

   loadQuestion();

}

function loadQuestion() {

   const q = game.data[game.que];

   output.innerHTML = ”;

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

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

   q.opts.sort(() => {

       return Math.random() – 0.5;

   });

   q.opts.forEach((el) => {

       const span = maker(‘span’, main, ‘box’, el);

       span.correct = q.answer;

       span.selOpt = el;

       span.classList.add(‘box1’);

       span.addEventListener(‘click’, checker);

   })

}

function checker(e) {

   const el = e.target;

   game.ans.push(el.selOpt);

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

   boxs.forEach((ele) => {

       ele.removeEventListener(‘click’, checker);

       ele.style.color = ‘#bbb’;

       ele.classList.remove(‘box1’);

       ele.disabled = true;

   })

   if (el.correct == el.selOpt) {

       game.score++;

       const main = maker(‘div’, output, ‘main’, ‘Correct answer’);

       el.style.color = ‘white’;

       el.style.backgroundColor = ‘green’;

   } else {

       const main = maker(‘div’, output, ‘main’, `Wrong answer was ${el.correct}`);

       el.style.color = ‘white’;

       el.style.backgroundColor = ‘red’;

   }

   const btn1 = maker(‘button’, output, ‘btn1’, `Next Question`);

   game.que++;

   const total = game.data.length – game.que;

   if (total == 0) {

       btn1.textContent = ‘Game Over See Score’;

       btn1.onclick = endGame;

   } else {

       btn1.onclick = loadQuestion;

   }

}

function endGame() {

   output.innerHTML = ”;

   game.ans.forEach((ele, ind) => {

       let html = `Q:${game.data[ind].question} C:${game.data[ind].answer} R:${ele}`;

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

       const bg = (game.data[ind].answer == ele) ? ‘green’ : ‘red’;

       div.style.color = bg;

   })

   const htmlScore = `Your final score :  ${game.score} correct out of ${game.data.length} questions.`;

   const div = maker(‘div’, output, ‘main’, htmlScore);

   div.style.fontSize = ‘1.5em’;

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

   const myInput1 = document.createElement(‘input’);

   myInput1.setAttribute(‘type’, ‘text’);

   const label1 = document.createTextNode(‘Name:’);

   userIn.append(label1);

   myInput1.value = ”;

   userIn.append(myInput1);

   const btnSend = maker(‘button’, userIn, ‘btn1’, ‘Send Score to Sheet’);

   btnSend.onclick = () => {

       userIn.innerHTML = ‘Score sent’;

       sendeData(myInput1.value, userIn);

   }

}

function sendeData(valName, ele) {

   const formData = new FormData();

   formData.append(‘name’, valName);

   formData.append(‘score’, game.score);

   formData.append(‘total’, game.data.length);

   fetch(url, {

           method: ‘POST’,

           body: formData

       }).then(rep => rep.json())

       .then(data => {

           ele.innerHTML = `Score Submitted Thank you`;

       })

}

function maker(t, p, c, h) {

   const el = document.createElement(t);

   el.classList.add(c);

   el.innerHTML = h;

   return p.appendChild(el);

}

GOOGLE APPS SCRIPT

function updater(e){

 const id = ‘1SE*******i0’;

 const sheet = SpreadsheetApp.openById(id).getSheetByName(‘score’);

 const headings = sheet.getDataRange().getValues()[0];

 const arr = [];

 headings.forEach((heading,ind)=>{

   const val = heading.toLowerCase();

   if(val in e.parameter){

     arr[ind] = e.parameter[val];

   }})

 sheet.appendRow(arr);

 return sheet.getLastRow();

}

function doPost(e){

 const obj1 = {id:updater(e),json:JSON.stringify(e)};

 const output = JSON.stringify(obj1);

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

}

function doGet(){

   const d = JSON.stringify(dataObj());

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

}

function dataObj(){

   const id = ‘1SE*******39pi0’;

   const data = SpreadsheetApp.openById(id).getSheetByName(‘questions’).getDataRange().getValues();

   const headings = data[0];

   const rows = data.slice(1);

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

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

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

       const val = row[i];

       if(val){

         temp.opts.push(val);

       }

     }

     return temp;

   })

   return res;

}

2 thoughts on “Dynamic Sheet Data with JavaScript and Google Sheets with Apps Script GET and POST”

Leave a Comment