Modifying apps script array items with formulas

Question

I am trying to create/modify elements of an array based on CONCATENATE and VLOOKUP formulas. When the code is executed, the formula is displayed instead of the results.

The goal is to take information in a section of the Specification Matrix sheet, perform a VLOOKUP, and concatenate the original value with the information in Column C of the same sheet separated by a dash. If formulas were used, the following formula works: =CONCATENATE(P107," - ",VLOOKUP(P107,C:D,2,FALSE))

The concatenated values will be distributed across (up to) 97 sheets within the spreadsheet, and I would like to perform this task automatically. In the script, the formula value for P107 is replaced by 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);
   }
}

The desired output of miniSheet and miniArray are posting correctly. The issue is that the formulas are not calculating for the pushArray. The expected output is

[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]

The actual output is:

[=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() and VLOOKUP() are functions that can be assigned to cells, not to arrays or variables.

There are two solutions for your issue:

• Apply the formula VLOOKUP() to a dummy cell in your worksheet and subsequently read the calculated value or
• Assign the values of your column C to an array and use the JavaScript function indexOf to get the index of miniArray[k] and use this index as a row number to retrieve the respective value in column D In both cases, strings can be concatenated in Apps Script by simply adding them with a+

Solution 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;
    ...
    }

Solution 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

You'll have to write a javascript equivalent of a vlookup function. lookupArray.indexOf(valueArray[k]) will give you the index of the value you're looking for.