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

Get Classroom Data into Google Sheets using Google Apps Script

Image credit: Aryan Irani

In this blog, I am going to show you how to get Google Classroom Data into your Google Sheet using Google Apps Script. Using this, you can keep track of all the classes that you have in your Google Classroom.

This is a nice introductory tutorial from Aryan Irani which looks at exporting basic information about your Google Classroom courses to Google Sheets. The example script imports the main details about your courses but if you are interested in finding out what else is available the official documentation for a course resource outlines what else is available.

Source: Get Classroom Data into your Google Sheet 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

Getting a Google Calendar event owner using Google Apps Script 

In the Google Group Apps Script community there was an interesting question about returning a Google Calendar event owner/organiser. The problem was that using the Calendar Service it is possible to use getGuestList(includeOwner) to return the EventGuest[] array and in theory using getGuestStatus() find the guest with the OWNER GuestStatus Enum:

const event = CalendarApp.getEventById('some_event_id');
const guestList = event.getGuestList(true); // get the guest list including owner  
// Iterate across EventGuest[]
for (let i = 0; i < guestList.length; i++){
  let guest = guestList[i].getEmail();
  let status = guestList[i].getGuestStatus().toString();
  Logger.log(guest + ' ' + status);
}

However, in practice as the organiser status defaults to ‘Yes’ and can change to ‘No’ or ‘Maybe’ the OWNER status is never returned:

How to solve? Well one solution to find the owner is to get the event guest list with and without the owner then filter out the list ignoring any accounts that appear twice:

  const event = CalendarApp.getEventById('some_event_id);
  const guestList = event.getGuestList(true); // get the guest list including owner
  const guestListWithoutOwner = event.getGuestList(); // get the guest list without the owner

  // filter the guest list to ingore duplicate emails leaving only the owner
  // based on https://stackoverflow.com/a/42995029/1027723
  const owner = guestList.filter(o1 => !guestListWithoutOwner.some(o2 => o1.getEmail() === o2.getEmail()));

  Logger.log('The owner is: ' + owner[0].getEmail());

Alternatively if the Calendar Advanced Service is enabled in your Apps Script project the owner email can be returned using:

  // With Calendar Advanced Service enabled
  // Note: eventId is different to the one returned by CalendarApp as it doesn't include @google.com
  // See https://stackoverflow.com/a/55545091/1027723 for how to handle 
  const event = Calendar.Events.get(CalendarApp.getDefaultCalendar().getId(), 'some_event_id');
  Logger.log('The owner is: ' + event.organizer.email); 

Clearly the second method is more direct, but which is quicker? I’ll let you tell me :)

How to remove a Google Sheets button (drawings) or images connected to a Google Apps Script after the script has been run – Yagisanatode

There have been a few instances in my work where I need to remove a button (more accurately, a button drawing) or and image from a Google Sheets tab once the associated script has been run.

Perhaps we just want the user to run a process on a Google Sheet workbook, just once but not more times. This would be a good case for removing the button or drawing after use.

Note: This tutorial expects that you know how to create a drawing or a button from the Google Sheets drawing tool. 

Some clever Google Sheets pseudo button manipulation shown off here by Scott Donald. If using Apps Script functions assigned to drawings and images is something you are interested by sure to also check out Kanshi Tanaike’s example of ‘Using Google Apps Script to disable custom functions assigned to Google Sheets buttons/images to prevent simultaneous execution‘.

Source: How to remove a Google Sheets button (drawings) or images connected to a Google Apps Script after the script has been run – Yagisanatode

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

NEW! Calling Google Apps Script from an automation in #AppSheet

Add custom integrations to your app using Apps Script. … You can use custom logic from within your AppSheet app by calling Apps Script from an AppSheet automation bot.

Examples of custom logic that you can implement include:

  • Create a calendar appointment when a button is clicked
  • Add a slide to a presentation when a new row is added
  • Save photos to Drive and share with specific individuals when uploaded using a form
  • Create an audit log by generating a Google Docs file with data from a table
  • Call an external service for machine learning prediction using data from a newly added row and write back model prediction

If you are not familiar with AppSheet it’s Google Cloud’s no-code platform that allows users to create custom applications that can be deployed across mobile and web, often setup using Google Sheets as a data source. AppSheet can be customised with workflows and automations, and those automations have recently been extended with a new preview integration to Google Apps Script.  The video in the Call Apps Script from an automation page provides a great summary of how the Apps Script integration works.

Source: Call Apps Script from an automation

Create multiple labels in Gmail with Google Apps Script

With this reusable script you can create multiple labels in Gmail whenever you need to do so. All you have to do is create the script as shown in the video and then use it to create multiple Gmail labels in one go.

Continuing the Gmail label theme in this latest video tutorial from Chanel Greco you can learn how to batch create Gmail labels from data in a Google Sheet.

How to build an automated PDF report in Google Sheets using Apps Script 

Grab this automated ConvertKit Report in Google Sheets to monitor your email list growth and use it to project your future list growth.

Ben Collins shares the solution he uses to produce a daily ‘ConvertKit’ PDF report. Even if you don’t use ConvertKit this post is still worth a read as the basic model of getting data from a third party API into Google Sheets and sending a PDF summary could come in handy. Perhaps not surprising given this post comes from Ben it includes some clever Google Sheet functions to prepare and shape data.

Source: Automated ConvertKit Report In Google Sheets

Google Apps Script: How to export Google Sheets as PDF – YouTube 

In this video you will see how you can use Google Apps Script to export a Google Sheets document as a PDF

It’s been a while since we featured a video tutorial from Chanel Greco, but that doesn’t mean she hasn’t been busy. In this recent tutorial from Chanel you can learn how to use Google Apps Script to export a Google Sheet as a PDF Document. You can watch this and other videos from Chanel on the saperis YouTube channel.

Source: Google Apps Script: How to export Google Sheets as PDF