neonblack

JS Sheet

Mar 2nd, 2020
229
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. function copyAllForms() {
  2.   copyForms(true,true);
  3. }
  4.  
  5. function copyStandardForms() {
  6.   copyForms(true,false);
  7. }
  8.  
  9. function copyLeaderForms() {
  10.   copyForms(false,true);
  11. }
  12.  
  13. function copyForms(sheet1,sheet2) {
  14.   var mSheet = SpreadsheetApp.getActiveSpreadsheet();
  15.   var sheetS = mSheet.getSheetByName('Standard');
  16.   var sheetL = mSheet.getSheetByName('Leadership');
  17.   var sheetD = mSheet.getSheetByName('Data');
  18.  
  19.   var copies = sheetD.getRange(2,2,sheetD.getMaxRows(),3).getValues()
  20.     .filter(function(x) { return x[0] != ''; });
  21.  
  22.   makeDupes(copies, sheetS, sheetL, sheet1, sheet2);
  23. }
  24.  
  25. function makeDupes(copies, sheetS, sheetL, sheet1, sheet2) {
  26.   copies.forEach((nSheet)=> {
  27.     if (nSheet[1] && sheet1)
  28.       dupSheet(nSheet[0], sheetS, "Standard");
  29.     if (nSheet[2] && sheet2)
  30.       dupSheet(nSheet[0], sheetL, "Leadership");
  31.   })
  32. }
  33.  
  34. function dupSheet(nName, oSheet, sName) {
  35.   var sParent = SpreadsheetApp.openById(nName);
  36.   var nSheet = sParent.getSheetByName(sName);
  37.   if (nSheet == null)
  38.     nSheet = sParent.insertSheet().setName(sName);
  39.   var mRanges = nSheet.getRange("A1:ZZZ9999").getMergedRanges();
  40.   mRanges.forEach((rng) => rng.breakApart());
  41.   if (nSheet.getMaxRows() > oSheet.getMaxRows())
  42.     nSheet.deleteRows(oSheet.getMaxRows()+1,nSheet.getMaxRows() - oSheet.getMaxRows())
  43.   if (nSheet.getMaxColumns() > oSheet.getMaxColumns())
  44.     nSheet.deleteColumns(oSheet.getMaxColumns()+1,nSheet.getMaxColumns() - oSheet.getMaxColumns())
  45.   mRanges = oSheet.getRange("A1:ZZZ9999").getMergedRanges();
  46.   var a1Note;
  47.   mRanges.forEach((rng) => {
  48.     a1Note = rng.getA1Notation();
  49.     nSheet.getRange(a1Note).merge();
  50.   });
  51.   dupRules(nSheet, oSheet);
  52.   for (var c = 0; c < oSheet.getMaxColumns(); c++) {
  53.     nSheet.setColumnWidth(c+1, oSheet.getColumnWidth(c+1));
  54.     for (var r = 0; r < oSheet.getMaxRows(); r++) {
  55.       dupCell(r+1,c+1,nSheet,oSheet);
  56.     }
  57.   }
  58.   nSheet.setFrozenRows(oSheet.getFrozenRows());
  59.   addImage(nSheet, sName);
  60. }
  61.  
  62. function dupCell(row,col,nSheet,oSheet) {
  63.   var oRng = oSheet.getRange(row,col);
  64.   var nRng = nSheet.getRange(row,col);
  65.   nRng.setValue(oRng.getValue());
  66.   if (oRng.getFormula() != "") {
  67.     nRng.setFormula(oRng.getFormula());
  68.     nRng.setNumberFormat(oRng.getNumberFormat());
  69.   }
  70.   nRng.setFontColor(oRng.getFontColor());
  71.   nRng.setFontSize(oRng.getFontSize());
  72.   nRng.setFontWeight(oRng.getFontWeight());
  73.   nRng.setFontStyle(oRng.getFontStyle());
  74.   nRng.setFontLine(oRng.getFontLine());
  75.   nRng.setFontFamily(oRng.getFontFamily());
  76.   nRng.setBackground(oRng.getBackground());
  77.   nRng.setHorizontalAlignment(oRng.getHorizontalAlignment());
  78.   nRng.setVerticalAlignment(oRng.getVerticalAlignment());
  79.   nRng.setWrapStrategy(oRng.getWrapStrategy());
  80.   if (oRng.getBackground() == '#fefefe') {
  81.     nRng.setBorder(true,true,true,true,true,true,'#fefefe',SpreadsheetApp.BorderStyle.SOLID);
  82.   } else {
  83.     nRng.setBorder(true,true,true,true,true,true,'black',SpreadsheetApp.BorderStyle.SOLID);
  84.   }
  85.   if (row == 1)
  86.     nRng.setBorder(true,null,null,null,null,null,'black',SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
  87.   if (col == 1)
  88.     nRng.setBorder(null,true,null,null,null,null,'black',SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
  89.   if (row == nSheet.getMaxRows())
  90.     nRng.setBorder(null,null,true,null,null,null,'black',SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
  91.   if (col == nSheet.getMaxColumns())
  92.     nRng.setBorder(null,null,null,true,null,null,'black',SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
  93.   nRng.setDataValidation(oRng.getDataValidation());
  94. }
  95.  
  96. function dupRules(nSheet, oSheet) {
  97.   var rules = oSheet.getConditionalFormatRules();
  98.   var nRules = []
  99.   rules.forEach((rule) => {
  100.     rule.getRanges().forEach((rng) => {
  101.       nRules.push(SpreadsheetApp.newConditionalFormatRule()
  102.         .withCriteria(rule.getBooleanCondition().getCriteriaType(),rule.getBooleanCondition().getCriteriaValues())
  103.         .setBackground(rule.getBooleanCondition().getBackground())
  104.         .setRanges([nSheet.getRange(rng.getA1Notation())])
  105.         .build());
  106.     });
  107.   });
  108.   nSheet.setConditionalFormatRules(nRules);
  109. }
  110.  
  111. function addImage(nSheet, sName) {
  112.   var images = nSheet.getImages();
  113.   images.forEach((image) => {
  114.     image.remove();
  115.   });
  116.   var blob = "https://drive.google.com/uc?export=view&id=1s4tCwyfEdTSwVUEVqHDlJpN9KtPVR6lj";
  117.   nSheet.insertImage(blob, 10, 1).assignScript("clear"+sName);
  118. }
RAW Paste Data