0

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))]
TheMaster
  • 45,448
  • 6
  • 62
  • 85
Chris MMgr
  • 49
  • 2
  • 8

2 Answers2

2

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;
      }
   ...
   }
ziganotschka
  • 25,866
  • 2
  • 16
  • 33
  • I found a solution similar to your solution 2, but ran into a separate issue. The following code returns the error "TypeError: Cannot read property "0" from undefined. (line 192, file "Code")" ```for(var k=0; k – Chris MMgr Aug 27 '19 at 14:50
  • I do not know what `specLoc` in your code is, but `IndexOf` works only for 1D arrays, this is why I pushed the values from the 2D array `myRangeValues` into the auxillary 1D array `auxArray`. – ziganotschka Aug 27 '19 at 15:04
  • The `specLoc` is a 1D array, and was producing correct indexOf locations for each value. `specNames` is a 2D array, and the code would work if I hard keyed a number like `specNames[40][1]`, and also produced a 2D value when I just used `specNames[description]`. It would error as soon as I put in the `[description][1]` option. I ended up making a second Array of just descriptions, and it worked. I just wondered if there's a specific reason why it didn't like the `specNames[x][y]` notation. – Chris MMgr Aug 27 '19 at 15:16
  • If the searched value is not found in the array, IndexOf will return you -1, this is a likely error source. To be sure, just log description in each loop, to see which values it gives you. – ziganotschka Aug 27 '19 at 20:32
1

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.

jdavis
  • 429
  • 6
  • 16