AppsScriptPulse

Solutions, Drive, Sheets, Intermediate

Automating the creation of multiple folders in Google Drive with Google Apps Script (with a nice Google Sheet processing code pattern)

Tags:
| | 0 Comments |

 

you can use the following Apps Script to create folders in Google Drive for students and share those folders with their email addresses based on data from a Google Sheet

Whilst this post from Amit Agarwal looks at the automatic creation of Google Drive folders, it’s also worth having a look at it at for an Apps Script pattern for iterating over and processing data from Google Sheets. In particular there is a getStudentData()function that turns the 2D array return from .getValues() into an object array which includes a rowIndex. This makes it easy to update your Google Sheet when each row is processed.

Source: Automating the Creation of Multiple Folders in Google Drive – Digital Inspiration

Solutions, Sheets, Intermediate

Create Your Own “UptimeRobot” to monitor websites using Google Sheets, Gmail, and Google Chat

Tags:
| | 0 Comments |

If you monitor important websites and require advanced features, tools such as UptimeRobot is the solution for you. However, if you only have a few personal websites and want a simple, customizable, and free alternative for monitoring them, this may be a better option.

In this tutorial, I will guide you through setting up a website monitor using Google Apps Script and Google Sheets. This monitor will periodically check the status of your websites and notify you via email and Google Chat (optional) if any issues are detected.

We’ve featured other website uptime solutions using Google Apps Script, this one is nice because it also includes an optional Google Chat integration allowing to send alerts your preferred Chat Space. Another useful feature, used by a number of paid for uptime monitors, is defining a keyword which must be found in the page. Follow the link to the source post for all the code and instructions on getting started.

Source: Tutorial: Create Your Own “UptimeRobot” to Monitor Websites Using Google Sheets, Gmail, and Google Chat

Solutions, Gmail, Intermediate

How to efficiently read email messages with the Gmail API and Google Apps Script batch requests

Tags:
| | 0 Comments |

There are two ways to pull email addresses from Gmail messages. The simpler, and more popular, method is that you pull a list of messages from which you wish to extract the email and loop over them to extract the email addresses. A more efficient way to pull email addresses from multiple email messages is to make a single batch request to the Gmail API with the help of Apps Script’s UrlFetch service.

We’ve featured a couple of posts on how you can make batch requests with Google Workspace APIs. With the limited runtime in Apps Script using batches can be an invaluable tool to finish your script in the execution limit and also improve your user experience. In this particular example for Amit Agarwal he looks at how batch requests can be made to the Gmail API by first get message details from a call to Gmail.Users.Messages.list, before getting message details in a single UrlFetchApp.fetchAll call. All the example code is included in the source post and to get to execute it copy/paste/run the following function:

const app = () => {
  const messageIds = searchGmailMessages();
  makeBatchRequest(messageIds);
}

Source: How to Efficiently Read Email Messages with the Gmail API and Apps Script – Digital Inspiration

Solutions, AI, Gmail, Sheets, Intermediate

Automate invoice management in Gmail with Document AI and Google Apps Script

Tags:
| | 0 Comments |

Image credit: Full diagram of invoice automation in Gmail with Document AI and Apps Script –
Stéphane Giron

Tired of manually managing your invoices in Gmail? Say goodbye to the hassle and hello to efficiency with Document AI and Apps Script.

There is a lot of buzz around generative AI, but it’s also worth remembering Google have a well established service offer in other forms of AI, the Document AI being one example of this. This post from Stéphane Giron highlight an Apps Script based workflow for analysing invoices attached to Gmail messages using the Document AI. There is a bit to setup in a Cloud project to get this working and as a paid for service costs to consider. Further information on this and all the code you need to get started are included in the source post.

Source: Automate invoice management in Gmail with Document AI and Google Apps Script

Solutions, Intermediate

Google Drive folder picker using jsTree with Google Apps Script and Javascript

Tags:
| | 0 Comments |

This is a sample script for the folder picker using jsTree with Google Apps Script and Javascript.

The Google Picker is a file picker that allows users to select files from Google Drive which can be used in your Apps Script projects (See Using Google Picker with Google Apps Script). The Picker is good but there are certain scenarios where the functionality isn’t available, one I’ve personally found is selecting the My Drive or Shared drive root. Here’s an alternative approach from Kanshi Tanaike for an alternative folder picker created using jsTree. In the post you’ll find more information plus how you can use this solution with a service account.

Source: Folder Picker using jsTree with Google Apps Script and Javascript

Solutions, Admin SDK, Sheets, Intermediate

Google Apps Script Patterns: Keeping a gam generated users report up-to-date with Google Apps Script

