CODEWITHSUNDEEP
arraysmultidimensional-arraygoogle-apps-scriptgoogle-sheets
user1788736
user1788736

Reputation: 2845

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

Upvotes: 4

Views: 51935

Answers (2)

Gemtastic
Gemtastic

Reputation: 6433

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. 
}

Upvotes: 3

Robin Gertenbach
Robin Gertenbach

Reputation: 10776

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.

Upvotes: 11

Related Questions