Build your own functions in Google Sheets that do anything you need them to in a few lines of code.
This is a great tutorial if you are interested in finding out more about developing custom functions in Google Sheets. The post includes everything you need to get started from handling single cell and range inputs, to documenting your function so that users see inline help.
Use Google Apps Script to create a prefilled URL of data that can be submitted to a Google Form.
Screenshot of Google Form questions
The following Google Apps Script is designed to submit specific data to a Google Form, by creating a prefilled URL. The reason for this was I needed some way of sending data from a number of individual Google Sheets (that I did not own) to one central location, but crucially it needed to be anonymised.
Sending the data directly to a Google Sheet includes version history both in the file and cell meaning it was not truly anonymous. Whereas sending the data through a Google Form and then on to the Response Sheet did strip away anything identifiable.
The logic behind this code is to create a prefilled URL which contains answers to each of the Forms questions. Use the previous webpage link to learn how to create such a URL first so that you understand each question on a Form has a unique value.
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog, where I also have a number of useful Google Add-ons: www.pbainbridge.co.uk
This is a quick discussion of how teacher can respond to student work using block based coding.
This is a very interesting prototype which makes it easier for users to visually develop Google Apps Script code. Developed by Stephen Callahan as part of a Google Innovator Project, TeacherBlocks lets to drag and drop blocks to design your code and automatically create the required script to copy/paste into your Google Doc. Watch the video to see it in action or visit the source link to try it yourself.
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog, where I also have a number of useful Google Add-ons: www.pbainbridge.co.uk
The basic principle behind tracking Google Sheet opens is the same as the one behind email tracking with a transparent pixel. In a Google Sheet, we will use the IMAGE()function, which will call a deployed Google Apps Script web app URL with some parameters in the query string. The web app will be able to do whatever you need among the following: record to a spreadsheet, create a message via a messenger, or simply send an email.
Compare a number of email addresses and check their domains against one you are filtering for via a regular expression.
Screenshot of email addresses in a Google Sheet cell
The following Google Apps Script is designed to use a regular expression (regex) to confirm if an email address domain matches one we specify eg: @hotmail.co.uk, @gmail.com, @outlook.com
I developed the code so I could screen email addresses to make sure they only came from within the institution – otherwise it would have broken the tool I was developing for creating Zoom meetings. The email addresses in this example are all stored in one cell in a Google Sheet and are separated via a comma and a single space.
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog, where I also have a number of useful Google Add-ons: www.pbainbridge.co.uk
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog, where I also have a number of useful Google Add-ons: www.pbainbridge.co.uk
Learn how to sort your data in a Google Sheet in randomized order using Excel formulas and Google Apps Script.
Nice little snippet from Amit Agarwal which includes some Google Apps Script code for randomising the order of rows in Google Sheets. The code has some clever use of .autoFillToNeighbor(), .offset() and .sort().
Keep up to date with any feed by having new posts published to a Google Chat room using Apps Script and Webhooks. Feeds included in this example are various official Google blogs.
This is a nice solution developed by Justin Wexler which provides an easy solution which can monitor a list of blogs and post any updates to a Google Chat room. The source post provides more detail on setup and how to use the code with your own Google Chat rooms. The code also provides a useful pattern for monitoring updates from RSS feeds.
