javascript google-apps-script google-workspace

Finding the google spreadsheet line with <input type="date">

I am working on a web application(google apps scripts) that summarizes the reports for the day(with bootstrap 5). I am using a data input to make the user choose a date for him to read the day's report.

A scriplet finds my gs data(with a back-end function) and with javascript i want the table on the html part to print out only the line selected corresponding to the date input. Finding the date with the input inside the google spreadsheet is bugged.

// this is in my html : <? var donnees = getConsignes(); ?> 
// <input id="date" class="form-control" type="date" style="width: 45%">
// <button class="btn btn-outline-primary" id="btn_date" style="margin: 5px;">voir</button>


var donnees = <?!= JSON.stringify(donnees) ?>;
btn_dateS = document.getElementById('btn_date')
btn_dateS.addEventListener('click', function(e) {
    e.preventDefault();
    var dateControl = document.querySelector('input[type="date"]').value;
    filterDataByDate(dateControl)
  })

I use this function next to get a date that isn't formatted up to milliseconds, and then print out the google spreadsheet into the html table:

function filterDataByDate(selectedDate) {
    var filteredData = donnees.filter(function(item) {
      var itemDate = new Date(item[0]).toISOString().split('T')[0]
      console.log("Item date:", itemDate) // Debugging log
      return itemDate === selectedDate
    })

    if (filteredData.length > 0) {
      document.getElementById('selectedDate').textContent = "Date : " + filteredData[0][0]
      document.getElementById('faitsMarquants').textContent = filteredData[0][1]
      document.getElementById('qualite').textContent = filteredData[0][2]
      document.getElementById('ess').textContent = filteredData[0][3]
      document.getElementById('observations').textContent = filteredData[0][4]
      document.getElementById('consigne').textContent = filteredData[0][5]
    } else {
      alert('No data found for the selected date.')
    }
  }

Would anyone know any better approach? I am a beginner in javascript and got the 'filterDataByDate' function from ai but i don't know how to make it work.

Here's a link to a copy of the gs file: https://docs.google.com/spreadsheets/d/11gm8_QU48PjZpTdBBcmfRGg1PydIAbUbVZ6Il-_YCpE/edit?usp=sharing

Also there's a part of my backend

function doGet(e) {
  let page = e.parameter.page;        
  if (page == null) page = "main";    
  var output = HtmlService.createTemplateFromFile(page); 
  
  return output.evaluate();  
}
function getRapports() {
  return SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/11gm8_QU48PjZpTdBBcmfRGg1PydIAbUbVZ6Il-_YCpE/edit?usp=sharing')
    .getActiveSheet().getDataRange().getValues()
}

Solution

I modified your code and it should now work as intended, I use google.script.run with the success handler to communicate with the backend function getRapports() by passing the data from frontend to backend. The response will be filtered by the backend and will return 2D array with the header included. In frontend I added a table where the response should be and using forEach to add it on the table.

Backend Code.gs:

function doGet(e) {
  let page = e.parameter.page;        
  if (page == null) page = "main";    
  var output = HtmlService.createTemplateFromFile(page); 
  return output.evaluate();  
}


function getRapports(date) {
  const data = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/11gm8_QU48PjZpTdBBcmfRGg1PydIAbUbVZ6Il-_YCpE/edit?usp=sharing')
    .getActiveSheet().getDataRange().getValues();

  const isSameDate = (date1, date2) => {
    return date1.getFullYear() === date2.getFullYear() &&
           date1.getMonth() === date2.getMonth() &&
           date1.getDate() === date2.getDate();
  }

  const filteredRows = data.filter((x, i) => {
    if (i === 0) return true;
    if (!(x[0] instanceof Date)) {
      return false;
    }
    return isSameDate(x[0], new Date(date));
  });

  const convertedDate = filteredRows.map((x, i) => {
    const newRow = x.slice(); 
    if (i !== 0 && newRow[0] instanceof Date) {
      newRow[0] = newRow[0].toISOString().split('T')[0]; 
    }
    return newRow;
  });

  return convertedDate;
}

Frontend main.html:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <script>
        function fetchRapports() {
            const dateInput = document.getElementById('date').value;
            google.script.run
                .withSuccessHandler(displayResults)
                .getRapports(dateInput);
        }
   

        function displayResults(data) {
          console.log(data.length);
            const resultDiv = document.getElementById('results');
            resultDiv.innerHTML = ''; // to clear the prev result

            if (data.length <= 1) {
                resultDiv.innerHTML = '<p>No data found for the selected date.</p>';
                return;
            }

            const table = document.createElement('table');
            table.border = '1';
            const headerRow = document.createElement('tr');
            data[0].forEach(header => {
                const th = document.createElement('th');
                th.textContent = header;
                headerRow.appendChild(th);
            });
            table.appendChild(headerRow);

            data.forEach((x, i) => {
                if (i === 0) return;
                const tr = document.createElement('tr');
                x.forEach(y => {
                    const td = document.createElement('td');
                    td.textContent = y;
                    tr.appendChild(td);
                });
                table.appendChild(tr);
            });

            resultDiv.appendChild(table);
        }
    </script>
</head>
<body>
   <input id="date" class="form-control" type="date" style="width: 45%">
   <button class="btn btn-outline-primary" onclick="fetchRapports()" style="margin: 5px;">voir</button>
    <div id="results"></div>
</body>
</html>

Reference: