Extract row of data, and paste transposed with four blank cells in between each value

Question

I have a script that extracts a row of data from one tab ("Weekly Selections"), based on criteria from a cell (Picks!J5) on another tab ("Picks"), and returns the list of data transposed in a column.

With the below script, the data is returned row-by-row in the below format.

Is it possible to add 4 blank cells in between each value like in the below picture?

I have included an example sheet for reference, along with my code below: https://docs.google.com/spreadsheets/d/1LGPVbqoVYECAO9kZgWA5uL6EN4uUJt3F_IfjwhDfLlQ/edit#gid=474820972

sh = SpreadsheetApp.getActiveSpreadsheet();
shForm = sh.getSheetByName("Picks");
shDb = sh.getSheetByName("Weekly Selections");
rng = shForm.getRange("B11:B46");
rng_val = rng.getValues()

   
function search(){

var rowNum = shForm.getRange("J6").getValue();

  if(rowNum === 0){
      SpreadsheetApp.getUi().alert("User not found!")
    
  }  else  {

    //get user team
    var data = shDb.getRange(rowNum,2,1,8).getValues()

    //display data to form
    //Defining a blank array that can hold the result
    trans = [];
    //transpose the data stored in range variable
    for(var column = 0; column < data[0].length; column++){
        trans[column] = [];
    for(var row = 0; row < data.length; row++){
        trans[column][row] = data[row][column];

      }
    }
    
    //printing values stores into trans variable on spreadsheet range
    shForm.getRange(11,9,8,1).setValues(trans);
  }
}

Answer 1

From your provided Spreadsheet, how about the following sample script?

Sample script:

Please confirm the sheet names again.

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet1 = ss.getSheetByName("Weekly Selections");
  const sheet2 = ss.getSheetByName("Picks");
  const [[v1], [v2]] = sheet2.getRange("J5:J6").getValues();
  const searchedRow = sheet1.getRange("A6:I" + sheet1.getLastRow()).getValues().find(([a]) => a == v1);
  if (!searchedRow) return;
  const emptyArray = Array(v2 - 2).fill([null]);
  const dstValues = searchedRow.slice(1).flatMap(c => [[c], ...emptyArray]);
  sheet2.getRange(11, 9, dstValues.length).setValues(dstValues);
}

• In this case, when "James" is selected at "J5" of "Picks" sheet, the value is set to 6 by the formula. And, when this script is run, the value of row 6 is retrieved from "Weekly Selections" sheet. And, the values of Apples Oranges Bananas Apricots Peaches Strawberries Blueberries Blackberries are put into the cells "I11:I". In this case, the empty rows between each word are 4.

• If you want to adjust the number of empty rows between each word, please modify 2 of const emptyArray = Array(v2 - 2).fill([null]);.

References:

• find()
• flatMap()