*You can access the classic feed here.
Setup Google Apps Script Word Completion in Sublime Text
When working on Google Apps Script projects locally with CLASP, it can be handy to have text completion for the Apps Script classes and methods. I’m currently shopping for a new IDE or text editor (the developer’s existential crisis) and have decided to pick up Sublime Text again after a 9-year hiatus after a mediocre … Continue reading "Setup Google Apps Script Word Completion in Sublime Text"...
CLASPALL – Update Multiple Apps Script Projects From a Single Source
CLASPALL is a command line app that runs with CLASP, allowing you to update multiple Google Apps Script projects from a single core project file. When developing for clients I typically have 3 separate projects: Development Testing Production CLASPALL allows me to update my Testing and Production projects from my Development project with a single … Continue reading "CLASPALL – Update Multiple Apps Script Projects From a Single Source"...
Extracting the Valid Workdays Between Two Dates in JavaScript
Given a start date, end date, weekends and, holidays we will extract the valid workdays between two dates in JavaScript in this tutorial. Sometimes we need to work backward from a date range and extract all the valid workdays within that range. Of course, we will need to exclude holidays and also days off for … Continue reading "Extracting the Valid Workdays Between Two Dates in JavaScript"...
Calculate the Workday End Date in JavaScript
Given a start date, number of days, and holidays, this script calculates the end date of a workday period using JavaScript. If you are looking to get a list of workdays in a range, then check out this tutorial: Extract Workdays From a Date Range in JavaScript...
Extracting Overlapping date ranges between 2 Date Ranges with JavaScript
I recently had a project where I had to extract the date range between two sets of overlapping date ranges in JavaScript*. Let’s say our user wanted to see any overlapping date ranges between "2024-09-01" and "2024-09-16". For the following set of date ranges: "29 August 2024" – "18 September 2024" "3 September 2024" – " 10 … Continue reading "Extracting Overlapping date ranges between 2 Date Ranges with JavaScript"...
Is a Year a Leap Year? Gregorian Calendar & JavaScript
Earth doesn’t perfectly orbit the Sun every 365 days. Rather, it irritatingly must make its orbit in oh, I don’t know, roughly 365.242190 days each year. Due to this inconsiderate behaviour, we must add a leap year into our common calendar known as the Gregorian Calendar. Like, I presume, many of us, I thought a … Continue reading "Is a Year a Leap Year? Gregorian Calendar & JavaScript"...
Get the Day of the Year in JavaScript
Sometimes it can be handy to find the day of the year* that a particular date falls on in JavaScript. Let’s say you want to get the day of the year for Pie Day (14 Mar 2024). Check out the code: *This tutorial is for the Gregorian Calendar...
Google Workspace Custom Dialog: ACCESS DENIED and similar errors
This guide is designed to help you quickly solve issues related to running scripts from custom-built dialogue boxes in Google Sheets, Docs, Forms and Slides, most notably, the “ACCESS DENIED” error and other related errors. This guide is for Google Workspace users and not necessarily for Google Workspace Developers. If you would like to learn … Continue reading "Google Workspace Custom Dialog: ACCESS DENIED and similar errors"...
GWAOw! 6 – Sign PDF with Jotform Sign
In this episode of GWAOw!!! we look at Sign PDF with Jotform Sign. About Sign PDF with Jotform Sign Sign PDF with Jotform Sign is a Google Workspace Add On (GWAO) that allows you to collect e-signatures for any PDFs stored in your Google Drive. This is such a useful tool that I have moved … Continue reading "GWAOw! 6 – Sign PDF with Jotform Sign"...
A Better Financial Year Formula In Google Sheets
The most common financial year formula for Google Sheets looks a little like this: =IF(MONTH(A3) < 7, YEAR(A3) - 1, YEAR(A3)) Where: =IF(MONTH(date) < financial_year_starting_month, YEAR(date) - 1, YEAR(date)) In Australia, our financial year starts on the 1 July. So that’s what I’ve used in the example...
Prevent consecutive clicks of Google Sheets Image Buttons in with Lock Service and Google Apps Script
Ever seen an overzealous user madly clicking on an image button in Google Sheets to run an automation script? It’s maddening, frustrating and most importantly, may cause errors in your beautifully crafted code. How Iplaysmash GIFfrom How Iplaysmash GIFs In this short tutorial, we explore how to use Google Apps Script’s Lock Service to prevent … Continue reading "Prevent consecutive clicks of Google Sheets Image Buttons in with Lock Service and Google Apps Script"...
Google Sheets FILTER function: Dates and Times
If you have landed on this page you may have discovered that dates and times don’t always work as you might expect with the Google Sheets FILTER function. In this tutorial, we are going to cover how to use FILTER on dates, times and date and time stamps (DTS). You can follow through the tutorial … Continue reading "Google Sheets FILTER function: Dates and Times"...
Preventing Cross-Site Request Forgery (CSRF) in Google Apps Script Dialogs and Sidebars
Cross-Site Request Forgery (CSRF) is a web security vulnerability that allows a nefarious entity to take actions on a website that are unintended by the user. Typically, this is done by tricking the user into using another website resembling the intended site and then submitting a form or clicking a button. The dodgy site then … Continue reading "Preventing Cross-Site Request Forgery (CSRF) in Google Apps Script Dialogs and Sidebars"...
Adding Charts to Google Workspace Add-on Sidebar Apps with Apps Script
Creating dynamic charts in the sidebar of your Google Workspace Add-on can be an effective approach to getting across a lot of meaning in a somewhat confined space. In this tutorial, we will use Google’s Chart API to generate a live chart, first from some static data and then live from an external data source … Continue reading "Adding Charts to Google Workspace Add-on Sidebar Apps with Apps Script"...
Add the Editor’s Email when they Tick the Check Box in Google Sheets with Apps Script
This tutorial is for Google Workspace Domain accounts. Recently, I was a guest interviewee at a Google Workspace Developer Summit in Singapore and an attendee asked me how they could automatically add the editor’s email to an adjacent cell when the user checks a check box in Google Sheets. This can be incredibly handy for … Continue reading "Add the Editor’s Email when they Tick the Check Box in Google Sheets with Apps Script"...
Creating Webhooks for Google Chat App with Apps Script
In this tutorial, we follow the adventures of Captain Webhook and his crew in their insatiable desire to report their latest booty conquest to me via Google Chat webhooks… That, dear readers, did not come out right. Webhooks are HTTP POST requests that are usually generated by a triggered event. The event could come from … Continue reading "Creating Webhooks for Google Chat App with Apps Script"...
Get the Difference Between Two Arrays in JavaScript
Sometimes we want to compare the difference between two arrays in JavaScript and return the remaining values in a new array. This can be useful for inventory or purchase management where we could compare the selected items against the available stock, for example. In this tutorial, we will cover two approaches to this: A single … Continue reading "Get the Difference Between Two Arrays in JavaScript"...
How do I reverse a range by Rows or Columns in Google Sheets
Google Sheets: SORT, INDEX, ROWS Sometimes you have a need to reverse a list quickly in Google Sheets. That’s pretty easy to do if the list is sorted alphabetically. Just go on into the <Data> menu and choose from one of the sort functions. But what if the data you want to flip is not … Continue reading "How do I reverse a range by Rows or Columns in Google Sheets"...
Add the Current Date to a Sheet When Data Is Added So That The Date Does Not Change(Static) – Google Sheets (Updated July 2023)
Google Sheets, Google Apps Script: onEdit You’ve probably come across the problem where you need to know when a piece of data has been added to your spreadsheet. You probably have been equally frustrated that there is no out-of-the-box function that will do just this. You’ve tried TODAY() and NOW(), but they change dynamically. What … Continue reading "Add the Current Date to a Sheet When Data Is Added So That The Date Does Not Change(Static) – Google Sheets (Updated July 2023)"...
List all files and folders in a selected folder’s directory tree in Google Drive: Apps Script
If you have ever tried to get a list of all the child files and folders of a parent folder in Google Drive, you’ve no doubt discovered that it is a slow old process. Iterating over each item in a folder and then reading the metadata of that file or folder before calling the next … Continue reading "List all files and folders in a selected folder’s directory tree in Google Drive: Apps Script"...
Creating an embedded interactive Chain Story app with Google Apps Script and Google Sheets
Google Apps Script: WebApp, HtmlService, LockService; Google Sheets In this tutorial, we are going to create an interactive story chain app that we can embed into a Google Site or your own site like WordPress. What’s a chain story, Yagi? Maybe you did this in school. Someone wrote the first part of a story. You … Continue reading "Creating an embedded interactive Chain Story app with Google Apps Script and Google Sheets"...
Creating Unique Ranges from 2D Arrays in Google Apps Script
Google Apps Script, Google Sheets, Javascript If you have ever done any work in Google Sheets you might have come across the incredibly useful UNIQUE function. This function can take a single row or multiple rows and generate a list of unique cell values. Sometime you might have a use case where you need to … Continue reading "Creating Unique Ranges from 2D Arrays in Google Apps Script"...
Add the User’s Signature Block to an Automated Gmail Email with Apps Script
Sending out emails as a part of a Google Workspace automated workflow is a very common task. In Google Apps Script we can send emails to users using the MailApp.sendEmail(), the GmailApp.sendEmail() method or even as a JSON payload with the Gmail Advanced API service. While one might expect that the sender’s signature block would … Continue reading "Add the User’s Signature Block to an Automated Gmail Email with Apps Script"...
Google Apps Script – How to Automatically Generate a Time From a Four Digit 24 hour Time in Google Sheets.
Google Apps Script, Google Sheets On a recent board post, a Google Sheets user wanted to change a four-digit number (for example, 1230) to a time, like 12:30, in the same cell that the item was entered. Unfortunately, the user was not in a position to change the starting values, so they were left with … Continue reading "Google Apps Script – How to Automatically Generate a Time From a Four Digit 24 hour Time in Google Sheets."...
How to Sort Tabs in Google Sheets with Google Apps Script
Sometimes your Google Sheet tabs can get out of hand. They can be mixed up and confusing to users. It’s often necessary to simply sort them in ascending or descending order. In this tutorial, we will use Google Apps Script to sort tabs in Google Sheets. We’ll also use a handy menu bar to quickly … Continue reading "How to Sort Tabs in Google Sheets with Google Apps Script"...
Find and Hide Rows in Google Sheets with Apps Script
Using the Spreadsheet App Class’ Text Finder Class to find and hide rows in Google Sheets containing target text in a cell can be a fast way to hide values. In many situations, this may be a faster way to hide rows based on cell value in Google Sheets with Google Apps Script than iterating … Continue reading "Find and Hide Rows in Google Sheets with Apps Script"...
Find and Select or Format rows in Google Sheets with Apps Script
In this tutorial, we create 3 Google Apps Script functions that are used to: Find and select the row containing the first or last value in a Google Sheets sheet tab. Find and select all rows containing the search value. Format all rows containing the search text. You can grab a copy of the starter … Continue reading "Find and Select or Format rows in Google Sheets with Apps Script"...
GWAOw! 5 – Insert Special Characters by Sheets Help
In this episode of GWAOw!!! we look at Insert Special Characters by Sheets Help. About Insert Special Characters for Google Sheets. As the name suggests, the Insert Special Character Add-on allows you to access the complete list of special characters like maths symbols, shapes and arrows, currency symbols, dingbat and Greek and Coptic symbols from … Continue reading "GWAOw! 5 – Insert Special Characters by Sheets Help"...
Performance of Google Apps Script Text Finder Class on 2 Approaches to Searching Large Datasets
Inspired by research into a recent blog post, the Google Apps Script Text Finder Class’ Find All (findAll()) and Find Next (findNext()) methods were benchmarked over two different datasets containing 50,000 rows. The first dataset contained 1,000 cells matching the search text. The second dataset contained 100 matching cells. For each dataset, a test was … Continue reading "Performance of Google Apps Script Text Finder Class on 2 Approaches to Searching Large Datasets"...
Find first, last & nth value in a Google Sheets range with Apps Script
In this tutorial, we will explore how to find the first and last values in a Google Sheets Range using Google Apps Script. Then we will modify our code to find a value at a specific location. We will then find the position of the first set of values in our Google Sheets range. Lastly, … Continue reading "Find first, last & nth value in a Google Sheets range with Apps Script"...
Find All Values in Google Sheets with Apps Script
Just like when using the Ctrl + F shortcut in Google Sheets to find values in your spreadsheet, there is a class in Google Apps Script that can do the same thing. This could be a useful tool as a part of an automation process. For example, finding the location of a value and applying formatting to … Continue reading "Find All Values in Google Sheets with Apps Script"...
How to Share a User on a Published Google Site with Google Apps Script
There are times when you might just want to share a user on a published Google Site as a part of a Google Apps Script Automation. Say you have an internal Google Site for a project and you want to share it with a member as a part of the onboarding process. Alternatively, you might … Continue reading "How to Share a User on a Published Google Site with Google Apps Script"...
Get the Creator’s Email of a Shared Drive with Google Apps Script
Google Drive’s Shared Drives are designed a little differently than regular Google Drive folders. Each Shared Drive file is collectively owned by the group working on the file or, more directly, the Google Workspace Domain account that manages the file. While you can search for shared drives and get some level of metadata (e.g. who … Continue reading "Get the Creator’s Email of a Shared Drive with Google Apps Script"...
Count the Occurrence of a Selection of a Cell in Google Sheets with Apps Script
A mate reached out to me last week asking if there was a way to monitor the times any article in a Google Sheet list goes through an editing stage before being published. From there, he wanted to report the number of edits on the article in an adjacent cell. Take a look at the … Continue reading "Count the Occurrence of a Selection of a Cell in Google Sheets with Apps Script"...
Sort and randomize data in Google Sheets with Apps Script
You might want to sort some Google Sheet data based on a condition in your Google Apps Script or simply sort by selected columns and rows after inserting a new row. If you are always building sample data for tutorials like me, you might also be interested in randomising your data programmatically. Fortunately, Google Apps … Continue reading "Sort and randomize data in Google Sheets with Apps Script"...
Add a Row of Data Below the Header in Google Sheets with Apps Script
Sometimes it is just more intuitive to add a row of data to a Google Sheet just below the header instead of at the bottom of the Sheet. One instance when this comes to mind is when we need to monitor time-dependent data as it is being input programmatically based on a trigger through Google … Continue reading "Add a Row of Data Below the Header in Google Sheets with Apps Script"...
Create a Dynamic Password Generator in Google Sheets
I need to create a lot of sample data for tutorials and courses. One of the things that I needed for a recent course I am building was to generate a column containing dummy passwords in Google Sheets. Each password needed to consist of letters, numbers and characters. Until recently, this task would have been … Continue reading "Create a Dynamic Password Generator in Google Sheets"...
5 ways to create an ordered alphanumeric list in Google Sheets
For whatever reason, sometimes we just need a list of alphanumeric letters and numbers like this “0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ” to run down a column or across a row, or just all packed into one string of text in a cell in our Google Sheet. I most commonly use an alphanumeric list to index data or use an … Continue reading "5 ways to create an ordered alphanumeric list in Google Sheets"...
The 5th-year Anniversary of Yagisanatode! An Origin Story.
It was a weird week. We had just started the second semester of a university course that I was instructing on and my operations director pulled me out of my first class for the semester within an hour and asked me to teach the advanced course. I lasted a day… The new gig The next … Continue reading "The 5th-year Anniversary of Yagisanatode! An Origin Story."...
Develop a Google Chat App Currency Converter with Google Apps Script
Have you ever wanted to convert currencies instantly while in Google Chat with colleagues and clients? In this tutorial, we are going to build a Currency Converter Google Chat App with Google Apps Script to do just that. This tutorial is a multi-media series containing step-by-step video instructions for each stage of the process along … Continue reading "Develop a Google Chat App Currency Converter with Google Apps Script"...
Google Chat Apps for Google Apps Script Developers (updated 2023-11-04)
In this article, we will look at Google Chat Apps and go over some of the basics of developing a chat app with Google Apps Script. Note that Chat Apps are currently only available to Google Workspace paid accounts and not standard ‘@gmail’ consumer accounts. What are Google Chat Apps? Google Chat is a business-level … Continue reading "Google Chat Apps for Google Apps Script Developers (updated 2023-11-04)"...
Get Hidden or Visible Google Sheet Tabs with Google Apps Script
Based on a viewer’s recent question, here is how you can get a list of all hidden or visible Google Sheet tabs with Google Apps Script. You can integrate these scripts into your own projects. The Video...
Create links to the first item of each group in Google Sheets
Imagine you have a Google Sheet tab containing a course worth of hundreds of students sorted into classes or groups. After each exam, quiz or assignment, your teacher for each class needs to enter in the grades. To make life easier for your teachers, we want to create a summary page where we can click … Continue reading "Create links to the first item of each group in Google Sheets"...
Duplicate Filter Views in Selected Google Sheet Tabs with Google Apps Script
While there is not way to directly duplicate Filter Views into other sheet tabs in Google Sheets we can do this with a little bit of Google Apps Script magic. In this tutorial, we will walk through how to duplicate all filter views from a source Google Sheets tab and duplicate them into selected sheets … Continue reading "Duplicate Filter Views in Selected Google Sheet Tabs with Google Apps Script"...
Adding Links to Images and Buttons in Google Sheets
Have you ever tried to add a link or URL to an image in Google Sheets only to discover it is deleted and replaced by just the URL? It’s a pain. In this tutorial, we will cover a few simple approaches to adding links to images in Google Sheets. First, we will cover an approach … Continue reading "Adding Links to Images and Buttons in Google Sheets"...
Creating Links from Custom Menus and Buttons in Google Sheets with Google Apps Script
This article should have been titled “Creating Links from Custom Menus and Buttons in Google Sheets with Google Apps Script: And Why it’s probably not a good idea”, but you know, I got to appease the SEO gods. There is no natural or “out-of-the-box” way to create hyperlinks for custom menu items and buttons in … Continue reading "Creating Links from Custom Menus and Buttons in Google Sheets with Google Apps Script"...
Find the item with the highest order of importance and store it with JavaScript
I recently came across (yet another) situation where I needed to work with a text-based ranking system. You know, when some gem decided to create a ranking system like good, great, awesome, spectacular and then we need to store each user’s highest level of achievement over a period. This occurs with surprising regularity in my work, … Continue reading "Find the item with the highest order of importance and store it with JavaScript"...
GWAOw! 4 – Forms History by Martin Hawksey
In this episode of GWAOw!!! we look at the Google Workspace add-on, Forms History by Martin Hawksey. About Forms History for Google Forms Unlike Google Docs and Google Sheets, there is no version history in Google Forms. Forms History is Martin’s solution to this problem. This free add-on allows you to review the version history … Continue reading "GWAOw! 4 – Forms History by Martin Hawksey"...
Using Hyperlinks in Dialogs and Sidebars to open a URL in a new Tab with Google Apps Script
If you’ve landed on this page you’re probably wondering why your hyperlinks are not working in your Google Workspace dialogue (dialog for my U.S. friends) box or sidebar. This affects all locations where you can build a sidebar or dialogue with Google Apps Scripts, Sheets, Docs, Slides and Forms. You might even hit F12 in your browser … Continue reading "Using Hyperlinks in Dialogs and Sidebars to open a URL in a new Tab with Google Apps Script"...
How to Easily Calculate Sales Tax (GST 🦘) in Google Sheets
Adding tax to the sales price or subtracting tax from a total price of an item in Google Sheets (GST) We have Goods and Services Tax (GST) here in Australia; it is a sales tax on all sorts of things. You might have something similar in your own country. If our business … Continue reading "How to Easily Calculate Sales Tax (GST 🦘) in Google Sheets"...
GWAOw! 3 – Crop Sheet by Eric Koleda
In this episode of Google Workspace Add On Walkthroughs (GWAOw!), we take a look at Crop Sheet by Eric Koleda. Crop Sheet allows you to quickly crop a Google Sheet tab by your own selection or by the data in your sheet tab. With over 1,000,000 installs this is a simple Google Workspace Add On that completes … Continue reading "GWAOw! 3 – Crop Sheet by Eric Koleda"...
Displaying Odd and Even Row Data in Google Sheets
Have you ever wanted to split a list of Google Sheet rows in two by odd or even? Or had a time when you wanted to display all items in a range by odd or even ids in two separate lists? You don’t have to do this manually, we can work smart and create a … Continue reading "Displaying Odd and Even Row Data in Google Sheets"...
GWAOw! 2 – ImportFromWeb by NoDataNoBusiness
In this episode of Google Workspace Add On Walkthroughs (GWAOw!), we take a look at ImportFromWeb by NoDataNoBusiness. ImportFromWeb is a powerful web scraping tool for Google Sheets that allows you to grab data from any website. The creators call it IMPORTXML on steroids. You can use ImportFromWeb as a Google Sheets function or use … Continue reading "GWAOw! 2 – ImportFromWeb by NoDataNoBusiness"...
One Approach to Encourage Users to Run Google Sheet-Bound Apps Script When They First Make a Copy of the File
I quite often get called upon by clients to create Google Sheet templates that have Google Apps Script Automations bound to them. Sometimes these Google Sheets require an automated setup process to run things like gathering initial data, setting up time triggers, approving scopes connected to onEdit() or onOpen() triggers or renaming connected forms and … Continue reading "One Approach to Encourage Users to Run Google Sheet-Bound Apps Script When They First Make a Copy of the File"...
GWAOw! 1 – Workbook Statistics by Sourabh Choraria
In this episode of Google Workspace Add On Walkthroughs (GWAOw!), we take a look at Workbook Statistics by Sourabh Choraria. Workbook Statistics is a FREE add-on that allows you to get intimate with all of your Google Sheets stats from your menu bar. You can grab stats on the number of rows, sheet names, pivot … Continue reading "GWAOw! 1 – Workbook Statistics by Sourabh Choraria"...
Filtering IMPORTRANGE data in Google Sheets
Working with IMPORTRANGE data in Google Sheets can be a little tricky. It may feel at times that it does not play by the same rules as when you are building formulas with data in the same Google Sheet. In this tutorial, we’ll go through two approaches to filtering and sorting your IMPORTRANGE data by … Continue reading "Filtering IMPORTRANGE data in Google Sheets"...
How to force subscribe a user in your domain to a Google Calendar with Google Apps Script.
If you have tried to seamlessly subscribe a user to a Google Calendar as part of an automation workflow in Google Apps Script and discovered that all that happens is that the user gets an automated email request to join, and then it is up to them to accept the calendar invitation to add it … Continue reading "How to force subscribe a user in your domain to a Google Calendar with Google Apps Script."...
How to remove a Google Sheets button (drawings) or images connected to a Google Apps Script after the script has been run
There have been a few instances in my work where I need to remove a button (more accurately, a button drawing) or and image from a Google Sheets tab once the associated script has been run. Perhaps we just want the user to run a process on a Google Sheet workbook, just once but not … Continue reading "How to remove a Google Sheets button (drawings) or images connected to a Google Apps Script after the script has been run"...
Copy and Paste Range Values from one Google Sheet into another with Google Apps Script
You can easily use Google Apps Script to copy a range of data from one Google Sheet to another Google Sheet, just like you would with using the IMPORTRANGE function in Google Sheets. However, there are some clear advantages to importing ranges with Google Apps Script. In this beginner-friendly tutorial, we’ll create an importRange() Google … Continue reading "Copy and Paste Range Values from one Google Sheet into another with Google Apps Script"...
Google Sheets IMPORTRANGE: Looking up data with the VLOOKUP function
Note! This is part of a series on using IMPORTRANGE in Google Sheets. If you don’t know how to use IMPORTRANGE or what it is, I encourage you to head back to the first tutorial. When I first sat down to write this tutorial, I had a specific opinion that one approach to using VLOOKUP on … Continue reading "Google Sheets IMPORTRANGE: Looking up data with the VLOOKUP function"...
Google Sheets IMPORTRANGE: Prevent clever editors from accessing other ranges of your imported sheet
Note! This is part of a series on using IMPORTRANGE in Google Sheets. If you don’t know how to use IMPORTRANGE or what it is, I encourage you to head back to the first tutorial here: https://yagisanatode.com/2022/01/26/importing-range-data-from-one-google-sheet-to-another/ And here is a link to the sample data that I am importing if you want to play along: … Continue reading "Google Sheets IMPORTRANGE: Prevent clever editors from accessing other ranges of your imported sheet"...
Importing Range Data From One Google Sheet to Another
One really amazing thing about Google Sheets is how easy it grab live data from one Google Sheet and import it into another. I’m not talking about a simple copy and paste job here. I am talking about real live data. Data that, when updated in the master sheet, will be reflected in the Google … Continue reading "Importing Range Data From One Google Sheet to Another"...
Create a ISO String from date text input intended for UTC date in JavaScript
Let’s say that you receive a date like “14/01/2022”, “14 January 2022”, “Jan, 14 2022” etc, and you need to convert this date to an ISO string in JavaScript while ensuring that the date that is inputted is for UTC (Universal Time Coordinated) timezone – no matter where you are in the world. It would seem … Continue reading "Create a ISO String from date text input intended for UTC date in JavaScript"...
How to create a time sequence in Google Sheets
Whether you are creating a Google Sheets data validation dropdown list of each minute in the day or want to create a daily progress log with 15-minute intervals, learning how to create a list of times in Google Sheets is a pretty solid skill to have. However, knowing how to create a list of times … Continue reading "How to create a time sequence in Google Sheets"...
Calculate the Total Duration of Time between two periods in Google Sheets
Calculating the duration of time between two times is a pretty common task. From measuring how long a process took, to entering the hours you put into a project, to creating a timesheet. You will find it everywhere, so working out duration is a hand skill to have in Google Sheets. However, there are some … Continue reading "Calculate the Total Duration of Time between two periods in Google Sheets"...
How to Automatically Share Teachable Students to Google Drive Files and Folders when they Enroll in your Course
Not only are Google Workspaces, Google Sheets, Docs, Forms and Slide great to work in and look awesome, but the convenience of collaborating and sharing your Google Drive Files and Folders is also super useful. So much so that many course creators share their documents with their students all the time. The problem that course … Continue reading "How to Automatically Share Teachable Students to Google Drive Files and Folders when they Enroll in your Course"...
Get 30% off my Google Sheets Mastery course this Black Friday Cyber Monday 2021.
I’m offering a whopping 30% off my Google Sheets Mastery course this Black Friday/ Cyber Monday this 2021. Learn Google Sheets as we walk through three real-life projects. Honestly, taking on real-life examples is the fastest way to learn a skill and understand it deeply. Now is the perfect time to upskill for the new … Continue reading "Get 30% off my Google Sheets Mastery course this Black Friday Cyber Monday 2021."...
Append List Items, Paragraphs and table cell items with a date-time stamp in Google Docs using Google Apps Script
Recently, I thought it would be a cool idea to add a date-time stamp to the end of a Google Doc checklist item with Google Apps Script. So I knew when I completed a task. I often share a project Google Doc with clients and then add my tasks to the document list. With Google’s … Continue reading "Append List Items, Paragraphs and table cell items with a date-time stamp in Google Docs using Google Apps Script"...
How to get the most frequently appearing words in a column in Google Sheets
I love it when a Google Sheets blog post generates more curiosity and further questions. Apart from the almost endless source of new material to write about, it is great to see peoples curiosity for a topic deepen. I wish I could reward you all for this curiosity every time, but sadly I seem to … Continue reading "How to get the most frequently appearing words in a column in Google Sheets"...
I hired a Google Apps Script developer and they delivered unreadable code. What’s going on?
A few days ago I got an update from a Google Apps Script group that I subscribe to. The poster shared a jumble unreadable single-line mess of code with a message that read something similar to: I hired a Google Apps Script developer and they shared me this. It works, but it is unreadable. What … Continue reading "I hired a Google Apps Script developer and they delivered unreadable code. What’s going on?"...
Why Object.create() doesn’t work in Google Apps Script and how to fix it.
If you have found this post while searching to try to figure out why your JavaScript Object.create() method is being a big stupid head isn’t working how you would expect it would in Google Apps Script, you have come to the right place. You’ve probably used Object.create() in a JavaScript project in the past to … Continue reading "Why Object.create() doesn’t work in Google Apps Script and how to fix it."...
How to Validate Specific Users on a Web App in Google Apps Scripts
You’ve created an awesome Google Apps Script web app for your secret society within your Google Workspace organisation or …dom! dom! DOM! … the world. The problem is that you only want to share your web app with the worthy. Those selected few. 🐐🛐🛐🛐 How do you do this? How to prevent this most coveted of … Continue reading "How to Validate Specific Users on a Web App in Google Apps Scripts"...
List All Users in an Organisation’s Google Workspace Account with Google Apps Script
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 … Continue reading "List All Users in an Organisation’s Google Workspace Account with Google Apps Script"...
Get a list of Google Shared Drives by ID and Name in Google Apps Script [updated 16 Dec 2022]
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 … Continue reading "Get a list of Google Shared Drives by ID and Name in Google Apps Script "...
Update a range of dropdown lists in a Google Sheet dynamically based on a corresponding dropdown choice (Updated 28 Mar 2022)
I don’t often do this*, but I recently got a question on my YouTube tutorial, Update dropdown list in Google Sheets dynamically based on previous dropdown choice: Data Validation, about whether or not this process can be applied to a column range. The short answer is yes. The long answer is that it is a … Continue reading "Update a range of dropdown lists in a Google Sheet dynamically based on a corresponding dropdown choice (Updated 28 Mar 2022)"...
Get a Unique List of Objects in an Array of Object in JavaScript
Recently, I needed a way to ensure that my JavaScript array of objects does not contain any duplicate objects based on an ‘id’ key. While I do enjoy more common approaches to this solution with the use of a simple ‘for’ loop it is always a bit of fun to see how folks have come … Continue reading "Get a Unique List of Objects in an Array of Object in JavaScript"...
Create a Google Workspace Add-on file picker card with CardService that opens a Google Picker in an overlay window – Google Apps Script
Google Apps Script: Card Service, Google Workspace Add-on, Google Picker 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. Sure, you could just open up another tab and find the link and paste … Continue reading "Create a Google Workspace Add-on file picker card with CardService that opens a Google Picker in an overlay window – Google Apps Script"...
How to find and replace text in a Google Doc with a link or a list of links with Google Apps Script
So you are a citizen Google Apps Script developer and you’ve decided to make yourself a mail-merge-type project where you want to create new documents from a template. You have discovered the simplicity of the replaceText() method: var body = DocumentApp.getActiveDocument().getBody(); body.replaceText("{{TEMPLATE TEXT}}", "My New Text"); Now you want to take it to the next … Continue reading "How to find and replace text in a Google Doc with a link or a list of links with Google Apps Script"...
Why your COUNT function might not be working in Google Sheets
It can be frustrating when our Google Sheets functions don’t go the way we expect them to. In this quick tutorial, we cover two common reasons why your COUNT function might not be running the way you expect it to. One beginner issue and one a little more advanced. Counting Characters When you count a … Continue reading "Why your COUNT function might not be working in Google Sheets"...
New course 🎉🎉🎉 Google Sheets: Learn the Essentials with Three Detailed Projects & 60% off 😃
It’s the big day! I’m very excited to introduce you to my new course: Google Sheets: Learn the Essentials with Three Detailed Projects Whether you are looking to skill up, make your work-life easier or impress your boss then this course will take you from Google Sheets beginner to producing corporate-level Google Sheets with three … Continue reading "New course 🎉🎉🎉 Google Sheets: Learn the Essentials with Three Detailed Projects & 60% off 😃"...
How to Create a Custom Email Auto-responder for a Google Forms Submission with Google Apps Script (Updated Feb 2022)
A few days before publishing this post, I put a call out for some Beta testers to get free access to my new course Google Sheet: Learn the Essentials through 3 Detailed Projects. Yeah, yeah, yeah, I am super excited about finishing this course. It’s been a labour of love for two and a half months. … Continue reading "How to Create a Custom Email Auto-responder for a Google Forms Submission with Google Apps Script (Updated Feb 2022)"...
Why can’t I edit this Google Sheet that was shared with me? 🤔
You’ve probably come to this page feeling pretty frustrated that you’ve received a link to a Google Sheet, Google Slide or Google Doc in an email or found a link to one of these documents in a website or even one of my tutorials, but you just can’t edit it. You can’t update the text … Continue reading "Why can’t I edit this Google Sheet that was shared with me? 🤔"...
Google Sheets Beginners: Getting Help (44)
One of the biggest revelations that I came across that really changed my whole view of learning was that learning how to ask the right questions or know where to get help completely transforms how you learn. Knowing how to get help quickly and efficiently in Google Sheets is really going to cut away a … Continue reading "Google Sheets Beginners: Getting Help (44)"...
Google Sheets Beginners: Understanding The Explore Tool (43)
The Google Sheets team seem to come out with more and more great tools to help us do more, help us think creatively about how we display our data and improve our efficiency. On the wave of machine learning, it seems that the Google Sheets devs are making our lives just that little bit easier … Continue reading "Google Sheets Beginners: Understanding The Explore Tool (43)"...
Google Sheets Beginners: Add-ons (42)
Sometimes you get to a point in your Google Sheets career where the vast array of tools that Google Sheets is not quite enough. Maybe you want to shortcut a process but don’t have time to learn or write Google Apps Script to do it. Well, the good news is that there is a huge … Continue reading "Google Sheets Beginners: Add-ons (42)"...
How to programmatically schedule weekly time triggers between two dates in Google Apps Script
Carelessly left behind Google Apps Script time triggers can be greedy little blighters that can ultimately end in a whole lot of noggin scratching when your scheduled scripts decide not to run all of a sudden. Then there is a whole lot of house cleaning to remove all those time triggers you couldn’t be bothered … Continue reading "How to programmatically schedule weekly time triggers between two dates in Google Apps Script"...
Help! My time triggers are not in sync! : How to update your Google Apps Script project time zone.
If you have made it to this post you are probably a little frustrated about why your Google Apps Script time triggers are not running on or around the times that you have designated. Of course, you could simply just be a well-oiled machine and want to know how to update the time zone of … Continue reading "Help! My time triggers are not in sync! : How to update your Google Apps Script project time zone."...
Google Sheets Beginners: Using Themes (41)
You don’t have to do all the hard work of creating all the formatting for your Google Sheet data, pivot tables and charts. Google Sheets actually comes with editable set themes that can quickly make your data look coordinated and stylish with a click of a few buttons. https://youtu.be/oBVnmY_DaQUVideo can’t be loaded because JavaScript is … Continue reading "Google Sheets Beginners: Using Themes (41)"...
Google Sheets Beginners: Charts (40)
A top data presentation is all about displaying your data in an easy to understand and memorable way. This is why being able to quickly create charts with your Google Sheets data is an essential tool. In this video tutorial, I’ll cover the basics of how to build a number of common chart types in … Continue reading "Google Sheets Beginners: Charts (40)"...
Get the Occurrence of a Value in a Range as a Percentage in Google Sheets (Updated Feb 2022)
Finding out how frequently something occurs compared to the rest of the data in a range is a pretty common task in Google Sheets. You will use it to find the frequency of grades in a grade book, the number of sales of an item compared to your total sales or the frequency of choices … Continue reading "Get the Occurrence of a Value in a Range as a Percentage in Google Sheets (Updated Feb 2022)"...
Google Sheets Beginners: Filter (38) & Filter View (39) (Updated 21 June 2022)
In these video tutorials, we will cover the Filter tool in Google sheets. Filter You can set up a specialised view of your Google Sheet data that will let you sort, or filter out unwanted data in any column with the Filter tool. This tool can pack a punch. You can sort different columns alphabetically, … Continue reading "Google Sheets Beginners: Filter (38) & Filter View (39) (Updated 21 June 2022)"...
Create custom prefilled Google Forms links in custom emails with Google Apps Script (Updated Feb 2022)
Recently I raised a support ticket with a tech company I was subscribed to where we were trying to resolve an integration issue I had with their service. Once we had it all resolved they followed up with a feedback form. That feedback form just happened to be a Google Form. Great, that’s cool. But … Continue reading "Create custom prefilled Google Forms links in custom emails with Google Apps Script (Updated Feb 2022)"...
Google Sheets Beginners: Grouping Rows and Columns (36)
When your Google Sheet spreadsheet starts to grow in complexity it is often a good idea to group similar parts of your data so you can easily collapse and expand them as you need them. Fortunately, Google Sheets has your back with the Grouping tool. In this tutorial, we will look at how to group … Continue reading "Google Sheets Beginners: Grouping Rows and Columns (36)"...
Google Sheets Beginners: Cutting, Copying and Pasting ( + bonus complete guide) (35)
Just when you thought that copy and paste was a no-brainer you start working in Google Sheets spreadsheets and find that there is a little more here than meets the eye. The good news is that Google Sheets has some great pasting types to help you copy or cut your data and put it in … Continue reading "Google Sheets Beginners: Cutting, Copying and Pasting ( + bonus complete guide) (35)"...
The Monster Guide to Data Validation in Google Sheets: Free Course (Updated May 2022)
In this tutorial, we will cover everything there is to know about Data Validation in Google Sheet. Why am I writing this tutorial? Well, there is a lot to cover for one, but I also find that there are a lot of snags and nuances to Data Validation in Google Sheets that make it easy … Continue reading "The Monster Guide to Data Validation in Google Sheets: Free Course (Updated May 2022)"...
Google Sheets Beginners: Data Validation (34)
Data validation helps you to control what your users enter into your cells. It also allows you to create dropdown menus for your users. In this tutorial, we will cover some basics on using Data Validation. Data validation can really help prevent your user from making errors and guide them towards good practice as well … Continue reading "Google Sheets Beginners: Data Validation (34)"...
Google Sheets Beginners: Split text to columns (33)
Have a list of names and need to separate the first and last name? Need to put a list of names in separate cells? Need to find a specific Sheet code in a website? Google Sheets has your back with Split text to columns. In this tutorial, we quickly and easily cover how to do … Continue reading "Google Sheets Beginners: Split text to columns (33)"...
Google Sheets Beginners: Accessing the Keyboard Shortcuts (32)
Learning keyboard shortcuts are a must to improve performance when using Google Sheets. Urban myth or not, I remember hearing a story about a manager in the financial industry who took away his new charge’s mouse and made sure the rest of the team didn’t give them one. This shocking bit of tough-love was to … Continue reading "Google Sheets Beginners: Accessing the Keyboard Shortcuts (32)"...
Automatic download link for non-Google Workspace files in Google Drive
Ever wanted your users to simply click a button on your webpage and,💥, a file you saved on Google Drive is automatically downloaded for them? Here have a look at two examples: My Pic! My Folder.zip NOTE! Clicking the links above will automatically download the file described. This quick tool allows you to do just … Continue reading "Automatic download link for non-Google Workspace files in Google Drive"...
Create Removable Item Buttons Generated From Select or Comma-separated Input elements with HTML, CSS and JS
I am currently working on a larger project at the moment that requires a lot of front-end wrangling. As a part of this project, I needed to create Button Items that are generated by the user from both an HTML select element for one section and, a comma-separated text input in another section. When a … Continue reading "Create Removable Item Buttons Generated From Select or Comma-separated Input elements with HTML, CSS and JS"...
Google Sheets Beginners: Editing Excel and ODS format files in Google Sheets (31)
You can now work on Excel and Open Document Format (ODS) file right in Google Sheets without converting the file to a Google Sheets file. This makes Googles Sheets a power collaboration tool. In this tutorial, I’ll cover, How to upload an Excel sheet to your Google Drive 00:16 Adding an Excel file from your … Continue reading "Google Sheets Beginners: Editing Excel and ODS format files in Google Sheets (31)"...
Google Sheets Beginners: You Can Create Custom Page Breaks for Printing and Exporting (30)
Yeap! That’s right. Google Sheets allows you to set custom page breaks for your Google Sheets tabs. So you can print exactly what you want on each page. What are custom page breaks? 00:00 How custom page breaks work 00:30 Turning custom page breaks off or on 00:58 How to move custom page break lines … Continue reading "Google Sheets Beginners: You Can Create Custom Page Breaks for Printing and Exporting (30)"...
Google Sheets Beginners: Printing your Google Sheet (29)
All the options available to you when printing your Google Sheet can be quite overwhelming. Find out everything you need to know about printing your Google Sheets documents, tabs and selected ranges. In this tutorial, I’ll cover, Ways to access the print button 00:05 Choosing your paper size and orientation 00:30 Using the scale function … Continue reading "Google Sheets Beginners: Printing your Google Sheet (29)"...
Google Sheets Beginners: Exporting your Google Sheet as different file types (28)
One of the benefits of using Google Sheets is that it can quickly be downloaded as different file types like Excel files (*.xlsx), Open Document Format **.ods), PDFs for reports or CSV or TSV for uploading onto other software systems. In this tutorial, I’ll cover, Why downloading in different formats can be useful. 00:15 How … Continue reading "Google Sheets Beginners: Exporting your Google Sheet as different file types (28)"...
2020 – Year in Review
It’s always nice to a have a bit of a look back at the previous year to see how you went, what you’ve achieved, what you can work on and where you want to go in the future. 2020 was a rough one for everyone and it was no different for this goat 🐐. Some … Continue reading "2020 – Year in Review"...
Create a custom automated Table of Contents for your Google Sheets tabs with Google Apps Script (Updated Feb 2022)
In this tutorial, we will cover creating a custom Table of Contents that lists your Google Sheets tabs on its own tab using Google Apps Script. This Table of Contents (TOC) will update whenever you open your sheet or choose to update it with a button. But Yagi! Can’t I just check the list of … Continue reading "Create a custom automated Table of Contents for your Google Sheets tabs with Google Apps Script (Updated Feb 2022)"...
Google Sheets Beginners: Trim Whitespace (27)
Trim Whitespace removes all spaces in a text string, leaving just a single space between words. Search not working? This may be why. This is a quick and easy tool that you can apply to all of your cells. https://youtu.be/RooWBx_-iLUVideo can’t be loaded because JavaScript is disabled: Google Sheets Beginners: Trim Whitespace (27) (https://youtu.be/RooWBx_-iLU) If … Continue reading "Google Sheets Beginners: Trim Whitespace (27)"...
Google Sheets Beginners: Workbook Edit Version History (26)
Google Sheets keeps a detailed version history of your workbook. You can use this version history to track changes and identify when, where and who made a change. You can make a copy of an old version or revert your current sheet back to an old version. Find out how to check your workbooks changes … Continue reading "Google Sheets Beginners: Workbook Edit Version History (26)"...
Google Sheets Beginners: Cell History (25)
Google Sheets Did you know you can peek in on the history or each one of your Google Sheets cells? via GIPHY Find out how in this short video tutorial: https://youtu.be/omrlbyiVMb4Video can’t be loaded because JavaScript is disabled: Google Sheets Beginners: Cell History (25) (https://youtu.be/omrlbyiVMb4) If you enjoy the video and want to learn more … Continue reading "Google Sheets Beginners: Cell History (25)"...
Use Google Sheets to store your Recipes to automatically change Batch Sizes and Recipe Amount by Weight
Your very own Google Sheets Recipe Template For my culture, the Christmas season has come. With it comes all the delightful homemade goodies and snacks you get for over the Christmas season. I do like to cook and over the years I have either inherited or discovered some great recipes. However, one of the problems … Continue reading "Use Google Sheets to store your Recipes to automatically change Batch Sizes and Recipe Amount by Weight"...
Google Sheets Beginners: Getting Email notifications on changes and form submits (24)
Google Sheets Google Sheets provides an email notification tool that allows you to get email notifications for changes to a Google Sheet or when a Google Form that is connected to your Google Sheet receives a submission. You can set your email notifications to be received when each change occurs or as a daily summary. … Continue reading "Google Sheets Beginners: Getting Email notifications on changes and form submits (24)"...
Google Sheets Beginners: Protecting cells, ranges and sheet tabs (23) [updated Feb 2022]
Google Sheets Have you ever had your editors edit something on your Google Sheet that they weren’t meant to? Find out how to protect cells, ranges and sheet tabs in Google Sheets. The main tutorial In this tutorial, I’ll cover, Why you may need to protect certain parts of your Google Sheet 00:02 Protecting a … Continue reading "Google Sheets Beginners: Protecting cells, ranges and sheet tabs (23) "...
Google Apps Script: Store a Unique User Key from a User Accessing your WebApp [updated Dec 2021]
Google Apps Script: WebApp In this tutorial, we will cover how you can get a unique temporary access key from a user accessing your WebApp that lasts for 30 days. Temporary access keys allow you to track users as they use your WebApp over time while … Continue reading "Google Apps Script: Store a Unique User Key from a User Accessing your WebApp "...
Google Sheets Beginners: Remove Duplicates from a Range (22)
Google Sheets If you are anything like me, you may find yourself regularly tidying up imported datasets or data from users that contain duplicates that could potentially corrupt your data analysis. Fortunately, Google Sheets has a quick tool to help you to remove duplicates with a simple click of the button. In this tutorial, I’ll … Continue reading "Google Sheets Beginners: Remove Duplicates from a Range (22)"...
Google Sheets Beginners: Linking (21)
Google Sheets Find out how to effectively link to websites, your own Google docs and within your very own Google Sheets. In this tutorial, I’ll cover, Types of linking in Google Sheets 00:00 Simple copy and paste approach 00:11 How the link is displayed and it’s properties 00:20 Add a link to an existing cell … Continue reading "Google Sheets Beginners: Linking (21)"...
Google Sheets Beginners: Sharing your Sheet (20)
The ability to share and co-edit documents live is a powerful tool for organisations. In this tutorial, we will dive in deep on sharing a Google Sheet. In this tutorial, I’ll cover, What is sharing? Who can you share your Google Sheet with and what permissions can you give them? 00:00 How to share your … Continue reading "Google Sheets Beginners: Sharing your Sheet (20)"...
Google Sheets Beginners: Communicating with Comments (19)
Google Sheets One of the beauties of Google Sheets is collaboration. In this tutorial, we will cover how to insert comments in Google Sheets so that you can target cells and ranges for discussion with comments. In this tutorial, I’ll cover, Uses of comments in Google Sheets. 00:16 Who can access comments (Permissions)? 01:08 How … Continue reading "Google Sheets Beginners: Communicating with Comments (19)"...
Google Sheets Beginners – Alternating Colours (17)
Google Sheets Find out how to add alternating colours in your rows in Google Sheets. Adding alternating colours in your spreadsheets can really help your viewers to reference data accurately while also allow your spreadsheet to look quite smart and professional. Google Sheets has a quick and easy alternating colours tool to help you to … Continue reading "Google Sheets Beginners – Alternating Colours (17)"...
Google Sheets Beginners – Inserting Images (18)
Google Sheets Find out how to insert images into your Google Sheets. In this tutorial, we will cover the different ways that you can insert images into Google Sheet. Adding images can be useful for styling up your Google Sheet or providing a visual explanation of some of your data. You can also add images … Continue reading "Google Sheets Beginners – Inserting Images (18)"...
Google Sheets Beginners – Change Your Current Workbook’s Timezone Date & Currency (16)
Google Sheets As we increasingly start to work from home and work with clients globally, it is vital that we prepare our Google Sheet for the country that our users and clients are from. In this tutorial, we will cover how to quickly change your current Google Sheet’s timezone, date and currency for the entire … Continue reading "Google Sheets Beginners – Change Your Current Workbook’s Timezone Date & Currency (16)"...
Google Apps Script – Javascript: Emulate the “Proper” Google Sheets Function
Google Apps Script / Javascript I just had a recent email from a reader who asked how to tidy up a user’s inputted name from say, a Google Form so that all the first letters of each work in the name are capitalised in the same way that the Google Sheets Proper function does. I … Continue reading "Google Apps Script – Javascript: Emulate the “Proper” Google Sheets Function"...
Google Sheets Beginners – Spell Check(15)
Google Sheets Did you know that you can use spell check in Google Sheets? No? Not many people do. Cleaning up your spelling in your Google Sheets can really give your work an edge and save you from any embarrassing moments when you using your data to present. While Google Sheets spreadsheets is quite primitive … Continue reading "Google Sheets Beginners – Spell Check(15)"...
Google Sheets Beginners – Creating Sheet Tabs (14)
Google Sheets Sometimes just one Google Sheets tab is not enough. Maybe you want to tidy up your data, create unique insights in a separate tab or generate a spectacular graphic dashboard or all your daw data, knowing how to create a Google Sheets tab is going to be a vital part of your success … Continue reading "Google Sheets Beginners – Creating Sheet Tabs (14)"...
Google Apps Script: How to create a basic interactive interface with Web Apps
Google Apps Script: Web Apps Did you know that you can easily create an interactive webpage web app that you can embed in your own website or your Google Workspace domain for internal use with your organisation with Google Apps Script? Perhaps you want to create a small online app using resources you have in … Continue reading "Google Apps Script: How to create a basic interactive interface with Web Apps"...
Google Sheets Beginners – Hide and unhide columns and rows(13)
Google Sheets Sometimes it is helpful to hide and unhide columns in Google Sheets. You may have some completed tasks that you want to indicate as completed to your users or you may want to hide columns that you have used for working. Whatever, the case hiding rows in columns is a helpful trick to … Continue reading "Google Sheets Beginners – Hide and unhide columns and rows(13)"...
Google Sheets Beginners: Insert Columns and Rows (12)
Google Sheets: Insert and Shift Columns and Row Even if you are a veteran Google Sheets users you might find a great little tip in this tutorial. Here we will cover how to insert columns and rows in Google Sheets spreadsheets. Oh, and how to cheat at connect 4. In this tutorial, I’ll cover, Inserting … Continue reading "Google Sheets Beginners: Insert Columns and Rows (12)"...
How to compare current week’s data with previous week’s data in Google Sheets
Google Sheets: WEEKNUM, WEEKDAY, FILTER, VLOOKUP, TODAY One really helpful metric to check performance is to compare the current weeks worth of data against the previous weeks. I have had need of this in all sorts of projects such as: Comparing sales performance from previous weeks to current. Comparing hours worked on different tasks from … Continue reading "How to compare current week’s data with previous week’s data in Google Sheets"...
Google Sheets Beginners: Freeze Rows and Columns(11)
Google Sheets: Freeze Have you ever wanted to lock those pesky rows of headers or columns so that when you scroll the titles are still there? In Google Sheets, this is done easily. You can either got to View > Freeze and select your preferred depth of freeze. Or you can do my favourite approach, click and … Continue reading "Google Sheets Beginners: Freeze Rows and Columns(11)"...
Google Sheets Beginners: Paint Format (10)
Google Sheets: Paint Format Want to know a quick way to update a cell or range with an already existing format in your Google Sheets? Paint Format is here to the rescue. This versatile tool will help you to quickly replicate font, alignment, border, rotation and other styling formats in your Google Sheet. In this … Continue reading "Google Sheets Beginners: Paint Format (10)"...
Google Apps Script: How to Add and Remove Editors to a Google Sheet based on Sheet Data (Updated Feb 2022)
Google Apps Script, Google Sheets, Javascript Have you ever wanted to have a Google Sheet available for only those users who need to edit and then once they are done, take their edit permissions away to maintain the integrity of the sheet? I know that I have come across these conditions a number of times … Continue reading "Google Apps Script: How to Add and Remove Editors to a Google Sheet based on Sheet Data (Updated Feb 2022)"...
Google Apps Script: How to Add and Remove Editors to A Google Sheet with Code (Updated Feb 2022)
Google Apps Script: SpreadsheetApp, addEditor/s, removeEditor/s, alert. Javascript: try…catch, forEach, join, push, template literal. Google Sheets In this tutorial, we will go over the basics of adding users as Editors to Google Sheets with Google Apps Script. We’ll go through the process step-by-step, starting with two very basic codes and then progress on to error … Continue reading "Google Apps Script: How to Add and Remove Editors to A Google Sheet with Code (Updated Feb 2022)"...
Google Apps Script: 3 Google Sheets Custom Functions to Allocate Items from a Column and Spread Them Out into a New Column N Times (Updated Feb 2022)
Google Apps Script, Google Sheets, Javascript: Spread operator, fill, new Array, forEach, isArray, flat, every, slice, length, typeOf Imagine if you have a set of data, say, Cat, Dog, Goat and you want to create a new column where each item is repeated 3 times. Essentially what we are doing here is spreading out our original … Continue reading "Google Apps Script: 3 Google Sheets Custom Functions to Allocate Items from a Column and Spread Them Out into a New Column N Times (Updated Feb 2022)"...
Running Google Apps Script for the First Time: What’s with all the Warnings!
Google Apps Script You’ve probably landed on this page because, like most of us, the first time we ran a Google Apps Script we came across a gauntlet of ever-increasing warnings asking us if we truly want to run this script. That’s cool. This is just Google trying to protect users. Unfortunately, it does make … Continue reading "Running Google Apps Script for the First Time: What’s with all the Warnings!"...
Google Apps Script: Automated PDF Certificated Creator That Is Emailed to the Attendee (Updated Feb 2022)
Google Apps Script: DriveApp, PropertiesService, SpreadsheetApp, GmailApp, SlidesApp. Google Slides, Google Sheets. Have you ever wondered how people create certificates en mass and send them out automatically? You are in the right place. In this tutorial, we are going to: Create Certificates of Attendance for multiple attendees using Google Slides and a list of attendees … Continue reading "Google Apps Script: Automated PDF Certificated Creator That Is Emailed to the Attendee (Updated Feb 2022)"...
Google Sheets Beginners: Conditional Formatting (09)
Google Sheets: Conditional Formatting Conditional formatting is a powerful tool that helps you to highlight cells that meet a certain value. You can format cells, that contains: date ranges Number ranges or specific numbers Exact text or partial text Once you have identified your condition you can change the cell’s background colour, font-weight or font … Continue reading "Google Sheets Beginners: Conditional Formatting (09)"...
Create a Student Certificate of Attendance with Google Slides and Export it as a PDF or Print it
Google Slides – Beginners I always enjoy finding new ways to create documents using the Google Suite. One project I have recently worked on has been to build a Certificate of Attendance template that I can update for non-grades courses or conferences. Why do this using a GSuite package? Well, 1, if you haven’t noticed, … Continue reading "Create a Student Certificate of Attendance with Google Slides and Export it as a PDF or Print it"...
Google Sheets Beginners: Borders (07 & 08)
In this next part of our Google Sheets Beginners Youtube course, we take a look at borders. Borders can provide a huge boost to the readability of both your digital Google Sheet and your printed spreadsheets. Not only that, but careful use of border styles, weight and colour can help guide your reader to the … Continue reading "Google Sheets Beginners: Borders (07 & 08)"...
Google Sheets Beginners: Backgrounds (06)
New to Google Sheets and spreadsheets in general? Find out everything you need to know about inserting a background cell colour efficiently. Changing cell colours in Google Sheets can really make your spreadsheets pop! In this tutorial, I’ll cover, 1. How to insert a background into a single cell. 2. Inserting a background into a … Continue reading "Google Sheets Beginners: Backgrounds (06)"...
Google Sheets Beginners: Merging Text (5)
Google Sheets: Merging Text New to Google Sheets and spreadsheets in general? Find out everything you need to know about merging cells inside a cell efficiently. Merging cells can really increase your design game. In this tutorial, I’ll cover, 1. Merging Across rows. 2. Merging Across columns. 3. Merging mixed ranges. 4. Highlight some things … Continue reading "Google Sheets Beginners: Merging Text (5)"...
Google Sheets Beginners: Formatting Text Inside Cell (4)
Google Sheets: Text Formatting In this tutorial, I am going to show you some tricks on how to format text inside cells that most spreadsheet users have no idea about. If you are new to Google Sheets or just spreadsheets in general, then this is for you. In this tutorial, I’ll cover: In this tutorial, … Continue reading "Google Sheets Beginners: Formatting Text Inside Cell (4)"...
Google Sheets Shorts: Text Formatting (3)
Google Sheets: Text Formatting If you a new to Google Sheets or spreadsheets in general, this short video is for you! We’ll go formatting text in Google Sheet. Including: Shorts cuts to access bold, italic, underline and strikethrough. Changing the font type. Changing the font colour. Changing the size of the font. Then I’ll wrap … Continue reading "Google Sheets Shorts: Text Formatting (3)"...
Google Apps Script: Create multiple versions of a document based on Google Sheet Data and a Google Doc Template (Mail Merge)
Google Apps Script: SpreasheetApp, DocumentApp, DriveApp; Google Sheets, Google Docs If you have ever worked in LibreOffice or Microsoft Excel you will probably be familiar with the mail merge. Traditionally, mail merge is used to create multiple versions of a document and snail-mail them to someone. These days, we don’t often use the snail mail … Continue reading "Google Apps Script: Create multiple versions of a document based on Google Sheet Data and a Google Doc Template (Mail Merge)"...
Google Sheets Shorts: Align and Wrap Text (2)
Google Sheets: Align and Wrap Text If you a new to Google Sheets or spreadsheets in general, this short video is for you! We’ll go over aligning text in cells vertically and horizontally. Then we’ll show you how to wrap long pieces of text so that they fit neatly in the cell. Lastly, we will … Continue reading "Google Sheets Shorts: Align and Wrap Text (2)"...
Google Sheets Shorts: Sorting Data (1)
Google Sheets: sorting data. In this Google Sheet tutorial, we’ll go through the nuances of using sort. You’ll find out how to sort by the sheet tab and by a selected range. Then, we will show you how sort works with text numbers and a mix of the two before diving into how to sort … Continue reading "Google Sheets Shorts: Sorting Data (1)"...
Google Sheets Shorts: Change the Column and Row Size (0)
Google Sheets: Columns and Rows If you are new to Google Sheets or spreadsheets in general. This is the post for you. In this tutorial, we walk through 3 ways to change the size of Columns and Rows. We also throw in some tips on change rows and columns in bulk to increase your workflow, … Continue reading "Google Sheets Shorts: Change the Column and Row Size (0)"...
Google Apps Script: Extract Specific Data From a PDF and insert it into a Google Sheet (Updated Feb 2022)
Google Apps Script: DriveApp, Advanced Drive Service, SpreadsheetApp, DocumentApp Javascript: spread operator, map, regex Ah, bureaucracy at its finest. Have you ever asked for a list from a client, another department or agency and instead of saving into a file that might even be considered marginally useful, they give it to you as a pdf. … Continue reading "Google Apps Script: Extract Specific Data From a PDF and insert it into a Google Sheet (Updated Feb 2022)"...
Count the Number of Selected Days of the Week in Google Sheets
Google Sheets: DAYS, NETWORKDAYS.INTL, NETWORKDAYS, VALUE A very common spreadsheet task is to get the total count of the days between two dates. We might need this information to: Find the number of days absent of staff or students. Find the total days worked or attending a class. Some times we need to avoid including … Continue reading "Count the Number of Selected Days of the Week in Google Sheets"...
Google Apps Script: Upload grades into a Google Classroom Coursework Assignment (Updated Feb 2022)
Google Classroom, Google Sheets, Google Apps Script: Classroom API, SpreadsheetApp, map, forEach At the date of writing this the world is in the midst of a worldwide pandemic – the Corona Virus. For educators and their Igors administrators like me, it has been an incredibly busy couple of months. Many of us have quickly sort … Continue reading "Google Apps Script: Upload grades into a Google Classroom Coursework Assignment (Updated Feb 2022)"...
Google Apps Script: Get Google Doc Text Based on Reference Characters
Google Apps Script: Google Docs, Document App. Javascript: Spread syntax, Set, IndexOf, Substring So here is the scenario, imagine you have a big Google Doc. You want to get a list of information from the document that you have noticed are between two sets of characters. Maybe something like this: You want to grab all … Continue reading "Google Apps Script: Get Google Doc Text Based on Reference Characters"...
Google Sheets: Get the Number of Words or Sentences in a Cell (Paragraph)
Google Sheets: ROUND, SPLIT, COUNTA Have you ever woken up in the middle of the night thinking – phawh! I’d really like to know how many words there are in a paragraph – Or stopped mid-chew on some arbitrary item carelessly left in the paddock by the boss and thought – geez! If there was … Continue reading "Google Sheets: Get the Number of Words or Sentences in a Cell (Paragraph)"...
Google Apps Script: Add and removed Google Sheets columns based on a search array
Google Apps Script: V8 engine, map, filter, reduce, includes, 2d arrays, matrix Have you ever wanted to delete or add columns in a Google Sheet, based on another set of Sheet data? I know I have. There have been a number of instances where I wanted to insert new columns or removed unused columns in … Continue reading "Google Apps Script: Add and removed Google Sheets columns based on a search array"...
Google Sheets: How to use OR inside a FILTER
Google sheets: FILTER, booleans, NOT, ISBLANK Have you ever had a set of data in Google Sheets where you want to use FILTER to grab rows based on if a condition in one column is met or if a condition is another column is met? Maybe you got frustrated that the OR function did not … Continue reading "Google Sheets: How to use OR inside a FILTER"...
Google Apps Script Course – Part 4: 2D Array Data Transformation of Multiple Question Multiple Group Items Data to Total Count of Rating Choices in Google Sheets
Google Apps Script, Google Sheets, SpreadsheetApp, 2d arrays <<Part 3 <<Intro>> In Part 3 of our 2D array data transformation course in Google Apps Script, we worked out how to get the count of each choice … Continue reading "Google Apps Script Course – Part 4: 2D Array Data Transformation of Multiple Question Multiple Group Items Data to Total Count of Rating Choices in Google Sheets"...
Google Apps Script Course – Part 3: 2D Array Data Transformation of Multiple Question Items Data to Total Count of Rating Choices in Google Sheets
Google Apps Script, Google Sheets, SpreadsheetApp, 2d arrays <<Part 2 <<Intro>> In our last tutorial, we grabbed the Google Sheets count data from a single rating survey question. We then displayed it two ways; horizontally with … Continue reading "Google Apps Script Course – Part 3: 2D Array Data Transformation of Multiple Question Items Data to Total Count of Rating Choices in Google Sheets"...
Google Apps Script Course – Part 2: 2D Array Data Transformation of Single Question Item Data to Total Count of Rating Choices in Google Sheets
Google Apps Script, Google Sheets, SpreadsheetApp, 2d arrays <<Part 1 <<Intro>> In our previous tutorial, we created a 2d array of count values for each item chosen in a survey form in Google Apps Script. In … Continue reading "Google Apps Script Course – Part 2: 2D Array Data Transformation of Single Question Item Data to Total Count of Rating Choices in Google Sheets"...
Google Apps Script Course – Part 1: 2D Array Data Transformation of Single Question Item Data to Total Count of Choices in Google Sheets (Updated Feb 2022)
Google Apps Script, Google Sheets, SpreadsheetApp, 2d arrays << back to Introduction Part 2 >> Hey, you made … Continue reading "Google Apps Script Course – Part 1: 2D Array Data Transformation of Single Question Item Data to Total Count of Choices in Google Sheets (Updated Feb 2022)"...
Google Apps Script Course – Intro: 2D Array Data Transformation for Google Sheets
Google Apps Script, Google Sheets, SpreadsheetApp, 2d arrays Note! This course requires a rudimentary understanding of very basic Javascript concepts and of what an array is. Nevertheless, if you want to come in blind you should be able to pick things up with a bit of Googling. Alternatively, if you just want the neat code snippets … Continue reading "Google Apps Script Course – Intro: 2D Array Data Transformation for Google Sheets"...
Google Apps Script: Filling out your column data to match the number of columns in a range
Google Apps Script, Google Sheets Have you ever experienced this warning error when trying to set values or paste a range of values back into your Google Sheet with Google Apps Script? The number of columns in the data does not match the number of columns in the range. The data has 6 but the … Continue reading "Google Apps Script: Filling out your column data to match the number of columns in a range"...
How to Automatically Navigate to the First Empty Row in a Google Sheet using Google Apps Script
Google Sheets, Google Apps Script: SpreadsheetApp, PropertiesService, Binary Search, onOpen, Button Ever had to open a huge data entry spreadsheet an all you want to do is enter your data and move onto another task? Instead, you have to waste precious time navigating all the way down to the bottom of the page to add … Continue reading "How to Automatically Navigate to the First Empty Row in a Google Sheet using Google Apps Script"...
Create a seat booking form with Google Forms, Google Sheets and Google Apps Script
Google Forms, Google Sheets, Google Apps Script: SpreadsheetApp, FormApp In this tutorial, we are going to build a simple seat booking Google Form. It will contain a registeree’s name and the session that they wish to attend. Each time a registeree submits a request to book a seat for a session, that seat is taken … Continue reading "Create a seat booking form with Google Forms, Google Sheets and Google Apps Script"...
Google Sheets: Counting and Filtering Rows where Cells Contain Particular Values.
Google Sheets: COUNTIF, ARRAYFOMULA, FILTER, REGEXMATCH, REGULAR EXPRESSIONS (RE2) Okay, wait! Stop!… …I know the byline contained regular expressions, but I promise you I won’t just leave you with the formula for you to figure out your own jumbled mass of jibberish characters or inundate you with a list of commands and leave to attempt … Continue reading "Google Sheets: Counting and Filtering Rows where Cells Contain Particular Values."...
Google Apps Script: How to force subscribe Google Group Members to a Google Calendar for G Suite [Update 06 Apr 2022]
Google Apps Script, G Suite Admins, Google Calendar, Calendar API, AdminDirectory, GroupsApp Note! This approach no longer force subscribes users to a Calendar. The new approach can be found here. It appears that the approach below no longer force-subscribes users in a Group to a calendar. Google Made this change in … Continue reading "Google Apps Script: How to force subscribe Google Group Members to a Google Calendar for G Suite "...
How to stop getting Google document requests to edit from users outside your organisation from publicly viewable files. (Updated Feb 2022)
G Suite – Paid editions, Gmail, Google Sheets, Google Docs, Google Slides, Google Drive Recently, I had updated all the course materials in a learning management system (Not Google Classroom. Sorry Google) to only use Google Workspace files like Docs, Sheets and Slides for students to access. For me, this was a pretty logical step. … Continue reading "How to stop getting Google document requests to edit from users outside your organisation from publicly viewable files. (Updated Feb 2022)"...
Google Apps Script: Google Standard Color Palette Picker for Sidebar and Dialog Boxes in Google Sheets, Google Docs and Google Slides (Updated Feb 2022)
Google Apps Script: Dev Tools, Color Picker, Side Bar, Custom Prompt, HtmlService, onOpen, Sidebar, Dialog Box I wanted to update one of my free Google Add-on apps that works with colour. What I had is just the standard HTML color input element where the user selects from the palette and that hexadecimal colour code is … Continue reading "Google Apps Script: Google Standard Color Palette Picker for Sidebar and Dialog Boxes in Google Sheets, Google Docs and Google Slides (Updated Feb 2022)"...
Google Apps Script: Hexadecimal Color Codes for Google Docs, Sheets and Slides Standard Palette
Google Apps Script – Google Workspace Standard Color Palette Hexadecimal Color Codes I am planning on updating a few Google Apps Script projects and updates soon. To accomplish them, I needed to get the full array of colours and their hexadecimal codes from the Google Sheets, Docs and Slides dropdown menus for the text and … Continue reading "Google Apps Script: Hexadecimal Color Codes for Google Docs, Sheets and Slides Standard Palette"...
Google Apps Script: Create a Select All Checkbox to Tick Checkboxes in Any Desired Range in Google Sheets
Google Apps Script: isChecked(), switch, filter, map One Checkbox to Rule them All Now that’s a fantasy novel I could be my gums into. Have you ever created a Google Sheet projects where you could really use a select all checkbox (they call them ‘Tick-boxes’ in Google Sheets)? Sure you can copy a range of … Continue reading "Google Apps Script: Create a Select All Checkbox to Tick Checkboxes in Any Desired Range in Google Sheets"...
Google Sheets Intermediate Project: Comparing the Best Daily Currency Exchange Rates Between Two Agencies – XRP/USD Chart Example Part 3
Google Sheets: Intermediate Chart Building, workflow This is Part 3 of this Google Sheets Intermediate Project Course. However, this part of the course can also stand on its own. If you are looking for part one or two, you can get it here: Part 1 || Part 2 In this tutorial, we will dive into … Continue reading "Google Sheets Intermediate Project: Comparing the Best Daily Currency Exchange Rates Between Two Agencies – XRP/USD Chart Example Part 3"...
Google Apps Script: How to check if there is a Tick Box (Check Box) in a cell or range
Google Apps Script: checkbox, Google Sheets This is something I came across today when working on a project. I needed my Google Apps Script to look at a cell or range and determine whether there is a checkbox in that Google Sheet cell. If there is, then my script would do something. Otherwise, I would … Continue reading "Google Apps Script: How to check if there is a Tick Box (Check Box) in a cell or range"...
Google Sheets Intermediate Project: Comparing the Best Daily Currency Exchange Rates Between Two Agencies – XRP/USD Example Part 2
Google Sheets: ARRAYFORMULA, IFERROR, SPARKLINE, Conditional formatting, workflow. This is Part 2 of this Google Sheets Intermediate Project. If you are looking for part one, you can get it here: Part 1 This project compares two currency exchanges rates, USD-XRP, in two different exchange gateways Bitstamp and Gatehub. Contents Recap Stage 2 Highest Exchange Rate … Continue reading "Google Sheets Intermediate Project: Comparing the Best Daily Currency Exchange Rates Between Two Agencies – XRP/USD Example Part 2"...
Google Sheets Intermediate Project: Comparing the Best Daily Currency Exchange Rates Between Two Agencies – XRP/USD Example Part 1
Google Sheets: FILTER, ARRAYFORMULA, UNIQUE, WEEKDAY, LEFT, MIN, MAX, IFERROR, Conditional formatting, workflow. In this tutorial, we are going to tackle some Intermediate level Google Sheets concepts including using functions like: FLITER ARRAYFORMULA MIN and MAX IFERROR SPARKLINE UNIQUE WEEKDAY LEFT We are then going to make our Google Sheet more presentable by doing some … Continue reading "Google Sheets Intermediate Project: Comparing the Best Daily Currency Exchange Rates Between Two Agencies – XRP/USD Example Part 1"...
My live Google Form response in Google Sheets won’t update in another Sheet Tab that references them. What do I do?
Google Sheets – Intermediate, Arrays, Form Data Picture this. You have set up your Google Sheet and attached your Google Form data to it so you might get a tab that looks a little something like this: You probably don’t want to mess with this tab because the Form is still live. Instead, you decide … Continue reading "My live Google Form response in Google Sheets won’t update in another Sheet Tab that references them. What do I do?"...
Google Apps Script – URL Fetch JSON data from an API and add it to a Google Sheet – Ripple API example
Google Apps Script – UrlFetchApp, SpreadsheetApp, Ripple API, Time Triggers I have been very fortunate of late to have the patronage of the Ripple XRP cryptocurrency community via XRP Tip Bot and Coil. This is no small part due to the support of user Recreational Rex from Twitter. Full disclosure here, I don’t really know … Continue reading "Google Apps Script – URL Fetch JSON data from an API and add it to a Google Sheet – Ripple API example"...
Google Apps Script: Delete Rows Based on a Column’s Cell Value in Google Sheet
Google Apps Script, SpreadsheetApp, Google Sheet, Iteration A not too uncommon task for Google Sheets users is to delete rows based on a cell’s values in a certain column. Often you don’t want to change the row position of the values so sorting the values and then deleting them in blocks is out. All you … Continue reading "Google Apps Script: Delete Rows Based on a Column’s Cell Value in Google Sheet"...
Google Apps Scripts: Create Time Triggers to automatically send email task reminders from a Google Sheets check list
Google Apps Script: Time Triggers, ClockTriggerBuilder, ScriptApp One of the most powerful things about Google Apps Script is the ability to automatically complete tasks with time-driven triggers. You can set up your code to run any one of your functions in your script by the minute, hour, day, week, or month. You can even set … Continue reading "Google Apps Scripts: Create Time Triggers to automatically send email task reminders from a Google Sheets check list"...
Google Apps Script: Create an HTML email reminder service from Google Sheet data.
Google Apps Script: SpreadsheetApp, Utilities, MailApp, UrlFetchApp, HtmlService, onOpen Ever wanted a quick way to send reminders to staff, letting them know that their task is overdue? I often have to provide Google Sheet checklists that administration team managers can crosscheck to see if their team members have complete a certain task. Reminding staff to … Continue reading "Google Apps Script: Create an HTML email reminder service from Google Sheet data."...
Google Workspace Group Email Reply Settings: How to reply to the group or just the sender of the group in Gmail
Gsuite: Groups for Business, Gmail Note: This post is for those who have Gsuite admin permissions. GSuite groups are a great bulk email tool. They can be used: To send announcements to a group. As a collaborative email for teams to communicate with each other. As an internal or public facing support email. To send … Continue reading "Google Workspace Group Email Reply Settings: How to reply to the group or just the sender of the group in Gmail"...
Google Sheets: How to create a task completion check sheet. (Updated Feb 2022)
Google Sheet: IMPORTRANGE, IF, TODAY, conditional formatting, tick box, alternating colors, protect sheet and ranges, share sheet Sometimes you have teams, staff or students who all need to complete reports and tasks by a certain date, but you need a way to keep track of when the reports are complete so that you can compile … Continue reading "Google Sheets: How to create a task completion check sheet. (Updated Feb 2022)"...
Google Apps Script: Get the last row of a data range when other columns have content like hidden formulas and check boxes [updated Mar 2022]
Google Apps Script: getRange, getLastRow, getDataRange, spreadsheetApp – requires a basic understanding of GAS. Updated 01 Dec 2021 Finding the last row of a data range in Google Sheets using Google Apps Script is a pretty common task. We often do this to find the next available free row to insert new data or to … Continue reading "Google Apps Script: Get the last row of a data range when other columns have content like hidden formulas and check boxes "...
Google Sheets: How to hide formula error warnings where there is no data or the data divides by zero
Google Sheets, IFERROR, ISBLANK, IF Have you ever set up a Google Sheet with formulas that you drag all the way down to the bottom? See all those messy error values littering your otherwise immaculate spreadsheet when those formulas don’t reference a value? It sucks, right? What if I told you that you could hide … Continue reading "Google Sheets: How to hide formula error warnings where there is no data or the data divides by zero"...
Google Apps Script: Send Bulk Emails and Share a Google Sheet with One Button Click
Google Sheets, Google Apps Script, GmailApp Ever wanted a quick way to give edit permissions to a user and then send them an email based on values in your Google Sheet? This task crosses my table several times a week. Whether it is sending reminders to complete a Google Sheets task or share a Google … Continue reading "Google Apps Script: Send Bulk Emails and Share a Google Sheet with One Button Click"...
Google Apps Script: Basic Beginners Guide to Using Strings in Code
Google Apps Script, Javascript, strings Hey there, Yagi here, you’ve probably stumbled across this page from a link from one of my other in-depth tutorials. This is just a quick primer on Javascript Strings in Google Apps Script for the non-coder. Here’s a bare-bones example of how a string of text might come together in … Continue reading "Google Apps Script: Basic Beginners Guide to Using Strings in Code"...
Google Apps Script: Getting Selected Google Sheets Columns and Reducing them to a Unique Value Based on a Key Column
Google Apps Script, Google Sheets, getRangeList, 3D arrays In a number of situations in my day-to-day work, I need to grab data from only Selected Columns in Google Sheets. Then using a selected column as a key, remove any duplicates in a similar way that a pivot table does. I might want to use this … Continue reading "Google Apps Script: Getting Selected Google Sheets Columns and Reducing them to a Unique Value Based on a Key Column"...
Working with Google Apps Script in Visual Studio Code using clasp
Google Apps Script, clasp, Visual Studio Code, Node.js, npm, Windows 10: Updated 23 Oct 21 I don’t mind the Google Apps Script built-in online editor. It provides word completion, error handling, and pretty formatting, making it ideal to hash out a small automation project pretty quickly. However, one of its major limitations is that after … Continue reading "Working with Google Apps Script in Visual Studio Code using clasp"...
Google Apps Script: Maintain Row Heights When Copying and Pasting Data in Google Sheets
Google Apps Script: getRowHeight, setRowHeight Copying and pasting data while maintaining row heights and widths can be a frustrating business in Google Apps Script. You see, there is a difference in how to set column width and set row heights. Let’s say we have already copied our data over with something like: sourceDataRange.copyTo(destination) Google Apps … Continue reading "Google Apps Script: Maintain Row Heights When Copying and Pasting Data in Google Sheets"...
Google Apps Script: How to Connect a Button to a Function in Google Sheets [Updated 08 Apr 2022]
Google Apps Script: UI, Google Sheets Ever wanted to use a button in Google Sheets to execute a function in Google Apps Script? Well, guess what? The process is super easy. Write your function in Google Apps Script editor. Create an image or upload an image and add it to your … Continue reading "Google Apps Script: How to Connect a Button to a Function in Google Sheets "...
Google Sheets: Conditional Formatting with Custom Formula
Feature inner image credit: Samuel King Jr. Google Sheets – Conditional Formatting Conditional formatting in Google Sheets is a powerful and useful tool to change fonts and backgrounds based on certain rules. This tutorial assumes that you already have a basic knowledge of Conditional Formatting but would like to uncover the mysteries of the Custom … Continue reading "Google Sheets: Conditional Formatting with Custom Formula"...
Google Apps Script – Why isn’t my add-on showing up in the add-on menu when I test it? (Updated Feb 2022)
Google Apps Script – AuthMode I mean, when I run it normally, it works just fine! This is an interesting problem I came across recently when reviewing one of my Google add-ons. The Problem The issue was that when I ran my Google Apps Script add-on code normally – in my case, connected to a … Continue reading "Google Apps Script – Why isn’t my add-on showing up in the add-on menu when I test it? (Updated Feb 2022)"...
How do I Forward and send Emails for my Website Email in my Personal Gmail account?
Gmail, cPanel, web host like GreenGeeks or HostGator One of the most frustrating things about using a domain email like imbatman@yagisanatode.com is that every time you want to check your email you need to log into your websites cPanel, navigate to the Email section, click ‘Email Accounts’ find the Access a Webmail provider like Roundcube … Continue reading "How do I Forward and send Emails for my Website Email in my Personal Gmail account?"...
Google Apps Script – When I add a value to a cell in a selected column, I want it to move to another Google Sheet [update Feb 2022]
Google Apps Script: onEdit, Google Sheets Sometimes you want to be able to automatically move a row from one sheet to another based on the value of a certain cell. The Example One of the first things that come to mind, and I am sure it does for you dear reader, is when I took … Continue reading "Google Apps Script – When I add a value to a cell in a selected column, I want it to move to another Google Sheet "...
Google Apps Script – Create Custom Unique IDs in Google Sheets [Update December 2021]
Google Apps Script, Google Sheet – Recent updates can be found in the changelog. Sometimes something on the surface that sounds downright easy turns out to be quite a complex procedure. My recent experience in creating a Custom Unique ID in Google Sheets using Google Apps Script was a case in point. In a previous … Continue reading "Google Apps Script – Create Custom Unique IDs in Google Sheets "...
How to display a date for one day in a week that automatically changes weekly on Google Sheets
Google Sheets – TEXT, TODAY, WEEKDAY If you are a busy admin nerd like me and have created a Google Doc or Sheet on the fly to meet your company’s demands for something or another, you probably also have a few Sheets lying around that are not 100% right. That obsessive-compulsive nature in you is … Continue reading "How to display a date for one day in a week that automatically changes weekly on Google Sheets"...
Google Apps Script – Adding a Unique ID in a Cell on Edit of an Adjacent Cell Using Time
Google Apps Script, onEdit, Date, Google Sheets Here is the scenario. You have a small business and you want to store your customers, products and sales information on separate Google sheets. You’re probably going to have someone from your staff enter new customers, products are sales transactions. We know if we get them to enter … Continue reading "Google Apps Script – Adding a Unique ID in a Cell on Edit of an Adjacent Cell Using Time"...
Creating a Google Sheet Geo Map From Form Data and Posting it to WordPress – Part 3 of Google Forms in WordPress with Live Chart Project (Updated Feb, 2022)
Google Forms, Google Sheets (IMPORTXML), XML Path, WordPress Note: This is part 3 of a larger project. Each part of the project is self-contained should you wish to reference just one particular aspect. Alternatively, you can follow along with the project to practise workflow and learn about Google Forms, Sheets, WordPress integration and a little … Continue reading "Creating a Google Sheet Geo Map From Form Data and Posting it to WordPress – Part 3 of Google Forms in WordPress with Live Chart Project (Updated Feb, 2022)"...
Embedding a Live Google Sheet Graph that Updates Every 30 Seconds into a WordPress Post – Part 2 of Google Forms in WordPress with Live Chart Project
Google Sheets, Forms, WordPress, HTML5, a touch of Javascript Note: This is part 2 of a larger project. Each part of the project is self-contained if you wish to reference just one particular aspect. Alternatively, you can follow along with the project to practise workflow and learn about Google Forms, Sheets, WordPress integration and a … Continue reading "Embedding a Live Google Sheet Graph that Updates Every 30 Seconds into a WordPress Post – Part 2 of Google Forms in WordPress with Live Chart Project"...
Creating a Short Google Form Survey and Embedding it into a WordPress Post – Part 1 of Google Forms in WordPress with Live Chart Project (Updated Feb 2022)
Google Forms, WordPress, HTML5 Note: This is the beginning of a larger project. Each part of the project is self-contained if you wish to reference just one particular aspect. Alternatively, you can follow along with the project to practise workflow and learn about Google Forms, Sheets, WordPress integration and a little HTML5 and Javascript. Intro … Continue reading "Creating a Short Google Form Survey and Embedding it into a WordPress Post – Part 1 of Google Forms in WordPress with Live Chart Project (Updated Feb 2022)"...
Multiple Cell Data and Formula Reference With Find and Replace – Google Sheets
One of my biggest regrets when I first got started with Google Sheets and spreadsheets in general, was not taking full advantage of Find and Replace. Find and Replace is the penicillin of the Spreadsheet world. It can rapidly cure all sorts or issue with a simple set of commands. It is seriously amazing stuff. … Continue reading "Multiple Cell Data and Formula Reference With Find and Replace – Google Sheets"...
5 Easy Shortcuts That Will Save you Heaps of Time – Google Sheets
It’s time to get evangelical, peeps! Release yourself from the yolk of the menu bar and its insidious demands on your coordination and time! Break free from the right-click menu! For is it not anything more than a proxy menu bar?! A veritable wolf in sheep’s clothing with no other design, but to bring you … Continue reading "5 Easy Shortcuts That Will Save you Heaps of Time – Google Sheets"...
Google Sheets – How to Separate the First Name from a Full Name Cell
left, right, find, length Every academic quarter I receive a list of students by their full name in one cell that I need to split into a cell for the first name and then a cell for the middle and last names combined. This fairly simple process can be achieved with the Google Sheets formulas left, … Continue reading "Google Sheets – How to Separate the First Name from a Full Name Cell"...
Google Apps Script – How to Get the Sheet Name and Spreadsheet Name and add to a Cell on Google Sheets with a Custom Function (Updated Feb 2022)
Google Apps Script, Custom Functions Boy, are these titles getting longer. But that’s pretty much the gist of it. In this post we will look at creating a Google Apps Script Custom Function that allows you to do Three things in Google Sheets: Get the current sheet name. That’s the same sheet name as the … Continue reading "Google Apps Script – How to Get the Sheet Name and Spreadsheet Name and add to a Cell on Google Sheets with a Custom Function (Updated Feb 2022)"...
Google Apps Script – Copy Selected Columns in One Sheet and Add Them To The Bottom of Different Selected Columns in Another [updated Feb 2022]
I recently had a legend (a.k.a reader) ask about how to copy selected columns from one sheet and append them to the bottom of different selected columns in another sheet. To me, this seemed like a pretty common task that one might face, so in response, I decided to create a template function to easily … Continue reading "Google Apps Script – Copy Selected Columns in One Sheet and Add Them To The Bottom of Different Selected Columns in Another "...
Google Apps Script – How to Change the File Name of Non-Native Files like MS Word Docs, PDF’s and Excel files.
Google Apps Script, DriveApp I had an unexpected need to change some non-native file names – in my case, MS Word docs – to something else to appease my masters. Being the resident Igor, I delighted in the task. The Problem I had a folder of MS Word documents on my Google Drive that needed … Continue reading "Google Apps Script – How to Change the File Name of Non-Native Files like MS Word Docs, PDF’s and Excel files."...
Google Apps Script: Get File By Name – With Optional Parent Folder Crosscheck
Google Apps Script: DriveApp One of my recent projects in Google Apps Script required me to search for a file by name and get its ID. This can be problematic in Google Drive because you can have multiple files of the same name in multiple locations. My solution was to also check the file’s parent … Continue reading "Google Apps Script: Get File By Name – With Optional Parent Folder Crosscheck"...
Simple Code for AB Testing Affiliate Recommendations in a WordPress Sidebar HTML Widget
Javascript, WordPress, Your Affiliate Program’s Campaign Link The Story When I created Yagisanatode.com my goal was to provide a resource for myself and others to reference on all the coding projects I work on. Since it’s beginnings in October 2017 I have seen a huge rise in my readership and am so pleased to see … Continue reading "Simple Code for AB Testing Affiliate Recommendations in a WordPress Sidebar HTML Widget"...
Google Sheets – Remove The Lowest Grade for Each Student on a Course
Google Sheets – MIN, FILTER, INDEX, MATCH, SUM, COUNTIF In the region of the world that I work in, it is a pretty common occurrence for university courses to run weekly assessment. At the end of the course, all the weekly assessment is then added together minus the lowest piece of assessment. For lecturers with … Continue reading "Google Sheets – Remove The Lowest Grade for Each Student on a Course"...
Google Sheets – Get the Top 2 Grades for Each Group in a Spreadsheet using RANK and FILTER (Updated Feb 2022)
Google Sheets: FILTER, RANK This week I was asked to provide a list of the top two student grades in each of the 100 classes in our university program. Normally, this would be a pretty easy task if there were only a half dozen or so classes. Simply sort by group (class) and then Grade. … Continue reading "Google Sheets – Get the Top 2 Grades for Each Group in a Spreadsheet using RANK and FILTER (Updated Feb 2022)"...
Google Apps Script – How to make a Custom Function to Use in Google Sheets
Google Apps Script: Custom Functions, Google Sheets Google Sheets has a vast library of functions (or formulas) you can use to get your Spreadsheet tasks done. However, there are some instances when you want a specific function that is not available in Google Sheets or wants to make a simplified version of some combined function … Continue reading "Google Apps Script – How to make a Custom Function to Use in Google Sheets"...
Google Sheets – Random Alphabetic, Random Alphanumeric and Random Alphanumeric + Character Custom Functions (Updated January 2022)
Google Sheets, Google Apps Script I was working on a Spreadsheet in Google Sheets a few days ago and needed to generate some random codes for my users. To do this, I just used the RANDBETWEEN(start val, end val) function built into Google Sheets. It goes a little like this. If I want to build … Continue reading "Google Sheets – Random Alphabetic, Random Alphanumeric and Random Alphanumeric + Character Custom Functions (Updated January 2022)"...
Google Apps Script – How to Find the Folder ID of a Non-Unique Folder Using File Path Names
Google Apps Script – DriveApp Well, that title is such a mouthful. So I think it needs some explaining… I’ve recently come across a task that requires me to access a particular folder in a directory based on the information in a Google Sheet. This means: I don’t have the folder or the sub-folder ID. … Continue reading "Google Apps Script – How to Find the Folder ID of a Non-Unique Folder Using File Path Names"...
Google Apps Script – How to Create Folders in Directories with DriveApp
Google Apps Script – DriveApp Well, it’s a rainy day here travelling in Romania, so time for a post. When creating a Google Apps Script’s I often find I am creating new folders and files in specific locations on Google Drive after, say, generating a report or something. A Note on Folders in Google Drive … Continue reading "Google Apps Script – How to Create Folders in Directories with DriveApp"...
Google Apps Script – Getting Input Data From a Dialog Box in Google Sheets (Updated January 2022)
Google Apps Script, Jquery, Javascript, HTML One of the weekly tasks in my day job as an academic administrator is to look at a number of ‘modules’ or courses that our college is running and complete some analysis and crosschecking. Each week the students complete a ‘unit’. However, during some quarters, not all modules are doing the … Continue reading "Google Apps Script – Getting Input Data From a Dialog Box in Google Sheets (Updated January 2022)"...
How to Hide a Row based on a Cell Value in Google Sheets with Filter or Google Apps Script.
Google Sheets, Google Apps Script, onEdit Sometimes, when you are working on a shared Google Sheet you might want to hide a row based on a cell value. For example, perhaps we don’t want to see row information of orders that have been paid for. Take a look at my D&D miniatures wholesale orders sheet (image … Continue reading "How to Hide a Row based on a Cell Value in Google Sheets with Filter or Google Apps Script."...
Sections to Sheets – Free Google Add-on That Separates Column Sections Into Individual Sheets for Page-by-Page Export or Printing
What is Sections to Sheets? Sections to Sheets is a free Google Sheets Add-on that separates rows by column sections and puts them in individual pages or sheets in a spreadsheet in preparation for export and printing. You can find Sections to Sheets at the Google Workspace Marketplace. ...
Google Apps Script – Copy and Paste Cells While Retaining Column Width in Google Sheets.
Google Apps Script: SpreadsheetApp, copyTo Have you ever copied and pasted something in Google Sheets only to be frustrated with the fact that it stubbornly refuses to paste the column widths? I mean, everything else is perfect, the formatting, the formulas the comments, they all were pasted across just fine. However, that dastardly column width … Continue reading "Google Apps Script – Copy and Paste Cells While Retaining Column Width in Google Sheets."...
Google Apps Script – How to create Javascript and CSS files for a Sidebar Project in Google Apps Script
Google Apps Script-templates, Javascript, CSS So, I started working on a larger Google Sheet Sidebar project in Google Apps Script recently and I quickly realised that it was going to be a mess if I didn’t separate my Javascript, CSS and even some of my HTML into separate files. However, if you have ever noticed … Continue reading "Google Apps Script – How to create Javascript and CSS files for a Sidebar Project in Google Apps Script"...
Google Apps Script – How to Alternate Colors in an Ordered List by Column Category.
Google Apps Script and Google Sheets Imagine that you have a Google Sheet that you have sorted by a certain column. You might be sorting by the surname of your sales team, class sections or regions. To make the sheet easier to read for your team, you want to alternate the background colours after each … Continue reading "Google Apps Script – How to Alternate Colors in an Ordered List by Column Category. "...
Google Apps Script – Get the Start Row and Length of Each Category in an Ordered Column in Google Sheets
Google Apps Script and Google Sheets Quite often I will need to get the range of each category in an item and do something with it in Google Sheets. For example, I work in education, I will often have rows of students that are categorized by class sections. I will then be asked to do … Continue reading "Google Apps Script – Get the Start Row and Length of Each Category in an Ordered Column in Google Sheets"...
Set the Paper Size and Orientation in a Doc Using Google Apps Script
Google Apps Script: DocumentApp setPageWidth, setPageHeight Sometimes you need to prepare a Google Doc’s paper size and orientation programmatically using Google Apps Script. Unfortunately, you can’t just call for say, A4 in Landscape. Okay, not until now (see my code below). Google Apps Script does provide a way to set the dimensions of your page … Continue reading "Set the Paper Size and Orientation in a Doc Using Google Apps Script"...
How to make multiple copies of a file with Python 3 and a file name list from an Excel sheet
Python 3, openPyXl, os, shutil on Windows 10 The Problem As an academic administrator, I have to prepare 70 empty grade report spreadsheets templates at the end of each academic quarter: one for each of my teachers. Each copy of the template sheet needs to be named with the teacher’s name and class number. Then … Continue reading "How to make multiple copies of a file with Python 3 and a file name list from an Excel sheet"...
How to Create a Simple YouTube Download Program with a Progress Indicator in Python 3 with pytube
Python 3, pytube, os in Windows 10 Some of my friends live in an area that really struggles to get decent internet speeds in the afternoons and evenings. So much so that they can barely watch a YouTube video at 144p some days, and that is not particularly useful if they are trying to watch … Continue reading "How to Create a Simple YouTube Download Program with a Progress Indicator in Python 3 with pytube"...