javascript - 检查其他单元格后写入工作表的脚本超时
问题描述
我有一个 11000 行 X 50 列的 Google 工作表。我需要检查每一行的城市和州,并相应地设置一个字段,调用区域。这需要很长时间,并且仅在完成工作表的 25% 后超时。我对 App Scripts 和 Javascript 非常陌生。必须有更好的方法来做到这一点。任何建议,将不胜感激。这是脚本:
function setRegion() {
var region = "Not Found";
var state;
var city;
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Comp Server List');
var lastRow =sheet.getLastRow();
var startRow = 5;
console.log("10");
for (n = startRow; n < lastRow; n++){
var state = sheet.getRange(n,8).getValue();
var city = sheet.getRange(n,7).getValue();
switch(state) {
case "AZ":
region = "Arizona";
break;
case "NV":
if (city == "Papillion"){
region = "Enterprise Data Center";
} else {
region = "Northern California";
};
break;
case "CA":
switch(city){
case "Santa Maria":
case "Rancho Cordova":
case "Sacramento":
case "Folsom":
case "Carmichael":
case "Grass Valley":
case "Woodland":
case "Shasta":
case "Redding":
case "Red Bluff":
case "Santa Cruz":
case "San Francisco":
case "Redwood":
case "San Francisco":
region = "Northern California";
break;
case "Camarillo":
case "Oxnard":
case "Bakersfield":
case "Merced":
case "San Andreas":
case "Stockton":
case "Arroyo Grande":
case "San Luis Obispo":
case "Los Angeles":
case "San Bernardino":
case "Glendale":
case "Northridge":
case "San Bernardino":
case "Long Beach":
case "Redlands":
case "Pasadena":
region = "Northern California";
break;
}
// code block
break;
case "NE":
if (city == "Papillion"){
region = "Enterprise Data Center";
} else {
region = "Texas";
};
break;
case "IA":
case "ND":
case "MN":
region = "Midwest";
break;
case "WA":
case "OR":
region = "Midwest";
break;
case "KY":
case "OH":
case "TN":
case "GA":
case "AR":
region = "Southeast";
break;
case "TX":
case "NM":
if (city == "Richardson"){
region = "Enterprise Data Center";
} else {
region = "Texas";
};
break;
case "CO":
region = "Enterprise Data Center";
break;
default:
region = "Not Found";
break;
}
console.log("row = "+n,"state ="+ state, "city ="+ city, "region = "+ region);
sheet.getRange(n,9).setValue(region);
}
}
解决方案
推荐阅读
- macos - 为什么我在 MacOS 中收到 Screen is terminating 错误
- c# - Worksheet.Range.Formula 中的 IF 语句 - Micorosft excel 库互操作
- python-3.x - 如何使用 QSqlQueryModel 仅在 sqlite 的 tableview 中显示最后两行?
- node.js - node.js mysql2如何从var中选择保存
- c++ - 为什么在 VS2019 中 std::initializer_list 的初始化似乎失败了
- javascript - 如何使用firebase auth方法启用一键登录?
- opencv - OpenCV 相机校准 - 如何将多个图像的校准结果合并为一组校准参数?
- html - 水平滚动 div 在 Firefox 中不起作用
- sql - 在 Oracle IN 子句中使用替换变量
- google-cloud-platform - 使用哪种协议与 google firestore 进行通信