If your organisation is using Google Workspace Business Standard, Business Plus, Enterprise, or one of the other supported plans, you are likely taking advantage of the power of Google’s Shared Drives.
If you have decided to create a Google Apps Script project that needs to get a list of your Shared Drive (or a user’s shared drives in the case of a WebApp), then you might be scratching your head right now wondering how to get this list using the built-in DriveApp class.
Whelp, unfortunately, at the time of writing this article the DriveApp class does not have this functionality. However, it is pretty easy to access in a single line of code using an Advance API.
It is easy to search and replace text in Google Documents with the DocumentApp service of Google Apps Script. You can use use findText method with simple regular expressions to find text elements in the document that match a pattern and replace them with the specified text. All well and good but in some cases, this simple search and replace function may fail if the search text does not transform into a valid regular expression.
Some more regex goodness this time from Amit Agarwal who provides some very useful tips on escaping characters when using the DocumentApp service and findText method. Click through to the source link for more details.
Sourabh Choraria has packaged some of the validator.js methods into a Google Apps Script library. Ported validators currently includeisUrl and isEmail, date validators and more. Sourabh has also posted the backstory behind this library.
I recently was processing some data using Apps Script, and needed to parse out second-level domain info from a bunch of URLs. This is definitely not the job for regular expressions, but it’s perfect for an npm module, psl, that uses the public suffix list.
But while Apps Script has come a long way, and features lots of ES2015+ goodness nowadays, it’s not possible to pull in arbitrary code from npm and run it directly. To work around this…
Jeff Posnick provides some guidance on how esbuild can be used to bundle npm libraries for use in Google Apps Script. For an alternative approach to this problem you might also want to check Adam Morris’ appscriptsModules.gs solution on GitHub.
There are many ways to push data from SQL Server to Google Sheets, including using Python or Google Apps Script. While Python is great for a heavy SQL query, I find it a bit of a hassle when it comes to scheduling. Meanwhile, Google Apps Script works perfectly well for middle-size SQL queries and the triggers make it very simple to schedule, there is also no need to use any API.
In this article, I will share with you how to push data from MS SQL Server to Google Sheets using Google Apps Script and the tips I learned when implementing the solution.
Continuing the SQL theme this post from Trang Nguyen Ngoc provides a useful overview and introduction for connecting to a MS SQL databases using JDBC service. The post is particularly useful as it contains some tips if you encounter issues with connection.
[Note: there have been some issues recently with the JDBC service and if you encounter problems we recommend checking the issuetracker]
While working on Sheets Workbook functions – converted to Apps Script I realized that I’d need to tackle the query language at some point, so I figured I may as well go the whole hog and implement a comprehensive SQL variant for Apps Script. Luckily though, I found alasql so with a few tweaks it was ready to go!
A couple Google Apps Script community contributors have shared solutions for using the AlaSQL.js library. Latest come from Bruce Mcpherson, which is included as the source link. If you use Bruce’s fiddler library his post is worth checking out as he provides examples showing how both libraries can be used together. Another version of AlaSQL.js you should look at is Alex Ivanov’s AlaSQLGS which also includes some data and code samples.
Currently it is unknown if the checklist can be manipulated in Docs. Clark Lind suggests to replace a list item with a copy of one of them (which is in expected state – check/uncheck)
A nice example of the Google Apps Script developer community helping each other out. In this case Alexander Ivanov was looking for a way to interact with the new Google Docs checklist feature. A direct way to interact with these isn’t possible but some clever coding from Alex demonstrates what is currently possible.
If you are handling data from sheets it might be useful to use the same logic as is available in the worksheet for common tasks rather than reinventing them. Dates and Times are especially complicated as JavaScript dates are not good with timezones and date arithmetic, especially since spreadsheets and scripts can exist in different timezones from each other. I’m using Temporal – dates, times and timezones and the proposed new Date system for ECMAScript to emulate what Sheets functions do, and I’ll go through the implementation as a learning aid for Temporal as much as a documentation of the functions.
In this post, I would like to introduce the xpath tester using Web Apps created by Google Apps Script.
This is an interesting little Apps Script solution from Kanshi Tanaike which uses the existing Google Sheets =IMPORTXML() in a Google Apps Script Web App to test an XPath. As there is a delay in loading some Google Sheets function results this post includes a nice little coding pattern using a do...while loop to wait for results to render.
So you have this awesome idea for a Google Workspace Add-on (GWAO), but you need to be able to select Google Drive files and folders as a part of your process. … Ideally, you would want a built-in File Picker class that would select the files and folders from the directories you need. Whelp… unfortunately, we don’t have that right now for Google Apps Script’s Card Service.
One approach might be to build out a file picker card selecting each parent’s files and folders and navigate through it like, say, a linked list. … Instead, I decided to incorporate Googles File Picker API as a popup window from the sidebar, because, it’s kinda what it is designed for.
The Google Apps Script GDEs are publishing some very thorough tutorials just now. The latest comes from Scott Donald who has published a tutorial exploring how the Google Drive Picker can be used with Workspace Add-ons that use the Card Service. The post is particularly good at highlighting the steps required as a Google Cloud Console project.
