Google-sheets – Auto create sheets within a spreadsheet with formatted columns and cells

google sheetsgoogle-apps-script

What I would like to do is be able to run the script and have it create four new sheets (within the current sheet I'm in) with the following formatting in each:

1st row frozen with column headings on Column A, B, C, D, and F.  
A formula in E2 (=D2*100) that would be copied all the way to E30

I want to also do some other formatting but if I see the script for the top two I can hopefully learn enough to do the rest on my own. Right now I have started to write a script that uses the insertSheet command to create the sheets but I am stuck at that point.

Best Answer

Here is the script that does what you described. The formulas are prepared in a double array formulas ahead of time. Then, each new sheet gets one frozen row, columns headings, and formulas. 

function setup() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var formulas = [];
  for (var i = 2; i <= 30; i++) {
    formulas.push(['=D' + i + '*100']);
  }
  for (i = 1; i <= 5; i++) { 
    var sheet = ss.insertSheet('New Sheet '+ i);
    sheet.setFrozenRows(1);
    sheet.getRange("A1:F1").setValues([['Heading 1', 'Heading 2', 'Heading 3', 'Heading 4', '', 'Heading 5']]);
    sheet.getRange("E2:E30").setFormulas(formulas); 
  }
}

However it's easier to use arrayformula command that deals with all multiplications at once: =arrayformula(D2:D30*100). Then the script is shorter: 

function setup() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  for (i = 1; i <= 5; i++) { 
    var sheet = ss.insertSheet('New Sheet '+ i);
    sheet.setFrozenRows(1);
    sheet.getRange("A1:F1").setValues([['Heading 1', 'Heading 2', 'Heading 3', 'Heading 4', '', 'Heading 5']]);
    sheet.getRange("E2").setFormula("=arrayformula(D2:D30*100)");
  }
}
Related Topic