将数据导出到Google工作表 [英] Exporting data to google sheet
问题描述
这更多地与性能有关.这是场景:
This is more about performance. This is the scenario:
此应用程序用于控制组织中PC的清单.因此，该应用程序的模型包含32个字段和1个关系.该模型中已经保存了2650条记录.我也有一个将所有记录导出到Google工作表的过程.即使工作正常，但从我的角度来看，导出会占用太多时间.
This app is used to control the inventory of PCs in an organization. So the app has a model that consists of 32 fields and 1 relation. The model already has 2650 records saved in it. I also have a process that exports all the records to a google sheet. Even though it works fine, the export consumes too much time from my point of view.
所以我的逻辑包括获取所有记录，遍历每个记录以及获取每个字段的数据.然后将所有字段放到一行中，最后将其保存到Google工作表中；因此看起来像这样:
So my logic consists of getting all the records, looping through each one and getting the data of each field. Then put all the fields into a row and finally save it to a google sheet; thus it looks like this:
var allRows ="";
header = ["Property Tag", "Status", "Building", "Department", "Floor", "Area", "Specific Location", "Serial Number", "Model", "Purchase Date", "Warranty End", "HD Size"];
header.push("Processor", "RAM", "PC Name", "MAC Address", "Monitor 1", "Monitor 1 Model", "Monitor 2", "Monitor 2 Model", "Notes", "Office", "Last Inventoried","SSO Type");
header.push("Static/Reserved IP Address", "Static IP Reason","Card Reader Installed", "Last Repair Issue", "Last Repair Date", "Created By", "Created On");
header.push("Last Modified By", "Last Modified On", "Item Type");
allRows += header.join() + "\r\n";
//get all pcItems and save them to google sheet
var pcItems = app.models.pcItems.newQuery().run();
for(i=0; i<pcItems.length; i++){
item = pcItems[i];
propTag = (item.propertyTag) ? ("'" + item.propertyTag) : "";
status = item.status || "";
building = item.building || "";
dept = item.department || "";
floor = item.floor || "";
area = item.area || "";
specLoc = (item.specificLocation) ? "'" + item.specificLocation : "";
serialNum = (item.serialNumber) ? "'" + item.serialNumber : "";
model = item.model || "";
purchase = (item.purchaseDate) ? Utilities.formatDate(item.purchaseDate, "GMT-6", "MM/dd/yyyy") : "";
warranty = (item.warrantyEnd) ? Utilities.formatDate(item.warrantyEnd, "GMT-6", "MM/dd/yyyy") : "";
hd = (item.hdSize) ? "'" + item.hdSize : "";
processor = item.processor || "";
ram = item.ram || "";
pcName = (item.pcName) ? "'" + item.pcName : "";
macAdd = (item.macAddress) ? "'" + item.macAddress : "";
monOne = (item.monitor1) ? "'" + item.monitor1 : "";
monOneMod = item.monitor1Model || "";
monTwo = (item.monitor2) ? "'" + item.monitor2 : "";
monTwoMod = item.monitor2Model || "";
notes = (item.notes) ? "'" + item.notes : "";
office = item.officeVersion || "";
lastInv = (item.lastInventoried) ? "'" + item.lastInventoried : "";
ssoType = item.ssoType || "";
staticIp = item.staticIpAddress || "";
staticIpReason = item.staticIpReason || "";
var cardReader = (item.cardReaderInstalled === true) ? true : (item.cardReaderInstalled === false) ? false : "";
createdBy = item.createdBy || "";
createdOn = (item.created) ? "'" + Utilities.formatDate(item.created, "GMT-6", "MM/dd/yyyy HH:mm") : "";
lastRepairDate = (item.lastRepairDate) ? Utilities.formatDate(item.lastRepairDate, "GMT-6", "MM/dd/yyyy") : "";
lastRepairIssue = item.lastRepairIssue || "";
//the history relation
hist = item.itemHistory;
if(hist.length){
lastModifiedBy = hist[hist.length-1].modifiedBy;
lastModifiedOn = (hist[hist.length-1].modified) ? ("'" + Utilities.formatDate(hist[hist.length-1].modified, "GMT-6", "MM/dd/yyyy HH:mm")) : "";
} else {
lastModifiedBy = "";
lastModifiedOn = "";
}
row = [propTag, status, building, dept, floor, area, specLoc, serialNum, model, purchase, warranty, hd];
row.push(processor, ram, pcName, macAdd, monOne, monOneMod, monTwo, monTwoMod, notes, office, lastInv, ssoType);
row.push(staticIp, staticIpReason, cardReader, lastRepairIssue, lastRepairDate, createdBy, createdOn, lastModifiedBy, lastModifiedOn, "PC");
formattedRow = [];
for(d=0; d<row.length; d++){
cellData = row[d];
if((typeof(cellData) === "string") && (cellData.indexOf(",") > -1)){
cellData = '"'+cellData+'"';
} else if(typeof(cellData) === "object"){
cellData = Utilities.formatDate(cellData, "GMT", "MM/dd/yyyy");
}
formattedRow.push(cellData);
}
csvRow = formattedRow.join();
allRows += csvRow+"\r\n";
}
var data = Utilities.newBlob("").setDataFromString(allRows, "UTF-8").setContentType("text/csv");
var newFile = Drive.Files.insert({title: fileName}, data, {convert: true});
var ss = SpreadsheetApp.openById(newFile.id);
var sheet = ss.getActiveSheet();
var fileHeader = sheet.getRange(1, 1, 1, sheet.getLastColumn());
fileHeader.setBackground("#efefef").setFontWeight("Bold").setVerticalAlignment("Middle");
sheet.setRowHeight(1, 30);
sheet.setFrozenRows(1);
var allData = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn());
allData.setNumberFormat("@");
sheet.autoResizeColumns(1, sheet.getLastColumn());
sheet.deleteColumns(sheet.getLastColumn(), 3);
return ss.getUrl();
此过程大约需要8-10分钟才能完成.我相信这可以更快地完成.我知道的原因是因为如果我转到设置">部署">导出数据"并导出所有数据，则只需要 1:30分钟.快速，因为它还可以导出其他数据.
This process takes approximately 8-10 minutes to complete. I believe this could be done faster. The reason I know that is because if I go to Settings > Deployments > Export Data and do an export of ALL data, it only takes 1:30 mins. That is blazingly fast, considering it also exports other data.
所以我的问题是...有谁知道一种更好的方法可以帮助我完成这项任务?对于此事的任何意见，我都非常感谢！
So my question is... Does anyone knows a better approach that could help me achieve this task? For any input on this matter, I am very thankful in advance!
推荐答案
首先，我建议您在代码中查找瓶颈.例如，您可以尝试使用console.time和console.timeEnd记录执行时间.一旦知道算法中最慢的部分在哪里，就可以解决如何改进它们.
First things first I would recommend to find bottlenecks in your code. For instance you can try to use console.time and console.timeEnd to log execution times. Once you know where are the slowest parts of your algorithm you can tackle how to improve them.
第二种尝试是使用预取.看来，现在您的脚本调用了数据库，以访问每个记录的关系.因此，对DB的调用总数为N * M + 1，其中N是记录总数，M是每个记录的关系数，而1是获取没有关系的记录的初始调用.
Second thing to try is to use prefetch. It seems, that now your script makes a call to the database to access relations for each record. So, total number of calls to DB is N * M + 1, where N is total number of records, M is number of relations for each record and 1 is the initial call to get records without relations.
var query = app.models.pcItems.newQuery();
query.prefetch.myModel._add();
var pcItems = query.run();
for (...) {
...
// after adding prefetch this line should not cause additional
// call to the database
hist = item.itemHistory;
...
}
这篇关于将数据导出到Google工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
