CODEWITHSUNDEEP
arraysgoogle-apps-scriptgoogle-sheets
Jason Torpy
Jason Torpy

Reputation: 144

push nesting array unexpectedly and info

Summary: In google apps script (v8 for sheets), I'm trying to slice an array element out of one array and push it into a different array. Array will be [ [] [] [] ] but I'm getting like [ [] [[]] [[]] ]. This (I think) is creating later issues with using that to getRange/setValues where the length changes from getrange to setvalues. I would separate the issues, but I don't know if they're related.

This seems like a lot to me, but when posting a short answer, I was asked for reproducible code, so this is longer.

Issue1: arrays nesting unexpectedly

Desired outcome1: single nesting (suitable for pasting into google sheets).

Issue2: When trying to getRange/setValues, the data[0].length in get range doesn't match the columns needed for setValues(data) in the same line of code.

Desired outcome2: getRange data[0].length = setValues data length for purposes of getRange and setValues (columns)

Code: info about code (can't put below or it errors it out): Function tester2 does push/slice and returns the mutated arrays as on object. Function tester1 pulls the returned object and logs two arrays. (not sure if this was relevant or not but it is part of the larger code). Logs are below the Code.


    function tester2(){
    
    var arrnew = tester();
    
    llog(arrnew.r1);
    llog(arrnew.r1.length);
    llog(arrnew.r1[0].length);
    llog(arrnew.r2);
    llog(arrnew.r2.length);
    llog(arrnew.r2[0].length);
    
    
    var sh = SpreadsheetApp.getActive().getSheetByName('temp');
    sh.getRange(1,1,arrnew.r1.length,arrnew.r1[0].length).setValues(arrnew.r1);
    sh.getRange(1,1,arrnew.r2.length,arrnew.r2[0].length).setValues(arrnew.r2);
    
    }
    
    function tester(){
    
    var arr1 = [[1,2,3],[4,5,6],[7,8,9],[112,211,311],[411,511,611],[711,811,911]];
    var arr2 = [[11,12,13],[14,15,16],[17,18,19]];
    var arr3 = [['hdr1','hdr2','hdr3']];
    
    for (var r=1;r<arr1.length;r++){
      if(arr1[r][0] % 2 == 0) {
        arr3.push(arr1.splice(1,1));
      }
    }
    
      var rtn = {r1: arr1, r2: arr3};
      return rtn;
    }

Logs:

the logs above are together and in order for easy matching. (this isn't code but I keep getting 'indent your code' error...

>[20-11-11 19:50:47:811 EST] [[1.0, 2.0, 3.0], [112.0, 211.0, 311.0], [411.0, 511.0, 611.0], [711.0, 811.0, 911.0]] 
>
>[20-11-11 19:50:47:815 EST] 4.0 
>
>[20-11-11 19:50:47:818 EST] 3.0 
>
>[20-11-11 19:50:47:821 EST] [[hdr1, hdr2, hdr3], [[4.0, 5.0, 6.0]], [[7.0, 8.0, 9.0]]]  *** notice double-nesting
>
>[20-11-11 19:50:47:823 EST] 3.0 
>
>[20-11-11 19:50:47:826 EST] 3.0 
>
>[20-11-11 19:50:48:240 EST] Exception: The number of columns in the data does not match the number of columns in the range. The data has 1 but the range has 3. at tester2(dcmv1:229:55)
>*** code line 229 begins:  sh.getRange(1,1,arrnew.r1

Upvotes: 1

Views: 66

Answers (2)

Jason Torpy
Jason Torpy

Reputation: 144

Marios gave a great answer below which provided a workaround for the specific example. I'm answering here about both issues I had with some additional detail.

Question 1: Why did I get a length mismatch exception setValues(data) and getRange(columns) in the expression below?

sh.getRange(1,1,arrnew.r1.length,arrnew.r1[0].length).setValues(arrnew.r1);

Answer 1: I had a jagged array, ie r1[0] != r1[1].

Question 2: How do I stop the expression below from creating a jagged array?

arr3.push(arr1.splice(1,1));

Answer 2: grab only the first element [0] of the splice to return [] rather than [[]].

arr3.push(arr1.splice(1,1)[0]);

Bonus insight from Marios: called spread syntax, kind of like 'arrayformula()' function in Sheets.

Upvotes: 1

Marios
Marios

Reputation: 27350

Explanation:

The issue is indeed the double brackets in the second and third element. The second and third element has the structure of [[]] therefore their length is 1 and not 3 as it is the case for the first element. You can see that if you do Logger.log(arrnew.r2[1].length) you will get 1 because there is only one element [] inside the brackets [[]]. But there are 3 elements hdr1, hdr2, hdr3 inside the first bracket [hdr1, hdr2, hdr3] therefore Logger.log(arrnew.r2[0].length) returns 3.

I am not sure what you are trying to achieve but you can "fix" that by introducing a new array with the correct format:

data2 = [arrnew.r2[0],...arrnew.r2[1],...arrnew.r2[2]]

and use that instead of arrnew.r2.

Workable example:

 function tester2(){
    
    var arrnew = tester();
    data2 = [arrnew.r2[0],...arrnew.r2[1],...arrnew.r2[2]]  
    var sh = SpreadsheetApp.getActive().getSheetByName('temp');
    sh.getRange(1,1,arrnew.r1.length,arrnew.r1[0].length).setValues(arrnew.r1);
    sh.getRange(1,1,arrnew.r2.length,arrnew.r2[0].length).setValues(data2);
    
    }
    
    function tester(){
    
    var arr1 = [[1,2,3],[4,5,6],[7,8,9],[112,211,311],[411,511,611],[711,811,911]];
    var arr2 = [[11,12,13],[14,15,16],[17,18,19]];
    var arr3 = [['hdr1','hdr2','hdr3']];
    
    for (var r=1;r<arr1.length;r++){
      if(arr1[r][0] % 2 == 0) {
        arr3.push(arr1.splice(1,1));
      }
    }
    
      var rtn = {r1: arr1, r2: arr3};
      return rtn;
    }

Upvotes: 1

Related Questions