I'm trying to merge 2 tables into a single array with a key value column merge. I can't make a formula or find a suitable script.

Sheet1 has the names of the classrooms and equipment items. Sheet1

Sheet 3 shows the characteristics of the equipment items. Sheet2

How can I implement a line-by-line merge to get a result like on the Output sheet?

Output

I will be very grateful for help.

https://docs.google.com/spreadsheets/d/1CAxL2GcDv5jfoKhwhtvBFQGWg9caG9FD7HLIwEiJXbI/edit?usp=sharing

CodePudding user response：

function combine() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet1");
  const vs1 = sh.getRange(1,1,sh.getLastRow(),5).getValues();//table 1
  const vs2 = sh.getRange(1,6,sh.getLastRow(),5).getValues();//table 2
  let v = vs1.map((r,i) => vs1[i].concat(vs2[i]));//concat them back together
  Logger.log(v);//display
}

Execution log
3:14:21 PM  Notice  Execution started
3:14:21 PM  Info    [[COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10], [1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0, 8.0, 9.0, 10.0], [2.0, 3.0, 4.0, 5.0, 6.0, 7.0, 8.0, 9.0, 10.0, 11.0], [3.0, 4.0, 5.0, 6.0, 7.0, 8.0, 9.0, 10.0, 11.0, 12.0], [4.0, 5.0, 6.0, 7.0, 8.0, 9.0, 10.0, 11.0, 12.0, 13.0], [5.0, 6.0, 7.0, 8.0, 9.0, 10.0, 11.0, 12.0, 13.0, 14.0], [6.0, 7.0, 8.0, 9.0, 10.0, 11.0, 12.0, 13.0, 14.0, 15.0], [7.0, 8.0, 9.0, 10.0, 11.0, 12.0, 13.0, 14.0, 15.0, 16.0], [8.0, 9.0, 10.0, 11.0, 12.0, 13.0, 14.0, 15.0, 16.0, 17.0], [9.0, 10.0, 11.0, 12.0, 13.0, 14.0, 15.0, 16.0, 17.0, 18.0], [10.0, 11.0, 12.0, 13.0, 14.0, 15.0, 16.0, 17.0, 18.0, 19.0], [11.0, 12.0, 13.0, 14.0, 15.0, 16.0, 17.0, 18.0, 19.0, 20.0], [12.0, 13.0, 14.0, 15.0, 16.0, 17.0, 18.0, 19.0, 20.0, 21.0], [13.0, 14.0, 15.0, 16.0, 17.0, 18.0, 19.0, 20.0, 21.0, 22.0], [14.0, 15.0, 16.0, 17.0, 18.0, 19.0, 20.0, 21.0, 22.0, 23.0], [15.0, 16.0, 17.0, 18.0, 19.0, 20.0, 21.0, 22.0, 23.0, 24.0]]
3:14:22 PM  Notice  Execution completed

Sheet1:

CodePudding user response：

My solution is here:

function main() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Output');

  var data = get_data_from_sheet1();   // get data from Sheet1
  var items = get_items_from_sheet3(); // get the items object from Sheet3

  // make the output table from the items object and the data
  var output = [];

  for (let row of data) {
    var type     = row[0];
    var subtype  = row[1];
    var position = row[3];

    for (let params of items[position]) {
      var model          = params[0];
      var characteristic = params[1];
      var value          = params[2];
      var unit           = params[3];
      output.push([type, subtype, '', position, model, characteristic, value, unit]);
    }
  }

  // clear the output range and put the output table on the sheet 'Output'
  sheet.getRange('b3:i').clear();
  sheet.getRange(3,2,output.length,output[0].length).setValues(output);
}

// additional functions

function get_data_from_sheet1() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');
  var data = sheet.getRange('b3:e' sheet.getLastRow()).getValues();
  return data;
}

function get_items_from_sheet3() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet3');
  var data = sheet.getRange('b3:f' sheet.getLastRow()).getValues();

  var items = {};
  data.forEach(x => {
    var position = x[0];     // Position
    var params = x.slice(1); // [ Model, Characteristic, Value, Unit ]
    try { items[position].push(params) } catch(e) { items[position] = [params] }
  })
  return items;
}

Rather for educational purposes, the loop in main() function can be less verbose:

function main() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Output');

  var items = get_items_from_sheet3();
  var data = get_data_from_sheet1();
  var output = [];

  for (let row of data) {
    var [type, subtype, _, position] = row;
    items[position].forEach(params => {
      output.push([type, subtype, _, position, ...params]);
    })
  }

  sheet.getRange('b3:i').clear();
  sheet.getRange(3,2,output.length,output[0].length).setValues(output);
}