AppsScriptPulse

[Testing] Concurrent writing to Google Sheets using Google Form and HTML form

When the users try to write to Spreadsheet using a form, the developers have to consider to the concurrent submission from the form. For example, when the multiple users submit the data with the form simultaneously, all data are possibly not to be saved to the Spreadsheet. So it is considered that it is important to know the information about the concurrent writing to Google Spreadsheet using a form. In this report, such situation was investigated.

A very useful analysis to see how many form submissions can be handled in Google Sheets at the same time. The experiment looks at both submissions using Google Forms and as a Web App.

Source: Concurrent Writing to Google Spreadsheet using Form

How to Request Payments with Stripe Checkout and Google Sheets – Digital Inspiration

How to use Google Sheets to generate Stripe payment links and request payments in any currency from customers anywhere in the world!

We’ve previously featured Amit Agarwal’s Razorpay and Google Sheets payment solution. This latest example from Amit highlights how to integrate Stripe payments with Google Sheets. The source post contains the code you’ll need, how to setup and also automation options.

Source: How to Request Payments with Stripe Checkout and Google Sheets – Digital Inspiration

How to Use Service Accounts and OAuth2 in Google Apps Script

Image: Dmitry Kostyuk

Give your scripts privileges that your users don’t have … In 99% of all cases, authorizations in Google Apps Script are extremely straightforward. When a user executes their script, they run it as themselves with their respective authorization scopes. … However, what if you need to give more rights to your app beyond what your intended users will have? … This is where service accounts come in.

A useful post to find out more about service accounts and how to use them with Google Apps Script. The tutorial put together by Dmitry Kostyuk includes example code for using a service accounts with Firestore, BigQuery and the Admin Directory API.

Source: How to Use Service Accounts and OAuth2 in Google Apps Script

Create Google Calendar events from sending yourself a Gmail on a mobile device using Google Apps Script

This Google Apps Script allows you to create a Google Calendar event by sending yourself an email in Gmail. Unlike Gmail’s current feature to create Google Calendar events, this script allows you to be on a mobile device.

We’ve featured a couple of contributions from Al Chen in the past and this is another great one. For some context Al has also posted Productivity hack for creating a Google Calendar event by sending yourself an email. For the source code and setup follow the link at the end of the post. If you use Product Hunt and find this solution you can upvote here.

Source: GitHub – al-codaio/events-from-gmail: Create Google Calendar events from sending yourself a Gmail on a mobile device using Google Apps Script.

How to Validate Specific Users on a Web App in Google Apps Scripts – Yagisanatode

Learn how to validate specific users on a Web App, Google Workspace sidebar or dialogue box with Google Apps Scripts. In this tutorial, we will explore how to validate selected users to provide access to your web app. For our example, we validate users based on whether or not they have edit access to a Google Drive file ( a common occurrence). In the discussion, we will also look at alternative ways of validating emails.

Interesting solution for access control to a Apps Script web app by using a Google Doc share permissions as a proxy. The post contains all the code you need and a discussion of other approaches.

Source: How to Validate Specific Users on a Web App in Google Apps Scripts – Yagisanatode

How to Create a Telegram Bot for Sending Notifications using Google Apps Script – Digital Inspiration

Learn how to create your own Telegram bot with Google Apps Script and post notification messages from Google Sheets, Forms and other Google apps.

We’ve a couple of Telegram bot posts already shared in Pulse. The latest come from Amit Agarwal who shares his insight, highlighting some particularly useful functionality and use cases. More details in the source link.

Source: How to Create a Telegram Bot for Sending Notifications using Google Apps Script – Digital Inspiration

Bypassing the Maximum Script Runtime in Google Apps Script

Image: Dmitry Kostyuk

Google Apps Script is an amazing language that can automate a lot of your work. However, working with GAS also means that you have to learn to live with its built-in limitations and quotas. One such quota is the total script runtime. … I have experienced that the time required to complete tasks like copying or even simply listing files on a drive or in a directory can be quite long. Merging hundreds or thousands of documents can also take longer than both thresholds. Now let’s look into how we can build a solution

Another community contribution looking at handling script runtime and this one is also worth looking into for tips on structuring your code. The tutorial includes lots of useful information and an explanation of what is going on and might be a useful example to look if you are interested in moving your Apps Script coding abilities to the next level.

Source: Bypassing the Maximum Script Runtime in Google Apps Script

List All Users in an Organisation’s Google Workspace Account with Google Apps Script – Yagisanatode

Note! This tutorial is for Google Workspace for organisations and not the free consumer account, unfortunately. While the Google Apps Script docs provide a great example of how to get a list of users in a Domain on a Google Workspace account, it is not in the scope of the documentation to go into the weeds and explain all the ways we can search for all users.

