My building’s Instructional Supervisor came to me recently with a challenge. They are using a Google Form to submit necessary information, when the nurse is notified a student needs to quarantine. The information is placed in a Sheet for all those who need to be notified, like our related arts teachers (specials), to see who’s quarantining, how long, when it starts, etc. so they can prepare virtual instruction.
He wanted a way to streamline communication (not rely on if someone went in & checked the Sheet) as well as wanted the Sheet formatted for the data collected so it was easy to read. To tackle the first challenge, we used the add-on formMule. I’ve talked about what formMule does and how I have used it in prior posts. For the second challenge, I looked to Google Apps Script. The issue was the data would be inserted as a new row in the Sheet after each Form submission. Any formatting in the Sheet wouldn’t carry over to the newly inserted row, such as a preset font, size, and justification. So I did a little digging and came up with this script:
function onEdit(e) {var ss = SpreadsheetApp.getActiveSheet();var range = ss.getRangeList( [ ‘A:G’ ] );range.setFontFamily( “Lexend Deca” ).setFontSize(14).setHorizontalAlignment( “center” );}
- screenshot of the Form (I didn’t embed so I wouldn’t get new submissions)
- embedded Google Sheet
- screenshot of example email from formMule
