AppsScriptPulse

The Gift of Script: Convert Google Doc to PDF in a given folder

Convert a Google Doc into a PDF in a given folder and optionally delete the original Doc file.

The following Google Apps Script is designed to create a PDF file of a Google Doc in a Drive folder that you specify, with the option to delete the original Doc. This snippet of code is from larger developed solutions and allows you to understand and replicate the process.

This post is somewhat of an updated version of this Convert Doc to PDF and move into a new folder blog post. Whilst the code in it still works it was part of my early days of learning Apps Script and it was also when Google allowed for files to exist in more than one place (a feature now replaced by Shortcuts).

Source: The Gift of Script: Convert Google Doc to PDF in a given folder

Bulk create Google Drive Folders and add Files 2.0

Bulk create Google Drive folders with optional files copied into each one. With full control over naming.

Bulk create Google Drive folders with optional files, from a Google Sheet

Bulk create Google Drive folders with optional files, from a Google Sheet

This post is a variation of this Bulk create Google Drive Folders and add Files tool post.

Key Functionality

  1. 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.
  2. Continue from where it left off – if you have a lot of Google folders and files to handle and the above runtime is reached the tool will complete the current row/folder and prompt you to run it again, without duplicating any folders or files already created.
  3. Popups – as well as the ‘Log’ sheet the tool displays a direct popup to the user if it encounters a problem.
  4. Full naming control – you have the ability to tweak every single folder and file name that is created by the tool. See the Concatenate section below to understand the true power of this.
  5. Clickable links – created by the tool which means you can navigate directly to the new folders and files directly from the Google Sheet.
  6. Add more files – the tool currently has columns for 10 files to be added to each folder, but you can actually append more as long as you follow the same convention of 2 columns per file (1 file name and 1 file ID). Do this before you run the tool as you cannot add more files to a folder once it has been created.
  7. Add more folders – after running the tool you can either choose to clear all of the data within it (use the ‘Reset sheets’ option from the ‘Admin’ menu) and start again or you can simply append further rows/folders and re-run without any duplication (if the ‘Folder Link’ column is not blank/empty then it will skip that row).

Source: The Gift of Script: Bulk create Google Drive Folders and add Files 2.0

Bulk convert Google Docs to PDFs

Bulk convert all the Google Docs within a given folder to PDFs and optionally delete the Docs afterwards. Specify the destination folder for the PDFs.

Bulk convert Google Docs to PDFs using Apps Script

Bulk convert Google Docs to PDFs using Apps Script

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.

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

Bulk create Google Drive Folders and add Files

Bulk create Google Drive folders with optional files copied into each one. Also optionally add edit permissions to these folders/files.

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

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

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.

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.

Source: The Gift of Script: Bulk create Google Drive Folders and add Files

Send data to a Google Form via Apps Script

Use Google Apps Script to create a prefilled URL of data that can be submitted to a Google Form.

Screenshot of Google Form questions

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.

Source: The Gift of Script: Send data to a Google Form via Apps Script

Search Google Drive files and extract row data

Search and extract the content of Google Drive files. Collate the row data into a collective Google Sheet.

Search and extract the content of Google Drive files

Search and extract the content of Google Drive files

This was created for a specific Subject Access Request but may be of interest/use to others.

Source: The Gift of Script: Search Google Drive files and extract row data

Check the domain of an email address by regex

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

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.

Source: The Gift of Script: Check the domain of an email address by regex

Search Google Drive for owned files

Use Google Apps Script to search Google Drive for all files owned by a user and collate their details onto a Google Sheet.

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

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

It will collate:

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

Source: The Gift of Script: Search Google Drive for owned files

Extract text from multiple Google Docs into a Sheet

The following Google Apps Script is designed to iterate through Google Docs in a given Google Drive folder and extract the paragraphs of text along with a link to each file into a Google Sheet. A new column will automatically be appended for each paragraph.

This tool was designed with the vision that you may wish to centrally collate some comments/feedback written by others in Docs, into one central location so you do not need to open each file in turn.

Provide a Google Folder ID to extract text from Docs

Provide a Google Folder ID to extract text from Docs

Source: The Gift of Script: Extract text from multiple Google Docs into a Sheet

Bulk create Sheets from a Google Sheet

Use Google Apps Script to bulk create Sheets from a Google Sheet of data. Loop through each row and copy the data to the new file.

Bulk create Sheets from Google Sheet

Bulk create Sheets from Google Sheet

Source: The Gift of Script: Bulk create Sheets from a Google Sheet

Bulk create Shared drives with permissions

