AppsScriptPulse

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

Importing data from MS Excel to Google Sheet with Google Apps Script

In this post we’re going to look at how we can get data from an Excel spreadsheet and import some of it into a Google Sheet. As an example, we’re going to upload an Excel which contains the current month’s royalty data from Amazon and add it to a central sheet which contains all the previous month’s royalties.

Similar to the post on converting a PDF document to text, this tutorial from Baz Roberts uses a similar approach of using the Google Drive API for file conversion, this time converting a MS Excel file into Google Sheets to make it possible to easily extract and update a master spreadsheet. The post includes a detailed explanation of the shared code.

Source: Importing Amazon royalty data from Excel to Google Sheet – Learning Google Workspace & Apps Script

How to extract text from PDF files with Google Apps Script – Digital Inspiration

Image credit: Amit Agarwal

This tutorial explains how you can parse and extract text elements from invoices, expense receipts and other PDF documents with the help of Apps Script.

We’ve previously featured a method for extracting text from a PDF from Scott Donald. This latest post from Amit Agarwal uses a similar technique of sending a PDF document to Google Drive API to convert to a text file and then using RegEx to extract the content you need. Given the number of PDF documents flying around between organisations both of these posts from Amit and Scott are worth being aware of for potential future projects.

Source: How to Extract Text from PDF Files with Google Apps Script – Digital Inspiration

Retrieving Google Docs summaries using Google Apps Script (hint: it’s using the existing Google Drive description property)

This is a sample script for retrieving the summary of Google Document using Google Apps Script. Recently, a blog of Auto-generated Summaries in Google Docs has been posted. I thought that this is very interesting function. I thought that when this function is released, checking each summary of a lot of Google Document will be much useful for simply confirming the document content. And also, I thought that when all summaries can be retrieved using a script, it will be also useful. In this post, I would like to introduce to retrieve the summary of Google Document using Google Apps Script.

There are a couple of pieces of interesting information highlighted by this post by Kanshi Tanaike that caught my eye . First, useful to have a reminder that Google announced automatically generated summaries in Docs in February 2022. This might be a premium feature for the paid Workspace accounts as like Kanshi I’m not seeing this yet in my own free Google Workspace domain. The bit that really caught my eye is Google Docs summaries are using the existing Google Drive description property, which means it is not currently available via DocumentApp or if using the Google Docs API as an Advanced Service Docs . I’m sure this will catch some people out and hopefully this post will point people in the right direction.

Source: Retrieving Summary of Google Document using Google Apps Script

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

Use Google Apps Script to pull all Google Shared Drives and permissions into Google Sheets

If you’re a Google Workspace Super Admin, you’ve probably been thinking of ways to inventory and keep a handle on Google Shared Drives. They are a really great addition to the Workspace offering, but there are still some gaps in reporting & oversight that some people have wanted. This solution uses Google Apps Script and the Drive API to pull information about all the Shared Drives in a domain into a Google Sheet. Then, it uses the Drive API again to loop through all the drives and get the top-level permissions.

For Google Workspace admins Nick Young has provided a nice solution for auditing Google Shared Drives within your Workspace domain. This post covers all the steps to setup, a link to the code on GitHub and some notes on limitations to be aware of.

Source: Use Apps Script to pull all Google Shared Drives and permissions into a Google Sheet

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

Code preparation for Hash Code 2022 with Apps Script [snippets for working with .CSV data files]

Apps script code to manage CSV files input and output for hash code 2022 of Google.

This post from Stéphane Giron provides some very useful Google Apps Script snippets for working with .CSV data files. Included in the post are ways to:

  • Retrieve CSV files with Apps Script and read data;
  • Treating CSV files; and
  • Create output CSV files

As a bonus the post shows you how you can create a backup text file of your apps script file.

Source: Code preparation for Hash Code 2022 with Apps Script

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

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 Share Files in Google Drive with Multiple Users using Google Apps Script – Digital Inspiration

Google Drive Access Screen

A limitation of the Drive API is that you can only share files with one user at a time. Google Apps Script is synchronous – it doesn’t support the async/await pattern of JavaScript Promises and you therefore cannot run the code in parallel.

There’s however a simple workaround to help you share a file or folder in Google Drive with multiple users in one go in parallel using the UrlFetchApp service.

On Pulse Phil recently shared a snippet for changing a Google Drive file permissions from Editor to Viewer. In this example for Amit Agarwal you can see how you can make these types of changes for multiple users without slowing down your script execution time.

Source: How to Share Files in Google Drive with Multiple Users – Digital Inspiration

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

How to Automatically Add Teachable Students to Google Drive Files and Folders when they Enroll in your Course – Yagisanatode

Learn how to instantly share newly enrolled teachable students to Google Drive files and folders with Google Sheets and Apps Script.

Scott ‘Yagi’ Donald provides a very thorough walk-through of how he has connected a webhook from a third party service to Google Sheets to share specific Google Drive folders and files with named users. This post is well worth a visit as it contains a number of tips and tricks like using Google Forms to follow-up with user email addresses that are not associated with a Google account.

Source: How to Automatically Share Teachable Students to Google Drive Files and Folders when they Enroll in your Course – Yagisanatode

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