In this tutorial, we will cover how to access your Google Workspace organisation’s user data, what data you can retrieve and how it looks, who can retrieve it and a couple of ways to display what you need.

Scott ‘Yagi’ Donald gives another thorough post for Google Apps Script users. Whilst targeted at Google Workspace users this tutorial has some great explanations of common Apps Script patterns, such as pagination and query parameters, that are useful to know when interacting with other Google APIs.

Source: List All Users in an Organisation’s Google Workspace Account with Google Apps Script – Yagisanatode

Website Status Monitoring using Google Sheets [and Google Apps Script with optional notifications to Google Chat]

A Spreadsheet-bound apps script solution to conduct automated status monitoring on websites listed by the user in a Google Sheets management file. A separate status log file in Google Sheets will be created so that users can easily integrate data with BI services such as Google Data Studio. Notifications of changes in website status will be sent to the user’s Gmail. An optional setting to send notifications to Google Chat is available.

Nice example of using Google Chat for individual/group notification. The developer of this is also very responsive on Github if you encounter issues or have suggested changes.

Source: Website Status Monitoring using Google Sheets

Deliver asynchronous notifications in Google Chat using webhooks (Webhooks + Apps Script = Magic)

For community members receiving these timely updates, this “bot” may seem magical. In reality, it’s neither magic nor a traditional Chat bot, so the reference in the Chat UI calling it a “bot” is a bit of a misnomer. The Google Updates “bot” is in fact a simple Google Apps Script application that parses the RSS feed about new posts, and sends them asynchronously to the room via webhooks.

We’ve highlighted the ‘Wexbot’ before both in a Pulse post and in a Totally Unscripted episode, but nice to see it also feature in the official Google Developers blog.

Source: Deliver asynchronous notifications in Google Chat using webhooks

Get a list of Google Shared Drives by ID and Name in Google Apps Script – Yagisanatode

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.

Source: Get a list of Google Shared Drives by ID and Name in Google Apps Script – Yagisanatode

How to Find and Replace Text in Google Docs with RegEx Search Patterns – Digital Inspiration

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.

Source: How to Find and Replace Text in Google Docs with RegEx Search Patterns – Digital Inspiration

String validation for Google Apps Script projects

String validation for Google Apps Script projects

Sourabh Choraria has packaged some of the validator.js methods into a Google Apps Script library. Ported validators currently include isUrl and isEmail , date validators and more. Sourabh has also posted the backstory behind this library.

Source: GitHub – validatorgs/validator.gs: String validation for Google Apps Script projects.

Using npm modules inside of Google Apps Script

Boboss74, CC BY-SA 4.0, via Wikimedia Commons

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.

Source: Using npm modules inside of Apps Script

Import data from MS SQL Server to Google Sheets using Google Apps Script

Image created by Trang Nguyen Ngoc with the icons by Freepik, Pixel perfect and phatplus on Flaticon

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]

Source: Import data from MS SQL Server to Google Sheets using Google Apps Script

SQL for Apps Script – it’s here – Desktop Liberation

alaa kaddour, CC BY-SA 4.0, via Wikimedia Commons

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.

Source: SQL for Apps Script – it’s here – Desktop Liberation

Check/uncheck the entire checklist in Google Docs with Google Apps Script

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.

Source: Uncheck a checklist

Sheets Workbook functions in Apps Script – Date and Time and Temporal primer – Desktop Liberation

Timezones

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.

Source: Sheets Workbook functions in Apps Script – Date and Time and Temporal primer – Desktop Liberation

XPath Tester using Web Apps Created by Google Apps Script (working with existing Google Sheets functions in Apps Script)

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.

Source: XPath Tester using Web Apps Created by Google Apps Script

Creates a Google Workspace Add-on file picker card with CardService that opens a Google Picker in an overlay window – Google Apps Script – Yagisanatode

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.

Source: Creates a Google Workspace Add-on file picker card with CardService that opens a Google Picker in an overlay window – Google Apps Script – Yagisanatode

Accelerate Google Workspace Add-on and chat bot development with the Card Builder tool | Demo

Take a quick tour through the new Card Builder tool for Google Workspace Add-ons and see how it helps you quickly design and generate code for card-based interfaces.

