将数据从Google表格中提取到HTML表格中 [英] Pulling Data From Google Sheets into HTML Table
问题描述
我在Google表格上有一个小型的Web应用程序设置，其中包含近1万行和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.
索引脚本.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
删除了服务器端的emption代码，因为我认为这会造成混乱.
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事件时，您 render 整个表(如果我正确理解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).
首先，访问表的子级(假设它是由<thead>和<tbody>元素构成的: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()函数也移至服务器异步调用，因为使用htmlString通过字符串连接(而不是document上的createElement())呈现了表行.
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.
有用的链接
- Document Object Model (DOM) reference;
- Server-client communication in GAS reference;
- Best practices for working with
HtmlService;
这篇关于将数据从Google表格中提取到HTML表格中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
