For a while now we have been using an expensive online professional development system that nobody seems to get on with too well. So I was asked to come up with a system that works in Google Apps and does the basic core things that the paid for system does.
So the key things are:
Seemed like an ideal opportunity to get my two favorite spreadsheet functions into action - importrange and query. So if you have not met these two functions - here are the basics:
Importrange
=IMPORTRANGE("1MHqboMSXVImyh_wFKi2WAazZinqCo_90tfYaEbOszqo","Form Responses 1!A1:N2000")
This pulls data from one Google Sheet into another. So I've got several forms as part of this system and want to pull data from them all into just one.
"1MHqboMSGVImyh_wFKi2ZAazZinqCo_90tfYaEbOszqo" = the spreadsheet key - found in its url
"Form Responses 1!A1:N2000" = the sheet you want to pull data from and the range
Query
=query(staff,"select F,I,C where D = '"&B2&"'")
This function turns your spreadsheet into a database - so you can ask questions and get the data you want.
So in the example above, staff, is a named range where we are looking for some data - but it could be a whole sheet or any range of cells. The bit after the comma is the key bit - its the query - or the question you are asking. So in this case I want the data in columns F,I and C but only where the value in column D is the same as that found in cell B2. So I can extract data based on the result of the form submission. If you are referencing a specific cell as the condition, the syntax is '"&CELL&"' (so single quotes outside - double inside) - bit weird.
There are many other operators and things you can do with query - but it's a really powerful and useful function when you get use to it.
So the upshot is I've got a basic and functional free system for Staff Development that uses Sites, Sheets, Forms and a few Docs. The video below explains a bit more:
No doubt I'll add more functions in the coming months - but it will do the job to start with. I'm guessing other people have far cleverer systems - but I think the point is that you don't need to spend 1000's on a system when you can get the functionality you actually need for free (well a few hours work!).
So the key things are:
- Easy way for staff to record objectives.
- Method for approving and reviewing these objectives.
- Method for submitting CPD activities and having them approved.
- Method of submitting overview statements.
- Keeping all of this in a central easy to navigate central store for the head.
Seemed like an ideal opportunity to get my two favorite spreadsheet functions into action - importrange and query. So if you have not met these two functions - here are the basics:
Importrange
=IMPORTRANGE("1MHqboMSXVImyh_wFKi2WAazZinqCo_90tfYaEbOszqo","Form Responses 1!A1:N2000")
This pulls data from one Google Sheet into another. So I've got several forms as part of this system and want to pull data from them all into just one.
"1MHqboMSGVImyh_wFKi2ZAazZinqCo_90tfYaEbOszqo" = the spreadsheet key - found in its url
"Form Responses 1!A1:N2000" = the sheet you want to pull data from and the range
Query
=query(staff,"select F,I,C where D = '"&B2&"'")
This function turns your spreadsheet into a database - so you can ask questions and get the data you want.
So in the example above, staff, is a named range where we are looking for some data - but it could be a whole sheet or any range of cells. The bit after the comma is the key bit - its the query - or the question you are asking. So in this case I want the data in columns F,I and C but only where the value in column D is the same as that found in cell B2. So I can extract data based on the result of the form submission. If you are referencing a specific cell as the condition, the syntax is '"&CELL&"' (so single quotes outside - double inside) - bit weird.
There are many other operators and things you can do with query - but it's a really powerful and useful function when you get use to it.
So the upshot is I've got a basic and functional free system for Staff Development that uses Sites, Sheets, Forms and a few Docs. The video below explains a bit more:
No doubt I'll add more functions in the coming months - but it will do the job to start with. I'm guessing other people have far cleverer systems - but I think the point is that you don't need to spend 1000's on a system when you can get the functionality you actually need for free (well a few hours work!).
Comments
Post a Comment