If you haven't already set up your GMail and your computer to run this macro, you will first need to go to this page and follow a lot of instructions.
The macro is going to assume that you downloaded everything from my .zip file and that you now have an ATR folder with the following contents:
In the one-time setup instructions on the above-referenced page, we already updated the two documents with the "(No Touchy)" labels, so you shouldn't need to touch these again. The exception is if you need/want to change your default email text. In that case, go ahead and adjust "^BodyText (No Touchy).txt" now. The default text is what you want it to send EVERY TIME. You will have an opportunity later in these instructions to add onto the email body with information unique to the current email being prepared.
Officially, you are done with the ATRMacro.txt document and can delete it.
Gathering your raw data
The 4 .xlsx files are your four input sources for all of the data that will become the On-Demand ATR (ODATR). To make this super quick to run in the future, I'm going to recommend you bookmark each one in a folder so you can just always grab the most recent data:
FullRoster.xlsx
Go to Looker and pull up the Full Roster report. It will default to your Service Unit, and the only filters you should set are:
Year: Most of the time, you will leave this set as "is Current Year", but when you're getting into Spring/Summer and need to see who is registered for next year, you can switch this toggle. This becomes important as people who have renewed will drop off of the current year rosters, so you may want to run both versions.
Troop or Group: Set the first drop-down to "is not" and in the second drop-down, choose "SU### Not Participating at this Time" and "Not Applicable".
In the new box that pops up, choose Excel Spreadsheet and hit Download:
If it prompts for where to save it, you should have it overwrite your existing "FullRoster.xlsx" in your ATR folder. This ensures you don't pick up any weird characters or accidentally misspell the name and cause the macro to freak out.
If it didn't prompt you to save, then go rescue it out of wherever your downloads go and make sure it is named "FullRoster.xlsx" with no spaces and save it over the existing one in your ATR folder.
gsLearn.xlsx
Go to Looker and pull up the gsLearn report. It will default to your Service Unit, and the only filters you should set are:
Course Completion Flag: Set it to "is Y". We only want to see a listing for classes that are actually complete.
Course Title: Set the first drop-down box to "is" and then make sure you have all of the following courses included in the second drop-down (order doesn't matter, but you need all 13 of these):
- 664 GSOSW Foundations
- 664 GSOSW Volunteer Essentials
- 664 GSOSW Troop Finance
- 664 GSOSW Mandatory Reporter Training for Volunteers
- 664 GSOSW Cookie Booth Helper Training
- 664 GSOSW Girl Scouts Inside & Out Training, Part 1
- 664 GSOSW Girl Scouts Inside & Out Training, Part 2
- 664 GSOSW Day Trips Training
- 664 GSOSW Indoor Overnights Training
- 664 GSOSW Extended Travel Training
- 664 GSOSW Outdoor Skills Training
- 664 GSOSW Fire Safety Training
- 664 GSOSW Grade Level Training COMPLETED
Just like you did for FullRoster.xlsx, you're going to repeat the following:
- Bookmark the report if you haven't already.
- Wait for the report to finish refreshing, then download the data as a Microsoft Excel file. This time you're going to name it "gsLearn.xlsx" and make sure it overwrites the older one in your ATR folder.
Certifications.xlsx
Little bit of background info on this spreadsheet. All of the data for this is hand-entered by GSOSW staff. They don't always name all of the trainings the exact same thing. This report is looking for two particular text strings to be contained in the cells that have the training descriptions: "First Aid, CPR, & AED" and "First Aid, CPR & AED" because they are inconsistent about whether or not they include the Oxford Comma. If you manually review the data for your Service Unit, you can send in any outliers to Answers@girlscoutsosw.org and have them fixed. (I found one with CPR spelled wrong and one missing a space that was causing them to get missed by my macro. Definitely good to review this manually at least once.)
Start by going to your Volunteer Reports Google Drive folder that GSOSW should have made available to everyone on your Service Team. In the root of that folder, is a Certifications Google Sheet. Double click on it to open it in a new browser window.
Once you have it in a new window, you can bookmark it and you won't need to go into the Volunteer Reports folder to get to it any more.
At this point, you'll also go to the File Menu and choose the Download option. This will convert it from a Google Sheets file to a Microsoft Excel .xlsx file. Save it to your ATR folder as "Certifications.xlsx".
Brief Intermission for Security Purposes!
At this point, you have 3 spreadsheets downloaded. And if you are using the default Windows level of security, they are going to not be allowed to be edited automatically by a macro. (You can confirm this by opening one of them, and if you get the yellow bar across the top asking if you want to enable editing, this is why.)
To bypass this error, you can just right click on each of the spreadsheets and choose Properties. Check the little box at the bottom that says "Unblock" and hit OK. Just like we did with the email testing spreadsheet earlier.
You will need to do this to all 3 spreadsheets that you have downloaded. And do it again, each time you download a fresh copy of any of those 3 spreadsheets.
FinDash.xlsx
We saved this one for last because you don't actually download it, so much as copy just what you need and paste it into a fresh clean spreadsheet. First, open your "My## Troop Financial Dashboard". Make sure you have the right year if you've been on your Service Team for a while... The two digit number will be the last 2 digits of the year that the current GS year started on. So, for 2023-2024, the prefix will be "MY23". This is the one that everyone on the Service Team can see, not just the Treasurer. It's the one that has one tab for each SU across the bottom, not the full product qualification sheets for just your SU. If you can't find it in your shared with me folders, you can open the most recent official ATR and the link will be on the top of the summary page.
Once you have this one open, you are going to highlight the active troop data in the middle. So, starting with the cell in column A and the row that has "20## Treasurer:" (row 13 in this example), you're going to highlight over through column M (which should be the financial notes), and down until you have selected only the active troops. Do not grab blank lines or disbanding troops. Once you have that block selected, choose Copy.
Open a new blank Excel spreadsheet. (Not a new tab in any of the existing ones, an actual new Book1.)
In cell A1, right click and choose the Paste Values (Match Destination Formatting) option. Please don't bring the Google Sheets formatting over with you.
If it worked, you should see a big gray block of unformatted text going as far as column M and with your SU# showing in cell B3. This is actually important... One of the first things this macro is going to do is store that number so that it can properly filter and name results later. If your data doesn't look like this right now, go fix it.
Save this spreadsheet as FinDash.xlsx in your ATR folder. Don't close it. Just save it.
Here we go!
This is the pause before the storm. If all of the directions were followed, and if neither GSUSA or GSOSW changed the format of anything we use, then we're about to get some epic results!
While you have FinDash.xlsx open (and no other spreadsheets open), go to the Quick bar at the top of your spreadsheet and choose the little macro button you saved there during your setup. (Or if you don't like to be efficient, you can go to the View menu, click on the Macros section on the ribbon, choose the MyATR macro and hit Run.)
You're going to get some prompts:
- Prompt #1 is for you to confirm if you remembered to unblock the spreadsheets that you downloaded. If you didn't, make sure you say "No", then go do that step. (The Security Intermission above.)
- Prompt #2 is for you to choose if you only want a formatted ODATR report, or if you want the whole enchilada! The whole enchilada version separates out all of the troop tabs into individual documents so that they can be sent to the troops. It makes you .csv files that you can import into GMail Contacts by role. It also builds the automatic emailer so that you can just click a button and send them all automatically. Honestly, I always take the option to separate everything for emailing.
- Prompt #3 isn't so much a prompt as a note that the macro is about to start doing things and NOT refreshing your screen while it does it. Do not panic. Do not click on anything (after clicking okay on this warning). Do not pass Go. Do not collect $200. The macro is building some complex cross-referencing and cleaning up a LOT of data so that you don't have to. It will take a few minutes. My SU1 data with just under 40 troops takes as much as 10 minutes. When it is done, there will be a new pop-up box telling you that it is done. Until you see that, please don't click on anything on your computer.
If it goes more than 30 minutes, you probably have a data problem somewhere. At that point, you can go to your Task Manager and kill Excel. When this has happened to me in the past, it was one of these things:
- GSUSA changed the number and/or order of the fields on their Looker output. I mean, it was beneficial because they finally added the GlobalID that I now use to link all the data sources, but they added it in column D and it mangled everything until I figured it out! The only fix for this is to figure out what changed and update the macro. If I catch it, I fix it. If you caught it, then I might not know about it yet.
- GSOSW changed something. GSOSW is in charge of the layout/format of the Certifications spreadsheet and the Financial Dashboard that we're using. If they changed any columns or data locations on either one of those, the macro will break. Same deal, I always fix it as quickly as I can after finding it.
- I messed up. Sometimes I forget to unblock the spreadsheets, but I still say that I did and lying to the macro is not a good idea. The most common problem I cause myself is forgetting to actually save the new blank FinDash spreadsheet before kicking off the macro. You HAVE TO SAVE IT first. That's how the rest of the macro knows the directory structure and how it will be able to re-reference the financial pieces while it's jumping between spreadsheets. Sometimes I decide to go do other things on my computer while the macro is running because I forget that it's doing something important and I shouldn't distract it. Just leave it alone until it says it's done.
OUTPUT!
If the macro completed successfully, you will either have just a new ODATR report or you'll also have an emailer spreadsheet and a bunch of separate files.
Features of the ODATR:
On the Summary tab, there are 5 levels of highlighting:
- RED - Not eligible for product sales
- ORANGE - Not eligible to meet in person
- YELLOW - Not a Troop in Good Standing
- GREEN - Not enough girls for official troop*
- BLUE - Not allowed to meet outside regular troop meetings/location
*Green won't highlight the Troop# column since it isn't something that can be fixed directly. The other 4 colors are adults not completing tasks, and are totally fixable.
In addition to flagging the troop number with the color, it will also highlight the cell(s) that triggered the alert. The First Aid column and each of the adult role columns (other than "Adult Members") will display a subtotal of people who have that certification or role, and if any of them need attention, it will include how many of them are missing something in brackets. IE: "5 [2]" means that you have 5 people in that role, but 2 of them have something that needs attention. Alerts are: if a required training is missing, if a background check or certification is missing or expired, if a background check or certification will be expiring in the next 45 days, or if you don't have the minimum number of people in that role/certification/training category.
It also does some basic lookups on the financial column to indicate what is missing:
- TFR - Last year's Troop Financial Report is missing or not yet approved
- Signer(s) - One or more bank signers are missing training or background checks.
- BankAcct - Troop needs to confirm that they have a working bank account.
On the Troop tabs, there are two types of flags:
- Red - A cell will be highlighted red if a required component is missing according to the role(s) selected for that adult. This could be training or an expired or missing background check.
- Yellow - The two fields with expiration dates (FA/CPR/AED & Background Checks) will start showing up as yellow when they are within 45 days of expiring.
Features of the Emailer:
In your ATR folder, you now have a subfolder with today's date and inside that folder are a few different things:
- GlobalAttach subfolder - Anything you drop in this folder will be included for ALL recipients when you trigger the mass emails later. Good for event flyers, announcements, etc. It is automatically populated with a copy of the ATR Summary page so that troops can see their status.
- ^BodyText.txt - This one doesn't say "No Touchy". Edit this copy of the text document to use as the body of the mass email you have planned with today's data. I like to put a "What's New" section on the top and leave my generic "how to read these reports" instructions that were brought over from my No Touchy version on the bottom.
- ^Send_ATR_Emails.xlsm - This is the copy of the spreadsheet for building today's actual mass email. It's been pre-populated with all of the leaders' email addresses and what unique attachments their troop should receive. The macro already left it open for you, so you shouldn't need to do anything with it from here.
- All {Treasurers/Troop Leaders/Troop Product Managers}.xlsx - These are spreadsheets filtered down to just those roles so that you can quickly grab info across the whole SU.
- Contacts - {Treasurers/Troop Leaders/Troop Product Managers}.csv - These are files that you can import into Google Contacts and use for future emails. (Unrelated to this mass email tool.) Makes it easier for the SUPM to import all TPM contact info, or the Treasurer to grab all the Treasurers, etc.
- Remaining *ATR.xlsx and *ATR.pdf files - These are the individual attachments for each troop. It's their tabs from the main ATR report, but with extra formatting to flag trouble areas. These will be sent by the mass emailer, just to the troop they belong to.
There are 3 main sections of the emailer spreadsheet:
- The Mass Email Settings section - When the emailing macro is triggered, it will use this section to determine the Subject of the email (replacing the <Troop#> field with the actual contents of the Troop# column in the Individual Troop Information section), and the folders where it will find the attachments and default body text. You can alter the text of the Subject, but probably won't ever mess with the folder options. You may want to take a look inside the actual GlobalAttach folder if you wat to send documents to everyone at the same time.
- The Individual Troop Information section - This is pre-filled by the ODATR macro to list all of the email addresses of the leaders of each troop, and will list the individualized attachments they will receive. You usually won't change anything down in this section, but you do have the option to put an "X" to disable a specific row. The Result column will be blank by default, but after you run the macro, it will populate with a date/time of successful transmission or an error message if it couldn't send. If you want to go back and send the emails for the ones you disabled, just remove the X and hit the send button again. It won't trigger a new email for anyone with "Sent" in the first column.
- The Pretty "Send the Gmail" Button - Once you've confirmed your subject line and disabled any troops you don't want to email right now, you click the pretty button in the upper right corner and it will send all of the troops' emails automatically. It generally takes about 2 minutes to send emails to a little over 100 leaders.
Future Reports
- Download Full Roster Looker report as FullRoster.xlsx.
- Download gsLearn Looker report as gsLearn.xlsx.
- Download Certifications spreadsheet from Google Drive as Certifications.xlsx.
- Right click on each spreadsheet and unblock the security.
- Open the Troop Financial Dashboard and copy the active troop area only.
- Open a new blank Excel file and right click in cell A1 to paste without formatting.
- Save this new spreadsheet as FinDash.xlsx.
- Hit the Macro button.
- Giggle and spam your troops with their data so they can fix any outstanding issues.