CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
xyz
xyz

Reputation: 2300

Reconfigure output for Matching function Google script part2

I'm am matching column A in both sheets

SheetA = FindReplace SheetB = Test

If a match exists then post back the values to the right of the match cell in SheetA to the same row in SheetB but starting in Column B

The ranges in the matching columns may be different in size

It came from a question I asked here

Match text strings of two columns on different sheet and post back using google script

The change I need to make is if a match is found I need to post back the cells from an array of column indices, not just a single cell

Ex: Post back columns 2, 4, 5 of SheetA if a match is found, so column of indices would be arr = [1,3,4]

I am trying this

var arr = [1,3,4];
var temp = [];
for (var i = 0; i < arr.length; i++) {
         temp[a][i] = [dataA[a][arr[i]]];
    };
 res.push([temp])

I get the error TypeError: Cannot set property "0.0" of undefined to "(class)@36bad839"

Here is a Google sheet https://docs.google.com/spreadsheets/d/1_pzHyMpeQtYs6xlLHa7EBtX2vI2y5lwwSktcgVHHcTs/edit?usp=sharing

Thanks, for any help on this

SheetA

enter image description here

SheetB after match

enter image description here

function MatchColumnsAlternate2(){
  // gets spreadsheet A and the range of data
  var sheetA =SpreadsheetApp.getActiveSpreadsheet().getSheetByName("FindReplace");
  var dataA = sheetA.getRange(2, 1, sheetA.getLastRow(), 2).getValues();

  // gets spreadsheet B and the range of data
  var sheetB = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Test");
  var dataB = sheetB.getRange(2, 1, sheetB.getLastRow(), 1).getValues();

  // Added
 var arr = [1,3,4];
  var temp = [];
  var res = [];
  for (var b in dataB) {
  for (var a in dataA) {
    if (dataA[a][0] == dataB[b][0]) res.push([dataA[a][1]]);
      for (var i = 0; i < arr.length; i++) {
             temp[a][i] = [dataA[a][arr[i]]];
        };
     res.push([temp])
  }
  if (b != res.length - 1) res.push([""]);
  }
 sheetB.getRange(2, 2, res.length, res[0].length).setValues(res);
 }

Upvotes: 0

Views: 92

Answers (1)

Jack Brown
Jack Brown

Reputation: 5892

The reason for the error is that you have defined the var temp =[] as a 1D array. You will need to define a second dimension for each of element in the 1 dimension of var temp like so:

function arrayFun(){
 var temp = []  //Define first dimesion of the array here
 for(var i= 0; i< 10 ; i++){
  temp[i] = []  //Define the second Dimension of the array here for each 1 dimension element
  temp[i][0]= i 
 }

  Logger.log(temp)

}

Edit: You can simplify the whole code in the following manner, you don't need to create a temp array. 

    var arr = [1,3,4];
  var count = 0
  var res = [];
  for (var b in dataB) {
  for (var a in dataA) {

    if (dataA[a][0].trim() == dataB[b][0].trim()) {    
      res[b] = []                      // create 2d array
      for (var i = 0; i < arr.length; i++) {
            res[b].push(dataA[a][arr[i]]);
        }; 
    } 
  }
  if (b != res.length - 1) 
  { res[b] = []
    for (var i = 0; i < arr.length; i++) {
            res[b].push(" ");   //Push empty data to keep dimesions consistent
        }; 
  }
  }
 Logger.log(res)
 sheetB.getRange(2, 2, res.length, res[0].length).setValues(res);

Also, note the use of push function for array (you can push individual elements) with array[0].push("element 0,0") or you can do that same by array.push(["Element 0,0"])(push the whole 2Dimensions).

Upvotes: 2

Related Questions