This is more about performance. This is the scenario:
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.
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();
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!
