Remove a users edit access to a Google Drive file via an Apps Script Web App.
Web App code sample
The following Google Apps Script is designed to remove a person’s edit access from a Google Drive file via a Web App. The reason for using a Web App in this instance is because removing a person’s access to a file whilst they are running Apps Script code typically results in an error (or most certainly the inability to cleanly end the code). In the project I was working on prior to this step the code needs to send some automated emails before finishing with removing the person’s access.
One of the great things about Google Apps Script is the way you can automate tasks. I’ve previously written about how I automate reporting and other examples like running backup processes. These usually run daily or once a month which is very straight forward to setup in Google Apps Script. If you want to run a script automatically every x number of months such as quarterly it gets a little harder. If you only want you script to run every three months … another option is to manage triggers programmatically which allows you to specify the date a function should be run again.
I’ve recently been revisiting some of my old Google Apps Script posts to do a bit of housekeeping. I thought this was a nice little snippet should you want to schedule a function to run on a time-driven trigger greater than one month. The trick used is to recursively create a time-driven trigger when the function is called. The solution comes with some caveats :)
I wanted to give a presentation to a group of people. In order to make the presentation more engaging & eliminate silences during the slideshow, I thought of conducting polls in between. … Since I am giving presentation using Google tools, I thought let’s try it out with Google Apps Script.
I thought this was a nice little Apps Script snippet from Prateek Sharma which hooks into a .onFormSubmit() trigger to update all charts embedded from Google Sheets in a Google Slides presentation. Prateek provides detailed steps including the code for setting this up. Unfortunately, a limitation of Google Slides is once you go into ‘slideshow’ mode all charts become static images so you need to jump back to the editor view to show real-time results.
Learn how to automatically preserve the formatting in Google Sheet when new Google Form responses are submitted.
Handy little Apps Script snippet from Amit Agarwal should you need to keep any custom formatting applied to linked Google Forms responses in Google Sheets. Another way you can approach this is using ARRAYFORMULA to reference the form responses in another sheet and apply your desired formatting.
The default ‘Form responses’ sheet can be hidden if needed. Downside of using ARRAYFORMULA is you are referencing a cell range which can cause confusion when using features like sort. See the source link for all the code used in Amit’s solution.
If you selected 1 or more rows, Publigo lets you generate a personalized document for those rows only or all visible rows in your sheet. Image credit: Romain Vialard
It is super easy in Google Sheets to select only specific rows, even if they are not adjacent. Simply hold down the CTRL / command key while making your selections. And those selections are also available in Google Apps Script via the method getActiveRangeList() … For Google Apps Script developers, here is a quick snippet showing how to process only selected rows.
Following the previous Pulse post on Get all selected Ranges in Google Sheets Romain Vialard got in touch to highlight the approach they have implemented to include this feature in the Mergo and Publigo Google Workspace Add-ons. This source post from Romain includes a snippet of code which might be useful for other Google Apps Script developers.
Get all selected Ranges in a Google Sheet along with values, row and column details.
Selected ranges in a Google Sheet
The following Google Apps Script was from some learning around multiple ranges being selected/highlighted by a user in a Google Sheet. I have always worked with individual cells or continuous ranges within Sheets, but during the development of my Archive Sheet data Add-on I wanted to enhance it by allowing more rows to be acted upon in one go.
I practiced getting a few values after looping through each selected range:
There may be times when you want to present different content in your add-on’s homepage depending on certain conditions.
For example, I want to present a card to the user when they run my add-on for the first time (I’ll call it ‘card-A’), but present a different card for each subsequent time the add-on is run (‘card-B’).
Whilst this post from Clark Lind focuses on Gmail add-ons the code snippet could be used for other Google Workspace Add-ons for Calendar, Drive, Docs, Sheets and Slides that use the Card Service.
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.
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.
