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

Formatting date/time values using the user’s timezone in Google Apps Script

Working with dates, times and time zones can often be a bit of a headache. If you’d like to learn more about some of the challenges of dealing with ‘big balls of wibbly-wobbly, timey-wimey… stuff’ I recommend watching Comptuerphile’s Problem with Time & Timezones.

For displaying dates/time values for users in different time zones you can use Utilities.formatDate(date, timeZone, format) , the challenge however is working out the user’s timeZone . For Google Workspace Add-ons developers can configure the manifest option to include user locale information in event objects included as part of action callback functions [See Accessing user locale and timezone].

For web apps and editor add-ons using HTML Service it is possible to get the user’s browser time zone setting with the JavaScript code Intl.DateTimeFormat().resolvedOptions().timeZone (at time of writing this works in 93.85% of browsers). This can be passed back to your server script and used to format date/times for the user’s time zone. This forked gist provides an example of how this can be used in Google Sheets (props to Eric Koleda for the original solution, which uses .getTimezoneOffset() to get the users time zone offset).

The merits of Intl.DateTimeFormat() and .getTimezoneOffset() are discussed in Getting the client’s time zone (and offset) in JavaScript.

If using HTML Service is not an option an alternative workaround is getting the user’s Google Calendar time zone using the Calendar Service and .getTimeZone() . Here is an example script shared on Stack Overflow:

function getUserTimeZone() {
var userTimeZone = CalendarApp.getDefaultCalendar().getTimeZone();
Logger.log(userTimeZone)
}

The downside of this approach is it potentially adds an additional authorisation scope the user will need to approve before the script can run. As noted by Google best practice is always to limit the scopes in your Apps Script projects to the ones you need.

If you’ve got other tips for handling user time zones feel free to pop them in the comments.

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

How to insert images in Google Sheet cells [with built-in functions and Google Apps Script] – Digital Inspiration

Image credit: Amit Agarwal (@labnol)

Learn about the different approaches that will help insert images in Google Sheets and understand the reason why you may prefer one approach over the other.

In this tutorial Amit Agarwal covers the various ways you can insert images into Google Sheets, including with Google Apps Script. As part of this you can learn about the CellImage and CellImageBuilder which are relatively new features for adding an image to a Google Sheets cell. There are some useful code snippets included in the tutorial that can be dropped into your own projects.

Source: How to Insert Images in Google Sheet Cells – Digital Inspiration

Manage Shared Drives in Google Drive with Google Apps Script – Digital Inspiration

Image credit: Amit Agarwal

These code samples show how you can use Google Apps Script to manage and search through the content of shared drives in Google Drive using the Drive API.

Some handy Google Apps Script snippets from Amit Agarwal for interacting with Shared Drives. The post includes solutions for:

  • Create a Shared Drive
  • Share a Shared Drive with a User
  • List all Shared Drives
  • List Files in a Shared Drive
  • Move Files in Shared Drives
  • Copy Files in Shared Drives

Source: Manage Shared Drives in Google Drive with Google Apps Script – Digital Inspiration

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

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

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

Subscribe to Apps Script Pulse...