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.
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
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:
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, where I also have a number of useful Google Add-ons: www.pbainbridge.co.uk
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.
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.
In this post I want to cover three things. First I want to introduce a little app I’ve developed which allows you to create a RSS feed for any of your Gmail labels (with the option to remove certain links – useful if you don’t want others unsubscribing you from mailing lists). Secondly I explain how it was made and how you can use it yourself. Finally I want to discuss how this could be used in an open course environment, utilising the vast processing power from services like Twitter and reusing their target marketing emails to your benefit with a bit of ‘dark social judo’.
This solution was first published in May 2013 and since then Google Apps Script has evolved deprecating services that originally made this solution possible, in particular, ScriptDB and the original XML service which includes a handy .parseJS() method.
Following a request I’ve recently updated the solution to make it work again. As well as swapping out ScriptDB in favor of using the Properties Service I used the makeRSS method previously highlighted here in Apps Script Pulse.
Learn how to easy import transactions from PayPal into Google Sheets with Google Apps Script. You can import standard transactions, recurring subscriptions and donations.
Even if you aren’t interested in PayPal this post is well worth looking at as the author, Amit Agarwal, is a master when it comes to handling data with Google Apps Script and there is a general patterns of ‘get data from an API, write it to a Google Sheet’ that might be useful for your own projects.
No Google Apps Script SDK for your favorite API? No problem!
I’ve never seen a Google Apps Script SDK made available as part of an API, as, surprise surprise, it’s not the most popular choice for professional development. It’s time to start changing that, so we will learn to build our own!
[Editor note: Really impressive work from Dmitry providing a framework you can use to develop your own service for interacting with 3rd party APIs. The post includes lots of clear instructions and guidance to help you understand and learn about approaches for structuring your Google Apps Script code projects]
A simple and small Google Apps Script library for quickly and easily finding and updating records in Google Sheets with a familiar ORM-like syntax
Following our previous post on Converting Google Sheets cell values to an object array, Vance Lucas (@vlucas) got in touch to highlight the SheetQuery library he has created which as well as being able to get Google Sheet data as an object array has some additional nice features for updating cell values. As Vance highlights:
sometimes working with spreadsheets to find and update specific rows of data can be awkward and tedious. There is no direct built-in way to search for specific values in rows by headings. To do this with the Google-supplied APIs, you have to keep track of row index numbers, column index numbers, and arrays of row data while planning your updates. It’s not fun code to write, and it’s relatively error-prone, especially if you are deleting rows, which causes the row index numbers to shift dynamically.
The library includes a .where method which can be used to apply a filtering function to select the rows of a spreadsheet to be read and/or updated. If you are regularly developing scripts that interact with Google Sheets data this can be a great library to keep in mind.
This is a sample script for converting the values of Google Spreadsheet to an object using Google Apps Script.
I have a minor obsession with methods for converting Google Sheets data to an object array. Over the years I’ve documented various approaches I’ve developed on my personal site, like this one, but this solution from Kanshi Tanaike has to be my new favourite:
This article will look at some of the opportunities you’ll have when you pull in your libraries inline rather than leaving them as references to external files, all without leaving the IDE
For the more advanced Google Apps Script developer this is a great article to get your teeth into which should also hopefully give you some tips to help manage and maintain complex Apps Script projects.