Bulk create Google Shared drives with permissions all from a Google Sheet.

Bulk create Shared drives from a Google Sheet

Bulk create Shared drives from a Google Sheet

  • Provide the name of the Shared drive on each row in column A.
  • Provide the email address(es) of the Google Account(s) under the relevant Access level column (Manager, Content Manager, Contributor, Commenter, Viewer). Use a comma and a space to separate multiple email addresses, eg: [email protected], [email protected], [email protected].
  • Ensure you include at least one Manager – the script will check for this – so as to prevent creating a Shared drive that you are then unable to access.
  • On the ‘Config’ sheet provide the column numbers – leave the default values unless you change the structure of the ‘Data’ sheet and move columns around.
  • The ‘Log’ sheet should help to troubleshoot any errors you experience, but there will also be a popup message should the script fail at some point.

Source: The Gift of Script: Bulk create Shared drives with permissions

Get the difference between dates in minutes

The following Google Apps Script is designed to get the difference (in minutes) between 2 dates from a Google Sheet. This was part of a tool used to create events from data in a Google Sheet where I needed to get the duration of the meeting for Zoom. The actual date, hour and minute values are separated in columns as it was easier to control user input in that format, so we will need to piece them together.

Start and End date values in a Google Sheet

Start and End date values in a Google Sheet

Source: The Gift of Script: Get the difference between dates in minutes

Set permissions on a Shared drive with Google Apps Script

The following Google Apps Script is designed to bulk set permissions on a Shared drive. It has been created as a standalone Function that requires the ID of the Shared drive and manually entered email addresses for the relevant permissions.

This is how I first worked with it to learn what information was required and how to structure the content, before combining it all into a Google Sheet tool which will be blogged about in the near future.

Source: The Gift of Script: Set permissions on a Shared drive

Generate a quick unique(ish) value

Generate a quick, random and fairly unique value in Google Apps Script. Found this useful for random strings to use as Meeting IDs for example.

Source: The Gift of Script: Generate a quick unique(ish) value

Get permissions of a Shared drive

The following Google Apps Script is designed to get permissions of a given Shared drive. It uses the Shared drive ID to then list a users email address and role they have. This page on Google Drive permissions is useful for what we need to put together to make the call.

Source: The Gift of Script: Get permissions of a Shared drive

Find and replace in a Google Sheets with Google Apps Script – whole data search

This solution cleanse Google Sheet data by searching for specific words and replacing them with an alternative. As part of this the solution highlights cell changes with an alternative colour. This example uses ‘TextFinder’ rather than ‘For Loops’.

Use the 'Welcome' sheet for input to run the tool

Use the ‘Welcome’ sheet for input to run the tool

Source: The Gift of Script: Find and replace in a Google Sheet – whole data search

Bulk create Google Drive folders with a sub-folder

Bulk create Google Drive folders with a sub-folder from data given in a Google Sheet. Optional user permissions can be added.

Bulk create Google Drive folders from a Sheet of data

Bulk create Google Drive folders from a Sheet of data

This tool and blog post is largely a continuation of this one for bulk creating Google Drive folders. The main difference being that there is an extra column to provide the name of your sub-folder. Note that the tool will try to create a sub-folder regardless of what is entered – as I was just aiming to create a version for the many requests I got from the previous blog post from people who wanted a sub-folder.

Source: The Gift of Script: Bulk create Google Drive folders with a sub-folder

Find and replace in a Google Sheet

Cleanse Google Sheet data by searching for specific words in a columns and replacing them with an alternative. Colour any cell changes to highlight. This is one of many ways of performing the task simply using loops.

A list of words to find and their replacement equivalent.

A list of words to find and their replacement equivalent.

Source: The Gift of Script: Find and replace in a Google Sheet

Extract a list of Google Group members into a Sheet with Google Apps Script

Google Groups

Image: Google

Extract a list of members of a Google Group (email address and role) into a Google Sheet. You simply enter the email address of the Google Group that you belong to and then run the Function.

Source: The Gift of Script: Extract a list of Google Group members into a Sheet

Submit a Google Form to a Slack channel via a Webhook

Send a Google Form submission to a specific Slack channel as a message, via a Webhook.

Messages can be posted in to a Slack channel via the Google Form

Screenshot of message when posted in Slack channel

Source: The Gift of Script: Submit a Google Form to a Slack channel via a Webhook

Create a Google Calendar Event via the Calendar API

Use the Calendar API to create an Event via Google Apps Script. Includes a tutorial video.

This is a simple Google Apps Script file that may look lengthy but in actual fact is just each possible item of an event that a user may wish to modify. Some of the items have default values which means you could remove them from the script and the Calendar API would use these defaults instead. My hope is that laying out the format like this allows you to then manipulate as you require and learn as I did.

Source: The Gift of Script: Create a Google Calendar Event via the Calendar API

Bulk create Google Calendar events with optional Meet or Zoom

Bulk create Google Calendar events with optional video conferencing (Google Meet or Zoom) all from a Google Sheet.

Google Sheet columns allow for event details to be added

Google Sheet columns allow for event details to be added

Features of the tool

  • Performs an initial check that you have access to the provided Calendar to create events on.
  • Allows for events to be created on another Calendar that you have suitable access to (not just your own).
  • Uses toast popups to inform you of the progress as each creates each event per row.
  • Fast and efficient for creating a large number of events in one go.
  • Will not duplicate events if re-run, so you can continue to append further if you wish.
  • Provides a direct link to the created event from within the Google Sheet for easy access.
  • Performs a check of any missing ‘required’ information and informs the user via a popup so they can resolve this.
  • Includes ‘Log’ sheet to help output any error messages.
  • Has a ‘Reset’ option in the menu bar to remove all entered data and start from scratch.
  • Replicates 90%+ of the settings you can adjust when directly creating an event in Google Calendar.

Source: The Gift of Script: Bulk create Google Calendar events with optional Meet or Zoom – overview

Bulk create Google Calendar events with optional Google Meet from a Google Sheet

Bulk create Google Calendar events with optional video conferencing (Google Meet) all from a Google Sheet.

Google Sheet columns allow for event details to be added

Google Sheet columns allow for event details to be added

Features of the tool

  • Performs an initial check that you have access to the provided Calendar to create events on.
  • Allows for events to be created on another Calendar that you have suitable access to (not just your own).
  • Uses toast popups to inform you of the progress as each creates each event per row.
  • Fast and efficient for creating a large number of events in one go.
  • Will not duplicate events if re-run, so you can continue to append further if you wish.
  • Provides a direct link to the created event from within the Google Sheet for easy access.
  • Performs a check of any missing ‘required’ information and informs the user via a popup so they can resolve this.
  • Includes ‘Log’ sheet to help output any error messages.
  • Has a ‘Reset’ option in the menu bar to remove all entered data and start from scratch.
  • Replicates 90%+ of the settings you can adjust when directly creating an event in Google Calendar.

Source: The Gift of Script: Bulk create Google Calendar events with optional Google Meet

Create a Zoom meeting via the API using Google Apps Script

Create a meeting in Zoom via the API and OAuth, using Google Apps Script and a Web App. The details for the meeting will be collected from a Google Sheet.

Zoom meeting details in a Google Sheet

Zoom meeting details in a Google Sheet

Source: The Gift of Script: Create a Zoom meeting via the API

Get your Zoom meeting settings via the API and storing in a Google Sheet using Google Apps Script

Get your Zoom meeting settings via the Zoom API and OAuth, using a Web App in Google Apps Script. Here we will access user settings in your Zoom account, specifically the meeting settings, and log the results in a Google Sheet. This provides both a way of confirming we have made a successful connection and will allow us to create subsequent Zoom meetings using these settings.

This will log the results in a Google Sheet to help you understand what is happening and the type of data that is returned.

Source: The Gift of Script: Get your Zoom meeting settings via the API

Connect to Zoom API with Apps Script and OAuth

How to connect to the Zoom API via OAuth. Using Google Apps Script and a Web App. An alternative to JSON Web Token. Includes a tutorial video.

Source: The Gift of Script: Connect to Zoom API with Apps Script and OAuth

The Gift of Script: Search spreadsheet for multiple criteria

Search through rows of a Google Sheet simultaneously looking for certain criteria all in one single loop. I needed a way of doing this without multiple loops despite searching for separate information and here is a way of achieving that via true/false flags.

Rows of email address and role in spreadsheet

Screenshot of spreadsheet data to search

Source: The Gift of Script: Search spreadsheet for multiple criteria

Using Google Apps Script to generate code for handling Google Forms submission data

Automate the generation of further Apps Script code typically used when capturing Google Form submission data. I tend to find there are 2 lines of code per Form question that I have to write which when scaled up to 60+ questions is a lot of time and chance for human error.

Apps Script code can be automatically generate as lines of text in a document.

Auto generate Apps Script code lines.

Source: Generate Apps Script code for Forms

Subscribe to Apps Script Pulse...