Is there a way that we can break the table based on 'Bank with' column (already sorted)?

Basically breaking into:

Thank you!

CodePudding user response：

It can be done several ways. Here is one of them:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();

  // get data from the first sheet
  var sheet = sheets[0];
  var [header, ...data] = sheet.getDataRange().getValues();
  var banks = Array.from(new Set(data.map(x => x[2])));

  // make the copies of the first sheet and rename them
  banks.forEach(b => sheet.copyTo(ss).setName(b));

  // remove redundant rows from the copied sheets
  for (let bank of banks) {
    let sheet = ss.getSheetByName(bank);
    let range = sheet.getDataRange();
    let [header, ...data] = range.getValues();
    data = data.filter(x => x[2] == bank);
    range.offset(1,0).clear();
    sheet.getRange(2,1,data.length,data[0].length).setValues(data);
  }
}

The script puts each new table on a new sheet.

CodePudding user response：

Break up into smaller tables

function breakthebank() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const osh = ss.getSheetByName("Sheet1");
  const vs = sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).getValues();
  const obj = {pA:[]};
  vs.forEach(r => {
    if(!obj.hasOwnProperty(r[2])) {
      obj[r[2]] = [];
      obj[r[2]].push(["Client Name","Age","Bank with"]);//running headers
      obj[r[2]].push(r);
      obj.pA.push(r[2]);
    } else {
      obj[r[2]].push(r);
    }
  });
  let arr = obj.pA.map(p => obj[p]);
  console.log(JSON.stringify(arr));
  osh.clearContents();//clear output sheet
  arr.forEach(e => {
    e.push(new Array(e[0].length).fill(' '));//insert space
    osh.getRange(osh.getLastRow()   1, 1, e.length,e[0].length).setValues(e);
  })
}

This version bolds each new header row.

function breakthebank() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const osh = ss.getSheetByName("Sheet1");
  const vs = sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).getValues();
  const obj = {pA:[]};
  vs.forEach(r => {
    if(!obj.hasOwnProperty(r[2])) {
      obj[r[2]] = [];
      obj[r[2]].push(["Client Name","Age","Bank with"])
      obj[r[2]].push(r);
      obj.pA.push(r[2]);
    } else {
      obj[r[2]].push(r);
    }
  });
  let arr = obj.pA.map(p => obj[p]);
  console.log(JSON.stringify(arr));
  osh.clear();
  arr.forEach(e => {
    e.push(new Array(e[0].length).fill(' '));
    osh.getRange(osh.getLastRow()   1, 1, 1, e[0].length).setFontWeight("bold");
    osh.getRange(osh.getLastRow()   1, 1, e.length,e[0].length).setValues(e);
  })
}

3 Separate Sections and 3 separate sheets

function breakthebank() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const osh = ss.getSheetByName("Sheet1");
  const vs = sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).getValues();
  const obj = {pA:[]};
  vs.forEach(r => {
    if(!obj.hasOwnProperty(r[2])) {
      obj[r[2]] = [];
      obj[r[2]].push(["Client Name","Age","Bank with"]);//running headers
      obj[r[2]].push(r);
      obj.pA.push(r[2]);
    } else {
      obj[r[2]].push(r);
    }
  });
  // all on one sheet
  let arr = obj.pA.map(p => obj[p]);
  console.log(JSON.stringify(arr));
  osh.clear();//clear output sheet
  arr.forEach(e => {
    e.push(new Array(e[0].length).fill(' '));//insert space
    osh.getRange(osh.getLastRow()   1, 1, 1, e[0].length).setFontWeight("bold");
    osh.getRange(osh.getLastRow()   1, 1, e.length,e[0].length).setValues(e);
  });
  //3 separate sheets
  const ishts = ["Sheet2","Sheet3","Sheet4"];
  arr.forEach((e,i) => {
    let ish = ss.getSheetByName(ishts[i]);
    ish.clear();
    ish.getRange(ish.getLastRow()   1, 1, 1, e[0].length).setFontWeight("bold");
    ish.getRange(ish.getLastRow()   1, 1, e.length,e[0].length).setValues(e);
  });
}