We’ve mentioned the Card Builder tool in a couple of episodes of Totally Unscripted but if you’ve got come across it yet Steve Bazyl provides a quick overview. This tool can be used to help developing Workspace Add-ons that use the Card Service (https://gw-card-builder.web.app/) and also Google Chat bots (https://gw-card-builder.web.app/chat).

Make your own Office Desk Booking with Apps Script

In this new era post Covid, to come back to work we have to maintain a certain quota of people at the office. At Devoteam G Cloud we had this needs and to be able to manage people at office we build a web app with Apps Script to manage team and people presence on site.

A great example from Stéphane Giron using Google Apps Script to rapidly develop and deploy an office management solution. The post highlights the utility of PropertiesService to store data in the web app. This solution falls into the category of minimum viable product, but given the nature of Apps Script could be a great project to build on with integration with other Google services.

Source: Make your own Office Desk Booking with Apps Script

Random and fake test data in Sheets with Google Apps Script – Desktop Liberation

Image credit: Bruce Mcpherson

Image credit: Bruce Mcpherson

Sometimes you need to generate some fake data for a spreadsheet. In this post I’ll cover a few utilities in 2 separate libraries that can help with this.

Bruce Mcpherson has put together a Google Apps Script library which is a port of Faker.js and elements of d3.js to make it easier to generate random data in your projects. You can see the full list of Faker.js methods which includes a wide range from names/addresses to dates to just lorem text. The library can also generate various fake datasets, strings and selectors.

Source: Random and fake test data in Sheets with Google Apps Script – Desktop Liberation

Telegram Bot with Inline Keyboard in Google Sheets [and Google Apps Script]

Questa funzionalità permette di visualizzare, insieme al messaggio del bot, una tastiera virtuale con una serie di bottoni dove gli basterà cliccare su uno di essi per scegliere la sua risposta

[Google Translate: This feature allows you to view, together with the message of the bot, a virtual keyboard with a series of buttons where it will be enough to click on one of them to choose its response]

The accompanying video and post for this solution are in Italian and Google Translate might be required. This is a great resource and another example of a high quality video produced by the Apps Script community. The solution provides an overview of how you can display an inline keyboard within a Telegram Bot using a single Google Apps Script project.

Source: Telegram Bot with Inline Keyboard in Google Sheets

Apps Script – How to track library and script usage – Desktop Liberation

You’ve written a great Apps Script library and you want to know how many people are using it, and perhaps even which parts of the library they are using, and how often. Perhaps you have a new version out, and need to know whether anybody is still using the old version. You may also have a set of libraries and you want to know whether users have flipped to the new version. All these things are important to know, but impossible to find out using the Apps script platform as is.

Bruce Mcpherson provides a detailed solution for tracking Google Apps Script library usage using the Properties Service. The source post contains everything you need to integrate this solution into your existing libraries as well as a quick way to chart usage.

Source: Apps Script – How to track library and script usage – Desktop Liberation

Google Apps Script powered extensions, add-ons, and customizations for the Habitica self-improvement web app

Habitica is a self-improvement web application with game mechanics overlaid in order to help the player keep track of and remain motivated to achieve their goals.

The game is laid out in the form of an RPG, in which the player collects items such as gold and armor to become more powerful. Rewards are achieved through maintaining real-life goals, in the form of Habits, Dailies, and To-Dos.

Wikipedia

Can’t say that I’ve come across the self-improvement web app Habitica before, but good to know there is a long list of Google Apps Script powered extensions, add-ons, and customizations.

Source: Extensions, Add-Ons, and Customizations – Habitica Wiki

How to Use Notion with Gmail and Google Sheets using Apps Script – Digital Inspiration

How to use the Notion API with Google Apps Script to connect Gmail, Google Forms, and Google Sheets with your Notion workspace.

Notion, my absolute favorite tool for storing all sorts of things from web pages to code snippets to recipes, just got better. They’ve released a public API and thus it will be a lot easier for developers to read and write to their Notion workspace from external apps.

For note keeping fans of Notion Amit Agarwal shares some basic code to interact with the Notion API. Amit also has wrapped this into a new private beta Gmail Add-on that you can see in the post with a link to request early access.

Source: How to Use Notion with Gmail and Google Sheets using Apps Script – Digital Inspiration

Using UrlFetchApp.fetch vs. fetchAll in Google Apps Script

Make multiple, asynchronous API calls simultaneously using UrlFetchApp.fetchAll in Apps Script. mostly when running through a list of resources against which an API call needs to be triggered, we end up using the UrlFetchApp ‘s fetch service; however, in case if the list is well-defined and needs to be consumed as a whole, you could make use of the fetchall service instead.

In this post sourabh choraria highlights the benefits of UrlFetchApp.fetchAll() when making asynchronous API calls in Google Apps Script.

Source: using UrlFetchApp.fetch vs. fetchAll in google apps script

Subscribe to Apps Script Pulse...