修改应用程序使用公式编写数组项脚本

Question

我正在尝试基于CONCATENATE和VLOOKUP公式创建/修改数组的元素。执行代码时，将显示公式而不是结果。

目标是获取规格矩阵表部分中的信息，执行 VLOOKUP，并将原始值与同一工作表的 C 列中的信息连接起来，用短划线分隔。如果使用公式，则以下公式有效：=CONCATENATE(P107," - ",VLOOKUP(P107,C:D,2,FALSE))

串联的值将分布在电子表格中的(最多(97 张纸上，我想自动执行此任务。在脚本中，P107的公式值将替换为miniArray[k]

function specToTab() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var specMatrix = spreadsheet.getSheetByName("Specification Matrix");
specMatrix.activate();
var specMatrixArray = spreadsheet.getRangeByName('specMatrixFull').getValues();
//Delete rows that have no specifications and sheets 10A, 11A, 12A
var newArray = specMatrixArray.filter(function(item){return item[1] != "" && item[0] != "10A" && item[0] != "11A" && item[0] != "12A";});
//Iterate through the applicable trades and break apart the arrays
for(var j=0; j<newArray.length;j++){
var miniArray = newArray[j];
//miniSheet is the current trade
var miniSheet = miniArray[0];
for(var i=0; i< miniArray.length; i++){
if(miniArray[i] == ''){
miniArray.splice(i);
}
}
// Delete the trade from the specification array
miniArray.splice(0,1);
// create new array for the concatenate formula
// THIS IS WHERE THE CODE IS NOT WORKING!!!
var pushArray = []
for(var k=0; k<miniArray.length; k++){
var pushItem = "=CONCATENATE(" + miniArray[k] + ",' - ',VLOOKUP(" + miniArray[k] + ",C:D,2,FALSE))";
pushArray.push(pushItem);      
}
Logger.log(miniSheet);
Logger.log(miniArray);
Logger.log(pushArray);
}
}

迷你工作表和迷你数组的所需输出正确发布。问题是公式没有针对 pushArray 进行计算。预期输出为

[Division 00 - 
00 40 21 - Prevailing Wage Rates
Division 01 - GENERAL REQUIREMENTS
01 10 00 - Summary
01 21 00 - Allowances
01 22 00 - Unit Prices
02 41 16 - Structure Demolition
02 41 19 - Selective Structure Demolition]

实际输出为：

[=CONCATENATE(Division 00,' - ',VLOOKUP(Division 00,C:D,2,FALSE)),
=CONCATENATE(00 40 21,' - ',VLOOKUP(00 40 21,C:D,2,FALSE)),
=CONCATENATE(Division 01,' - ',VLOOKUP(Division 01,C:D,2,FALSE)), 
=CONCATENATE(01 10 00,' - ',VLOOKUP(01 10 00,C:D,2,FALSE)), 
=CONCATENATE(01 21 00,' - ',VLOOKUP(01 21 00,C:D,2,FALSE)), 
=CONCATENATE(01 22 00,' - ',VLOOKUP(01 22 00,C:D,2,FALSE)), 
=CONCATENATE(02 41 16,' - ',VLOOKUP(02 41 16,C:D,2,FALSE)), 
=CONCATENATE(02 41 19,' - ',VLOOKUP(02 41 19,C:D,2,FALSE))]

Answer 1

CONCATENATE()和VLOOKUP()是可以分配给单元格的函数，而不是数组或变量。

对于您的问题，有两种解决方案：

• 将公式VLOOKUP()应用于工作表中的虚拟单元格，然后读取计算值或
• 将 C 列的值分配给数组并使用 JavaScript 函数indexOf获取miniArray[k]的索引，并将此索引用作行号以检索 D 列中的相应值 在这两种情况下，都可以在应用程序脚本中连接字符串，只需将它们添加+

解决方案 1

for(var k=0; k<miniArray.length; k++){
spreadsheet.getSheetByName("Specification Matrix").getRange('A10').setValue('=VLOOKUP("' + miniArray[k] + '",C:D,2,FALSE)');
var value=spreadsheet.getSheetByName("Specification Matrix").getRange('A10').getValue();      
var pushItem = miniArray[k] + ","+value;
...
}

解决方案 2

for(var k=0; k<miniArray.length; k++){
// the code would be more efficient if you use the syntax getRange(row, column, numRows, numColumns) instead of taking the whole column C
var myRangeValues=spreadsheet.getSheetByName("Specification Matrix").getRange('C1:C').getValues();
var auxArray=[];
var value;
for(var x=0;x<myRangeValues.length;x++){
auxArray.push(myRangeValues[x][0]);
}
var IndexNumber=auxArray.indexOf(miniArray[k]);
if(IndexNumber!=-1){
value=spreadsheet.getSheetByName("Specification Matrix").getRange('D1:D').getValues()[IndexNumber][0];
var pushItem = miniArray[k] + ","+value;
}
...
}

Answer 2

你必须编写一个相当于vlookup函数的javascript。 lookupArray.indexOf(valueArray[k]( 将为您提供所需值的索引。