Google Script: getValues() and setValues() for rectangular grid

Question

In Google Sheets, when I store a given a1notation into an appendRow, I want the values from this appended Row to be shown on another sheet while retaining the grid structure it had when it was saved intitially.

So from the below code, the data from the appendedRow(11,5,1,5) shall be set to the grid I5:J7.

Sadly I am not proficient enough to work with for loops and push() / array, so I would appreciate your support greatly.

Thank you.

function Test() {
  var rs = SpreadsheetApp.getActiveSpreadsheet();
  var ss = rs.getSheetByName("Sheet");

  var tempArray = [ss.getRange(11,5,1,5)]
  var values = tempArray.getValues();

  ss.getRange('I5:J7').setValues(values);
}

Your ss.getRange(11,5,1,5) gives you a 1x5 grid, but your destination range ('I5:J7') is giving you a 2x3 grid. — Aung49
Please show your script that contains appendRow(), also your tempArray is pointless because getValues() returns an array anyway. — ross

ziganotschka ziganotschka · Accepted Answer · 2019-06-25T07:46:37

Google Apps Script documentation has a Reference section which gives you a brief explanation and example of different methods. Under

https://developers.google.com/apps-script/reference/spreadsheet/range#setvaluesvalues

you can see that the method setValues(values) requires the dimension of the origin and destination range to match. Thus, you cannot paste a 1x5 range into a 2x3 range.

What you can do is to loop through the cells of the destination range and to assign them sequentially a value from the origin range - until either all the values have been passed or the destination range is full. You could implement it like this:

function Test() {
  var rs = SpreadsheetApp.getActiveSpreadsheet();
  var ss = rs.getSheetByName("Sheet");

  var values = ss.getRange(11,5,1,5).getValues();
  var destination=ss.getRange('I5:J7');
  var value=0;
  for(i=1;i<=3;i++)
  {
    for(j=1;j<=2;j++)
    {
      var cell=destination.getCell(i,j);
      if(typeof values[0][value] !== "undefined")
      {
        cell.setValue(values[0][value]);
      }
      value=value+1;
    }
  }  
}

Google Script: getValues() and setValues() for rectangular grid

1 Answers