Google Apps Script Tools
Welcome!
This Wiki space collates the tools developed by Phil Bainbridge over a number of years for staff that aim to support the University and reduce the burden of those repetitive tasks that take up a lot of time.
If you have any queries about this page or need some further guidance please email itsupport@york.ac.uk and include "FAO Phil Bainbridge - Apps Script Tools" in your message.
If you are not part of the University of York then you may find the tools you require via my blog.
Looker dashboard showing the positive impact of time saved.
Contents
Google Apps Script
Firstly, you are not required to understand Apps Script in order to make use of these tools - they have been designed so that as long as you are comfortable with Google apps like Drive, Sheets, Docs, etc then you can use them. What is Apps Script though!? If you would like to understand more about the coding language and how we use it at the University see our Coding Practical Guide on Google Apps Script.
If you need a refresher on how Google works also see the Google Workspace Skills Guide and the Google Drive Skills Guide.
The Tools
Feel free to try any of them out and see what works for you.
Drive
Bulk Rename Files Add-on
Bulk rename Google Drive files quickly and easily with this Add-on. You define exactly what you want to search for in the file name and the text to replace it with.
Examples: archiving/reorganising. PDRs. Mass renaming of documents.
Bulk Rename Files Add-on Google Site.
Search Drive for Files 'owned'
Enter your email address and off you go! This tool will search your entire My Drive for all of the files that you are the 'owner' of and collate them into a single Google Sheet. This Sheet will contain a clickable link to the file, the date it was created, when it was last updated and the folder path where it is stored.
Examples: managing leavers and file ownership.
Search Drive for Files 'owned' Google Site.
Search Drive folders for creation/modified dates
This tool will let you search a specific set of folders within a given My/Shared Drive folder and collate their 'date created' and 'last updated' information, along with a clickable link, into a single Google Sheet. It is only designed for one-level of folders however and so will not go into sub-sub-folders (the instructions document shows an example of this).
Search Drive folders for creation/modified dates download.
Search Drive for Shared with me
Wanting to index all of those files you see in Google Drive's "Shared with me" space into a Google Sheet? Maybe you want to see exactly what is being shared with your account. Well now you can. This tool will collate information such as: file type, date created/modified, email address of the owner, a clickable link directly to it and more. Note: depending on how long you have been at the University there could be a significant number of results returned.
Search Drive for Shared with me download.
Search Google Drive File names
It is easy to search for the names of files within My/Shared Drive, but what happens when there are a lot of results that you need to go through?! Scrolling continuously down the page becomes quite clunky and frustrating, so with this tool you can perform that same search in a Google Sheet but have the results collated into the Sheet also, so they are much easier to then work with.
Search Google Drive File names download.
Index Drive Folders/Files
Wanting to index a set of folders/files within My/Shared Drive so you can better understand the structure regarding all of the sub-folders/files? This tool will index the layout of that space into rows in a Google Sheet so you can better see how your items are organised. It will collate information such as: file/folder path, date created/modified, a clickable link directly to the item and the email address of the owner.
Index Drive Folders/Files download.
Bulk create Drive Folders
Maybe you simply need a number of My/Shared Google Drive folders creating that you can fill with files, etc at a later date. This tool will simply let you provide the names of all of the folders you require and then create them along with a direct clickable link to each one in the Google Sheet.
Bulk create Drive Folders download.
Bulk create Drive Folders with Subfolders
Need to create a number of My/Shared Drive folders all containing a number of subfolders? This is the tool for you! Control exactly how you want the subfolders named (even use Concatenation if you want to be clever) and if you want 1 or up to 10 subfolders creating.
Examples: folder creations that happen every calendar/academic year e.g. student/committee folders. Recruitment paperwork.
Bulk create Drive Folders with Subfolders download.
Bulk create Drive Folders with Permissions
Whilst this tool may not quite have the 'finesse' of the others (as it was adapted ad hoc) it does allow for the bulk creation of Google Drive folders along with 'edit' access for any email addresses you provide (without the automated notification email being sent to each individual).
Bulk create Drive Folders with Permissions download.
Bulk create Drive Folders with optional Files
A new intake of students? Organising an event? Whatever your need save time with this tool for bulk creating My/Shared Google Drive folders with the ability to have files copied into each folder, all from a Google Sheet. You have full control over the naming convention for every folder/file and because it is a Google Sheet this quickly scales up to be a real time saver.
Bulk create Drive Folders with optional Files download.
Bulk add Files to Folders Tool
Take a Google/Shared Drive folder full of files that you want to make a copy of and place into another set of existing Drive folders. Decide if you want the subfolder name appending/prepending/ignoring in the file name of the new copy to help make it unique.
Bulk add Files to Folders Tool download.
Bulk copy Drive Files Tool
Take a Google Doc/Sheet/Slide, etc and make multiple copies of it with unique file names, into a single Google Drive folder.
Examples: recruitment - copying interview/shortlisting forms for candidates/panellists. PDRs.
Bulk copy Drive Files Tool download.
Copy or move file into folder Tool
Need to quickly copy or move existing files into folders within Google Drive? Want the flexibility to vary how each file is handled and do this in bulk? With this tool you can easily list your file(s), the destination folder(s) and whether you want to copy or move them.
Examples: uploading feedback into student folders. Collating PDR forms. Organising and distributing files when you decide.
Copy or move file into folder Tool download.
Notification email on Drive file edits
A tool to periodically check every X number of hours to see if any edits/changes have been made to a Google Drive file and then only send you an email if there has been. It looks a little bit more raw than the usual tools as it was created ad-hoc as a solution for something else, but quite a few people have found it useful.
Notification email on Drive file edits download.
Bulk convert Google Docs to PDFs
This tool will take a My/Shared Drive folder full of files and create a PDF version of each of the Google Docs within it (all other file types will be ignored). You can control where you want the new PDF files to be created and also if you want the original Google Docs to be deleted afterwards.
Bulk convert Google Docs to PDFs download.
Bulk convert Google Sheets to PDFs
This tool will take a My/Shared Drive folder full of files and create a PDF version of each of the Google Sheets within it (all other file types will be ignored). You can control where you want the new PDF files to be created and also if you want the original Google Sheets to be deleted afterwards.
Bulk convert Google Sheets to PDFs download.
Bulk convert Google Sheets to CSVs
This tool will take a My/Shared Drive folder full of files and create a CSV version of each of the Google Sheets within it (all other file types will be ignored). You can control where you want the new CSV files to be created and also if you want the original Google Sheets to be deleted afterwards.
Bulk convert Google Sheets to CSVs download.
Archive Sheet data Add-on
This Add-on will move rows of data from one Google Sheet (tab) to another, within the same file - so in effect allowing you to archive data. It will remember your individual preferences for each file so it is even quicker to use going forwards.
Archive Sheet data Add-on Google Site.
Append Drive file permissions
Need to quickly add different users to different files in Google Drive? This tools allows you to append new permissions to existing files by listing them within a Google Sheet. By inserting email addresses using a comma-and-space format you can optionally select to send a notification email to the new user(s).
Examples: sharing student feedback documents. PDRs. Attendance reports.
Append Drive file permissions download.
Append Drive folder permissions
Need to quickly add different users to different folders in Google Drive? This tools allows you to append new permissions to existing folders by listing them within a Google Sheet. By inserting email addresses using a comma-and-space format you can optionally select to send a notification email to the new user(s).
Examples: sharing student documents. PDR documents. Various reports.
Append Drive folder permissions download.
Document Merge Tool
Create customised/personalised Google Docs from a Google Sheet of data. The tool will iterate through each row and create a Google Doc based on a template you have formatted that is then populated with your Sheet data. You can then choose to share each Doc with any email addresses you wish and control which level of access they have e.g. viewer, commenter, editor.
Examples: certificates. PDR documents. Attendance/Feedback reports. Welcome letters. Basic replacement for Autocrat.
Gmail
Send Recurring Emails
This tool allows you to easily send recurring emails at a frequency of your choosing e.g. every 7 days, every 27 days, every 2 months, etc. Write your email(s) once, select your start date, frequency, recipients, then kick back and relax. The tool will automatically check every day at a time that you specify to see if any emails need sending.
Send Recurring Emails download.
UoY Out of Office Tool
Forget to set your Gmail out-of-office when on annual leave or away at a conference? Want to have a regular out-of-office for part-time working? This tool scans your main University of York Google Calendar and automatically sets your Gmail out-of-office based on these types of events. UoY Out of Office Google Site.
Bulk Email Saving Tool
This tool provides a way of saving the contents (and any attachments) of emails in a given Gmail Label into a Google Drive folder as PDF files.
Bulk Email Saving Tool download.
Calendar
Bulk create Calendar events with optional Meet/Zoom
This tool provides a way of bulk creating future Google Calendar events with optional video conferencing (Google Meet or Zoom). These events can be created in your own calendar or another calendar for which you have sufficient permissions to manage. At the end of the process you will be provided with a link to the new Google Calendar event and it will also display in the calendar just like any other. You can add items such as guests, email notification, event visibility, etc.
Examples: GTA bookings for jobs/classes that are not on the standard timetable. Regularly scheduled events or team meetings such as BoE, BoS or DMT.
Bulk create Calendar events with optional Meet/Zoom download.
Calendar event invites from a Form submission
This tool allows you to link an existing Google Form with a single Google Calendar event so that when people complete the Form they are sent an email invite to the Calendar event. This allows you to have a single Calendar event with all guests attached to it. Therefore any changes to the event (date/time/description/conferencing, etc) will be reflected for all guests - just like any other event - and you can easily see guests/attendees.
(If you want to connect a Form to multiple events for people to choose which one to sign up to then please see the tool listed below).
Examples: sign-ups for a Conference. Attend a single training session.
Calendar event invites from a Form submission download.
Multiple event invites from a Form submission
This tool allows you to link an existing Google Form with multiple Google Calendar events so that when people complete the Form and select a particular event title/date/time from it, they are sent an email invite to one of the respective Calendar events. This allows you to have Calendar events with all guests attached to it. Therefore any changes to an event (description/conferencing, etc) will be reflected for all guests - just like any other event - and you can easily see guests/attendees.
(If you want to connect a Form to a single event for people to sign up to then please see the tool listed above).
Examples: sign-ups for an event repeated throughout the year e.g. staff training. Regular Workshops/Drop-ins for people to choose from.
Multiple event invites from a Form submission download.
Misc
Probation Tracker Tool
Need to easily keep track of new staff starters and when their probation reviews are due? Want to have an automated email to their Line Manager 14 days in advance and on the day of the review date? Want flexibility between varying probation periods e.g. 3 month, 6 month, 9 month, etc? Then it sounds like this is for you.
Probation Tracker Tool download.
Bulk export Qualtrics survey results
This is a bit more of a niche tool. It will allow you to export the results data from multiple Qualtrics surveys at once. The data will be exported as CSV and require a bit of tidying.
Bulk export Qualtrics survey results download.
Manage a Google Group
This tool provides a way of managing a Google Group via a Google Sheet by allowing you to synchronise a list of users (their email address and role). Users listed in the Sheet will either be added, removed or changed (eg their role of member/manager/owner) by the click of a button. The caveat of it is that Google does not make this process easy so there are a number of hoops to jump through that you should read about in the documentation first.
Manage a Google Group download.