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

Using Google Apps Script as a Webhook

Google Apps Script projects can be published as web apps …. This is helpful if you’re building a tool with a simple UI (using HTMLService), but they can also be used as webhooks to do work within a user account.

Google provides the option of service accounts, so why use webhooks?

For my project, it was because I needed to send calendar invites to other people. Service accounts are generally the way to handle those kinds of jobs, but it needed to have full account delegation, which means it can act on behalf of any user in the domain. That wasn’t an option in this case, so a webhook became the next best option.

Brian Bennett highlights the benefits of using ContentService and Web Apps as a way to let you interface other services.

Source: Using Google Apps Script as a Webhook

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

Google Apps Script for Android Translation – Saravana Thiyagaraj

Normally we get strings.xml files from our translation team, but today was one of those days, where (due to time constraints) we got the source Google Sheet file. We had to generate strings.xml files out of these. This gave me the perfect opportunity to explore Google Apps Script which you could run on your Google Sheet.

This post highlights how Google Apps Script can quickly solve those little problems, in this case how to generate language .xml files used in Android. Follow the link to the source post for an explanation and code.

Source: Google Apps Script for Android Translation

Google Apps Script Basics – Variables and getting & setting values

Image: Baz Roberts

In this post, we’re going look at how variables are set up and how they can store various pieces of information. We’re also going to look at reading and writing data from a spreadsheet, which is one of the most common tasks when working with one.

We offer a wide range of community contributions here on AppsScriptPulse. This one might be useful if you are just learning to code and want to understand some of the fundamentals or if you’ve been using Apps Script for a while learn about variable types available in the V8 runtime.

Source: Apps Script Basics – Variables and getting & setting values

How to Request Payments with Razorpay and Google Sheets – Digital Inspiration

How to use Google Sheets to generate Razorpay payment links and easily accept payments from customers anywhere in the world!

Razorpay is a popular payment gateway in India that allows you to accept online payments from customers anywhere in the world. Your customers can pay with credit cards, debit cards, Google Pay, Walmart’s PhonePe and other UPI apps.

A great solution from Amit Agarwal for creating payment links in Google Sheets for Razorpay. The source post provides more details of how this works and setup required. The post also outlines how you can combine this with a mail merge to sort all your invoicing needs.

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

Using community connectors to go beyond filter by email in Data Studio – pablofelip.online

This article discusses row-level security in Google Data Studio and shows how community connectors can be used to overcome the limitations of the native feature when accessing data stored in Google Sheets.

I don’t think one of our summaries could ever do justice to this contribution from Pablo Felip. The post has a very thorough summary of row-level security and how Community Connectors coded in Apps Script can be used for additional levels of functionality.

Source: Using community connectors to go beyond filter by email in Data Studio – pablofelip.online

Google Apps Script Tutorial: Letterhead in Google Docs

In this Google Apps Script tutorial you will learn how to automatically create a letterhead in Google Docs.

This is a nice short video tutorial from Chanel Greco highlighting how you can manipulate a Google Doc header using Google Apps Script.

Get the redirect location of a URL using Google Apps Script

Have a list of shortened urls and need their final destination? Use this custom function in google sheets to extract them all at once.

Handy little snippet that can make testing redirects on URLs a lot easier, which can be useful for expanding short links from social media sites like Twitter.

Source: get the redirect location of a url using google apps script

How to Convert HTML to PDF with Google Script – Digital Inspiration

With Google Apps Script, you can easily convert any HTML content into a PDF file. The converted PDF file can be either saved to a folder in your Google Drive, you can email the file as an attachment or the use the UrlFetchApp service of Apps Script to post the PDF file to an external service like Amazon S3 or Dropbox.

Source: How to Convert HTML to PDF with Google Script – Digital Inspiration

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 Build Custom Functions in Google Sheets with Google Apps Script

Build your own functions in Google Sheets that do anything you need them to in a few lines of code.

This is a great tutorial if you are interested in finding out more about developing custom functions in Google Sheets. The post includes everything you need to get started from handling single cell and range inputs, to documenting your function so that users see inline help.

Source: How to Build Custom Functions in Google Sheets with Google Apps Script

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

Tracking Google Sheet Opens with Google Apps Script

The basic principle behind tracking Google Sheet opens is the same as the one behind email tracking with a transparent pixel. In a Google Sheet, we will use the IMAGE()function, which will call a deployed Google Apps Script web app URL with some parameters in the query string. The web app will be able to do whatever you need among the following: record to a spreadsheet, create a message via a messenger, or simply send an email.

Source: Tracking Google Sheet Opens with Google Apps Script

How to Automatically Archive Gmail Messages and update your Gmail signature with Google Apps Script

Chanel Greco at saperis has shared some useful video tutorials that should help beginner Google Apps Script developers with working with Gmail:

In this Google Apps Script tutorial we will learn how to automatically archive Gmail messages.

In this video tutorial you will learn how to create an email signature in Gmail.

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

An easy way to deal with Google Apps Script’s 6-minute limit

Image credit: Inclu Cat

Google Apps Script is handy, and it will help you a lot in your work. However, as you use it, you may hit a big wall. That is the six-minute limit on execution time. As the official documentation states, the maximum allowed time per execution of Google Apps Script is 6 minutes.

If the script execution time reaches 6 minutes, the script will stop suddenly, and an error message “Exceeded maximum execution time” will be displayed.

For as long as Google Apps Script has been around there have been various solutions published for handling the 6 minute execution limit. This post from Inclu Cat presents a nice overview and solution for the execution limit.

This is also an issue also recently covered by Amit Agarwal who takes a slightly different approach in Exceeded maximum execution time Exception in Google Apps Script, by breaking the execution before the limit is reached.

Source: An easy way to deal with Google Apps Script’s 6-minute limit

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

Sort by Random – How to Randomize the Order of Rows in Google Sheets – Digital Inspiration

Learn how to sort your data in a Google Sheet in randomized order using Excel formulas and Google Apps Script.

Nice little snippet from Amit Agarwal which includes some Google Apps Script code for randomising the order of rows in Google Sheets. The code has some clever use of .autoFillToNeighbor() , .offset() and .sort() .

Source: Sort by Random – How to Randomize the Order of Rows in Google Sheets – Digital Inspiration

A Google Apps Script powered Google Chat Updates Bot

Keep up to date with any feed by having new posts published to a Google Chat room using Apps Script and Webhooks. Feeds included in this example are various official Google blogs.

This is a nice solution developed by Justin Wexler which provides an easy solution which can monitor a list of blogs and post any updates to a Google Chat room. The source post provides more detail on setup and how to use the code with your own Google Chat rooms. The code also provides a useful pattern for monitoring updates from RSS feeds.

Source: jdw353/google-workspace-apps-script-toolbox

Using Google Apps Script to find/print inactive users in Google Workspace – xFanatical

In large organizations, it’s overwhelming for admins to find inactive users in the User List of Google Admin Console. These inactive users have not been logged in their Google accounts for a long period. It imposes a potential security risk to the organizational data if these accounts are not taken care of. A free apps script to print all inactive users in Google Workspace. The following open source Google Apps Script can automate the process of printing inactive users in your domain.

Nice little snippet from xFanatical. Check out the source link for all the code and instructions for using.

Source: Print inactive users in Google Workspace – xFanatical

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

Subscribe to Apps Script Pulse...