I have 3 different sheets in my spreadsheet that I need some specific columns to be combined into a new one. The sheets are as follow:

Sheet 1

Sheet 2

Sheet 3

So basically what I need to is to make like an union between the 3 sheets mentioned above, so in the final sheet it will look something like this:

I have created this script for getting data out off sheet 2:

function copySheet() { 
 var sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
 var destSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet Final");

 var columns_to_be_copied = [4,1];

 for (var i = 0; i < columns_to_be_copied.length; i  ) {
  var sourcerange = sourceSheet.getRange(2, columns_to_be_copied[i], sourceSheet.getLastRow()-1,1)
  sourcerange.copyTo(destSheet.getRange(1,(i 1)))
 }
}

But I don't know how to recreate this on a single code for the other sheets? Is there any way that it can be done?

CodePudding user response：

Get id and alliances

function combine() {
  const ss = SpreadsheetApp.getActive();
  const osh = ss.getSheetByName("Sheet0")
  const shts = ["Sheet1","Sheet2","Sheet3"];
  const sObj = {"Sheet1":{id:1,alliance:2},"Sheet2":{id:4,alliance:1},"Sheet3":{id:2,alliance:4}}
  let oA=[["ID","Alliance"]];
  ss.getSheets().filter(sh => ~shts.indexOf(sh.getName())).forEach(sh => {
    let n = sh.getName();
    sh.getRange(2,1,sh.getLastRow() - 1,sh.getLastColumn()).getValues().forEach(r => oA.push([r[sObj[n].id-1],r[sObj[n].alliance-1]]))
  });
  osh.clearContents();
  osh.getRange(1,1,oA.length,oA[0].length).setValues(oA);

}

Output:

CodePudding user response：

• Create a configuration object with the columns needed.

• Manipulate the array using methods like Array.map

const getData = () => {
  const config = {
      Sheet1: { id: 1, alliance: 2 },
      Sheet2: { id: 4, alliance: 2 },
    },
    ss = SpreadsheetApp.getActive(),
    sheets = Object.keys(config),
    data = sheets.flatMap((name) =>
      ss
        .getSheetByName(name)
        .getDataRange()
        .getValues()
        .map((row) => [row[config[name].id-1], row[config[name].alliance-1]])
    ),
    outputsheet = 'Sheet[out]';
  ss.getSheetByName(outputsheet).getRange(1, 1, data.length, 2).setValues(data);
};