1

Desired Behaviour

Populate Google Sheet with array of arrays from a Javascript file. 

// in js file (within google apps script project)   
var aoa = [["row_01_val01", "row_01_val02", "row_01_val03"],
           ["row_02_val01", "row_02_val02", "row_02_val03"]]; // etc
google.script.run.withSuccessHandler(console.log("success")).populateSpreadsheet(aoa); 


// in code.gs
// define target_range (ignore discrepancies between example values above)
var target_range = active_sheet.getRange(2, 1, 834, 20); 

// set values of target_range
target_range.setValues(aoa);

Current Behaviour

// chrome developer tools (firefox/firebug not supported at work)  
Uncaught Cannot convert Array to Object[][]

Question

I'm unable to post more specific code due to work restrictions (cannot access SO from work, or take code home, hence the pseudo code), but are there any common 'gotchas' when it comes to passing an array of arrays from a Javascript file to Code.gs for use in setValues()?

What I've Tried

In the js file, console.log(typeof(aoa)) returns object just before passing it to populateSpreadsheet() within Code.gs, and then Logger.log(aoa) within populateSpreadsheet() also returns object

Example:

var outer_array = [];
inner_array = [1,2,3];
outer_array.push(inner_array);
typeof(outer_array);
"object"

I've tried to refactor the code several times, but am wondering if there are any common gotchas like size constraints, quotas, or timeouts or some other 'quirk' I haven't considered?

This question and answer, for example, required the array of arrays to be reconstructed before passing it to setValues() (I tried this but it didn't work in my instance):

https://stackoverflow.com/a/11066322/1063287

Another thing I considered is that I'm using splice()(MDN reference) to add some values to the inner array's within Code.gs, so I'm not sure if that somehow changes the type permissible to be passed to setValues()?

Community
  • 1
  • 1
user1063287
  • 10,265
  • 25
  • 122
  • 218
  • 1
    When you get the target range, it must be exactly the same dimensions as the two dimensional array. First test the data in code.gs for it's data type with `typeof aoa` For example: `if (typeof aoa !== 'object') {aoa = JSON.parse(aoa)}; //convert string to array` Then get the range with `var target_range = active_sheet.getRange(2, 1, aoa.length, aoa[0].length); //set rows to number of inner arrays and columns to number of elements in first array` – Alan Wells Oct 12 '16 at 17:13
  • Your suggestions assisted me think more clearly about the problem, it turns out I had a nested object within my inner arrays that I had forgotten about (the array and objects I was working with were large) and that was the issue - once I did some refactoring and made sure everything was an array value it worked well. – user1063287 Oct 15 '16 at 01:26

0 Answers0