How to write array out to spreadsheet. setValues throws "cannot convert array to object"

Question

I am trying to write my array out to a spreadsheet but I am doing something wrong. I have read many posts that say to do what I am doing but it is not working. See my last line of code below

on the very last line of code
'''consol_sheet.getRange(2,1,x,10).setValues(final_values);''' I get an error that I

"cannot convert array to object"

function iterateSheets() 
{
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('filesSheet');
  sh.clear();
  var folder=DriveApp.getFolderById('1axTLHOBPhl5_u7ngFOxViHg14OSw7pVC');//replace id with actual id of folder
  var files=folder.getFilesByType(MimeType.GOOGLE_SHEETS);
  var consol_sheet = SpreadsheetApp.openById('18JnkQYa1L-FhXFmKJBrqJ6GI7BBO-V8QAVKP8PLLoXo').getActiveSheet();
  var x=2;
  var z=0;
  var final_values = new Array(50000);

while(files.hasNext())
 {
   var file=files.next();
    var ts=SpreadsheetApp.openById(file.getId());
    var allShts=ts.getSheets();

       for(var i=0;i<3;i++)   //allShts.length   
       {  
            var consol_values = allShts[i].getRange(8,1,40,26).getValues();  //.getRange(8, 1, 1, 1).getValues();
            var headers = allShts[i].getRange(7,6,1,20).getValues();  //.getRange(8, 1, 1, 1).getValues();
            var position= allShts[i].getRange("B1").getValue();
            var period = allShts[i].getRange("B2").getValue();
            var email = allShts[i].getRange("B3").getValue();    

         for (var z = 0;z<20;z++)//
         {   

           for (var y= 0;y<40;y++)              
           {          
               if (consol_values[y][i] != "")
               {
              /*  consol_sheet.getRange(x,1).setValue(consol_values[y][0]);  //AI pack 
                consol_sheet.getRange(x,3).setValue(consol_values[y][1]); //measure
                consol_sheet.getRange(x,4).setValue(email);
                consol_sheet.getRange(x,5).setValue(position);     


                final_values[x] = [];
                final_values[x][0]  =consol_values[y][0];  //AI pack 
                 //blank
                final_values[x][2]  =(consol_values[y][1]); //measure
                final_values[x][3]  =(email);
                final_values[x][4]  =(position);     
                final_values[x][5]  =(headers[0][z]);//location
                final_values[x][6]  =1;
                final_values[x][7]  =(period);
                final_values[x][8]  =(consol_values[y][3]); //price
                final_values[x][9]  =(consol_values[y][z+5]); //fcst

                if (consol_values[y][z+5] != "")
                { 
                    final_values[x][10] =(consol_values[y][25]); //fcst value             
                }                          


                x = Number(x)+1  //row count for consolidation output

              }
          }          
         }    
      }
    }


consol_sheet.getRange(2,1,x,10).setValues(final_values);

 }

This forum has been extremely helpful in getting me to this point now I just have one more hurdle here to finish this project.

x starts at 2. What happened to 0, 1 and 2? final_values[0] will be undefined so are indexes 1 and 2. And there must be 50k elements as you've declared that this array has 50k elements. — TheMaster
– TheMaster, Commented Jun 17, 2019 at 19:09
setting x = 0 fixed my length issue. I will work on the blanks now. — datafarmer
– datafarmer, Commented Jun 17, 2019 at 19:35
I did that in my code so I am guessin the blank is now my issue. — datafarmer
– datafarmer, Commented Jun 17, 2019 at 20:43

Wicket · Accepted Answer · 2019-06-17 18:23:48Z

3

The argument of setValues should be an array of arrays (2D Array) where all the inner arrays have the same number of elements and those elements should be strings, numbers or Date objects, there can't be empty elements.

NOTE:

If your array look like this

[
  [1,,'orange'], // 2nd element of this inner array is empty
  , // 2nd element of the outer array is empty
  [] // This array is empty
]

add the missing inner arrays and '' on each "empty spot" of the inner arrays. The resulting array should look like this:

[
  [1,'','orange'],
  ['','',''],
  ['','','']
]

(add an empty string '' on each empty element place).

How to write an array to Google Spreadsheet?

edited Jun 17, 2019 at 18:23

answered Jun 17, 2019 at 17:45

Wicket

39.4k9 gold badges81 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

datafarmer Over a year ago

Thank you very much for your input, I feel like I am doing exactly what is presented in this link. When you say no empty elements, I am have a lot of empty elements because of the nature of my data. I am not sure I can eliminate them but I will give it a try.

Wicket Over a year ago

@datafarmer I added a note to my answer. Hopefully it make what I tried to said more clear.

datafarmer Over a year ago

I figured the best way to do this was to write a formula like this-- if (consol_values[y][0].length != 0 ) {consol_values[y][0];} else {"";} but when I set this = to a variable I get an error. how do you suggest I write blank values in to the null array elements??

J. G. · Accepted Answer · 2019-06-17 19:41:30Z

0

Have you considered making your modifications to the original 2d array you pulled, consol_values? That way you'll know you have the same number of elements you expect. you can start your loops at somewhere other than 0/1 depending on what values from the original you want to ignore (your pull area isn't quite the same as your write area in this case).

answered Jun 17, 2019 at 19:41

J. G.

1,8302 gold badges16 silver badges27 bronze badges

Comments

Cooper · Accepted Answer · 2019-06-17 21:54:49Z

0

Writing Rows and Columns to a Spreadsheet

Here's a simple function that writes rows and columns out to a spreadsheet.

function writeRowColToSpreadsheet() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  sh.clear();
  var rg=sh.getRange(1,1,25,25);
  var vA=rg.getValues();
  for(var i=0;i<vA.length;i++) {
    for(var j=0;j<vA[i].length;j++) {
      vA[i][j]=Utilities.formatString('%s,%s', i+1,j+1);
    }
  }
  rg.setValues(vA);
}

edited Jun 17, 2019 at 21:54

answered Jun 17, 2019 at 17:45

Cooper

64.2k7 gold badges29 silver badges65 bronze badges

2 Comments

datafarmer Over a year ago

Cooper, thanks for your input I have tried this exact code and get an error on the second part of the getRange final_value[0] and that is why I hard coded the value. The error I get is TypeError: Cannot read property "length" from undefined.

J. G. Over a year ago

That means you definitely don't have a 2-d array.

Collectives™ on Stack Overflow

How to write array out to spreadsheet. setValues throws "cannot convert array to object"

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related