Organise Google Drive files into a JavaScript Object so that they can be looped through for each named individual.
Files to loop through and organise
The following Google Apps Script is designed to go through a folder of Google Drive files where a number of tutors have individual files for groups of students they teach. The code will collate all of the relevant files (specifically their IDs) that belong to each tutor, so that at a later date we can loop through the organised data set and create individual Google Sheets for each tutor and compile their student data into it.
What this code ultimately allows us to achieve is the ability to go through the files and get them organised for further coding.
In this example we have a file name pattern of ModuleCode – Tutor Name – Group Number for example ABC – Jane Doe – Grp 02. So we want to collate all of the files that belong to Jane Doe first, then move on to the next tutor (Micky Mouse in this example).
The collated data will look like this:
{
"Jane Doe":[
"FILE ID HERE",
"FILE ID HERE",
"FILE ID HERE"
],
"Micky Mouse":[
"FILE ID HERE",
"FILE ID HERE"
]
}
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.
Bulk add files to existing Google Drive folders
The following Google Apps Script tool is designed to 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.You can decide if you want the subfolder name appending/prepending/ignoring in the file name of the new copy to help make it unique.
The tool will iterate through a single level of folders (not sub-subfolders) and for each one place a copy of the files into it.
Sort through an array of duplicates and extract unique values into a new array.
Remove array duplicates
The following Google Apps Script is designed to go through an array of values that contains duplicates and create a new array of only the unique ones, arranged alphabetically. I needed this code when looping through files in a Google Drive folder where tutors and their groups formed part of the filename that I needed to extract for the end file that was created.
Bulk combine multiple CSV files into a single Google Sheet, putting the data in specific locations and creating multiple tabs.
Bulk combine CSV files into a single Google Sheet
The following Google Apps Script is designed to take a number of CSV files (structured in an identical way) stored in a Google Drive folder and combine them all into a single Google Sheet file. It maps the data within the CSV file to specific cells in the Google Sheet, along with creating a tab for each of the CSV files to separate them out.
Search a try/catch error message for keywords to determine the error message to log using JavaScript match.
Filter an error message in a try/catch
The following Google Apps Script is designed to exercise how you might go about searching the error message in a ‘try/catch’ for keywords. I wanted this specifically for a tool I built that contains a lot of code (and hence a lot of potential error messages) between a try/catch, for which a very small number of people were experiencing a timezone issue with their Google Sheet file.
The aim was to use a JavaScript ‘match’ to find the keyword timezone and display a set of instructions for the user to resolve the issue themselves instead of just a generic error message.
Bulk convert Google Sheets within a given Google Drive folder into PDFs and optionally delete the original file.
Bulk convert Google Sheets to PDFs using Apps Script
Features
Maximum runtime – in order to prevent the tool from reaching the limits imposed by Google you can adjust the number of minutes the tool can run for. Change this in the ‘GlobalVariables.gs’ file in the Script Editor.
Continue from where it left off – if you have a lot of Google Sheets to convert and the above runtime is reached the tool will save its progress and prompt you to run it again, avoiding any file duplication.
HTML popup – as well as the ‘Log’ sheet the tool displays a direct popup to the user if it encounters a problem.
PDF counter – after successfully running the tool will include the number of PDFs created as part of the success popup to the user.
Loop through CSV files in Google Drive and extract their contents into specific areas in Google Sheet files.
CSV File template to copy data into
The following Google Apps Script is designed to loop through a folder of CSV files in Google Drive, extract the data, create a Google Sheet file per CSV, and insert the data into specific rows/columns.
Use the Qualtrics API to connect with a Survey and get its details.
Qualtrics API Survey details
Most of the complexity with this simply comes in getting the correct syntax for connecting to the Qualtrics API. This Get Survey Qualtrics webpage provides a little bit more technical detail for what exactly is sent/received.
You will need to complete the 3 pieces of information at the top of the Apps Script code for your own setup:
Use Google Apps Script to Autofill a formula down rows in a Google Sheet.
Autofill Google Sheet Formula
The following Google Apps Script is designed to insert a formula into the first row of data in a Google Sheet, then use Autofill to add the formula to the subsequent rows below. In this example I am just using a simple Sum formula to add up age and shoesize for demo purposes. The need to do this came about when combining multiple Google Sheet files however, that needed calculations adding to the final composed version.
