How to enter an entire array values into a cell in google spreadsheet?

0
votes

I have a google spreadsheet with values. I want to enter the values from 5 cells into one other cell, one after the other.

Let' say I have five different cells in a column like this. 1 2 3 4 5

So I created an array and pushed the values into the array. Then I have a script to assign cell.setValue(array) to the cell. The cell displays only the first value of the array. I want to display all values one below the other, but inside the same cell.

How do I do it with script?

I can do it like the below version, but the execution time is high.

for(var i = 0; i<variations.length; i++)
    {
      var cval = s.getRange(lsrn, 3).getValue();
      if(i ==variations.length-1){s.getRange(lsrn, 3).setValue(cval+variations[i]);}
      else{s.getRange(lsrn, 3).setValue(cval+variations[i]+"\n");}
    }

Here, the variations is the array.

I want to do it without having to copy the values of the cell time and time again. How do I do it?

1
google-apps-scriptgoogle-sheets

1 Answers

2
votes

Your code snippet is slow be cause of the many calls to SpreadsheetApp, see Best Practices

  • You can singnificatly optimize it by retrieving the value of getRange(lsrn, 3) only once
  • Then feed the values of your array plus \n into a dummy string within the loop
  • Set the result into your spreadsheet only once - after exiting the loop

Sample:

function myFunction() {
  var s = SpreadsheetApp.getActiveSheet();
  var lsrn = 1;
  var variations = [1,2,3,4,5];
  var string = "";
  var cval = s.getRange(lsrn, 3).getValue();
  for(var i = 0; i<variations.length; i++)
  {
    if(i ==variations.length-1){
      string+=variations[i];
    }
    else{
      string+=variations[i]+"\n";
    }
  }
  s.getRange(lsrn, 3).setValue(cval+"\n"+string);
}