将 Google 表格中的数据提取到 HTML 表格中 [英] Pulling Data From Google Sheets into HTML Table

问题描述

我在 google 表格上设置了一个小型网络应用程序，它有近 10k 行和 9 列.

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

目前，我从 Google 表格中获取所有数据并将其放在 HTML 表格中，然后我几乎没有输入，通过这些输入我使用事件侦听器过滤表格.

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.

之前我有一个交互式过滤器，每次按键时都有一个事件侦听器，我已将其更改为Enter"键，因为前两三个字符花费了太多时间.

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.

index.HTML 上的脚本

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>

我的代码.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.

推荐答案

除了将 map() 移动到异步服务器调用之外，您还可以通过创建一个有效的排序函数来优化客户端代码在 DOM 上.目前，每次触发 keyup 事件时，您都会重新渲染整个表格(如果我正确理解 Spreadsheet 大小，则每次迭代 10K).

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).

首先，访问您的表的子项(假设它由 和 元素构成:var collection = nameTable.children.item(1).children(返回所有行的HtmlCollection).

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).

其次，遍历行并使用hidden属性隐藏不满足过滤条件的行(或创建并toggle一个CSS类):

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;
  }

}

第三，将 renderTableRows() 函数也移动到服务器异步调用，因为您使用字符串连接(而不是 createElement() on >document) 和 htmlString.

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.

有用的链接

1. 文档对象模型 (DOM) 参考；
GAS 参考中的
2. 服务器-客户端通信；
使用 HtmlService 的
3. 最佳做法;

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

这篇关于将 Google 表格中的数据提取到 HTML 表格中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！