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

Question

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);
}

Robin Gertenbach · Accepted Answer · 2015-10-24 10:38:40Z

11

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.

edited Oct 24, 2015 at 10:38

answered Oct 24, 2015 at 9:23

Robin Gertenbach

10.9k3 gold badges30 silver badges38 bronze badges

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

1 Comment

user1788736 Over a year ago

Thanks i had to put your above code just after var values_array=[....]; and worked like a charm!

Gemtastic · Accepted Answer · 2015-10-24 14:56:18Z

The items you receive from the sheet are immutable and that's why you can't append to them, however you can copy the values into a new array and overwrite the old one. I however, would have taken another approach to this issue and just printed to the end of the column like this:

function appendToSheet() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('test'); // Get sheet so I don't have to retype it every time

  var toAppend = [['Append 1'],['Append 2']];                    // Note that this is an array of arrays.
  var newRow = sheet.getLastRow() + 1;                           // This fetches the number of the last row of the entire sheet and adds one to it.
  var endRange = lastRow + toAppend.length - 1;                  // Calculate the last row to append to

  sheet.getRange('A' + newRow + ':A' + endRange).setValues(toAppend); // Write to sheet.
}

But if you want to use the sheet array and the appended data in your code elsewhere I'd do like this where I dynamically calculate the range I need and only fetch that, instead of fetching the entire sheet and work with that:

function appendToValues() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('test');

  var lastRow = sheet.getLastRow();
  var valuesFromSheet =  sheet.getRange('A1:A' + lastRow).getValues(); // Get values from the sheet

  var toAppend = [['value 1'],['value 2']];

  var newArray = valuesFromSheet.concat(toAppend);                     // .concat() copies the old immutable array and makes a new one with the append array included. 
}

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related