我的谷歌表单的时间效率低下的代码

Question

我写了一些代码来浏览一个包含大量数据的现有电子表格，并根据其内容设置每个单元格的背景色。然而，我有大约80行和200列，我的代码太慢了。有人能帮我了解如何使它更有时间效率吗？有更好的方法吗？

function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var rangeData = sheet.getDataRange();
var lastColumn = 5;
var lastRow = 80;
var searchRange = sheet.getRange(3, 9, lastRow - 1, lastColumn - 1);
//Loop through each column and each row in the sheet.
for (i = 1; i < lastColumn; i++) {
for (j = 1; j < lastRow; j++) {
var cell = searchRange.getCell(j, i);
var cellValue = cell.getValue();
var agentArray = splitTextEntry(cellValue);
var agentName = agentArray[0]; //this gets the agent name from the cell entry
var guestName = agentArray[1]; //this gets the guest name from the cell entry
if ((cellValue === "") || (guestName === "")) {
cell.setBackground("WHITE");
cell.setValue("");
} else {
if (agentName === "UQ") {
cell.setBackground("#ff00ff");
}
if (agentName === "BCOM") {
cell.setBackground("#d5a6bd");
}
if (agentName === "GTR") {
cell.setBackground("#d9d2e9");
}
if (agentName === "FIT") {
cell.setBackground("#8e7cc3");
}
if (agentName === "AG") {
cell.setBackground("#0000ff");
}
if (agentName === "TU") {
cell.setBackground("#4a86e8");
}
if (agentName === "TG") {
cell.setBackground("#c9daf8");
}
if (agentName === "RA") {
cell.setBackground("#a2c4c9");
}
if (agentName === "AP") {
cell.setBackground("#00ff00");
}
if (agentName === "GI") {
cell.setBackground("#d9ead3");
}
if (agentName === "MMT") {
cell.setBackground("#b6d7a8");
}
if (agentName === "EXP") {
cell.setBackground("#f6b26b");
}
if (agentName === "CON") {
cell.setBackground("#f4cccc");
}
if (agentName === "TO") {
cell.setBackground("#ea9999");
}
}
};
};
//forceRefresh(); //call
}

Answer 1

我会这样做：

1. 获取数据范围
2. 获取数据范围的值的二维数组(getValues(((
3. 获取背景的二维数组数据范围的颜色(getBackgrounds(((
4. 迭代数组(从数组中迭代更改值要快得多！(
5. 应用您的逻辑在循环中使用值数组和改变背景数组
6. 循环后，立即将新背景数组设置为您的数据范围(.setBackgrounds(newBackgroundArray((

Answer 2

function onMyOpen() {//probably needs to be installable
const names=["UQ","BCOM","GTR","FIT","AG","TU","TG","RA","AP","GI","MMT","EXP","CON","TO"];
const colors=["#ff00ff","#d5a6bd","#d9d2e9","#8e7cc3","#0000ff","#4a86e8","#c9daf8","#a2c4c9","#00ff00","#d9ead3","#b6d7a8","#f6b26b","#f4cccc","#ea9999"];
const ss=SpreadsheetApp.getActive();
const sh=ss.getActiveSheet();
const rg=sh.getDataRange();
var v=rg.getValues();
var c=rg.getBackgrounds();
v.forEach(function(r,i){
r.forEach(function(c,j){
let t=c.split(',');//had to guess at this since that function was undefined in your example
let a=t[0];
let g=t[1];
if(!a || !g) {//this will fail if you don't have a
c[i][j]="#ffffff";
v[i][j]='';         
}else{
c[i][j]=colors[names.indexOf(a)];//you may need to trim a
}
});
});
rg.setBackgrounds(c);
rg.setValues(v);
}