AppsScriptPulse

Bulk convert Excel files to Google Sheet files

Iterate through a Google Drive folder of Microsoft Excel files and convert them into individual Google Sheet files.

Bulk convert Excel files to Google Sheets

Bulk convert Excel files to Google Sheets

The following Google Apps Script is designed to iterate through a Google Drive folder of Microsoft Excel files and convert them all to individual Google Sheet files.

Source: The Gift of Script: Bulk convert Excel files to Google Sheet files

Email notification of Drive file changes within last x hours

Periodically check for any changes in a given Google Drive file and receive an email notification if there are any, within your given time-frame.

Email notification of Drive file changes. Image credit: Phil Bainbridge

The following Google Apps Script is designed to periodically check for any changes that have occurred to a given Google Drive file (ie edits to it) and send an email to notify of that. It goes through the Revision (Version History) of the file and looks at the modification dates for those that match with your given timeframe, to then collect the Username & Email address of the person that made those edits for inclusion in the email.

So as an example you may have a Google Sheet where you want to setup a check every 4 hours, to then be emailed if there have been any edits to it, along with who made those edits.

Source: The Gift of Script: Email notification of Drive file changes within last x hours

Search Google Drive folders for creation/modified dates using Google Apps Script

Search one level of Google Drive folders and extract creation, last updated and folder names into a Google Sheet.

Enter the Parent folder ID and search the folders within

Enter the Parent folder ID and search the folders within

The following Google Apps Script is designed to search through one level of Google Drive folders and extract the following information into a Google Sheet:

  • The folder name as a direct clickable link,
  • The folder creation date,
  • The folder last updated date,
  • The folder ID.

Source: The Gift of Script: Search Google Drive folders for creation/modified dates

Bulk rename files in Google Workspace/Drive with this new Add-on

Google Add-on to bulk rename Google Drive files quickly and easily in a given folder. You define exactly what you want to search for in the file name and the text to replace it with. Includes instructions and demo video.

Bulk Rename Files Add-on Interface

Bulk Rename Files Add-on Interface

Source: The Gift of Script: Bulk Rename Files Add-on

Archive Sheet data using tickboxes and Google Apps Script

Automatically move a row of data from one Google Sheet tab to another when a tickbox is selected.

Archive a row of data by ticking a box

Archive a row of data by ticking a box

The following Google Apps Script is designed to move a row of data from one Google Sheet tab to another when a tickbox has been selected in a given column. The tool was developed to help colleagues with automatically archiving data that had been actioned.

The tool uses an ‘onEdit(e) simple trigger’ to constantly monitor the Google Sheet for any changes in values. Is it specifically targeted at a tickbox-column only so that it does not archive rows of data on every edit of the Google Sheet.

Source: The Gift of Script: Archive Sheet data using tickboxes

Search Drive File Names Add-on

An Add-on to perform a Google Drive file name search from within a Google Sheet to keep the file information saved for future use.

Search Drive file names in Google Sheets

Search Drive file names in Google Sheets

It is easy to search for the names of files within Google 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.

This Google Sheet will contain:

  • a clickable link to the file,
  • the file type,
  • the date it was created,
  • when it was last updated,
  • the folder path where it is stored,
  • the ID of the file.

Source: The Gift of Script: Search Drive File Names Add-on

How to pass Apps Script data directly into a HTML page

The following Google Apps Script code is designed to create a HTML popup in a Google Sheet and pass some data directly into it so that the popup can display the information.

Display Apps Script code directly in a HTML popup

This was part of a larger project I was working on so I have stripped all of that away here and kept the bare minimum for demo purposes. I already knew how to get the HTML page to run a Function once it had loaded to then access static data from within the Google Sheet, but what was tripping me up here was if that data was coming directly from Apps Script itself – such as an error message from a try/catch.

Source: The Gift of Script: How to pass Apps Script data directly into a HTML page

The Gift Of Script: Archive Sheet data Add-on

Move/archive a row of data from one Google Sheet (tab) to another, within the same file, using this Google Workspace Add-on. It will also remember your individual preferences for each file so it is even quicker to use next time around.

New Features
January 2022
A new ‘Settings’ page allowing you to select more than just cell values when archiving row data. You can now also select:
> Text formatting: bold, italics, colour, size and style.
> Cell: background colour, Notes and wrapping.

Source: The Gift of Script: Archive Sheet data Add-on

Change file permissions from Editor to Viewer

Google Drive Access Screen

How to change Google Drive file permissions from Editor to Viewer as said user.

The following Google Apps Script is designed to change the Google Drive file permissions of a specific user from Editor to Viewer. The function actually came about when needing to end a process where a user had been editing a Google Sheet on a Shared drive that they should only then have Viewer access to. To complicate matters the Apps Script code is running as said user when it needs to reduce their own permissions.

Source: The Gift of Script: Change file permissions from Editor to Viewer

Create a new Sheet tab and format a header row

Create a new Sheet tab within an existing Google Sheet file and format a header row (font size, colour, alignment, etc) for appending data to.

Header row

Header row

The following Google Apps Script was developed as part of a larger tool for collating file information into a specifically formatted Google Sheet. As the tool was an Add-on I needed to setup the Google Sheet file in advance so that information could be later appended to it. This code:

  • Checks if a particular named Sheet (tab) already exists within the Google Sheet file. If it does then it will delete the existing Sheet and create a brand new one.
  • Reduce the overall number of columns within the Sheet.
  • Insert 1 row of data to use as the Header row.
  • Set the Header row font size, colour, weight (bold) and horizontal/vertical alignment.
  • Set the Header row background colour, height and column widths.

Source: The Gift of Script: Create a new Sheet tab and format a header row

Subscribe to Apps Script Pulse...