AppsScriptPulse

Totally Unscripted – Automating your workflows with Document AI – 22 September at 1200 PDT / 1500 EDT / 2000 GMT+1

We are back with another episode of Totally Unscripted this time joined by Anu Srivastava, Senior Developer Programs Engineer, Google Cloud. As part of this show we are looking at Document AI, one of the latest Google services that lets you do magical things with scanned document images:

Document AI uses machine learning on a scalable cloud-based platform to help your organization efficiently scan, analyze, and understand documents. Manually inputting invoices, receipts, and expenses can be time-consuming, but it doesn’t have to be. The Procurement Document AI is a tool that allows you to digitize unstructured content into structured data. This is just one of the many scenarios where Document AI can be used to develop smart workflows. In this episode of Totally Unscripted we are joined by Anu Srivastava, Senior Developer Programs Engineer, Google Cloud, who will show how Document AI can be used to automate a range of workflows. This episode will be broadcast live and you can join the conversation in the YouTube Live chat.

We ‘air’ at 1200 PDT / 1500 EDT / 2000 GMT+1 on Wednesday, with the recording immediately available – https://youtu.be/_KeRNBcz_4w .

[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

Totally Unscripted – Wholly Sheets: Tips for building productivity solutions with Google Sheets 15 September at 1200 PDT / 1500 EDT / 2000 GMT+1

The Google Workspace developer community show, Totally Unscripted, is back this Wednesday! We’ll be broadcasting live at the usual time 1200 PDT / 1500 EDT / 2000 GMT+1 with Wednesday’s targeted as our new ‘day’.

For our return we’ll be bringing you an episode on ‘Wholly Sheets: Tips for building productivity solutions with Google Sheets’. Tune in live to ask questions or immediately watch the recording on demand.

Full show description below:

Spreadsheets are the swiss army knife of productivity tools where they are used (and sometimes misused) as databases, workflow engines, project management tools, custom calculators, and oh yeah… spreadsheets. In this episode of Totally Unscripted, we will chat with Jonathan Butler, Software Engineer at Viriya Consulting, and Spencer Farris, a Google Sheets & Apps Script Consultant, about how they are build spreadsheet solutions that range from small problem-solving automations to full blown critical applications.

Guests:

Track Customer Subscriptions with Google Spreadsheet & App Script by sending an Email Automatically

Do you still spend time every day monitoring your sales report, identifying which customer subscription is going to expire soon, and manually draft an email to send out to your customer? Would it be better if this manual task can be automated every day for you? If that’s the case, this article can be a great help for you!

Following on from some recent Google Sheet payment solutions here’s a tutorial that highlights how you can use Sheets and Apps Script as a payment reminder tool. Combine with Stripe or Razorpay and you have a complete subscription management solution.

Source: Track Customer Subscriptions with Google Spreadsheet & App Script by sending an Email Automatically

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

Create personal stock portfolio tracker with Google Sheets and Google Data Studio

Image credit: All Stacks Developer

The idea is to take advantage of what Google Sheets and Google Data Studio offer and pull them together to build a personal stock portfolio tracker.

This post provides an insight into how you can maximise the combination of Google Sheets, Data Studio and Apps Script to create powerful dashboards. The source link provides an overview of the solution and if you are interested in finding out more about how this solution works visit LION stock portfolio tracker guide (from there you can make a copy of the demo and see the code).

Source: Create personal stock portfolio tracker with Google Sheets and Google Data Studio

How to Create Automated Snapshots of your Google Sheet using Google Apps Script

In this video, we’ll show you how to automate snapshots of your Google Sheets and have those logs stored in a separate Google Drive folder so that you can keep your logs nicely organized for future reference. To do this, we’ll be using Google Apps Script to create a script that references the SpreadsheetApp library and the DriveApp library.

There seems to be an increasing number of YouTube channels dedicating content to highlight Google Apps Script solutions. Here’s the latest one we’ve come across from aguycalledjoe at bootstrapping.tools

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.

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 I manage password encryption for my last Google Workspace add-on onleetransfer | by Stéphane Giron | Aug, 2021 | Medium

Manage password encryption in Firebase Cloud Functions for Apps Script using bcrypt with an example of front-end integration.

Useful post from Stéphane Giron with an overview of how Firebase Cloud Functions can be used in combination with Google Apps Script to provide add-on functionality.

Source: How I manage password encryption for my last Google Workspace add-on onleetransfer | by Stéphane Giron | Aug, 2021 | Medium

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

Mergo – Mail Merge for Gmail – A free lifetime subscription for Google Workspace for Education users

As teachers gear up for the new school year, we’re glad to offer a free lifetime subscription to your school, college or university. 🥳

I’m sure many of you have come across Romain Vialard who has been contributing to the Google Apps Script community since the product was in beta.

Mergo, Mail Merge for Gmail, is one of the latest products to be developed by Romain and for Google Workspace for Education users the great news is that until the 20 September 2021 there is an offer for a free lifetime subscription! Follow the source link for more details.

Source: Mergo – Mail Merge for Gmail – Back to school

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

Visually code Google Apps Script with Teacher Blocks

This is a quick discussion of how teacher can respond to student work using block based coding.

This is a very interesting prototype which makes it easier for users to visually develop Google Apps Script code. Developed by Stephen Callahan as part of a Google Innovator Project, TeacherBlocks lets to drag and drop blocks to design your code and automatically create the required script to copy/paste into your Google Doc. Watch the video to see it in action or visit the source link to try it yourself.

Source: Teacher Blocks

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

Subscribe to Apps Script Pulse...