Working with Multi Dimensional Array in Google Script

Question

I have a sheet where the first row is the number of days. The second and third row is the daily production of different companies in those number of days. Its something like this.

My requirement is to get the number of days row and the a particular company production details row in a 2D array.

Here is my code.

function GetData()
 {
  myFunction("Konabay");
 }

 function myFunction(Company)
 {
   var dSheet = SpreadsheetApp.getActiveSpreadsheet();
   var aSheet= dSheet.getSheetByName("Data");
   var range = aSheet.getDataRange();
   var values = range.getValues();
   var VNeeded=[];

   for (var i = 0; i < values.length; i++) 
   {
   //var row = "";
   var companyCheck="No";

   for (var j = 0; j < values[i].length; j++) 
     {
       if (values[i][j]) 
         {
           if (i==0)//Need the first row(days) in my new array always
           {
             VNeeded[i,j]=values[i][j];
             Logger.log("VNeeded i=0 (0,3) = "+ VNeeded[0,3]);
             Logger.log("VNeeded i=0(1,3)= "+ VNeeded[1,3]);
             //row = row + values[i][j];
           }
           else if (i>0)//From second row onwards check for the company needed
           {
             if(values[i][0]==Company)
             {
               companyCheck="Yes";
             }
             if(companyCheck=="Yes")
             {
               VNeeded[i,j]=values[i][j];
               Logger.log("VNeeded i>0 (0,3) = "+ VNeeded[0,3]);
               Logger.log("VNeeded i>0 (1,3)= "+ VNeeded[1,3]);
               //row = row + values[i][j];
             }
           }

         }
      //row = row + ",";
     }
      //Logger.log(row);

 }
}

I was looking for some thing like this

VNeeded[] =[10,2.5],[30,4],[40,5],[30,5]

But what i am getting is

VNeeded[] =[2.5,2.5],[4,4],[5,5],[5,5]

to check what is going on i used the Logger.log and it gave this result.

[18-01-28 22:36:30:121 IST] VNeeded i=0 (0,3) = undefined
[18-01-28 22:36:30:122 IST] VNeeded i=0(1,3)= undefined
[18-01-28 22:36:30:123 IST] VNeeded i=0 (0,3) = undefined
[18-01-28 22:36:30:123 IST] VNeeded i=0(1,3)= undefined
[18-01-28 22:36:30:124 IST] VNeeded i=0 (0,3) = undefined
[18-01-28 22:36:30:124 IST] VNeeded i=0(1,3)= undefined
[18-01-28 22:36:30:125 IST] VNeeded i=0 (0,3) = 30
[18-01-28 22:36:30:126 IST] VNeeded i=0(1,3)= 30
[18-01-28 22:36:30:126 IST] VNeeded i=0 (0,3) = 30
[18-01-28 22:36:30:127 IST] VNeeded i=0(1,3)= 30
[18-01-28 22:36:30:127 IST] VNeeded i>0 (0,3) = 30
[18-01-28 22:36:30:128 IST] VNeeded i>0 (1,3)= 30
[18-01-28 22:36:30:128 IST] VNeeded i>0 (0,3) = 30
[18-01-28 22:36:30:129 IST] VNeeded i>0 (1,3)= 30
[18-01-28 22:36:30:130 IST] VNeeded i>0 (0,3) = 30
[18-01-28 22:36:30:130 IST] VNeeded i>0 (1,3)= 30
[18-01-28 22:36:30:131 IST] VNeeded i>0 (0,3) = 5
[18-01-28 22:36:30:132 IST] VNeeded i>0 (1,3)= 5
[18-01-28 22:36:30:132 IST] VNeeded i>0 (0,3) = 5
[18-01-28 22:36:30:133 IST] VNeeded i>0 (1,3)= 5

So where am i getting it wrong?? When i am setting values to VNeeded[1,3], the same value is being set to the other dimension (VNeeded[0,3]) too. How is that possible?

Please help me get out of this issue.

How can i get the desired result. What am i doing wrong

The link to my test sheet is : https://docs.google.com/spreadsheets/d/15hOjIr11Czf1KtrvEgo00pvE0kc7fMfSvRhmrn1tPBM/edit?usp=sharing

Please help

Logger.log(JSON.stringify(VNeeded)) gives this result :  ["Konabay",2.5,4,5,5]

Answer 1

Your reference to an array position VNeeded[i,j] is not giving you the result you expect. In effect, you are passing an array of position integer into VNeeded which will resolve to the last figure. i.e. VNeeded[j]

Use VNeeded[i][j] instead.

VNeeded[i][j]=values[i][j];

HOWEVER!

This will throw an error in your code because the "array" at position i you are trying to put a value in at position 'j' doesn't exist. So, you'll need to prefill the position with an array if required:

VNeeded[i] = VNeeded[i] || [];
VNeeded[i][j] = values[i][j];

HOWEVER!

to get the result you required:

[10,2.5],[30,4],[40,5],[30,5];

You need to switch VNeeded[i][j] to VNeeded[j][i] in both parts of your row conditional check.

VNeeded[j] = VNeeded[i] || [];
VNeeded[j][i] = values[i][j];

Because what you are trying is in effect a filter on a pivot, your code could be much simplified by performing the pivot, first, checking the position of the company in the list and returning the pair of results that way:

function getData(company) {

  var dSheet = SpreadsheetApp.getActiveSpreadsheet();
  var aSheet= dSheet.getSheetByName("Data");
  var range = aSheet.getDataRange();
  var values = range.getValues();
  var vNeeded=[];
  var pivotedData = []; // Used to carry the transposed data

  for (var i = 0, l = data.length; i < l; i += 1) {
    for (var j = 0, k = data[i].length; j < k; j += 1) {    
      pivotedData[j] = pivotedData[j] || []; // allows for entry into preexisting array.
      pivotedData[j][i] = data[i][j];
    }
  }

  var companyIndex = pivotedData[0].indexOf(company); // find company in the list

  if (companyIndex < 0) return; // There is no matching company, returns undefined

  for (var i = 1, l = pivotedData.length; i < l; i += 1) { 
    vNeeded[i-1] = [pivotedData[i][0], pivotedData[i][companyIndex]]; // [expected,found]
  }
  return vNeeded;
}

Answer 2

In JavaScript VNeeded[i,j] evaluates to VNeeded[j] because of the comma operator.

Use VNeeded[i][j] instead.

I had to paste the code in https://www.typescriptlang.org/play/ to realise the issue.

Answer 3

You can flatten the array using this function:

function flattenArray(array){
var flatArray = []
  for (var i = 0; i < array.length; i++) {
    flatArray.push(array[i][0])
  }
  return flatArray
}