代码之家 › 专栏 › 技术社区 › Morfinismo

将数据导出到谷歌工作表

google-app-maker

Morfinismo · 技术社区 · 6 年前

这更多的是表现。这是一个场景:

此应用程序用于控制组织中PC的库存。所以这个应用程序有一个由32个字段和1个关系组成的模型。模型中已经保存了2650条记录。我还有一个将所有记录导出到google表的过程。尽管效果不错,但从我的观点来看,出口消耗了太多时间。

所以我的逻辑是获取所有的记录,遍历每个记录并获取每个字段的数据。然后将所有字段放在一行中,最后将其保存到google工作表中;因此如下所示:

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分钟。 考虑到它还输出其他数据,这是惊人的速度。

所以我的问题是…有没有人知道更好的方法可以帮助我完成这项任务?对于这件事的任何意见,我都非常感谢!

1 回复 | 直到 5 年前

Pavel Shkleinik 6 年前

首先,我建议您找出代码中的瓶颈。例如,您可以尝试使用 console.time 和 console.timeEnd 记录执行时间。一旦你知道你的算法中最慢的部分在哪里,你就可以解决如何改进它们。

第二件事是使用预取。现在,您的脚本似乎调用了数据库来访问每个记录的关系。所以,对db的调用总数是 N * M + 1 ,其中n是记录总数,m是每个记录的关系数,1是获取无关系记录的初始调用。

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; 

...
}