javascript - Pulling Data From Google Sheets into HTML Table

Question

I have a small web application setup on google sheets which have almost 10k rows and 9 columns.

currently, I took all the data from Google sheets and putting it on an HTML Table and Then I have few inputs through which I filter the table using event listener.

As you could have guessed already it is taking too much of memory since it is on the client side and loading and filtering are slow.

Earlier I was having an interactive filter with an event listener on each key press I have changed it to "Enter" key since it was taking too much time for first two or three characters.

Script on index.HTML

 <script>
    //global variables
    var rows = []; //rows 
    var currentOrder = 'ascending'; //sorting order
    var inputFilter = document.getElementById('partNum'); //input field for ItemName
    var inputFilterDes = document.getElementById('partDes'); //input field for description
    var nameTable = document.getElementById('table'); //html table

    //load function being used for pulling data from google sheet

    function load() {
    //calling get data function with array and filter array inside
    google.script.run
        .withSuccessHandler(function(response) {
        //response function will be separted into column values
        rows = response.map(function(element) {
            //all the elements converted into columns
            return {
            itemCode: element[0],
            itemName: element[1],
            itemDescription: element[2],
            inStock: element[3],
            committed: element[4],
            onOrder: element[5],
            available: element[6],
            warehouse: element[7]
            };
        });
        //rows mapping finished

        renderTableRows(rows);
        //initial load finished here

        //filter section starts

        //Item name filter

        inputFilter.addEventListener('keyup', function(evt) {
            if (evt.keyCode === 13) {
            // Cancel the default action, if needed
            evt.preventDefault();
            var filter = evt.target.value.toString().toLowerCase();
            }
            var filteredArray = rows.filter(function(row) {
            return row.itemName.toString().toLowerCase().includes(filter);
            });

            renderTableRows(filteredArray);
        });
        //description filter

        inputFilterDes.addEventListener('keyup', function(evt) {
            if (evt.keyCode === 13) {
            // Cancel the default action, if needed
            evt.preventDefault();
            var filterDes = evt.target.value.toString().toLowerCase();
            }
            var filteredArrayDes = rows.filter(function(row) {

            return row.itemDescription.toString().toLowerCase().includes(filterDes);
            });
            renderTableRows(filteredArrayDes);
        });

        })
        .getData("SAP"); //pull data from defined sheet
    }
    //retruing array values in HTML table and placing them in page
    function renderTableRows(arr) {
    nameTable.innerHTML = arr.map(function(row) {
        return '<tr>' + 
        '<td>' + row.itemCode + '</td>' + '<td>' + row.itemName + '</td>' + 
        '<td>' + row.itemDescription + '</td>' + '<td>' + row.inStock + '</td>' + 
        '<td>' + row.committed + '</td>' + '<td>' + row.onOrder + '</td>' + '<td>' + 
        row.available + '</td>' + '<td>' + row.warehouse + '</td>' + '</tr>';
    }).join('');
    };


    load();
  </script>

My code.gs

function doGet(e) {

  if (!e.parameter.page) {
    // When no specific page requested, return "home page"
    return HtmlService.createTemplateFromFile('index').evaluate().setTitle("My Web App");
  }
  // else, use page parameter to pick an html file from the script
  return HtmlService.createTemplateFromFile(e.parameter['page']).evaluate();
}

function getData(sheetName) {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);

  return sheet.getSheetValues(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn());
}

function getScriptUrl() {
  var url = ScriptApp.getService().getUrl();
  return url;
}

I tried to move it on the server side using the following but failed

EDIT : Removed my Server side atempt code as i think it will create confusions.

I'm not a coder so please excuse me if it sounds silly or unorganized.

SO I am trying to increase the speed and for this, I want to Move scripts server-side however I am not fully confident it will help me or not so I am open to any other methods to improve the speed of application.

Answer 1

Apart from moving map() to async server call, you can optimize the client-side code by creating an ordering function that works over DOM. Currently, each time a keyup event is fired, you rerender the whole table (10K iterations each time if I understand the Spreadsheet size correctly).

First, access your table's children (assuming it is constructed with both <thead> and <tbody> elements: var collection = nameTable.children.item(1).children (returns HtmlCollection of all the rows).

Second, iterate over rows and hide ones that do not satisfy the filtering criteria with hidden property (or create and toggle a CSS class instead):

for(var i=0; i<collection.length; i++) {
  var row      = collection.item(i);
  var cells    = row.children;
  var itemName = cells.item(1).textContent; //access item name (0-based);
  var itemDesc = cells.item(2).textContent; //access item description (0-based);

  var complies = itemName==='' && itemDesc===''; //any criteria here;

  if( complies ) {
    row.hidden = false;
  }else {
    row.hidden = true;
  }

}

Third, move the renderTableRows() function to server async call as well, since you render your table rows with string concatenation (instead of createElement() on document) with htmlString.

Useful links

1. Document Object Model (DOM) reference;
2. Server-client communication in GAS reference;
3. Best practices for working with HtmlService;