Trying to copy cell value to different column(s) dependent on variable value

Question

i'm trying to transpose an array to a column dependent on a variable value.

Essentially what i'm trying to do is build a script which will transpose an input sheet, and depending on the language used in the specific input sheet row, transpose that rows values to a different column on an output.

i.e. depending on column value (C), values in columns A,B transpose to Column I,J,K depending on column value C.

Google sheets link: https://docs.google.com/spreadsheets/d/1wuBfK4PKg79vCGGhsBPmHPT7hjfYNdR0srvX9nj0Ajs/edit?usp=sharing

I've already found a simple copyto script which works if it's one array with same language, but I need it to offset based on language.

Any help will be appreciated.

example code:

//missing the offset

function transpose() {
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange("A2:C").copyTo(sheet.getRange("F2"), 
  SpreadsheetApp.CopyPasteType.PASTE_VALUES,true);
}

Answer 1

Try this code:

function Sort()
{
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sh = ss.getSheetByName("Blad1")
  var input = sh.getRange(1, 1, 4, 4).getValues();
  var output = sh.getRange("H1:K1").getValues();
  var index = [0]; var nInput = []
  for(var j = 1; j < output[0].length; j++)
  {
    for(var i = 1; i < input.length; i++) 
    if(output[0][j] == input[i][3]) index.push(i)
  }
  for(var i = 0; i < input.length; i++) nInput.push(input[index[i]])
  var nData = [];
  for(var i = 1; i < nInput.length; i++)
  {
    for(var j = 0; j < nInput[0].length-1; j++)
    {
      if(nData[j] == null) nData[j] = []
      nData[j].push(nInput[i][j])
    }
  }
  sh.getRange(2, 9, nData.length, nData[0].length).setValues(nData); 
}

Answer 2

Try this code:

function Sort()
{
 var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sh = ss.getSheetByName("Blad1")
  var input = sh.getRange(1, 1, 7, 4).getValues();
  var output = sh.getRange("H1:K1").getValues();
  var index = [0]; var nInput = []
  for(var j = 1; j < output[0].length; j++)
  {
    nInput[j-1] = []
    for(var i = 1; i < input.length; i++)
    if(output[0][j] == input[i][3]) 
      for(var k = 0; k < input[0].length-1; k++) nInput[j-1].push(input[i][k])
  }
  var nData = [];
  for(var i = 0; i < nInput.length; i++)
  {
    for(var j = 0; j < nInput[0].length; j++)
    {
      if(nData[j] == null) nData[j] = []
      if(nInput[i][j]) nData[j].push(nInput[i][j])
      else nData[j].push("")
    }
  }
  sh.getRange(2, 9, nData.length, nData[0].length).setValues(nData)
}

This code will work only if you have same number of inputs (3 in this case) for each language.

Answer 3

Proposed Script

function createReport() {
  // Initilaize Sheets, Ranges, Values
  let file = SpreadsheetApp.getActive();
  let sheet1 = file.getSheetByName("Blad1");
  let range1 = sheet1.getDataRange();
  let values1 = range1.getValues();

  let sheet2 = file.getSheetByName("Blad2");
  let range2 = sheet2.getDataRange();
  let values2 = range2.getValues();

  // Deal with headers
  let langs = values2[0];
  values1.shift(); // to remove headers

  // Creating array of sub arrays with info to paste into report
  // In this format:
  // [[Column to paste in, Input 1, Input 2, Input 3]]
  let output = [];
  values1.forEach((row, i) => {
    let outputRow = [];
    let whichCol = langs.findIndex((i) => i == row[3]) + 1;
    outputRow.push(whichCol);
    for (let i = 0; i < 3; i++) {
      outputRow.push(row[i]);
    }
    output.push(outputRow);
  });

  // With output array, pasting into report
  output.forEach((entry) => {
    let col = entry.shift();
    // Find where the next free slot is in column
    let occupiedRange = sheet2
      .getRange(1, col)
      .getDataRegion(SpreadsheetApp.Dimension.ROWS)
    let height = occupiedRange.getHeight();

    // Transposing array
    set = entry.map((val) => [val]);

    // Inserting Values to Report
    let outRange = sheet2.getRange(height + 1, col, 3, 1);
    outRange.setValues(set);
  });
}

Source Data in Blad1

Destination Template and script in action in Blad2

Explanation

You'll notice its quite a bit longer than your script! What you are trying to do is deceptively complex, which is why I hesitated to answer fully as this script is so far removed from what you initially posted that it almost seemed like it was a "give me the code" question. Though you are new on the site and I had already written out most of the code, so what the hell. In future please try to include more info in your original question, your attempts and research. I have tried to keep it an concise as possible, but there may be certain syntax that you haven't come across, like forEach and map.

• The script first gets the data with getValues that returns 2D arrays of the values.

• I take out the headers on the source data, and use the headers on the target data to find the column index where the source data will end up. So ENG is index 1, and X index 2 etc.

• For each row in the source data it transforms it into an intermediary array (which is not necessary, but I think its clearer to understand each step). The intermediary array is composed of sub arrays representing each "set". Each sub array has this format [Column_Index, Input1, Input2, Input3].

• Once this has been build, each of those sub arrays can be gone through to insert them into the output sheet, which I have called the "Report".

• Within this process is the need to get the first unoccupied row of the target column. So if ENG already has 3 sets that have been filled in, the script needs to know where the next set starts. It does this by using getDataRegion(SpreadsheetApp.Dimension.ROWS) then getHeight() + 1 to find the starting row for the set to be inserted.

• Also within this final process is the need to transform the array from this format:

  [1,2,3]
  

  Which Apps Script understands as a row, to a column, which would be this:

  [[1],[2],[3]]
  

  which was done with map.

I encourage you to use Logger.log to log a bunch of the values and inspect the output so that you can understand the script and adapt it to your needs. I have tried to name everything in a "friendly" manner.

References

• Map
• forEach
• Range object