我正在尝试在大约600行中用VLOOKUP公式（由用户请求）填充多列，但是脚本花费的时间太长（4-5分钟）。我想通过在第一行中插入公式，使用绝对引用锁定VLOOKUP范围，然后将公式复制到范围中来加快处理速度。但是，我找不到设置绝对引用的GAS方法（例如A1：A100变为$ A $ 1：$ A $ 100）
我浏览了Google的开发人员中心，发现像isEndColumnBounded（）这样的方法返回一个布尔值。虽然，我找不到任何可以设置绝对参考的东西。
现在，我将带有增量搜索ID和静态范围的vlookup公式填充到二维数组中，然后将范围值设置为数组值。这对于两列是必需的，并且它们每个花费大约2分钟...太长了，无法仅填充约600行。
function myFunction()
var idCol =
compSht.createTextFinder("ID")
.matchEntireCell(true).findNext().getColumn();
var vIndex = getVIndex(arcSht);
var aEquation = [];
for(i=0;i<compSht.getLastRow()-1;i++) {
aEquation[i] = new Array;
aEquation[i][0] =
"=IFERROR(VLOOKUP(" + compSht.getRange(i+2, idCol).getA1Notation()
+ ",'" + arcStr + " Submission'!" + vIndex[2] + "," + vIndex[0] +
',false), "No Data")';
}
compSht.getRange(2, hcCol, lRow-1).setValues(aEquation);
}
function getVIndex(sheet) {
var idCol =
sheet.createTextFinder("ID").matchEntireCell(true);
.findNext().getColumn();
var hcCol = sheet.createTextFinder("Total HC").findNext().getColumn();
var tcCol = sheet.createTextFinder("Total Cost").findNext().getColumn();
var range =
sheet.getRange(2,idCol,sheet.getLastRow()-1,tcCol-idCol+1)
.getA1Notation();
var index = [];
index[0] = hcCol - idCol + 1;
index[1] = tcCol - idCol + 1;
index[2] = range;
return index;
}
我想这个问题有两个解决方案：要么我找到一个设置绝对引用的方法，要么有人告诉我为什么我的代码这么慢。我是新手，还有很多改进的空间。让我知道您有任何建议。
我相信您的速度问题就在这里
for(i=0;i<compSht.getLastRow()-1;i++) {
aEquation[i] = new Array;
aEquation[i][0] =
"=IFERROR(VLOOKUP(" + compSht.getRange(i+2, idCol).getA1Notation()
+ ",'" + arcStr + " Submission'!" + vIndex[2] + "," + vIndex[0] +
',false), "No Data")';
}
compSht.getRange(2, hcCol, lRow-1).setValues(aEquation);
}
进一步来说 compSht.getRange(i+2, idCol).getA1Notation()
从本质上讲，正在发生的是，每次循环迭代时，您都会从工作表中获得一个范围，这会极大地减慢速度。更好的解决方案是获取整个工作表的范围（或您正在使用的范围有多大），并用于var values = compSht.getRange(desiredRange).getValues()获取所有单元格值（包括空单元格）的2D数组。
然后可以根据需要修改values数组，然后将其复制回范围 compSht.getRange(desiredRange).setValues(values);
只需记住要更改在values数组中的查找位置，以说明从0而不是1开始的数组索引。
在这种情况下，就获得A1表示法而言，这有点草率，但是您可以创建一个数组来引用
好的，现在一切正常
var alpharray = ["A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"];
var doubleAlpha = [];
var fullArray = [];
var k = 0;
for(var i = 0; i < 26; i++){
for(var j = 0; j < 26; j++){
doubleAlpha[k] = `${alpharray[i]}` + `${alpharray[j]}`;
fullArray[i] = alpharray[i];
k++;
}
}
for(var k = 26; k < 26 + doubleAlpha.length; k++){
fullArray[k] = doubleAlpha[k-26];
}
这将为您提供一个数组，其中所有列均达到ZZ，然后您只需将行号附加到列的字母上，便会得到A1表示法。因此，对于您的情况，如果您正在查看第i + 2行和第idCol列，则可以通过执行以下操作获得A1表示法
var string = "";
string += fullArray[idCol-1] + (i+2);
因此，如果idCol为49且i为5，string则将输出AW7。
希望这可以帮助
编辑：如果您还想节省那些for循环的时间，如果您只想复制它，则生成了一个包含所有组合的数组：
var columnArray = ["A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z","AA","AB","AC","AD","AE","AF","AG","AH","AI","AJ","AK","AL","AM","AN","AO","AP","AQ","AR","AS","AT","AU","AV","AW","AX","AY","AZ","BA","BB","BC","BD","BE","BF","BG","BH","BI","BJ","BK","BL","BM","BN","BO","BP","BQ","BR","BS","BT","BU","BV","BW","BX","BY","BZ","CA","CB","CC","CD","CE","CF","CG","CH","CI","CJ","CK","CL","CM","CN","CO","CP","CQ","CR","CS","CT","CU","CV","CW","CX","CY","CZ","DA","DB","DC","DD","DE","DF","DG","DH","DI","DJ","DK","DL","DM","DN","DO","DP","DQ","DR","DS","DT","DU","DV","DW","DX","DY","DZ","EA","EB","EC","ED","EE","EF","EG","EH","EI","EJ","EK","EL","EM","EN","EO","EP","EQ","ER","ES","ET","EU","EV","EW","EX","EY","EZ","FA","FB","FC","FD","FE","FF","FG","FH","FI","FJ","FK","FL","FM","FN","FO","FP","FQ","FR","FS","FT","FU","FV","FW","FX","FY","FZ","GA","GB","GC","GD","GE","GF","GG","GH","GI","GJ","GK","GL","GM","GN","GO","GP","GQ","GR","GS","GT","GU","GV","GW","GX","GY","GZ","HA","HB","HC","HD","HE","HF","HG","HH","HI","HJ","HK","HL","HM","HN","HO","HP","HQ","HR","HS","HT","HU","HV","HW","HX","HY","HZ","IA","IB","IC","ID","IE","IF","IG","IH","II","IJ","IK","IL","IM","IN","IO","IP","IQ","IR","IS","IT","IU","IV","IW","IX","IY","IZ","JA","JB","JC","JD","JE","JF","JG","JH","JI","JJ","JK","JL","JM","JN","JO","JP","JQ","JR","JS","JT","JU","JV","JW","JX","JY","JZ","KA","KB","KC","KD","KE","KF","KG","KH","KI","KJ","KK","KL","KM","KN","KO","KP","KQ","KR","KS","KT","KU","KV","KW","KX","KY","KZ","LA","LB","LC","LD","LE","LF","LG","LH","LI","LJ","LK","LL","LM","LN","LO","LP","LQ","LR","LS","LT","LU","LV","LW","LX","LY","LZ","MA","MB","MC","MD","ME","MF","MG","MH","MI","MJ","MK","ML","MM","MN","MO","MP","MQ","MR","MS","MT","MU","MV","MW","MX","MY","MZ","NA","NB","NC","ND","NE","NF","NG","NH","NI","NJ","NK","NL","NM","NN","NO","NP","NQ","NR","NS","NT","NU","NV","NW","NX","NY","NZ","OA","OB","OC","OD","OE","OF","OG","OH","OI","OJ","OK","OL","OM","ON","OO","OP","OQ","OR","OS","OT","OU","OV","OW","OX","OY","OZ","PA","PB","PC","PD","PE","PF","PG","PH","PI","PJ","PK","PL","PM","PN","PO","PP","PQ","PR","PS","PT","PU","PV","PW","PX","PY","PZ","QA","QB","QC","QD","QE","QF","QG","QH","QI","QJ","QK","QL","QM","QN","QO","QP","QQ","QR","QS","QT","QU","QV","QW","QX","QY","QZ","RA","RB","RC","RD","RE","RF","RG","RH","RI","RJ","RK","RL","RM","RN","RO","RP","RQ","RR","RS","RT","RU","RV","RW","RX","RY","RZ","SA","SB","SC","SD","SE","SF","SG","SH","SI","SJ","SK","SL","SM","SN","SO","SP","SQ","SR","SS","ST","SU","SV","SW","SX","SY","SZ","TA","TB","TC","TD","TE","TF","TG","TH","TI","TJ","TK","TL","TM","TN","TO","TP","TQ","TR","TS","TT","TU","TV","TW","TX","TY","TZ","UA","UB","UC","UD","UE","UF","UG","UH","UI","UJ","UK","UL","UM","UN","UO","UP","UQ","UR","US","UT","UU","UV","UW","UX","UY","UZ","VA","VB","VC","VD","VE","VF","VG","VH","VI","VJ","VK","VL","VM","VN","VO","VP","VQ","VR","VS","VT","VU","VV","VW","VX","VY","VZ","WA","WB","WC","WD","WE","WF","WG","WH","WI","WJ","WK","WL","WM","WN","WO","WP","WQ","WR","WS","WT","WU","WV","WW","WX","WY","WZ","XA","XB","XC","XD","XE","XF","XG","XH","XI","XJ","XK","XL","XM","XN","XO","XP","XQ","XR","XS","XT","XU","XV","XW","XX","XY","XZ","YA","YB","YC","YD","YE","YF","YG","YH","YI","YJ","YK","YL","YM","YN","YO","YP","YQ","YR","YS","YT","YU","YV","YW","YX","YY","YZ","ZA","ZB","ZC","ZD","ZE","ZF","ZG","ZH","ZI","ZJ","ZK","ZL","ZM","ZN","ZO","ZP","ZQ","ZR","ZS","ZT","ZU","ZV","ZW","ZX","ZY","ZZ"]
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句