AppsScriptPulse

Delete Google Calendar Events by keyword and date range

Search a date range in Google Calendar and match any events with a given string, then delete those events.

Search for and delete Google Calendar events

Search for and delete Google Calendar events

The following Google Apps Script is designed to search a date range for Google Calendar events containing a given string (something to help target those events) and then delete them.

There are 4 items at the beginning of the script to complete for your requirements:

  1. Start date – format mm/dd/yyyy – forms the date range to look for events within.
  2. End date – format mm/dd/yyyy – forms the date range to look for events within.
  3. Search string – bit of text that is unique to the events you wish to delete to differentiate them from other Calendar events you may not wish to remove. Note: is not case-sensitive and will not pick-up search string inside of another word eg ‘the’ in ‘there’.
  4. Calendar ID – typically your email address for your personal Google Calendar to search for events in.

Source: The Gift of Script: Delete Google Calendar Events by keyword and date range

Organise Google Drive files into a JavaScript object for use with Google Apps Script

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

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"
   ]
}

Source: The Gift of Script: Organise files into a JavaScript Object

Bulk add files to existing Google Drive folders using Google Apps Script

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

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.

Source: The Gift of Script: Bulk add files to existing Google Drive folders

Google Apps Script Tip: Sorting through an array of duplicates

Sort through an array of duplicates and extract unique values into a new array.

Remove array duplicates

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.

Source: The Gift of Script: Sort through an array of duplicates

Bulk combine CSV files into a single Google Sheet with Google Apps Script

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

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.

Source: The Gift of Script: Bulk combine CSV files into a single Google Sheet

Filter a try/catch error message when coding with Google Apps Script

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

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.

Source: The Gift of Script: Filter a try/catch error message

Bulk convert Google Sheets to PDFs using Google Apps Script

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

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.

Source: The Gift of Script: Bulk convert Google Sheets to PDFs

Import CSV data into Google Sheet using Google Apps Script

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

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.

Source: The Gift of Script: Import CSV data into Google Sheet files

Reading Qualtrics survey details with Google Apps Script

Use the Qualtrics API to connect with a Survey and get its details.

Qualtrics API Survey 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:

  1. API Token from the Qualtrics website,
  2. ID of the Survey you want the details of,
  3. Your Data Center ID.

Source: The Gift of Script: Get Qualtrics Survey details

Search Google Drive for ‘Shared with me’ with Google Apps Script

Search Google Drive for all of the files found in the Shared with me space and collate the results into a Google Sheet.

Search Google Drive 'Shared with me' and collate the results

Search Google Drive ‘Shared with me’ and collate the results

It will collate the following information into a Google Sheet:

  • The file name as a direct clickable link,
  • The file ID,
  • The type of file eg PDF, Google Sheet/Doc,
  • The file creation date,
  • The file last updated date,
  • The folder path,
  • The file owner.

Source: The Gift of Script: Search Google Drive for Shared with me