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
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.
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog: www.pbainbridge.co.uk
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].
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.
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.
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog: www.pbainbridge.co.uk
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.
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:
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.
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog: www.pbainbridge.co.uk
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.
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog: www.pbainbridge.co.uk
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
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.
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog: www.pbainbridge.co.uk
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).
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog: www.pbainbridge.co.uk
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 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.
Popups – as well as the ‘Log’ sheet the tool displays a direct popup to the user if it encounters a problem.
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.
Clickable links – created by the tool which means you can navigate directly to the new folders and files directly from the Google Sheet.
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.
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).
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog: www.pbainbridge.co.uk
