Workflow Automation
Increasing productivity one post at a time
Simplifying reports with a sales tracker in Google Sheets
today May 1, 2024
Successful sellers know how to increase demand for their products and services, and subsequently grow their sales. But aggregating daily sales into one unified sheet, and creating reports really need to be automated. After all, in the fast-paced world of business, staying on top of your sales data is crucial, but manual tracking can be time-consuming and prone to errors. That's where an automated sales tracker in Google Sheets comes in, offering a myriad of benefits for businesses of all sizes.
Transforming sales communication with a targeted auto-responder
today Apr 17, 2024
In the dynamic world of sales, effective communication is paramount. Sales professionals are tasked with building relationships, nurturing leads, and closing deals—all while managing a constant influx of emails.
However, the manual nature of email communication can often lead to inefficiencies, missed opportunities, and overwhelmed sales teams. And one-size-fits-all auto-responders fail to deliver personalized and targeted communication that helps customers feel appreciated.
Infinity scroll with Google Apps Script
today Sep 7, 2023
In this post, we will create a small Google Apps Script web app that receives data requests. The app will fetch rows from a Google Sheet. A client side web page will execute calls to the apps script for new data as the user scrolls down the page.
Create a Google Drive AddOn for Stability.ai
today May 18, 2023
Stability.ai offers a publicly-available service to create and edit images using artificial intelligence. The service is very intuitive to use and yields great results. It is similar to the Dall-E service from OpenAI, which I've previously written about here and here.
In this post, we will create a Google Drive AddOn that will interact with the Stability.AI API to create and edit images.
Build a Chrome extension for DeepL translator API
today May 9, 2023
DeepL Translator is a great service for translating content between different languages. The company offers a Chrome extension to use the service in the browser, but we can build our own extension. Let's see how!
Send Google Search Console data to ChatGPT
today April 30, 2023
Artificial Intelligence is getting into new business workflows by the day. One way we can increase work productivity, as well as the Search Engine Optimization (SEO) of our websites is by connecting AI with Google Search Console. Let's see how to pull search queries out of GSC and have OpenAI's ChatGPT create blog post titles for these queries – all using Google Apps Script.
Create an Add-to-Calendar widget with a form
today April 20, 2023
Are you familiar with the Add-to-Calendar widget that shows up in confirmation emails and web pages after a user registers to an event or a meeting? the widget enables the user to quickly add the event to their favorite calendar.
If you'd like to have the ability to quickly create such widgets for your own audiences, then you're in luck, because today I'll be sharing the code for an online form that you can use to generate these widgets.
Add a timer to a Google Form with Apps Script
today April 13, 2023
Adding a timer to a form is a great way to limit the time alloted to fill out and submit the form. If you run online quizzes, for instance, a timer mapped to a form can go a long way. Google Form doesn't allow us to embed a timer inside a form, but luckily we can use Google Apps Script to build a web app that has a timer and embeds a form.
In this tutorial we will build such a web app. In addition to the timer, the web app will allow the form user to pause the timer once, and then resume filling out the form (in case they need a bathroom break, for example).
But wait, there's more: our web app will also collect data on how users interact with the form: when they access it, pause it, resume it, run out of time, or submit the form.
Automate image edits and variations with Dall-E API and Google Apps Script
today April 4, 2023
In my previous post we saw how to use the OpenAI Dall-E API to convert text prompts to images programmatically, using Google Apps Script. In this post we will see how to use the API to manipulate existing images. We will edit areas of an image, as well as create whole variations of an image. Let's get going with Apps Script.
Use Dall-E and ChatGPT APIs with Google Apps Script to convert text to images automatically
today March 29, 2023
Since AI is all the rage now, I wanted to explore how to use the OpenAI Dall-E service to create images automatically. How about creating images for my blog posts? I could use the posts' summaries as prompts for ChatGPT, asking the service to produce a list of physical objects that could represent the contents of the summaries. Then I'll feed those objects to Dall-E, asking it to create an image that contains those objects. Once Dall-E provides the image URLs, I'll have the automation download the files to my Google Drive. Let's see how to do this with Google Apps Script.
Get more customer reviews with ChatGPT and Apps Script
today March 15, 2023
I have a reviews page on my site where I list client testimonials. I have a simple review submission form that clients populate with their review text, name, and email. When the form is submitted, I receive its information in a simple email message. I then copy/paste the information to my reviews page. Everything works fine except for one issue: often, clients don't know what to say in the review or how to structure their message. They want to help me but they are not sure what the review should say.
Luckily, ChatGPT can assist clients in drafting their personalized reviews. In this post, we'll see how to build a Google Apps Script-based system that accepts talking points from clients, converts them to a ChatGPT prompt, get a review draft from the AI, and enables the clients to edit and post their reviews. We'll get their review in an email message.
Build a Telegram chat bot for ChatGPT with Google Apps Script
today March 9, 2023
In my previous post, we saw how to use Google Apps Script to read real-estate property highlights from a Google Sheet, feed them to ChatGPT, and store the property summaries that ChatGPT wrote for us in Google Docs. Subsequently, I received a request on my YouTube channel to do a follow-up tutorial on how to build a Telegram chat bot that interacts with ChatGPT using Apps Script. Well, I think that it's a great idea, so let's see how to do that.
Use ChatGPT API with Google Apps Script
today March 1, 2023
ChatGPT! It's on everyone's mind these days. Luckily, the OpenAI company offers a public API for ChatGPT, which means that we can connect to ChatGPT from Google Apps Script. Hooray!
To understand how to use the API, let's imagine that we work in a real-estate agency, where our agents tour new properties, take notes about each property, and compose property overviews with the notes from their tours to be posted on the MLS. We can use the ChatGPT API to feed the agent notes and receive a compelling overview automatically.
Prevent CSS overwrites with iFrames
today February 27, 2023
In a previous post we saw how to aggregate email messages on a web page, so that we could read our newsletters on a single page, as opposed to fetching them from our overflowing inbox. One problem with injecting external HTML into your page is that the HTML can come with its own styling rules, and those rules can conflict with and overwrite other rules, whether in other HTML snippets or in your own page. This is happening because as the web browser renders the injected HTML, it processes any styling it encounters, and applies it – if relevant – to other snippets or to the containing page.
Push Airtable data to Google Docs
today February 21, 2023
Airtable is a great solution for storing data of all kinds, and many people are using it for different use cases. Let's learn how to push Airtable data to a Google Doc. We'll use a simple Airtable base with a single Products table that has three columns: Name, SKU, and Quantity. We want to push to Google Doc all of the out-of-stock products whose quantity equals zero.
Print labels with mail-merge using Apps Script
today February 18, 2023
Are people still printing labels these days? I guess they are. Well, we can help those poor souls with some good-old Apps Script magic. Let's build a system that lets the user select a label type. The script will create a template that corresponds with the user's selection. The user will then paste a set of placeholders inside the template, and our Apps Script will take some fake recipients' data and mail-merge it into the labels. Sounds good? Lovely!
Fetch YouTube subscribers and comments with Apps Script
today February 9, 2023
YouTube has a terrific user interface that provides channel managers and owners like you and me with valuable statistics about the performance of our channels and their videos. But YouTube also has rich APIs that enable us to pull the data out of the system to anywhere we to consume it. So lets build a Google Apps Script webapp that will respond to GET requests and send over subscriber and comment information.
Get the function's caller
today February 7, 2023
Let's say that you have a bunch of functions that do different things, but at the end, they all call the same function. Let's call that function "wrapUp." Each time wrapUp gets called, you want it to log the name of its caller. How can you do that?
Well, the easiest thing would be to have each of the calling functions pass strings containing their names to wrapUp and have the latter log those parameters. But what if you don't or can't have the functions pass such strings?
Aggregate newsletter emails with a Google Apps Script webapp
today February 6, 2023
I don't know about you, but I have a love-hate relationship with newsletter emails. On one hand, newsletters have terrific content: I get new, in-depth information directly from the source. Newsletters keep me informed about the subjects I care about the most.
But I really, really dislike emails. For starters, I'm a zero-inbox kind of guy: I don't like emails sitting in my inbox. As soon as an email comes in, I'd like to process it and get rid of it right there and then. But I'm also very busy which means that I usually don't have the time to read newsletter emails when they decide to pop in.
Create dynamic custom menus with Apps Script
today February 2, 2023
Remember the petition you and I wrote to the management team at our company regarding their recent decision to discontinue the work-from-home policy and have everyone back at the office? Well, I have bad news and good news: The bad news is that management would not relent: all employees must return to the office full-time. But the good news is that management has decided to institute a new performance-based bonus program!
Now, our payroll manager needs our help in automating parts of the bonus program. She has a Google sheet where all the employees are listed with their names, email addresses, and other data. The manager is asking us to create a custom menu in the spreadsheet that will list all the employees alphabetically. She wants to be able to choose an employee in the menu and have our automation kick-off a bonus payment for that employee.
Download web images to Google Drive automatically using Apps Script
today February 1, 2023
Your team keeps a large storage of high-quality images on some web server, but the guy who manages the server was caught in the latest round of layoffs, and now nobody knows how to access and get these images. Luckily, Apps Script can automatically download these images to Google Drive, so no need to fret.
Insert form submissions into Google Docs automatically
today January 28, 2023
All good things, as the saying goes, come to an end. After letting us work fully remote for past couple of years, management at our fledging startup has recently announced that it expected everyone back at the office five days a week. Goodbye work flexibility, and hello (again) insane commutes.
You, dear reader, and I – AKA The OGs on the company's Slack – are of course not going to put up with that. After all, our fellow employees are looking to us to take a stand, and, darn it, we will. We are going to write a petition in a Google Doc that we will share with management, letting them know exactly how we feel about their inane decision. But it's not just you and I to do the writing: we'll get the troops to contribute their thoughts as well. We'll collect comments from colleagues and embed them in the document using a simple Google Form with two questions: "Enter your comment" and "Enter your name."
Add a form to an email and save responses in a Google Sheet
today January 25, 2023
Let's pretend that you run a home-improvement store, with lots of products on the shelves. Every day or so, you pick a bunch of products and note them in a Google Sheet.
You'd like to have your loyal employee run an inventory check and note the quantity of each product. There's only one wrinkle: as loyal as the employee is, you don't want to give them access to the Google Sheet itself; who knows what they'd do with it?
How can we automate our way around this pickle?
Sync Gmail and Google Calendar recipients to Google Contacts - Part 2
today January 23, 2023
In the previous post we looked at how to populate a Google Sheet with names and email addresses from our Gmail and Google Calendar accounts. In this post we'll see how to push this information into Google Contacts using the Google People API.
Sync Gmail and Google Calendar recipients to Google Contacts
today January 19, 2023
I interact with a lot of people using Gmail messages and Google Calendar events. I'd like to collect the contact info of these individuals in Google Contacts as my de-facto CRM. Adding these records manually takes too much time: I'd like this done programmatically. Thing is, I don't want to mine every email and invite: some of these are transactional emails or one-off meetings. Instead, I'd like to have the contact info aggregated into a Google Sheet where I can visually inspect the rows, mark which ones to save and which ones to ignore.
This post will show you how to create such a system using Google Apps Script.
Improve user experience with custom menus in Google Sheets
today January 16, 2023
Custom menus in Google Sheets are a terrific way to let users kick off automation functions on their own terms. When we empower users to decide when and what automation to run, we improve the effectiveness of our automation code.
But custom menus have two issues when they're part of scripts that require access to sensitive or restricted scopes...
Handle column changes in Google Sheets with Apps Script
today January 12, 2023
A client of mine recently asked for my help in automating the data pulls they were doing. They wanted me to use the system's REST API to fetch new data and write it to a Google Sheet. The response payload of the API was pretty easy to navigate: essentially an array with a bunch of objects, where each object represented a user with certain keys for first and last names, phone, email -- that sort of thing. All I had to do was call the API periodically, get back new user objects, and them to a nested array so that I can append the data to Google Sheets.
Easy enough, but there was one wrinkle...
Exchange data between two Apps Scripts using a webapp
today January 10, 2023
We use Apps Script to increase our productivity when we use google sheets or google docs. But did you know that apps script can communicate with another apps script? We can exchange data between sheets that have their own scripts without having the user in the middle. One way to accomplish that is with the use of apps script web apps. A web app provides a url that users can access via a browser, but the url can also be used by another script.
Take your GAS to the next level with higher-order functions
today January 9, 2023
Imagine you have a Google Sheet with tabular sales data. The table contains sales volumes from four regions, represented in columns, and monthly sales volume represented in rows. Let's also say that you are developing a Google Apps Script that needs to do a few things, including summing up the sales in the East region.
How would you go about coding that? Well, the easiest thing to do is to pull the values out of the East column one by one and...
Increase coding productivity with Apps Script macros
today January 8, 2023
Google Apps Script is all about increasing work productivity through automation. But the road to automation involves a lot of mental and manual work. When we're developing scripts, we are often asked to make changes in Google Sheets or Google Drive items. Since we don't always get the scripts to make the right changes right away, we often have to undo the script changes manually, as we develop and enhance the script. And there's only so much we can do with the UNDO button in google sheets.
Let me show you how I use macros in Apps Script to cut down on the manual undoing, and make my work as a developer more efficient. I'll be covering four areas of changes...
Validate user files with Google Apps Script
today January 6, 2023
Let's say that you have a Google Apps Script that processes files that are generated by other people. Maybe it's your clients, or your team members, or your channel partners who provide the files – whatever the case may be, you build your script to operate on other people's files. In such a scenario I would assume that as a good developer, you would reach out to these people and agree with them on how they should populate and handle those files. Maybe you even give them a file template and ask them not to change its structure, but merely to duplicate it and fill out the information and then submit it to your script.
Writing a script under the assumption that people would always abide by agreed-upon rules is asking for trouble. Even if they mean well, people are still human, and humans can and will – for whatever reason – break the rules. And when they break the rules, they can break down your script. And tracking down the cause of the script error because of a malformed input file can be difficult and time consuming.
Display a sidebar inside Google Sheet
today January 4, 2023
A Google Sheet sidebar is an extra pane that you can open alongside your sheets. It lets you manipulate the contents of the sheets from a centralized place.
I'll review the sidebar in the context of mail merge, a solution that I covered in previous posts. Mail merge has an admin sheet where the user can select an email template, preview the campaign, and launch the campaign. In this post, I'll migrate this functionality to a sidebar.
Display HTML page inside Google Sheets
today January 1, 2023
Happy New Year! Here's to a calmer and saner year!
Displaying HTML pages inside a spreadsheet is quite common and useful when you do automation stuff. The HTML resides inside a popup that we can easily show when the user clicks on a button or selects from a custom menu. The HTML inside a popup supports CSS and Javascript, so you can offer really beautiful and useful HTML experiences inside a spreadsheet.
Send SMS Messages automatically from Google Sheets
today December 29, 2022
Sending SMS messages directly from a Google Sheet is both easy and useful. Let's assume that we have a Google Sheet that gets populated with phone numbers of people that we'd like to contact via an SMS message. In this post we will see how to implement a solution using Google Apps Script.
Using Google Docs text in Apps Script
today December 14, 2022
In the previous post, we looked at how to scan a Google Drive folder in order to collect the names of its files, and use them to populate a dropdown in Google Sheet. We will now see how to access the text inside these Google Docs in order to use them as email templates in our mail-merge solution.
Populate Google Sheet dropdown with names of Google Drive files
today December 10, 2022
In the previous post we looked at a rudimentary mail merge for Gmail using Apps Script. The script used a global object that housed the default subject line and body for our emails. We incorporated placeholders into the text that the script replaced with recipients' data. There are, however, a couple of issues with this approach. So, instead, we are going to store the email templates in a Google Docs. We will populate a dropdown with the names of the files, so that we can choose the email template with a single mouse click.
Create mail merge for Gmail using Apps Script
today December 9, 2022
In this post I offer a short and sweet Google Apps Script for creating mail merge in Gmail. The script is very basic, merely meant to introduce the topic. In future posts I'll add more features and capabilities.
Mail merge is essentially the ability to send multiple emails in one operation, yet personalize the email to each recipient. It saves you the time and energy to compose multiple emails to multiple recipients that say the same thing. With mail merge, you take an email template, and a list of recipients. You then replace placeholders in the template with the data of each recipients. Like I said, pretty simple stuff.
How to extract YouTube stats automatically and at scale
today November 1, 2022
There's often a need to extract statistics about YouTube channels; specifically, data about the channels themselves, as well as their videos and playlists. While it's possible to extract this data manually by visiting various YouTube pages and doing a lot of copying and pasting, this approach does not scale well.
Luckily, Google offers an API (application programming interface) to extract this very information. The Google Data API offers a consistent way for developers to pull data about YouTube channels, videos, and playlists. Developers can write programs or scripts that call the different endpoints (URLs) of the API to extract the information. Continue reading [...]
Deploying Stripe payments with Google Apps Script
todayOctober 26, 2022
This post details my experience in deploying Stripe payments using Google Apps Script (GAS) in three different projects. The Stripe platform is easy to use, and when it's combined with GAS, launching a payment system is straightforward. Continue reading [...]
How to duplicate Google Drive folders automatically
todayMarch 4, 2021
As a Google Workspace developer, I live in Google Drive all day long. It's often the case that I need to duplicate a complex structure of Drive folders and files.
Unfortunately, there is no easy way in Google Drive right now to duplicate folders quickly like you can do with a Google Sheet, for example. You have to copy stuff manually one by one, which is very annoying.
In this blog post I'll walk you though the process of creating a Google Apps Script web app that will automate the folder duplication process. Continue reading [...]
Filtering a nested array with another nested array
todayFebruary 26, 2021
I often write code that operates on Google Sheets, which means that I have to deal with nested arrays a lot. One of the things I have to do is to find rows in one array that don't have corresponding rows in another nested array.
For instance, I may have a Google Sheet with some user information, and another sheet with file information. Files are created for unique combinations of user departments and user roles. As more users are added to the users sheet, I need to find department-role combinations that don't already have a corresponding file. Continue reading [...]
Dynamic data validation in Google Sheets
todayFebruary 18, 2021
I have a Google Sheet with a column for company names. To ensure data quality, I prefer to select the company name in each row from a dropdown list so that the company is always spelled the same way. I could use the standard data validation in Google Sheet, but my list of companies contains over 35,000 names, and adding all of them into a data validation rule slows down Google Sheets considerably. Continue reading [...]
Recalculating custom functions in Google Sheets
todayAugust 2, 2020
I use Google Sheets to track my stock investments. Sure, there are plenty of online tools that can do this for you, but I use Sheets because I like to develop my own metrics and see if they help me make smarter buy/sell decisions in the long run.
Having custom metrics means developing custom functions in Google Apps Script and then applying them inside Google Sheet cells. Continue reading [...]
Using a single environment variables file for both Google cloud function and local dev
todayJuly 11, 2020
Whenever I need to write a cloud function for a Google Cloud Platform (GCP) project, I prefer to develop and test my code locally and deploy it as a cloud function only when it's done. It's often the case that I need to use environment variables to store important and sensitive information. For instance, if my code needs to access a Google Sheet via the Google Sheet API, then I prefer to store the sheet ID in an environment variable, instead of hard-coding it in the code. Continue reading [...]
Using the Google Sheets API in a Cloud Function on Google Cloud Platform
todayJuly 2, 2020
Cloud functions on Google Cloud Platform (GCP) are a great way to ship production code quickly and efficiently. I often use them to take advantage of the services that GCP offers, such as Google Sheets API.
This post details the process of using Google Cloud Platform (GCP) to deploy a cloud function that uses the Sheets API. I use the code included in this article as a template for all my GCP projects. This speeds up the process of creating new projects. Continue reading [...]