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” )
.setHorizontalAlignment( “center” );
The script is triggered every time a submission from the Form is received.
We also used conditional formatting for the cell (‘when remote instruction starts’) to change color to bright yellow when the cell is not empty. Take a look at the embedded items below:
  • screenshot of the Form (I didn’t embed so I wouldn’t get new submissions)
  • embedded Google Sheet
  • screenshot of example email from formMule

Have you worked with Google Apps Script? Would love to read about how you have utilized this tool in the comments below.

Leave a Reply

Your email address will not be published. Required fields are marked *