Bulk convert Google Docs to PDFs

The following Google Apps Script tool is designed to bulk convert all Google Docs within a given Google Drive folder into PDFs. You can choose the destination folder for the PDFs to be put into and also whether you want the original Docs to be deleted. This tool does work on Shared drives.

Bulk convert Google Docs to PDFs using Apps Script

Find and replace in a Google Sheet - ReplaceAllWith

This blog post is a slight adaptation of the one here for Find and replace on a whole Google Sheet. This time we are focussing upon the fastest way to change all of the text values without highlighting changed cells. In effect we have deleted a chunk of code and replaced it with one single line:

textFinder.replaceAllWith(replaceWord);

So whilst we can no longer target a specific cell for any formatting changes, this method saves us time by not performing any more loops, and so may be your chosen method for speed.

Bulk create Google Drive Folders and add Files

The following Google Apps Script tool was developed to bulk create Google Drive folders with optional files copied in to each one, all from data within a Google Sheet. There is also the option to add specific 'edit' permissions to the newly create Drive folders of which the files would automatically inherit this access level.

The tool is an expansion of the 'Bulk create Google Drive folders 2.0' blog post here, so you may wish to read and watch the video on there first.

Bulk create Google Drive folders and add files, from a Sheet of data

Key Functionality

• Complete the necessary information in the Config sheet before proceeding. Then use the Create folders option from the Admin menu at the top of the Google Sheet.
• Adding permissions is optional - use the Config sheet to change the dropdown as required. If you select 'No' then the usual Google Drive inheritance will occur based on the parent Google Drive folder.
• You can add multiple File IDs into the relevant cell and they can be different for each row. Leaving this cell blank/empty means no files will attempt to be copied into the new folder.
• The original filename will be updated during the copy to append the folder name to the end of it, in order to prevent creating a large number of files with identical names.
• There is a Log sheet to help troubleshoot any errors which may occur when running the tool.

Send data to a Google Form via Apps Script

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.

Screenshot of Google Form questions

Search Google Drive files and extract row data

The following Google Apps Script is designed to search the content of files on Google Drive which includes the given search word(s). Once it has collated these files it will then go into each one, search for the row of data that matches our search word(s) and extract that into a collective Google Sheet.

The information collected from the initial search to collate the files includes:

• The filename,
• The file ID,
• A direct/clickable link to the file,
• The type of file - this tool is restricted to Google Sheet.

The information collected from the second part of the tool includes:

• A direct/clickable link to the file,
• The Header row from the file,
• The row of data from the file.
  

Search and extract the content of Google Drive files

Check the domain of an email address by regex

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.

Screenshot of email addresses in a Google Sheet cell

Search Google Drive for owned files

The following Google Apps Script is designed to search Google Drive for all of the files owned by an individual (as defined by their email address). It will collate this information onto a Google Sheet, including:

• The filename,
• The file ID,
• A direct/clickable link to the file,
• The type of file eg PDF, Googe Sheet/Doc, etc,
• The file creation date, 
• The file last updated date.
  

Provide the owner and maximum script runtime for search Drive for files.

Display HTML modal dialogue popup

The following Google Apps Script is designed to display a dialogue box within a Google Sheet. Rather than using the typical Alert dialogue box however, this one does not suspend the server-side script whilst open. What this means is that the Apps Script code does not pause until the user has interacted with the popup in some way.

This is really useful for some of the systems I have produced because they can be running for several minutes at a time during which a user is unlikely to simply be sat watching them. So if they are off doing something else and the typical Alert dialogue pops up, it's possible the script will timeout and produce a bad error message.

With a HTML modal dialogue popup I can incorporate it into a try/catch and continue to display a user-friendly message whilst programming the code to terminate in a more controlled manner - regardless of how long the user takes to interact with it.

HTML modal dialogue box with a message