Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to append array data to spreadsheet column at once using Google Script?

I am trying to append data of addArray[] to end of column A at once. However, my array is not multidimensional to be able to use this method:

this is how i add to array:

  var toAdd=[];
  var thisurl="red apple http://awebsite.com/1.jpg";
  toAdd.push(thisUrl);

And this is the way i would like to append the array at once

function AddToSpreadsheet()
{
 var data = SpreadsheetApp.getActiveSheet().getRange('A12:A').getValues();
    var toAdd=["red apple http://awebsite.com/1.jpg",
              "green apple http://1awebsite.com/2.jpg",
              "red apple http://1awebsite.com/3.jpg",
              "rotten apple http://rottenApple.com"];



  if(toAdd.length > 0){
    for(i = 0; i < data.length; ++i){
      if(data[i][0] == ""){break;}
    }
    SpreadsheetApp.getActiveSheet().getRange(12+i, 1, toAdd.length, 1).setValues(toAdd);
  } 

}

if i use this above code i get this error:

Cannot convert Array to Object[][]. (line 

My addArray is not multidimensional array ! How i can convert it to multidimensional array like the following example so i can use the above code ?

Multidimensional array example :

var toAdd=[["red apple http://awebsite.com/1.jpg"],
               ["green apple http://1awebsite.com/2.jpg"],
               ["red apple http://1awebsite.com/3.jpg"],
               ["rotten apple http://rottenApple.com"]];

Edit: I converted the array at start and worked!

var values_array=["red apple http://awebsite.com/1.jpg",
               "green apple http://1awebsite.com/2.jpg",
               "red apple http://1awebsite.com/3.jpg",
               "rotten apple http://rottenApple.com"];

  var toAddArray = [];
for (i = 0; i < toAdd.length; ++i){
    toAddArray.push([toAdd[i]]);
}
........... 

and this way i inserted the whole new array to column A:

............
     SpreadsheetApp.getActiveSheet().getRange(12+i, 1, toAddArray.length, 1).setValues(toAddArray);
}
like image 963
user1788736 Avatar asked Dec 08 '22 01:12

user1788736


1 Answers

You need to "transpose" the array. You can do that by creating a new array and appending the values as arrays with one value.

before you call setValues(toAdd) try

var toAddArray = [];
for (i = 0; i < toAdd.length; ++i){
    toAddArray.push([toAdd[i]]);
}

And replace the set values call with

 SpreadsheetApp.getActiveSheet().getRange(12+i, 1, toAdd.length, 1).setValues(toAddArray);

The set values function needs a matrix as an input. Since there is no native matrix it requires an array of arrays where each of the subarrays have the same length and contain no arrays.
Each array within the "matrix" is a row and each element of each arrays goes into a column.
If you want one column of rows from a one dimensional array you need an array with one-element arrays. If you want to write columns you make an array containing one array of all values.

like image 120
Robin Gertenbach Avatar answered Dec 28 '22 06:12

Robin Gertenbach