Tags:
| | 0 Comments |

In Google Workspace gam is probably the ‘go to’ tool command line tool which allows administrators to easily manage domain and user settings. Recently I was asked about how you can keep gam generated reports up-to-date using Google Apps Script. It’s worth remember that gam uses public Google Workspace APIs when it performs actions and reports. In this post I show you a pattern for building script to keep gam reports fresh.

Source: Google Apps Script Patterns: Keeping a gam generated users report up-to-date with Google Apps Script

Library, Solutions, AppSheet, Intermediate, No Code

Introducing AppSheetApp: A Google Apps Script library/helper class for the AppSheet API

Tags:
| | 0 Comments |

AppSheet is headlined as a no-code platform but this doesn’t mean for developers there aren’t coding opportunities. In this post I’ll be sharing AppSheetApp, a Google Apps Script helper library we’ve made at CTS which makes it easy to use the AppSheet API in your own AppSheet apps. As part of this I’ll highlight how this library has made our AppSheet powered Grab n Go Chromebook loaner solution immediately scalable and data source agnostic.

In this post the worlds of AppSheet and Apps Script collide, the resulting fusion is a library which makes it easier to integrate external data and events into your AppSheet app. This solution hopefully makes it a lot easier for developers and low coders to do more with AppSheet creating the possibility to use one (or a couple) of lines of code to interact with your app and not worry which data source your app is using. This post has more details of how you can get started and an example of the impact its already making with an alternative Grab n Go Chromebook loaner solution I’ve been part of at CTS.

Source: Introducing AppSheetApp: A Google Apps Script library/helper class for the AppSheet API

Library, V8, Drive, Intermediate

FilesApp is a GAS library for retrieving file and folder list in Google Drive

Tags:
| | 0 Comments |

When I create some applications using Google Drive, there are often the case which is required to retrieve the file list and folder list. I had prepared the script each time for each case so far. But recently, I thought that if there is a library for retrieving the file and folder list (as a tree), it will be useful for me and other developers.

The FilesApp library by Kanshi Tanaike has been around since 2018. I have to confess I wasn’t aware of this library until I read a more recent post, Retrieving Total File Sizes in Specific Folder of Google Drive using Google Apps Script. Similar to my own post on Creating a Google Drive report in Google Sheets the FilesApp library directly communicates to the Drive API, this library however goes a step further in terms of performance by using UrlFetchApp.fetchAll to handle some asynchronous Drive API calls.

Source: GAS Library – FilesApp

New IDE, Solutions, Web App, V8, Intermediate

Apps Script: solutions to the issue “installable triggers failing when created from another trigger function in V8”

Tags:
| | 0 Comments |

There’s a long standing issue with triggers installed programmatically by other triggers, that was kind of fixed last year, but users are still reporting problems, at least in certain locations (Japan, India and others). The issue in short: triggers installed programmatically would not be created, or fail systematically.

In many cases this affects add-ons (and, to be noted, the issue still persists when testing them, see Testing details); I had a slightly different scenario: a webapp that, when executed (doGet), would install a time based trigger for the user executing the script.

This article provides two solutions to an annoying issue and unlocks many possibilities for programmatically installed triggers.

Source: Apps Script: solutions to the issue “installable triggers failing when created from another trigger function in V8”

Solutions, Drive, Sheets, V8, Intermediate

Creating a Google Drive report in Google Sheets: Making Google Workspace Enterprise solutions with Google Apps Script

Tags:
| | 0 Comments |

Image credit: Martin Hawksey (with the help of DALL·E 2)

Think 10x — supercharging your Google Apps Script solutions by directly calling Google Workspace Enterprise APIs.

The Google Apps Script built-in services like SpreadsheetApp, Maps and GmailApp are a great onramp for users with limited coding experience, the flip side is you can find yourself easily getting results but not in the most efficient way. DriveApp is a great example where Google have made it easy to iterate across folders and files, but when you have lots of folders and files it becomes a time consuming process and you hit execution limits.

An alternative approach is using Google Apps Script to make direct calls to the Drive API. The benefits of this approach is you can be more specific in the data you want back and it gives more flexibility with how you call the API, in some cases with the ability to make batch or asynchronous processes.

In this post I highlight a method ideal for scenarios when you want to index larger volumes of My Drive files and folders to a Google Sheet with calls directly to the Drive API. The post includes some sample code you can use which instead is able to reduce a 4 minute runtime to index 10,000 files and folders to one that can complete in under 40 seconds!

Source: Creating a Google Drive report in Google Sheets: Making Google Workspace Enterprise solutions with Google Apps Script