Search code examples
javascriptjsongoogle-apps-scriptgoogle-sheetsfetch

Searching for values in JSON using Fetch in Javascript

I'm making an app in VS Code with a html form for customer data. Because of our company's setup I have to store the data in a Google Sheet. After much searching I found a great series of tutorials on YouTube to show me how to access the data as JSON by publishing it as an apps script web app and then using fetch to go get the data.

BUT, I'm super new to all of this and the one specific thing I didn't learn, and have been having a lot of trouble trying to find (even on this website), is how to search the data to see if a value already exists in the google sheet. I don't want to be creating duplicate customers in the sheet. What I'm hoping is that someone can show me how to search the JSON data via fetch to get a true of false in a variable or some better method, for a single key value, as well as a set of pairs (multiple key pairs). For instance, say I'm trying to make the phone # be the unique key and I want to be able to search for that or the phone # with the last name perhaps. Then I can go on and create the appropriate behavior if the value(s) already exist, or if it/they doesn't.

Here's what my HTML looks like:

<!doctype html>
<html lang="en">
  <head>
    <!-- Required meta tags -->
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">

    <!-- Bootstrap CSS -->
    <link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-eOJMYsd53ii+scO/bJGFsiCZc+5NDVN2yr8+0RDqr0Ql0h+rP48ckxlpbzKgwra6" crossorigin="anonymous">

    <title>Add a Customer</title>
  </head>
  <body>
    <div class="container">
      <h1>Add a Customer</h1>
      <form id="customerForm" novalidate>
          <div class="row p-2">
              <div class="col">
                <input type="text" class="form-control" placeholder="First name" aria-label="First name" id="firstName" required minlength="2">
                <div id="validationServer05Feedback" class="invalid-feedback">
                  Please provide a valid First Name.
                </div>            
              </div>
              <div class="col">
                <input type="text" class="form-control" placeholder="Last name" aria-label="Last name" id="lastName" required minlength="3">
                <div id="validationServer05Feedback" class="invalid-feedback">
                  Please provide a valid Last Name.
                </div>            
              </div>
            </div>
            <div class="row p-2">
              <div class="col">
                <input type="text" class="form-control" placeholder="Telephone" aria-label="Telephone" id="telephone">
              </div>
              <div class="col">
                <input type="text" class="form-control" placeholder="City" aria-label="City" id="city">
              </div>
            </div>
            <div class="col-12">
              <button type="submit" class="btn btn-primary" type="button" id="submitButton">
                <span id="buttonSpinner" class="spinner-border spinner-border-sm d-none" role="status" aria-hidden="true"></span>
                <span id="buttonText">Send</span>
              </button>
              <div class="alert alert-danger mt-2 d-none" role="alert" id="unknownError">
                Something Went Horribly Wrong!
              </div>
            </div>
      </form>
    </div>

    <!-- Optional JavaScript; choose one of the two! -->

    <!-- Option 1: Bootstrap Bundle with Popper -->
    <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js" integrity="sha384-JEW9xMcG8R+pH31jmWH6WWP0WintQrMb4s7ZOdauHnUtxwoG2vI5DkLtS3qm9Ekf" crossorigin="anonymous"></script>

    <!-- Option 2: Separate Popper and Bootstrap JS -->
    <!--
    <script src="https://cdn.jsdelivr.net/npm/@popperjs/[email protected]/dist/umd/popper.min.js" integrity="sha384-SR1sx49pcuLnqZUnnPwx6FCym0wLsk5JZuNx2bPPENzswTNFaQU1RDvt3wT4gWFG" crossorigin="anonymous"></script>
    <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.min.js" integrity="sha384-j0CNLUeiqtyaRmlzUHCPZ+Gy5fQu0dQ6eZ/xAww941Ai1SxSY+0EQqNXNE6DZiVc" crossorigin="anonymous"></script>
    -->
    <script src="customers.js"></script>
  </body>
</html>

And the Client side JavaScript:

var customerForm = document.getElementById("customerForm");
var firstName = document.getElementById("firstName");
var lastName = document.getElementById("lastName");
var telephone = document.getElementById("telephone");
var city = document.getElementById("city");
var submitButton = document.getElementById("submitButton");
var buttonSpinner = document.getElementById("buttonSpinner");
var buttonText = document.getElementById("buttonText");
var unknownError = document.getElementById("unknownError");


function afterSubmit(e){

    e.preventDefault();

    if (customerForm.checkValidity() === false) {
        e.stopPropagation();
        for(field of customerForm.elements) {
            if(!field.checkValidity()){
                field.classList.add("is-invalid");
            }
        }
        return;
      }
    //   customerForm.classList.add('was-validated')
    for(field of customerForm.elements) {
        field.classList.remove("is-invalid");
    }

    var info = {
        first: firstName.value,
        last: lastName.value,
        telephone: telephone.value,
        city: city.value,
    };

    var url = "https://script.google.com/macros/s/AKfycbyWBN9OE7USFtzweft6V7XvS2nD8fDsyuCDb0pN_jGApUFr_UUsXBG1SQ-oU0OT8Ep6/exec";

    buttonText.textContent = "Sending...";
    buttonSpinner.classList.remove("d-none");
    submitButton.disabled = true;

    fetch(url,{
        method: 'POST',
        cache: 'no-cache',
        redirect: 'follow',
        body: JSON.stringify(info)
    })
    .then(res => res.json())
    .then(res => {
        console.log(res);
        customerForm.reset();
        buttonText.textContent = "Send";
        buttonSpinner.classList.add("d-none");
        submitButton.disabled = false;
    })
    .catch(err => {
        console.log(err);
        console.log("Something went wrong");
        unknownError.classList.remove("d-none");
        setTimeout(function(){
            unknownError.classList.add("d-none");
            buttonText.textContent = "Send";
            buttonSpinner.classList.add("d-none");
            submitButton.disabled = false;
        },3000);
    });
}

