Working with dates in the Query function in Google Sheets can be tricky. This tutorial shows you the correct syntax and examples.
This year I have been creating a monthly summary of posts and updates associated with all things GSuite. It occurred to me after nine months that I should really be storing all the links in some sort of database. My question is how to automate the process of turning that into a monthly post.
I have been getting on the GAS and am thinking that QUERY might be a part of my solution. I am therefore trying to get everything working in Sheets first. I have followed your guide to QUERY, even adding in two dynamic selectors (is that what they are called?) that I got from your work on VLOOKUPS. My question is filtering by dates. I have followed your instructions for filtering between two dates:
=QUERY(Data!$A$1:$H$136,”select C, B where B > date ‘”&TEXT(A1,”yyyy-mm-dd”)&”‘ and B <= date ‘”&TEXT(B1,”yyyy-mm-dd”)&”‘”,1)
But fear that I maybe limited as I have recorded my dates using DD-MM-YYYY. Sheets recognises this as a date as a formatted the cells as ‘DATES’. My spreadsheet settings have also been changed to ‘Australia’. I am wondering if you have any thoughts or suggestions on this? Here is a link to my sheet.
Whilst seeking examples of projects created with Scalar, I also came across a similar offering, Omeka. Whilst they may not produce the exact look I was after, I think they might be able to replicate some of the functionality. I’ve only had a quick look and need to read and think through the IP issues, ethical issues and the workload that taking this direction might generate. However, reality quickly kicks in and I return to some of the issues discussed earlier. I’m obliged to ask myself, what’s the purpose of the thesis? It’s not an ebook, it’s a piece of work presented for assessment in partial fulfilment for the award of Doctor of Philosophy. Weighed against that are, for me, two things: reducing the online, multimodal, hyperlinked realm that has provided the setting for my study to a static pile of papers somehow seems to lessen the work; thesis-lite! And there’s also the thought that adding some of the aforementioned enhancements might just make it more useful in a broader scholarly environment. Perhaps making it of value to a larger audience than a thesis might usually enjoy? Undergraduates interested in sociomateriality. Masters students considering digital ethnography. Doctoral researchers wishing to build arguments for and against post-qualitative research.
Ian, I really like the idea of developing a digital thesis. One of the concerns and questions I would have about your work is that longevity of the data and reference. I see potential of such plugins as Amber and sites like Internet Archive to create a historical reference point.
Simon Sinek discusses the difference between rationalising a decision and finding real purpose:
Providing jobs, driving the economy, serving the shareholder are not purposes. They are rationalizations used when a greater cause or purpose is not clear or not there. Real purpose has a clear and definitive direction. It is a path that points towards a very specific vision of the future. Rationalizations have no destination, they are simply a calculation to demonstrate some benefit to justify the actions. source
A better world for wheels on Google Maps – Google Maps now offers answers that allow those on wheels to find accessible places. They are calling on Local Guides, a community of people who contribute their expertise about places on Google Maps, to add more wheelchair accessibility attributes to the map.
Optimize your Android apps for Chromebooks – While most apps and games already work fairly well on Chromebooks without any changes, Google is still pushing for a developer to consider the various options associated with a laptop device.
Embed websites as iframes in the new Google Sites – Going forward, you can embed an entire web page as an iframe in a new Google site. This will allow you to pull in content from other websites and Google tools like Apps Script, Data Studio, and App Maker, saving you the trouble of duplicating and updating that information on your page.
Export data from Google Vault in PST format – To save those businesses time and to make the process of producing and importing data easier, we’re now giving Vault admins the ability to export Gmail, Google Groups, and on-the-record Google Hangouts chat data in Microsoft Personal Storage Table (PST) format.
Chrome’s Plan to Distrust Symantec Certificates – Devon O’Brien, Ryan Sleevi, and Andrew Whalley explain how at the end of July 2018, the Chrome team and the PKI community converged upon a plan to reduce, and ultimately remove, trust in Symantec’s infrastructure in order to uphold users’ security and privacy when browsing the web.
3 Things to Know in Google Forms – Alice Keeler documents some lesser known features of Forms, including the ability to change preferences, validate responses and choose the sheet where responses go to.
Amazing Mobile Features of Google Classroom – Eric Curts unpacks the various features associated with Google Classroom mobile app, including the ability to add digital handwriting, recording media, inserting content from other apps and receiving notifications.
Google Classroom: Post to Multiple Classes – Alice Keeler explains how to assign work and posts to multiple classes, useful if several classes require the same information. This, however, does not work with scheduling.
View the world through someone else’s lens in Google Earth – Whether you’re looking for travel inspiration, preparing a geography report for school, or simply taking flight from the comfort of your couch, Stafford Marquardt shows how the new Photos layer in Google Earth gives you the ability to look at far off places up close.
Step inside of music – Alexander Chen discusses the new collaboration between Google and the Song Exploder podcast which allows viewers to interact with music but turns parts on and off.
Something’s coming … “West Side Story” on Google Arts & Culture – Google Arts & Culture is launching a new collection honoring “West Side Story,” bringing together artifacts and mementos from the making of the musical and movie, behind-the-scenes photographs, and a peek into the modern-day representation of the musical, this collection explores the history, artistic value and social relevance of “West Side Story.”
A Blogger Privacy Setting You Might Want to Use – Richard Byrne discusses some privacy settings to consider when using Blogger, such as the request that your blog doesn’t appear in Blogger’s list of published blogs, restrict viewing to those who have been invited by email and turn on moderation for comments.
As part of the investigation of systems, Julian Stodd provides a definition for innovation:
Innovation is an emergent feature of a dynamic system, not a process within a system, or a target set upon it. Innovation may be nurtured, but is equally often provoked, and here’s the funny thing: innovation itself is rarely enough, unless accompanied by the ability to exploit it. We have to be able to hear weak voices within the system, to develop, nurture, sense make, and prototype, before we can exploit.
The challenge is nuturing it, rather than pushing and provoking it:
If we want to avoid the innovation trap, we must understand the two spaces, and everything that comes with them: innovate, broadly, at scale, and exploit it, when you have raced the ideas. But do not try to constrain the race: by doing so, we build our own failure, we engineer our own constraint.source
For the 100th anniversary of the TER Podcast, Cameron Malcher put out an invitation for listeners to contribute. I recorded a piece on the obstacles to blogging.
In regards to work, I continued my deep dive into Synergetic. This included some more tinkering with options associated with generating a timetable, as well as refining the structural aspects of our reporting package. I also did some preliminary work around developing a dashboard as a live analysis of data captured in Google Sheets. I was also lucky enough to attended the #EduChange Conference, where I heard Peter Hutton discuss his new venture EdRevolution.
On the family front, I am reminded every day about the differences between siblings, especially as our youngest approaches her second birthday. It felt like one day she was our baby and then the next day she was a hurtling down on a flying fox. My wife and I were also lucky enough to get away for a night to celebrate our wedding anniversary, as well as take the kids away for a few days to Warrnambool.
Organising Data with Forms and Sheets – I use a lot of Google Forms to collect information. Here then are some of the steps that I have taken to streamline the steps and processes associated with reflecting on the data.
Developing a Writing Workflow – Inspired by Doug Belshaw’s series on blogging, here is a reflection on my writing process from Evernote to Google Docs to Trello.
Strategic questioning is key to assessment for learning. While questioning is essential for students in all grade levels, teachers can take the opportunity of new syllabuses and school based assessment requirements for the HSC to re-think how they design and implement assessment for learning in Stage 6. However, questioning is often viewed as an intuitive skill, something that teachers “just do”. At a time when many teachers are creating new units of work and resources for the new Stage 6 syllabuses, it may be a good opportunity to look at strategic questioning and embed some quality questions and questioning techniques.
Suggesting nearly all parent involvement programs are too passive, Mapp says there are three things parents should know about their child’s school and school-related experiences. (2)
Parents are the child’s first teacher. Parents need to know they are an essential aspect of their children’s development.
Parents possess a deeper knowledge about their children. Educators are better able to differentiate and individualize instruction when armed with the background information parents can provide.
Parents need to know they have access and support from their child’s teacher and school. Parents should have a direct line to the feedback that helps support their child’s learning and development.
Show a Pro – Emily Fintelman provides her thoughts and suggestions for engaging with professionals as a means of provocation. Some ideas include contacting the local council, videoing in a guest or drawing on the parent community for expertise. The reality is that developing connections, whether it be experts or co-collaborators, is hard work, as Lee Hewes highlights. Another useful resource associated with PBL and more authentic learning is Michael Niehoff’s exploration of professional presentations.
Ask your students who they think they should talk to to learn more about their topic. Have them make suggestions about WHO might have the knowledge they need, and HOW they might get in touch with them.
Some people you ask (especially parents) might feel that they don’t have enough to share. It’s important to be clear on what information you would like them to talk about, what you want them to demonstrate, and what level of understanding the students will come with. This can make it easier for your guest to understand how their expertise can help your class.
In most cases, experts are experts in their field, not in teaching or public speaking. It can be very helpful to provide some information on how to run the session, or for you to run it and allow time for your guest to share, and manage question time for them.
If your expert is willing, get their contact details so that if students have a follow up question, you can get in touch to find out their answers.
Excursions and incursions can be very expensive. Finding experts in other ways is often extremely inexpensive and is most likely more tailored to what the learning needs of your students are.
I realize I have some questions yet to tackle when it comes to using this sketchnoting concept with them:
How to help students already easily distracted to listen and doodle at the same time?
How to help them filter out what is important enough to be doodled and how to figure out what to leave out?
How to teach them the use of artistic lettering in order to use words as art in meaningful ways?
How do I demonstrate that sketchnoting has actually helped improve their writing and understanding of complex topics?
How to help them form a personalized systematic approach for the flow of their own sketchtnoting?
Lorde Remix Competition – Triple J have provided access to the stems to Lorde’s track ‘Homemade Dynamite’ from her recent album, Melodrama. I am less interested in the competition as I am in the opportunity to hear the song broken down into its parts. Lorde also reflects on her track, ‘Sober’, on the Song Exploder podcast. Another resource for digging deeper into the layers of songs is the recent collaboration between Google and Song Exploder, which provides a virtual space within which you can turn parts on and off. I also came upon PennSound, a collection of poetry recordings, both past and present.
Triple j Unearthed is teaming up with Lorde to give you the chance to remix her track ‘Homemade Dynamite’.
Take A Knee Padlet – A multimedia collection crowdsourced by educators and curated for middle year students interested in understanding the context associated with the Take A Knee movement. Not only is this a useful resource, but another great example of the way that Padlet can help facilitate collaboration. In a way, I think this is what Mike Caulfield envisaged for Wikity. Julian Stodd also provides a commentary on the current situation, focusing on the different forms of power at play, while Bill Ferriter discusses inadvertently second guessing students of colour.
Why RSS Still Beats Facebook and Twitter for Tracking News – David Nield provides an introduction to RSS and why it can be better than social media for consuming content. One of biggest benefits is that it is unfiltered by the stacks. Nield provides some strategies for working with RSS, such as IFTTT and feed readers. Alan Levine lifts the hood on RSS, explaining how it works and what OPML is, while Bryan Alexaner states why he recently decided to rededicate himself to RSS reading. In the end, it comes back to Doug Belshaw’s question of curating or being curated?
One of the main reasons RSS is so beloved of news gatherers is that it catches everything a site publishes — not just the articles that have proved popular with other users, not just the articles from today, not just the articles that happened to be tweeted out while you were actually staring at Twitter. Everything. In our age of information overload that might seem like a bad idea, but RSS also cuts out everything you don’t want to hear about. You’re in full control of what’s in your feed and what isn’t, so you don’t get friends and colleagues throwing links into your feeds that you’ve got no interest in reading.
Is The Inbox Zero Strategy All Hype? – Scott Friesen explains that Inbox Zero is more about the process of getting through the mail than getting to the magical ‘zero’ mark. He lists some applications to help with this. Another hack Cal Newport suggests is to have all mail delivered into a sort folder, while Lauren Brumfield recommends thinking about an application which allows you to easily manage a number of accounts in the one space. Along with Doug Belshaw’s 10 tips to email productivity, this collection of posts provides a useful point of reflection for those struggling with email anxiety.
So how can you become more effective with managing your email? – Consider using the concepts of Inbox Zero to speed up the way you process your messages. Remember, it’s not about keeping your inbox empty. It’s about getting through a large number of messages quickly and being able to identify the ones that deserve your attention. – Stop checking email so frequently! Did you know that the average professional spends 6.3 hours a day dealing with email? See if you can spend as little as 3 to 4 email sessions a day so you can focus on your most important work. Studies show that you will enjoy less stress as a result. – Use applications such as Boomerang, Trello, or Slack to keep your communication focused and on target. If you work with a team, make sure everyone knows how to use the communication tools within your project management system. You’ll save time and a lot of headaches for everybody.
Podcast Generator – Jim Groom unpacks the process of publishing a podcast on your own domain with Podcast Generator. This is the tool that Doug Belshaw uses for the Tide Podcast. One of the benefits of publishing a podcast yourself is that you control the content, something that John Johnston has been reflecting on of late with AudioBoo(m)’s decision to become a paid service. He has also shared the process that he went through in downloading the Edutalk recordings housed there.
After being asked by a friend about podcast options on Reclaim, I started playing with the podcasting tool Podcast Generator. I heard about it thanks to this thread by Tim Klapdor on the Reclaim Hosting Community Forums. It’s a really simple content management system designed specifically for podcasts. It provides a stripped down space to upload files and simple metadata like title, description, and categories. It also provides iTunes integration and an OG RSS feed.
schools and districts need to start putting their money where their mouths are when it comes to technology spending. In the words of Richard Elmore, for every new increment of performance that you demand from classroom teachers, you have an equal obligation to provide the time, the tools and the training necessary to meet those new expectations. That’s impossible when we aren’t making deliberate choices about the digital tools and services that we are purchasing.
Using Hitachi Data Systems to improve student life at Curtin University – James Gallaway documents the way in which Curtin University is using 1600 cameras around its campus to capture staff and students for attendance and security purposes. However, this is only seen as the beginning, with the intent being on actively collecting data with an openness to future innovation. Continuing with this theme, Emily Talmage discusses the move in education to focus on psychological data, something that Ben Williamson has been addressing in regard to platforms like ClassDojo. Privacy International provide a case study for how data and algorithms are being used against us. Mike Caulfield wonders if there needs to be a state tax on personal data that is stored about us. Martin Weller argues for a mixed diet of data consumption. Kin Lane and Audrey Waters discuss the way in which technology companies shape public discourse in Episode 66 of Contrafabulists podcasts.
Keeping track of Curtin University – 1600 cameras – 60,000 students – 4000 staff – 300,000sq m of floor space – Facial recognition software – Data and video analytics
Tackle Workload. This bandwagon actually matters – Tom Sherrington discusses the problem of workload piled on the modern teacher. He highlights a number of elements to reconsider, such as report comments and pointless assessment. Considering the problem from the perspective of the teacher, Jamie Thom advocates becoming a minimalist and cutting back. Steve Brophy suggests looking after our own wellbeing by putting on your oxygen mask first. One thing that matters is our own development.
Some workload issues require a major culture shift; some simply need us to rebalance the trade-off between the benefits of autonomy and the benefits of working collaboratively within an agreed system; others need us to stop doing certain things altogether.
I’ve come to think that, in today’s world, one of the most valuable lessons we can give to students is not “how to build their identity on the web,” but how to selectively obscure it. How to transcend it. How to personally track it. How to make a difference in the world while not being fully public. To teach students not just to avoid Google, but to use Google safely (or as safely as possible). To have them look at their information environments not as vehicles of just self-expression, but as ways to transcend their own prejudices. To read and listen much much more than we speak. And to see what is needed through the lens of privilege – teaching the beauty of deference to the students with self-confidence and social capital, while teaching marginalized students to find communities that can provide them with the self-confidence they need.
Suis-je flâneur? – Ian Guest reflects on the data his has gathered associated with Twitter and wonders if he is a ‘flâneur’, in that he both captures and actively creates in the spaces where he works. Also writing about research, Julia Lindsay shares the lesson that she has learned that the coding of data is very much an interpretative act. This all adds to the questions to consider when it comes to data.
The flâneur is more of a serendipitous explorer, receptive to whatever comes along. They are a combination of curious explorer (having no goal other than to experience city life), critical spectator (balanced analyst, seeing beauty, but aware of social inequities), and creative mind (an interpreter who renders the urban landscape legible).
The Seven Keys to Creative Collaboration – In the first of a series of posts unpacking creative collaboration, John Spencer highlights seven keys to success. These include ownership, dependability, trust, structure, shared vision, fun and candor. It is important to point out, as Gary Stager, not everything has to be collaborative.
When collaboration works well, there is a certain group flow experience, where you are totally “in the zone.” There’s this dance back and forth where you get lost in the work and you realize that you are a part of something bigger than yourself. In the process, you create something as a team that you would have never been able to produce on your own.
There is nothing magical about the items in this list, but they are guaranteed to stretch you, give you a new perspective, and add some freshness to the school year. Pick one or two, give them a try, and if you are willing,
The Education Paradoxes of Singapore – Pak Tee Ng shares the five paradoxes associated with the Singaporian education system. They are: timely change, timeless constants, compassionate meritocracy, centralized decentralization and teach less, learn more. It is interesting to compare this with the Finnish story. It would seem that the only constant is a commitment to change.
Singapore’s experience with educational change show that paradoxes can be powerful in driving positive change, provided people are united in a common purpose, and there is commitment and tenacity to see through meaningful and long-term education reform. Despite achieving what would appear to be great success in education, Singapore is choosing to ditch its past success formula for the sake of the future. It recognizes that every country or jurisdiction is different and each will have to find its own path. For a small country that has survived against the odds for five decades, it has the gumption to chart its own path and every intention to thrive for many decades to come.
A struggling school in Norfolk, England has taken drastic measures to turn around results. This includes providing a bucket to vomit in instead of being allowed to leave classrooms. Click here for the original list of rules as they have since been amended. Here is a collection of posts reflecting on the question of classroom behaviour:
Do What They Told Ya – Sue Crowley argues that the question is not about whether students are behaving or not, but what their behaviour looks like.
students forget to misbehave – Sue Crowley suggests that the challenge associated with ‘classroom management’ is structuring learning so that students forget to misbehave.
Breaking all the rules… – Linda Graham warns against simply blaming parents for the behaviour of their children as there are some children who have to do things their way.
Providing two separate case studies, Kisha Tracy and Katharine Covino explain why diversity is so important now and offers us an opportunity to resist:
Embracing diversity, in all its forms, promotes critical thinking and empathy … In such a place and time, forefronting diversity takes on an overtly political mien, becomes an overtly political act. Promoting diversity in a time when diversity is viewed negatively offers educators an avenue of resistance. It is imperative that we not shrink from this duty, but rather that we embrace it. We must model through our pedagogy that we see, know, and value each and every student sitting in front of us not in spite of their differences, but because of them. source
Another take on the topic is Ellen Ullman and her discussion of [[diversity and perspective]].