Google sheets app script - taking horizontal data and setting it vertically

Question

I'm trying to take a sequence of data that's arranged horizontally in a single row from the google sheets app using the google script function getValues, and then put them into a different sheet arranged vertically in a single column. My code currently looks like this:

function tester() {
  var app = SpreadsheetApp;
  var loader = app.getActiveSpreadsheet().getSheetByName('Loader');
  var stats = app.getActiveSpreadsheet().getSheetByName('Stats');
  var list = app.getActiveSpreadsheet().getSheetByName('List');  

  var xpos = 19;
  var ypos = 2;

  var abilityBlock = list.getRange(ypos,xpos,1,16).getValues();

  Logger.log(abilityBlock);

  //loader.getRange(10,5,16,1).setValues(abilityBlock);

  }

I have the setValues portion commented out because it returns an error, saying it expected the data to be 16 wide, when the data it's grabbing is 16 tall.

From what I'm able to gather the culprit here is how the data is getting pulled into the array. The Logger displays the data as [[value1,value2,value3,value4,etc..]] instead of [[value1],[value2],[value3],[value4],[etc...]]

I'd like to avoid using a for loop to push the data in one at a time, is there another way to convert the data in the array? Or another way to use the getValues operation to grab it in a format I can actually use it?

corn3lius corn3lius · Accepted Answer · 2018-11-14T15:32:50

I think you want to use the transpose functionality.

try this

list.getRange(ypos,xpos,1,16).copyTo(loader.getRange(10,5), 
                     spreadsheetApp.CopyPasteType.PASTE_VALUES, true);

description here : https://developers.google.com/apps-script/reference/spreadsheet/range#copyTo(Range,CopyPasteType,Boolean)

Google sheets app script - taking horizontal data and setting it vertically

1 Answers