customerForm.addEventListener("submit",afterSubmit);

And the Apps Script JavaScript:

function doGet(e) {
    const limit = e.parameter.limit === "undefined" ? null : e.parameter.limit;
    const limitNumber = /^[1-9]\d*$/.test(limit) ? parseInt(limit) : null;
    const offset = e.parameter.offset === "undefined" ? null : e.parameter.offset;
    const offsetNumber = /^[1-9]\d*$/.test(offset) ? parseInt(offset) : null;  
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const ws = ss.getSheetByName("customers");
    let data = ws.getRange("A1").getDataRegion().getValues();
    const headers = data.shift();  
    const jsonArray = data.map(r => {
      let obj = {};
      headers.forEach((h,i) => {
        obj[h] = r[i];
      });
      return obj;
    });
  
    const response = [{status: 200,data: jsonArray}];  
    return sendJSON_(response);    
  }

function doPost(e){  
    const requiredColumns = ["first","last"];
    let jsonResponse;  
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const ws = ss.getSheetByName("customers");  
    const headers = ws.getRange(1,1,1,ws.getLastColumn()).getValues()[0];
    const headersOriginalOrder = headers.slice();
    headersOriginalOrder.shift();
    headers.shift();
    headers.sort();  
    const body = e.postData.contents;
    const bodyJSON = JSON.parse(body);
    const headersPassed = Object.keys(bodyJSON).sort();
  
    if(!checkColumnsPassed_(headers,headersPassed,requiredColumns)){
      throw new Error("Something went wrong");
      return;
    }
  
    const arrayofData = headersOriginalOrder.map(h => bodyJSON[h]);  
    const aoaIds = ws.getRange(2,1,ws.getLastRow()-1,1).getValues();
    const newIDNumber = getMaxFromArrayOfArray_(aoaIds) +1;
    arrayofData.unshift(newIDNumber);
    ws.appendRow(arrayofData);
    bodyJSON.id = newIDNumber;
    return sendJSON_(bodyJSON);
  }

function checkColumnsPassed_(arrAllColumns,arrColumnsPassed,arrRequiredColumns){
    if(!arrRequiredColumns.every(item => arrColumnsPassed.includes(item))) return false;
    if(!arrColumnsPassed.every(item => arrAllColumns.includes(item))) return false;
    return true;
  }
  
  function sendJSON_(jsonResponse){
    return ContentService
      .createTextOutput(JSON.stringify(jsonResponse))
      .setMimeType(ContentService.MimeType.JSON);
  }
  
  //return the highest number / id
  function getMaxFromArrayOfArray_(aoa){
    let maxID = 0; 
    aoa.forEach(r => {
      if(r[0] > maxID) maxID = r[0];
    });
    return maxID;
  }

Sorry if that's too much code but I wanted to supply everything I'm working with in case there's something I need to be made aware of. Please let me know if I need to clarify my question.

Thanks in advance for any help you can provide!

Solution

  • I believe your goal as follows.

    • When the HTML form is submitted, you want to check whether the inputted phone number and last name are existing in "customers" sheet of the Spreadsheet.
    • When the inputted phone number and last name are NOT existing in "customers" sheet of the Spreadsheet, you want to add it.
    • When the inputted phone number and last name are existing in "customers" sheet of the Spreadsheet, you DON'T want to add it.

    In this case, how about modifying the function doPost of your Google Apps Script as follows?

    From:

    if(!checkColumnsPassed_(headers,headersPassed,requiredColumns)){
  throw new Error("Something went wrong");
  return;
}

const arrayofData = headersOriginalOrder.map(h => bodyJSON[h]);

    To:

    if (!checkColumnsPassed_(headers, headersPassed, requiredColumns)) {
  throw new Error("Something went wrong");
  return;
}

// --- I added below script.
const checkHeaders = ["telephone", "last"];
const [, ...v] = ws.getDataRange().getValues();
const headerObj = headers.reduce((o, f, i) => Object.assign(o, {[f]: i}), {});
const duplicateCheck = v.some(r => checkHeaders.every(h => r[headerObj[h]] == bodyJSON[h]));
if (duplicateCheck) return sendJSON_({duplicated: true});
// ---

const arrayofData = headersOriginalOrder.map(h => bodyJSON[h]);
    • By above modification, when the values of telephone and last are existing in "customers" sheet, duplicateCheck returns true and the inputted value is NOT added and {duplicated: true} is returned.
    • On the other hand, when the values of telephone and last are NOT existing in "customers" sheet, duplicateCheck returns false and the inputted value is added.

    Note:

    • When you modified the Google Apps Script for Web Apps, please modify the deployment as new version. By this, the modified script is reflected to Web Apps without changing the URL of Web Apps. Please be careful this.

    • In this modification, I'm not sure about the header values. So before the values are searched, the column index of each header value is retrieved as headerObj.

    References: