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