1

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

2
  • Please put your code in a code block for formatting and readability. Editing Help#code Nov 21, 2022 at 19:18
  • Thanks! New to this, so I appreciate the heads up! Nov 21, 2022 at 22:09

1 Answer 1

0

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 |
//  +----------+

  }

}

sample

7
  • Thank you so much! I'm super new to macros and code. So should I copy what you have there into the Script Editor, and then just copy and paste my code from above in the section you indicated? Nov 21, 2022 at 22:08
  • This worked perfectly, thank you so much ! It's exactly what we needed. This is just minor, so if you can't do it no worries, we can manage without it. But 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 Nov 24, 2022 at 13:16
  • 1
    Thank you again, especially for coming back to do the small edits! It's very appreciated :) Nov 28, 2022 at 17:52
  • Hi again! Hoping you may be able to help. Is there a way for me to add a sheet to the front and have it NOT be included in the loop that is updating every other page in the spreadsheet? So that that one sheet just stays normal and doesn't add the new date/formatting every day? Feb 10 at 15:16
  • 1
    Thanks again! You're a life saver! Feb 10 at 16:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.