Embedded Files
Google Announcements System
A simple system I setup so that staff could fill in a Google Form and have the announcements view-able on any web connected device. The morning registration period is when the the Google Data Studio reports is shown to the relevant year groups, the pupils also have access to the reports from their homepages and mobile phones.
Feel free to make a copy of the files below, I have also included the formulas that are used on the Google Sheet at the bottom of the page.
- Apologies for my dog's claws on the laminate flooring :(
- In order to auto refresh the Google Sheets data to the Google Data Studio then please use this Chrome Extension:
https://chrome.google.com/webstore/detail/data-studio-auto-refresh/inkgahcdacjcejipadnndepfllmbgoag
Google Form:
Google Sheet:
Google Data Studio:
Formulas:
- Display Today's Date:
=now()
- Pull data from one sheet to another:
=query('Form Responses 1'!A2:G)
- Making sure the start date => today's date
=ArrayFormula({"Start"; ArrayFormula(if( ISBLANK(B3:B), "", IF($A$2>=F3:F,"inc","not")))})
- Making sure the end date =< today's date
=ArrayFormula({"End"; ArrayFormula(if( ISBLANK(B3:B), "", IF(($A$2-1)<=G3:G,"inc","not")))})
- Include the announcement at all (GlobalInclude)
=ArrayFormula({"GlobalInclude"; ArrayFormula(if( ISBLANK(B3:B), , IF((H3:H="inc")*(I3:I="inc"),"inc","not")))})
- Include Years / Key Stages / All
- Year 7
=ArrayFormula({"Y7"; ArrayFormula(if( ISBLANK(B3:B), , IF((B3:B=List!A5)+(B3:B=List!A2)+(B3:B=List!A12),"inc","not")))})
- Year 8
=ArrayFormula({"Y8"; ArrayFormula(if( ISBLANK(B3:B), , IF((B3:B=List!A5)+(B3:B=List!A3)+(B3:B=List!A12),"inc","not")))})
- Year 9
=ArrayFormula({"Y9"; ArrayFormula(if( ISBLANK(B3:B), , IF((B3:B=List!A5)+(B3:B=List!A4)+(B3:B=List!A12),"inc","not")))})
- Year 10
=ArrayFormula({"Y10"; ArrayFormula(if( ISBLANK(B3:B), , IF((B3:B=List!A6)+(B3:B=List!A8)+(B3:B=List!A12),"inc","not")))})
- Year 11
=ArrayFormula({"Y11"; ArrayFormula(if( ISBLANK(B3:B), , IF((B3:B=List!A7)+(B3:B=List!A8)+(B3:B=List!A12),"inc","not")))})
- Year 12
=ArrayFormula({"Y12"; ArrayFormula(if( ISBLANK(B3:B), , IF((B3:B=List!A9)+(B3:B=List!A11)+(B3:B=List!A12),"inc","not")))})
- Year 13
=ArrayFormula({"Y13"; ArrayFormula(if( ISBLANK(B3:B), , IF((B3:B=List!A10)+(B3:B=List!A11)+(B3:B=List!A12),"inc","not")))})
Google Sites
Report abuse