I have an array/table in Google Sheets with data in the following format:

I want to use Google App Script to convert this array into the following format:

Thanks in advance.

CodePudding user response：

app script

Try

function myFunction(){
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sh = ss.getActiveSheet()
  var list1 = sh.getRange(1,2,1,sh.getLastColumn()-1).getValues().flat().filter(onlyUnique)
  var list2 = sh.getRange(2,2,1,sh.getLastColumn()-1).getValues().flat().filter(onlyUnique)
  var values = sh.getRange(1,2,3,sh.getLastColumn()-1).getValues()
  var result = Array.from({ length: list1.length }, () => Array.from({ length: list2.length }, () => ''));
  for (var i=0;i<sh.getLastColumn()-1;i  ){
    result[list1.indexOf(values[0][i])][list2.indexOf(values[1][i])] = values[2][i]   
  }
  sh.getRange(6,1,list1.length,1).setValues(transpose([list1]))
  sh.getRange(5,2,1,list2.length).setValues([list2])
  sh.getRange(6,2,list1.length,list2.length).setValues(result)
}
function onlyUnique(value, index, self) {
  return self.indexOf(value) === index;
}
function transpose(a){
  return Object.keys(a[0]).map(function (c) { return a.map(function (r) { return r[c]; }); });
}

formula

=query(TRANSPOSE(B1:3),"select Col1, sum(Col3) where Col1 is not null group by Col1 pivot Col2")

CodePudding user response：

Here is my variant:

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();

  // get the data (without first column)
  var data = sheet.getDataRange().getDisplayValues().map(x => x.slice(1));

  // get rows and columns names
  var rows = Array.from(new Set(data[0])); // A, B, C...
  var cols = Array.from(new Set(data[1])); // Q1, Q2, Q3...
  
  // create the empty table
  var table = Array(rows.length).fill('')
    .map(x => x = Array(cols.length).fill(''));

  // fill the table with values from third row of data ('Proportions')
  for (let i in data[0]) {
    let row = rows.indexOf(data[0][i]);
    let col = cols.indexOf(data[1][i]);
    table[row][col] = data[2][i];
  }

  // add the header and first column (A,B,C...) to the table 
  var header = ['Category', ...cols];
  var body = table.map((x,i) => [rows[i], ...x]);
  table = [header, ...body];

  // put the table on the sheet
  sheet.clear().getRange(1,1,table.length,table[0].length).setValues(table);

  // set font of the header to 'Bold'
  sheet.getRange(1,1,1,sheet.getLastColumn()).setFontWeight('Bold');
}

It should work for any number of categories and letters. And it doesn't even need to sort the columns, they can be like: Q1, Q3, Q1, Q2... etc

If your data always sorted the script can be a little bit simpler and probably more efficient:

function myFunction2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getDisplayValues();
  
  var letters = Array.from(new Set(data[0].slice(1)));    // A,B,C
  var categories = Array.from(new Set(data[1].slice(1))); // Q1,Q2,Q3,Q4,Q5
  var proportions = data[2].slice(1);                     // %,%,%...
 
  // make a table from the proportions
  var table = [];
  var rows = letters.length
  while (rows--) {
    var row = [];
    var cols = categories.length;
    while (cols--) row.push(proportions.shift());
    table.push(row);
  }
  
  // add a header and first column to the table
  var header = ['Categories', ...categories];
  var body = table.map((x,i) => [letters[i], ...x]);
  table = [header, ...body];
  
  // put the table on the sheet
  sheet.clear().getRange(1,1,table.length,table[0].length).setValues(table);
}