Timed Triggers for Macros on Multiple Sheets in a Spread Sheet

Question

I'm trying to make a student signout sheet for a whole school. We have one main spreadsheet but multiple separate sheets within that (one for each homeroom, about 25 homerooms so 25 sheets). I created a macro that would automatically add two new columns (with the proper boarders and the new date being added to the top). I've set that macro to be triggered once a day (at midnight) so that these new columns populate on their own each day.

My issue is that the macro will only run on the first sheet in the spread sheet. I can't get it to run on the other sheets. I've tried creating each sheet (within the main spreadsheet) it's own macro and all that does is add them all to the first sheet instead.

Any idea how to make my macro run on every sheet in the spreadsheet instead of just the first one? I've attached my macro below:

function NewBlankDay() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('D:D').activate();
  spreadsheet.getActiveSheet().insertColumnsBefore(spreadsheet.getActiveRange().getColumn(), 1);
  spreadsheet.getActiveRange().offset(0, 0, spreadsheet.getActiveRange().getNumRows(), 1).activate();
  spreadsheet.getActiveSheet().insertColumnsBefore(spreadsheet.getActiveRange().getColumn(), 1);
  spreadsheet.getActiveRange().offset(0, 0, spreadsheet.getActiveRange().getNumRows(), 1).activate();
  spreadsheet.getRange('D3').activate();
  spreadsheet.getCurrentCell().setFormula('=F3+1');
  spreadsheet.getRange('D4').activate();
  spreadsheet.getCurrentCell().setValue('Left');
  spreadsheet.getRange('E4').activate();
  spreadsheet.getCurrentCell().setValue('Returned');
  spreadsheet.getRange('D3:E3').activate();
  spreadsheet.getActiveRangeList().setBorder(true, true, true, true, null, null, '#000000', SpreadsheetApp.BorderStyle.SOLID)
  .setBorder(true, true, true, true, null, null, '#000000', SpreadsheetApp.BorderStyle.SOLID_THICK);
  spreadsheet.getRange('D4:D184').activate();
  spreadsheet.getActiveRangeList().setBorder(null, null, null, true, null, null, '#000000', SpreadsheetApp.BorderStyle.SOLID_THICK)
  .setBorder(null, null, null, true, null, null, '#000000', SpreadsheetApp.BorderStyle.SOLID);
  spreadsheet.getRange('D3:E184').activate();
  spreadsheet.setCurrentCell(spreadsheet.getRange('E184'));
  spreadsheet.getActiveRangeList().setBorder(true, true, true, true, null, null, '#000000', SpreadsheetApp.BorderStyle.SOLID)
  .setBorder(true, true, true, true, null, null, '#000000', SpreadsheetApp.BorderStyle.SOLID_THICK);
  spreadsheet.getRange('H14').activate();
};

Also, is there a way to get it to add a boarder after every 5th row? We have it sorted into class periods (5 in a day). I've attached a picture of how ours looks in case that helps. imgur.com/a/wPUF1cK

Answer 1

Your code is designed to work only on the active sheet spreadsheet.getActiveSheet(). You need a way to activate and loop through each individual sheet.

You can get a list of sheets in your spreadsheet using .getSheets(), then activate the first sheet and run through your code, activate the second sheet, run through your code, and so forth, stopping only after you reach the last sheet. The for loop will run the same number of times as there are sheets i < sheets.length.

Here is an example :

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets = ss.getSheets();
    
  function doToEachSheet() {
    for (let i = 0; i < sheets.length; i++) {
      ss.setActiveSheet(sheets[i]);
      
      [ code goes here to loop
        through once per sheet ]

    }
  }

Incorporating Code to addColumns:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets = ss.getSheets();

function doToEachSheet() {
  for (let i = 0; i < sheets.length; i++) {
    
    let x = ss.setActiveSheet(sheets[i]);
    
//  +-----------------+
//  | loop through -> |
//  +-----------------+

    x.insertColumnsBefore(4, 2);

    x.getRange('D3').setValue('=F3+1');
    let nDate = x.getRange('D3').getValue();

    let values = [ [ nDate, nDate ],[ "Left", "Returned" ] ];
    x.getRange('D3:E4').setValues(values);
    
    let rng = x.getRange('D4:E184');
    rng.setBorder(true, true, true, true, null, null, '#000000', SpreadsheetApp.BorderStyle.SOLID_MEDIUM );
    rng.setBorder(null, null, null, null, null, true, '#818181', null );
    rng.setBorder(null, null, null, null, true, null, '#d8d4d4', null );

    for ( let j = 4; j < 184; j+=5 ) {
      let r = "D" + [j] + ":E" + [j];
      x.getRange(r).setBorder( null, null, true, null, null, null, '#000000', SpreadsheetApp.BorderStyle.SOLID_MEDIUM );
    };

    
//  +----------+
//  | end loop |
//  +----------+

  }

}