The Blog

*You can access the classic feed here.

Google Chat Apps for Apps Script Developers

Google Chat Apps for Google Apps Script Developers

Last Updated on 2022-09-29 by Yagi 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 … Continue reading "Google Chat Apps for Google Apps Script Developers" ...
Get Hidden or Visible Google Sheet Tabs with Google Apps Script

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

Last Updated on 2022-09-07 by Yagi 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 … Continue reading "Create links to the first item of each group in Google Sheets" ...
Duplicating Sheet Views in other Sheet Tabs using Google Apps Script

Duplicate Filter Views in Selected Google Sheet Tabs with Google Apps Script

Last Updated on 2022-08-19 by Yagi 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 … Continue reading "Duplicate Filter Views in Selected Google Sheet Tabs with Google Apps Script" ...
Adding links to images in Google Sheets

Adding Links to Images and Buttons in Google Sheets

Last Updated on 2022-08-10 by Yagi 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. … 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

Last Updated on 2022-08-10 by Yagi 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 … Continue reading "Creating Links from Custom Menus and Buttons in Google Sheets with Google Apps Script" ...
Record the Item with the Highest Order of Importance with JavaScript

Find the item with the highest order of importance and store it with JavaScript

Last Updated on 2022-08-02 by Yagi 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 … Continue reading "Find the item with the highest order of importance and store it with JavaScript" ...
GWAOw! Episode 4 Forms History by Martin Hawksey

GWAOw! 4 – Forms History by Martin Hawksey

Last Updated on 2022-07-26 by Yagi 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 … 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

Using Hyperlinks in Dialogs and Sidebars to open a URL in a new Tab with Google Apps Script

Last Updated on 2022-07-19 by Yagi 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. … Continue reading "Using Hyperlinks in Dialogs and Sidebars to open a URL in a new Tab with Google Apps Script" ...
Calculating Sales Tax In Google Sheets

How to Easily Calculate Sales Tax (GST 🦘) in Google Sheets

Last Updated on 2022-07-16 by Yagi 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 … Continue reading "How to Easily Calculate Sales Tax (GST 🦘) in Google Sheets" ...
GWAOw! 3 Crop Sheet by Eric Koleda a Google Workspace Add On

GWAOw! 3 – Crop Sheet by Eric Koleda

Last Updated on 2022-07-16 by Yagi 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 … Continue reading "GWAOw! 3 – Crop Sheet by Eric Koleda" ...
Display values by odd or even rows or col vals in Google Sheets

Displaying Odd and Even Row Data in Google Sheets

Last Updated on 2022-06-01 by Yagi 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 … Continue reading "Displaying Odd and Even Row Data in Google Sheets" ...
GWAOw!!!_2_ImportFromWeb by NoDataNoBusiness

GWAOw! 2 – ImportFromWeb by NoDataNoBusiness

Last Updated on 2022-05-31 by Yagi 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 … Continue reading "GWAOw! 2 – ImportFromWeb by NoDataNoBusiness" ...
Run Google Apps Script when setting up a new Google Sheet before revealing other tabs_v2Run Google Apps Script when setting up a new Google Sheet before revealing other tabs_v2

One Approach to Encourage Users to Run Google Sheet-Bound Apps Script When They First Make a Copy of the File

Last Updated on 2022-05-26 by Yagi 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() … 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 Choriaria

GWAOw! 1 – Workbook Statistics by Sourabh Choraria

Last Updated on 2022-05-31 by Yagi 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 … Continue reading "GWAOw! 1 – Workbook Statistics by Sourabh Choraria" ...
Filtering IMPORTRANGE data in Google Sheets

Filtering IMPORTRANGE data in Google Sheets

Last Updated on 2022-05-11 by Yagi 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 … Continue reading "Filtering IMPORTRANGE data in Google Sheets" ...
force subscribe a user in your domain to a Google Calendar with Google Apps Script

How to force subscribe a user in your domain to a Google Calendar with Google Apps Script.

Last Updated on 2022-08-15 by Yagi 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 … Continue reading "How to force subscribe a user in your domain to a Google Calendar with Google Apps Script." ...
How to delete a Google Sheets Button once it is clicked 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

Last Updated on 2022-03-29 by Yagi 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 … Continue reading "How to remove a Google Sheets button (drawings) or images connected to a Google Apps Script after the script has been run" ...
Import range values from one Google Sheet into another with Google Sheet

Copy and Paste Range Values from one Google Sheet into another with Google Apps Script

Last Updated on 2022-02-23 by Yagi 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 … Continue reading "Copy and Paste Range Values from one Google Sheet into another with Google Apps Script" ...
Using VLOOKUP with IMPORTRANGE in Google Sheets

Google Sheets IMPORTRANGE: Looking up data with the VLOOKUP function

Last Updated on 2022-02-23 by Yagi 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 … Continue reading "Google Sheets IMPORTRANGE: Looking up data with the VLOOKUP function" ...
importrange prevent editors from accessing other ranges of your imported Google Sheet

Google Sheets IMPORTRANGE: Prevent clever editors from accessing other ranges of your imported sheet

Last Updated on 2022-05-12 by Yagi 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: Importing Range Data From One Google Sheet to Another And here is a link … Continue reading "Google Sheets IMPORTRANGE: Prevent clever editors from accessing other ranges of your imported sheet" ...
Importing live range data from one Google Sheet to another

Importing Range Data From One Google Sheet to Another

Last Updated on 2022-05-12 by Yagi 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, … 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

Create a ISO String from date text input intended for UTC date in JavaScript

Last Updated on 2022-01-31 by Yagi 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 … 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

How to create a time sequence in Google Sheets

Last Updated on 2022-01-21 by Yagi 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 … Continue reading "How to create a time sequence in Google Sheets" ...
Google Sheets - calculate duration between two times

Calculate the Total Duration of Time between two periods in Google Sheets

Last Updated on 2021-12-30 by Yagi 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 … Continue reading "Calculate the Total Duration of Time between two periods in Google Sheets" ...
black friday sale for Google Sheets Course 2021

Get 30% off my Google Sheets Mastery course this Black Friday Cyber Monday 2021.

Last Updated on 2021-11-26 by Yagi 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 … Continue reading "Get 30% off my Google Sheets Mastery course this Black Friday Cyber Monday 2021." ...
Append a datetime stamp to google doc elements with google apps script

Append List Items, Paragraphs and table cell items with a date-time stamp in Google Docs using Google Apps Script

Last Updated on 2022-01-31 by Yagi 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 … Continue reading "Append List Items, Paragraphs and table cell items with a date-time stamp in Google Docs using Google Apps Script" ...
Get the most frequently occurring word in a column with Google Sheets v2

How to get the most frequently appearing words in a column in Google Sheets

Last Updated on 2021-10-19 by Yagi 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 … Continue reading "How to get the most frequently appearing words in a column in Google Sheets" ...
my Google Apps Script developer gave me obfuscated code What to do

I hired a Google Apps Script developer and they delivered unreadable code. What’s going on?

Last Updated on 2021-10-31 by Jarret Hooper 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. … 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

Why Object.create() doesn’t work in Google Apps Script and how to fix it.

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Why Object.create() doesn’t work in Google Apps Script and how to fix it." ...
validate users to access a web app with Google Apps Script

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

Last Updated on 2021-10-31 by Jarret Hooper 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? … Continue reading "How to Validate Specific Users on a Web App in Google Apps Scripts" ...
list all users in an organisations google workpsace account with google apps script

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

Last Updated on 2021-10-31 by Jarret Hooper 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 … 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

Get a list of Google Shared Drives by ID and Name in Google Apps Script [updated 10 Feb 2022]

Last Updated on 2022-02-18 by Yagi 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 … Continue reading "Get a list of Google Shared Drives by ID and Name in Google Apps Script " ...
Update a Dropdown List Dynamically Across a Range in Google Sheets

Update a range of dropdown lists in a Google Sheet dynamically based on a corresponding dropdown choice (Updated 28 Mar 2022)

Last Updated on 2022-03-29 by Yagi 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 … 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 objects in JavaScript

Get a Unique List of Objects in an Array of Object in JavaScript

Last Updated on 2022-01-11 by Yagi 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 … Continue reading "Get a Unique List of Objects in an Array of Object in JavaScript" ...
Google Apps Script GWAO File Picker Title

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

Last Updated on 2021-10-31 by Jarret Hooper 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 … 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" ...
Find and replace text with a link in Google Apps Script using DocumentApp

How to find and replace text in a Google Doc with a link or a list of links with Google Apps Script

Last Updated on 2021-10-31 by Jarret Hooper 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 … 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

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Why your COUNT function might not be working in Google Sheets" ...
Google Sheets Course Title

New course 🎉🎉🎉 Google Sheets: Learn the Essentials with Three Detailed Projects & 60% off 😃

Last Updated on 2021-05-02 by Yagi 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 … Continue reading "New course 🎉🎉🎉 Google Sheets: Learn the Essentials with Three Detailed Projects & 60% off 😃" ...
Google Apps Script custom auto email response on form submit

How to Create a Custom Email Auto-responder for a Google Forms Submission with Google Apps Script (Updated Feb 2022)

Last Updated on 2022-02-23 by Jarret Hooper 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 … 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 document that was shared with me

Why can’t I edit this Google Sheet that was shared with me? 🤔

Last Updated on 2022-05-31 by Yagi 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 … Continue reading "Why can’t I edit this Google Sheet that was shared with me? 🤔" ...
43 - Getting Help

Google Sheets Beginners: Getting Help (44)

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Google Sheets Beginners: Getting Help (44)" ...
43 - The explore Tool Google Sheets

Google Sheets Beginners: Understanding The Explore Tool (43)

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Google Sheets Beginners: Understanding The Explore Tool (43)" ...
Google Sheets Shorts - 42 Add-ons

Google Sheets Beginners: Add-ons (42)

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Google Sheets Beginners: Add-ons (42)" ...
Add weekly time triggers in date range in Google Apps Script

How to programmatically schedule weekly time triggers between two dates in Google Apps Script

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "How to programmatically schedule weekly time triggers between two dates in Google Apps Script" ...
how to change your Google Apps Script project time zone

Help! My time triggers are not in sync! : How to update your Google Apps Script project time zone.

Last Updated on 2022-03-25 by Yagi 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 … Continue reading "Help! My time triggers are not in sync! : How to update your Google Apps Script project time zone." ...
Google Sheets Shorts - Themes

Google Sheets Beginners: Using Themes (41)

Last Updated on 2021-10-31 by Jarret Hooper 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. … Continue reading "Google Sheets Beginners: Using Themes (41)" ...
Google Sheets - Charts

Google Sheets Beginners: Charts (40)

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Google Sheets Beginners: Charts (40)" ...
Get the occurrence of a Value as a percentage in Google Sheets

Get the Occurrence of a Value in a Range as a Percentage in Google Sheets (Updated Feb 2022)

Last Updated on 2022-02-23 by Jarret Hooper 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 … Continue reading "Get the Occurrence of a Value in a Range as a Percentage in Google Sheets (Updated Feb 2022)" ...
38 - Google Sheets Shorts - Filter

Google Sheets Beginners: Filter (38) & Filter View (39) (Updated 21 June 2022)

Last Updated on 2022-06-21 by Yagi 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. … Continue reading "Google Sheets Beginners: Filter (38) & Filter View (39) (Updated 21 June 2022)" ...
Custom Google Forms pre-fills with Google Apps Script

Create custom prefilled Google Forms links in custom emails with Google Apps Script (Updated Feb 2022)

Last Updated on 2022-02-12 by Jarret Hooper 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 … Continue reading "Create custom prefilled Google Forms links in custom emails with Google Apps Script (Updated Feb 2022)" ...
36 - Grouping Columns and Rows

Google Sheets Beginners: Grouping Rows and Columns (36)

Last Updated on 2021-10-31 by Jarret Hooper 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, … Continue reading "Google Sheets Beginners: Grouping Rows and Columns (36)" ...
Copy cut n paste Google Sheets Tribbles

Google Sheets Beginners: Cutting, Copying and Pasting ( + bonus complete guide) (35)

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Google Sheets Beginners: Cutting, Copying and Pasting ( + bonus complete guide) (35)" ...
monster guide to data validation in Google Sheets v2

The Monster Guide to Data Validation in Google Sheets: Free Course (Updated May 2022)

Last Updated on 2022-05-31 by Yagi 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 … Continue reading "The Monster Guide to Data Validation in Google Sheets: Free Course (Updated May 2022)" ...
Data Validation in Google Sheets

Google Sheets Beginners: Data Validation (34)

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Google Sheets Beginners: Data Validation (34)" ...
Split Text to columns in Google Sheets

Google Sheets Beginners: Split text to columns (33)

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Google Sheets Beginners: Split text to columns (33)" ...
Accessing the keyboard shortcuts in Google Sheets

Google Sheets Beginners: Accessing the Keyboard Shortcuts (32)

Last Updated on 2021-10-31 by Jarret Hooper 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. … Continue reading "Google Sheets Beginners: Accessing the Keyboard Shortcuts (32)" ...
Automatically download google drive files on-click

Automatic download link for non-Google Workspace files in Google Drive

Last Updated on 2021-01-17 by Yagi 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 … Continue reading "Automatic download link for non-Google Workspace files in Google Drive" ...
create removable button items from text input or select elements

Create Removable Item Buttons Generated From Select or Comma-separated Input elements with HTML, CSS and JS

Last Updated on 2021-01-11 by Yagi 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 … Continue reading "Create Removable Item Buttons Generated From Select or Comma-separated Input elements with HTML, CSS and JS" ...
31 Editing Excel and ODS format files in Google Sheets

Google Sheets Beginners: Editing Excel and ODS format files in Google Sheets (31)

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Google Sheets Beginners: Editing Excel and ODS format files in Google Sheets (31)" ...
Creating Custom Page Breaks for Printing and Exporting 30

Google Sheets Beginners: You Can Create Custom Page Breaks for Printing and Exporting (30)

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Google Sheets Beginners: You Can Create Custom Page Breaks for Printing and Exporting (30)" ...
Printing in Google Sheets (29)

Google Sheets Beginners: Printing your Google Sheet (29)

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Google Sheets Beginners: Printing your Google Sheet (29)" ...
28 Downloading or Exporting your Google Sheet

Google Sheets Beginners: Exporting your Google Sheet as different file types (28)

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Google Sheets Beginners: Exporting your Google Sheet as different file types (28)" ...

2020 – Year in Review

Last Updated on 2020-12-31 by Yagi 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 … Continue reading "2020 – Year in Review" ...
Custom Table of Contents in Google Sheets

Create a custom automated Table of Contents for your Google Sheets tabs with Google Apps Script (Updated Feb 2022)

Last Updated on 2022-02-12 by Jarret Hooper 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! … Continue reading "Create a custom automated Table of Contents for your Google Sheets tabs with Google Apps Script (Updated Feb 2022)" ...
Trim Whitespace

Google Sheets Beginners: Trim Whitespace (27)

Last Updated on 2021-10-31 by Jarret Hooper 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. If you enjoy the video and want to learn … Continue reading "Google Sheets Beginners: Trim Whitespace (27)" ...
26 Workbook Edit History

Google Sheets Beginners: Workbook Edit Version History (26)

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Google Sheets Beginners: Workbook Edit Version History (26)" ...
25 Google Sheets Shorts - Get Cell User History

Google Sheets Beginners: Cell History (25)

Last Updated on 2021-10-31 by Jarret Hooper 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: If you enjoy the video and want to learn more about how to use Google Sheets, check out … Continue reading "Google Sheets Beginners: Cell History (25)" ...
Google Sheets Recipe Template cover image

Use Google Sheets to store your Recipes to automatically change Batch Sizes and Recipe Amount by Weight

Last Updated on 2022-08-02 by Yagi 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 … Continue reading "Use Google Sheets to store your Recipes to automatically change Batch Sizes and Recipe Amount by Weight" ...
24 Google Sheets Shorts - Get Notifications for Edits and Form Submissions

Google Sheets Beginners: Getting Email notifications on changes and form submits (24)

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Google Sheets Beginners: Getting Email notifications on changes and form submits (24)" ...
23 Google Sheets Shorts - Protect Cells Ranges Sheet Tabs

Google Sheets Beginners: Protecting cells, ranges and sheet tabs (23) [updated Feb 2022]

Last Updated on 2022-02-11 by Yagi 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 … Continue reading "Google Sheets Beginners: Protecting cells, ranges and sheet tabs (23) " ...
Google Apps Script Store Unique Key from users on your WebApp

Google Apps Script: Store a Unique User Key from a User Accessing your WebApp [updated Dec 2021]

Last Updated on 2021-12-17 by Yagi 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 … Continue reading "Google Apps Script: Store a Unique User Key from a User Accessing your WebApp " ...
22 Google Sheets Shorts - Remove Duplicates From a Range

Google Sheets Beginners: Remove Duplicates from a Range (22)

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Google Sheets Beginners: Remove Duplicates from a Range (22)" ...
21 Google Sheets Shorts - Linking

Google Sheets Beginners: Linking (21)

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Google Sheets Beginners: Linking (21)" ...
Chain Story WebApp uisng Google Apps Script and Google Sheets

Creating an embedded interactive Chain Story app with Google Apps Script and Google Sheets

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Creating an embedded interactive Chain Story app with Google Apps Script and Google Sheets" ...
20 Google Sheets Shorts - Sharing & Permissions

Google Sheets Beginners: Sharing your Sheet (20)

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Google Sheets Beginners: Sharing your Sheet (20)" ...
19 Google Sheets Shorts - Comments and Communication

Google Sheets Beginners: Communicating with Comments (19)

Last Updated on 2021-10-31 by Jarret Hooper 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  … Continue reading "Google Sheets Beginners: Communicating with Comments (19)" ...

Google Sheets Beginners – Alternating Colours (17)

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Google Sheets Beginners – Alternating Colours (17)" ...
18 Google Sheets Shorts - Inserting Images

Google Sheets Beginners – Inserting Images (18)

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Google Sheets Beginners – Inserting Images (18)" ...
16 Google Sheets Shorts - Timezone date n time

Google Sheets Beginners – Change Your Current Workbook’s Timezone Date & Currency (16)

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Google Sheets Beginners – Change Your Current Workbook’s Timezone Date & Currency (16)" ...
Creating Google Sheets Proper in Javascript

Google Apps Script – Javascript: Emulate the “Proper” Google Sheets Function

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Google Apps Script – Javascript: Emulate the “Proper” Google Sheets Function" ...
15 Google Sheets Shorts - Spell check in Google Sheets

Google Sheets Beginners – Spell Check(15)

Last Updated on 2021-10-31 by Jarret Hooper 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. … Continue reading "Google Sheets Beginners – Spell Check(15)" ...
14 Google Sheets Shorts - Add and remove Google Sheet Tabs

Google Sheets Beginners – Creating Sheet Tabs (14)

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Google Sheets Beginners – Creating Sheet Tabs (14)" ...
Google Apps Script Web App title image

Google Apps Script: How to create a basic interactive interface with Web Apps

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Google Apps Script: How to create a basic interactive interface with Web Apps" ...
13 Google Sheets Shorts - Hide columns and rows in Google Sheet Tabs

Google Sheets Beginners – Hide and unhide columns and rows(13)

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Google Sheets Beginners – Hide and unhide columns and rows(13)" ...
12 Google Sheets Shorts - Insert Columns and Rows

Google Sheets Beginners: Insert Columns and Rows (12)

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Google Sheets Beginners: Insert Columns and Rows (12)" ...
Compare current week with previous Google Sheets automation 1

How to compare current week’s data with previous week’s data in Google Sheets

Last Updated on 2021-10-31 by Jarret Hooper 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. … Continue reading "How to compare current week’s data with previous week’s data in Google Sheets" ...
11 Google Sheets Shorts - Freeze Columns and Rows

Google Sheets Beginners: Freeze Rows and Columns(11)

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Google Sheets Beginners: Freeze Rows and Columns(11)" ...
10 Google Sheets Shorts - Paint Format

Google Sheets Beginners: Paint Format (10)

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Google Sheets Beginners: Paint Format (10)" ...
creating unique ranges from 2d arrays in Google Apps Script

Creating Unique Ranges from 2D Arrays in Google Apps Script

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Creating Unique Ranges from 2D Arrays in Google Apps Script" ...
add remove editors based on sheet data with Google Apps Script

Google Apps Script: How to Add and Remove Editors to a Google Sheet based on Sheet Data (Updated Feb 2022)

Last Updated on 2022-02-10 by Jarret Hooper 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 … Continue reading "Google Apps Script: How to Add and Remove Editors to a Google Sheet based on Sheet Data (Updated Feb 2022)" ...
Add and remove editors in a Google Sheet with Google Apps Script

Google Apps Script: How to Add and Remove Editors to A Google Sheet with Code (Updated Feb 2022)

Last Updated on 2022-02-10 by Jarret Hooper 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 … Continue reading "Google Apps Script: How to Add and Remove Editors to A Google Sheet with Code (Updated Feb 2022)" ...
SPREAD_BYRANGE custom function in Google Sheets

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)

Last Updated on 2022-02-24 by Jarret Hooper 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 … 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

Running Google Apps Script for the First Time: What’s with all the Warnings!

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Running Google Apps Script for the First Time: What’s with all the Warnings!" ...
Automated PDF Certificated Creator That Is Emailed to the Attendee Google Apps Script

Google Apps Script: Automated PDF Certificated Creator That Is Emailed to the Attendee (Updated Feb 2022)

Last Updated on 2022-02-10 by Jarret Hooper 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 … Continue reading "Google Apps Script: Automated PDF Certificated Creator That Is Emailed to the Attendee (Updated Feb 2022)" ...
09 Google Sheets Shorts - Conditional Formatting

Google Sheets Beginners: Conditional Formatting (09)

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Google Sheets Beginners: Conditional Formatting (09)" ...
Certificate of attendance using Google Slides

Create a Student Certificate of Attendance with Google Slides and Export it as a PDF or Print it

Last Updated on 2020-12-14 by Yagi 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? … Continue reading "Create a Student Certificate of Attendance with Google Slides and Export it as a PDF or Print it" ...
Google Sheets Borders

Google Sheets Beginners: Borders (07 & 08)

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Google Sheets Beginners: Borders (07 & 08)" ...

Google Sheets Beginners: Backgrounds (06)

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Google Sheets Beginners: Backgrounds (06)" ...
5 Google Sheets Shorts - merging cells

Google Sheets Beginners: Merging Text (5)

Last Updated on 2021-10-31 by Jarret Hooper 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. … Continue reading "Google Sheets Beginners: Merging Text (5)" ...
4 Google Sheets Shorts - text formatting in cells

Google Sheets Beginners: Formatting Text Inside Cell (4)

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Google Sheets Beginners: Formatting Text Inside Cell (4)" ...
3 Google Sheets Shorts - text formatting

Google Sheets Shorts: Text Formatting (3)

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Google Sheets Shorts: Text Formatting (3)" ...
Document Merge using Google Apps Script

Google Apps Script: Create multiple versions of a document based on Google Sheet Data and a Google Doc Template (Mail Merge)

Last Updated on 2022-04-04 by Yagi 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 … Continue reading "Google Apps Script: Create multiple versions of a document based on Google Sheet Data and a Google Doc Template (Mail Merge)" ...
2 Google Sheets Shorts - align n wrap text

Google Sheets Shorts: Align and Wrap Text (2)

Last Updated on 2021-10-30 by Jarret Hooper 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 … Continue reading "Google Sheets Shorts: Align and Wrap Text (2)" ...
1 Google Sheets Shorts - Sorting Data

Google Sheets Shorts: Sorting Data (1)

Last Updated on 2021-10-30 by Jarret Hooper 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 … Continue reading "Google Sheets Shorts: Sorting Data (1)" ...
0 Google Sheets Shorts - resizing columns and rows

Google Sheets Shorts: Change the Column and Row Size (0)

Last Updated on 2021-10-30 by Jarret Hooper 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 … Continue reading "Google Sheets Shorts: Change the Column and Row Size (0)" ...
Extract IDs from PDFs and instert into Google Apps Scirpt

Google Apps Script: Extract Specific Data From a PDF and insert it into a Google Sheet (Updated Feb 2022)

Last Updated on 2022-02-10 by Jarret Hooper 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 … 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 days of the week in a range Google Sheets

Count the Number of Selected Days of the Week in Google Sheets

Last Updated on 2021-10-31 by Jarret Hooper 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. … Continue reading "Count the Number of Selected Days of the Week in Google Sheets" ...
Grade Upload for Google Classroom

Google Apps Script: Upload grades into a Google Classroom Coursework Assignment (Updated Feb 2022)

Last Updated on 2022-02-10 by Jarret Hooper 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 … Continue reading "Google Apps Script: Upload grades into a Google Classroom Coursework Assignment (Updated Feb 2022)" ...
Get Google Doc Text Based on Reference Characters GAS Header1

Google Apps Script: Get Google Doc Text Based on Reference Characters

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Google Apps Script: Get Google Doc Text Based on Reference Characters" ...
Number of Words and Sentences in a Paragraph Google Sheets

Google Sheets: Get the Number of Words or Sentences in a Cell (Paragraph)

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Google Sheets: Get the Number of Words or Sentences in a Cell (Paragraph)" ...
Google Apps Script: Add and remove Google Sheets columns based on search array

Google Apps Script: Add and removed Google Sheets columns based on a search array

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Google Apps Script: Add and removed Google Sheets columns based on a search array" ...
Google Sheets FILTER with OR

Google Sheets: How to use OR inside a FILTER

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Google Sheets: How to use OR inside a FILTER" ...
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 3: 2D Array Data Transformation of Multiple Question Items Data to Total Count of Rating Choices in Google Sheets

Last Updated on 2021-10-31 by Jarret Hooper 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 … 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 Sheets 2d count array with Google Apps Script

Google Apps Script Course – Part 2: 2D Array Data Transformation of Single Question Item Data to Total Count of Rating Choices in Google Sheets

Last Updated on 2021-10-31 by Jarret Hooper 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 … 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" ...
datatransformation course header Google Sheets Google Apps Script

Google Apps Script Course – Intro: 2D Array Data Transformation for Google Sheets

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Google Apps Script Course – Intro: 2D Array Data Transformation for Google Sheets" ...
expand column data to macth all rows Google Sheets

Google Apps Script: Filling out your column data to match the number of columns in a range

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Google Apps Script: Filling out your column data to match the number of columns in a range" ...
Google Sheets onOpen activate next empty row

How to Automatically Navigate to the First Empty Row in a Google Sheet using Google Apps Script

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "How to Automatically Navigate to the First Empty Row in a Google Sheet using Google Apps Script" ...
Google Form Seat Booking and Registeree list

Create a seat booking form with Google Forms, Google Sheets and Google Apps Script

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Create a seat booking form with Google Forms, Google Sheets and Google Apps Script" ...
Google Sheets Filter & Count cells that contain values

Google Sheets: Counting and Filtering Rows where Cells Contain Particular Values.

Last Updated on 2021-10-31 by Jarret Hooper 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 … Continue reading "Google Sheets: Counting and Filtering Rows where Cells Contain Particular Values." ...
force change calendar subscriptions for Google Group members in Google Apps Script

Google Apps Script: How to force subscribe Google Group Members to a Google Calendar for G Suite [Update 06 Apr 2022]

Last Updated on 2022-04-06 by Yagi 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 … Continue reading "Google Apps Script: How to force subscribe Google Group Members to a Google Calendar for G Suite " ...
Gmail request for access filter

How to stop getting Google document requests to edit from users outside your organisation from publicly viewable files. (Updated Feb 2022)

Last Updated on 2022-04-27 by Yagi 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, … Continue reading "How to stop getting Google document requests to edit from users outside your organisation from publicly viewable files. (Updated Feb 2022)" ...
Google Standard Color Palette Picker for Sidebar and Prompt

Google Apps Script: Google Standard Color Palette Picker for Sidebar and Dialog Boxes in Google Sheets, Google Docs and Google Slides (Updated Feb 2022)

Last Updated on 2022-02-10 by Jarret Hooper 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 … 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 Sheets Docs Slides Color Palette

Google Apps Script: Hexadecimal Color Codes for Google Docs, Sheets and Slides Standard Palette

Last Updated on 2021-10-30 by Jarret Hooper Google Apps Script – GSuite Standard Color Palette 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 … Continue reading "Google Apps Script: Hexadecimal Color Codes for Google Docs, Sheets and Slides Standard Palette" ...
Google Select All Check Box cover pic

Google Apps Script: Create a Select All Checkbox to Tick Checkboxes in Any Desired Range in Google Sheets

Last Updated on 2021-10-30 by Jarret Hooper 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)? … Continue reading "Google Apps Script: Create a Select All Checkbox to Tick Checkboxes in Any Desired Range in Google Sheets" ...
Google Sheets -Dail USD-XRP Exchange Rate by Gateway final Tutorial Chart

Google Sheets Intermediate Project: Comparing the Best Daily Currency Exchange Rates Between Two Agencies – XRP/USD Chart Example Part 3

Last Updated on 2021-10-30 by Jarret Hooper 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 … 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

Last Updated on 2021-10-30 by Jarret Hooper 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 … 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 tutorial part 2 XRP

Google Sheets Intermediate Project: Comparing the Best Daily Currency Exchange Rates Between Two Agencies – XRP/USD Example Part 2

Last Updated on 2021-10-30 by Jarret Hooper 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. … 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

Last Updated on 2021-10-30 by Jarret Hooper 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 … 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?

Last Updated on 2021-10-30 by Jarret Hooper 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 … 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

Last Updated on 2021-10-30 by Jarret Hooper 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. … Continue reading "Google Apps Script – URL Fetch JSON data from an API and add it to a Google Sheet – Ripple API example" ...
Delete rows based on a cell value in Google Sheets with Google Apps Script

Google Apps Script: Delete Rows Based on a Column’s Cell Value in Google Sheet

Last Updated on 2021-10-30 by Jarret Hooper 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 … Continue reading "Google Apps Script: Delete Rows Based on a Column’s Cell Value in Google Sheet" ...
Create a time trigger to run reminder notices in google apps script

Google Apps Scripts: Create Time Triggers to automatically send email task reminders from a Google Sheets check list

Last Updated on 2022-03-03 by Yagi 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, … Continue reading "Google Apps Scripts: Create Time Triggers to automatically send email task reminders from a Google Sheets check list" ...
Google Apps Script email remind from Google Sheet The Badger 2

Google Apps Script: Create an HTML email reminder service from Google Sheet data.

Last Updated on 2022-09-15 by Yagi 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 … Continue reading "Google Apps Script: Create an HTML email reminder service from Google Sheet data." ...
modify email replies for google groups

Google Workspace Group Email Reply Settings: How to reply to the group or just the sender of the group in Gmail

Last Updated on 2022-04-27 by Yagi 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 … 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)

Last Updated on 2022-04-27 by Yagi 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 … Continue reading "Google Sheets: How to create a task completion check sheet. (Updated Feb 2022)" ...
Google Sheets get last row with hidden formulas and checkboxes with GAS

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]

Last Updated on 2022-03-04 by Yagi 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 … Continue reading "Google Apps Script: Get the last row of a data range when other columns have content like hidden formulas and check boxes " ...
Hide formula on empty rows in Google sheets

Google Sheets: How to hide formula error warnings where there is no data or the data divides by zero

Last Updated on 2021-10-30 by Jarret Hooper 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 … Continue reading "Google Sheets: How to hide formula error warnings where there is no data or the data divides by zero" ...
Send Emails and Edit permission from Google Sheet Uniques list with Google Apps Script

Google Apps Script: Send Bulk Emails and Share a Google Sheet with One Button Click

Last Updated on 2021-10-30 by Jarret Hooper 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 … 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

Last Updated on 2021-10-30 by Jarret Hooper 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 … Continue reading "Google Apps Script: Basic Beginners Guide to Using Strings in Code" ...
Reduce selected columns in Google Sheets by unique values in Google Apps Script

Google Apps Script: Getting Selected Google Sheets Columns and Reducing them to a Unique Value Based on a Key Column

Last Updated on 2021-10-30 by Jarret Hooper 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 … Continue reading "Google Apps Script: Getting Selected Google Sheets Columns and Reducing them to a Unique Value Based on a Key Column" ...
Google Apps Script to Visual Studio Code with Clasp

Working with Google Apps Script in Visual Studio Code using clasp

Last Updated on 2021-10-30 by Jarret Hooper 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 … Continue reading "Working with Google Apps Script in Visual Studio Code using clasp" ...
Maintaining Row height in copy and paste in Google Sheets with Google Apps Script

Google Apps Script: Maintain Row Heights When Copying and Pasting Data in Google Sheets

Last Updated on 2022-01-28 by Yagi 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 … 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]

Last Updated on 2022-04-08 by Yagi 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 … Continue reading "Google Apps Script: How to Connect a Button to a Function in Google Sheets " ...

Google Sheets: Conditional Formatting with Custom Formula

Last Updated on 2021-10-30 by Jarret Hooper 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 … 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)

Last Updated on 2022-02-10 by Jarret Hooper 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 … 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)" ...
send and forward website emails in Gmail

How do I Forward and send Emails for my Website Email in my Personal Gmail account?

Last Updated on 2020-12-14 by Yagi 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 … Continue reading "How do I Forward and send Emails for my Website Email in my Personal Gmail account?" ...
GAS onEdit move to new sheet

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]

Last Updated on 2022-02-23 by Yagi 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 … 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]

Last Updated on 2022-04-04 by Yagi 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 … Continue reading "Google Apps Script – Create Custom Unique IDs in Google Sheets " ...
Weekly Date Google Sheets

How to display a date for one day in a week that automatically changes weekly on Google Sheets

Last Updated on 2021-10-30 by Jarret Hooper 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% … Continue reading "How to display a date for one day in a week that automatically changes weekly on Google Sheets" ...
uniqueIDs Google Apps Script

Google Apps Script – Adding a Unique ID in a Cell on Edit of an Adjacent Cell Using Time

Last Updated on 2021-10-30 by Jarret Hooper 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 … Continue reading "Google Apps Script – Adding a Unique ID in a Cell on Edit of an Adjacent Cell Using Time" ...
Google sheets Geo Chart Completed

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)

Last Updated on 2022-02-10 by Jarret Hooper 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 … 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

Last Updated on 2021-10-30 by Jarret Hooper 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 … 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)

Last Updated on 2022-02-10 by Jarret Hooper 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 … 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)" ...
Harry Potter Google Sheets

Multiple Cell Data and Formula Reference With Find and Replace – Google Sheets

Last Updated on 2021-10-30 by Jarret Hooper 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 … Continue reading "Multiple Cell Data and Formula Reference With Find and Replace – Google Sheets" ...
google sheets efficiency

5 Easy Shortcuts That Will Save you Heaps of Time – Google Sheets

Last Updated on 2021-10-30 by Jarret Hooper 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 … Continue reading "5 Easy Shortcuts That Will Save you Heaps of Time – Google Sheets" ...
split the first name from a cell - Google Sheets

Google Sheets – How to Separate the First Name from a Full Name Cell

Last Updated on 2021-10-30 by Jarret Hooper 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 … Continue reading "Google Sheets – How to Separate the First Name from a Full Name Cell" ...
Get Sheet Names and Spreadsheet Names: Google Apps Script

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)

Last Updated on 2022-02-10 by Jarret Hooper 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.  … 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)" ...
append Values - Google Apps Script

Google Apps Script – Copy Selected Columns in One Sheet and Add Them To The Bottom of Different Selected Columns in Another [updated Feb 2022]

Last Updated on 2022-02-23 by Yagi 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 … Continue reading "Google Apps Script – Copy Selected Columns in One Sheet and Add Them To The Bottom of Different Selected Columns in Another " ...
File Name Change - Google Apps Script

Google Apps Script – How to Change the File Name of Non-Native Files like MS Word Docs, PDF’s and Excel files.

Last Updated on 2021-10-30 by Jarret Hooper 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 … 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

Last Updated on 2021-10-30 by Jarret Hooper 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 … Continue reading "Google Apps Script: Get File By Name – With Optional Parent Folder Crosscheck" ...
A B testing for Affiliate Recommendation with Javascript

Simple Code for AB Testing Affiliate Recommendations in a WordPress Sidebar HTML Widget

Last Updated on 2019-11-25 by Yagi 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 … Continue reading "Simple Code for AB Testing Affiliate Recommendations in a WordPress Sidebar HTML Widget" ...
Remove Lowest Grade - Google Sheets

Google Sheets – Remove The Lowest Grade for Each Student on a Course

Last Updated on 2021-10-30 by Jarret Hooper 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 … Continue reading "Google Sheets – Remove The Lowest Grade for Each Student on a Course" ...
Top 2 grades for each group - Google Sheets

Google Sheets – Get the Top 2 Grades for Each Group in a Spreadsheet using RANK and FILTER (Updated Feb 2022)

Last Updated on 2022-02-10 by Jarret Hooper 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 … 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

Last Updated on 2021-10-30 by Jarret Hooper 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 … Continue reading "Google Apps Script – How to make a Custom Function to Use in Google Sheets" ...
randalpha examples = Google Sheets

Google Sheets – Random Alphabetic, Random Alphanumeric and Random Alphanumeric + Character Custom Functions (Updated January 2022)

Last Updated on 2022-01-08 by Jarret Hooper 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 … 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

Last Updated on 2021-10-30 by Jarret Hooper 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 … Continue reading "Google Apps Script – How to Find the Folder ID of a Non-Unique Folder Using File Path Names" ...
coding in Romania

Google Apps Script – How to Create Folders in Directories with DriveApp

Last Updated on 2021-10-30 by Jarret Hooper 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. … 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)

Last Updated on 2022-02-24 by Jarret Hooper 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, … Continue reading "Google Apps Script – Getting Input Data From a Dialog Box in Google Sheets (Updated January 2022)" ...
Four Digits to Time in Google Sheets with Google Apps Script

Google Apps Script – How to Automatically Generate a Time From a Four Digit 24 hour Time in Google Sheets.

Last Updated on 2021-10-30 by Jarret Hooper 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 … Continue reading "Google Apps Script – How to Automatically Generate a Time From a Four Digit 24 hour Time in Google Sheets." ...

How to Hide a Row based on a Cell Value in Google Sheets with Filter or Google Apps Script.

Last Updated on 2022-02-25 by Yagi 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 … Continue reading "How to Hide a Row based on a Cell Value in Google Sheets with Filter or Google Apps Script." ...
Sections to Sheets Google Sheet Add-On

Sections to Sheets – Free Google Add-on That Separates Column Sections Into Individual Sheets for Page-by-Page Export or Printing (Updated January 2022)

Last Updated on 2022-01-08 by Jarret Hooper 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. Example 1 Let’s say … Continue reading "Sections to Sheets – Free Google Add-on That Separates Column Sections Into Individual Sheets for Page-by-Page Export or Printing (Updated January 2022)" ...
Paste without column widths.

Google Apps Script – Copy and Paste Cells While Retaining Column Width in Google Sheets.

Last Updated on 2021-10-30 by Jarret Hooper 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 … Continue reading "Google Apps Script – Copy and Paste Cells While Retaining Column Width in Google Sheets." ...

Google Apps Script – Disable Enable Submit Button in Sidebar

Last Updated on 2021-10-30 by Jarret Hooper Google Apps Script, Javascript, Jquery, HTML I was working on a Google Apps Script project lately in Google Sheets that set up parameters in a sidebar and then ran the process once the user clicked the “Submit” button. The problem was that the process was taking a while … Continue reading "Google Apps Script – Disable Enable Submit Button in Sidebar" ...

Google Apps Script – How to create Javascript and CSS files for a Sidebar Project in Google Apps Script

Last Updated on 2021-10-30 by Jarret Hooper 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 … 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. 

Last Updated on 2021-10-30 by Jarret Hooper 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 … 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

Last Updated on 2021-10-30 by Jarret Hooper 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. … 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

Last Updated on 2021-10-30 by Jarret Hooper 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 … 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

Last Updated on 2021-10-25 by Jarret Hooper 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 … 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

Last Updated on 2021-10-25 by Jarret Hooper 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 … Continue reading "How to Create a Simple YouTube Download Program with a Progress Indicator in Python 3 with pytube" ...

How to Check a User’s Home Directory for a Folder – Python 3

Last Updated on 2021-10-24 by Jarret Hooper Python 3, OS, Example in Windows 10 If you are creating a program for a user where you want to store or use a file in the users home directory, it is not as easy as simply preparing a fixed file location like: C:Usersyagisanatode Because if another user … Continue reading "How to Check a User’s Home Directory for a Folder – Python 3" ...

How do I download YouTube videos with Python 3 using Pytube?

Last Updated on 2021-10-24 by Jarret Hooper Python 3, pytube 9.0.6 in Windows 10.  pytube is a very easy to use light-weight library that you can use to download YouTube Videos. Installation can be achieved in the terminal or command prompt with pip: pip install pytube To download a video from YouTube you don’t really … Continue reading "How do I download YouTube videos with Python 3 using Pytube?" ...

How do I reverse the Rows in A Column in Google Sheets? (Updated January 2022)

Last Updated on 2022-01-07 by Jarret Hooper 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 … Continue reading "How do I reverse the Rows in A Column in Google Sheets? (Updated January 2022)" ...

How to Display an Entry in a Label – Tkinter Python 3

Last Updated on 2021-10-24 by Jarret Hooper Python 3, Tkinter 8.6. GUI examples in Windows 10 Probably one of the most common things to do when using a Graphical User Interface (GUI) is to display something from an entry from the user. Below is a simple example that allows the user to input text in … Continue reading "How to Display an Entry in a Label – Tkinter Python 3" ...

How to Center the Main Window on the Screen in Tkinter with Python 3

Last Updated on 2021-10-24 by Jarret Hooper Python 3, Tkinter 8.6. GUI examples in Windows 10 When your first window loads in Tkinter it will generally appear slightly offset from the top left-hand corner of the screen. This is a fairly counter-intuitive location and most of the GUI driven programs that I run usually open … Continue reading "How to Center the Main Window on the Screen in Tkinter with Python 3" ...

How Do I Change the Size and Position of the Main Window in Tkinter and Python 3

Last Updated on 2021-10-24 by Jarret Hooper Python 3, Tkinter 8.6. GUI examples in Windows 10 When you create your first window in Tkinter, you can set it’s starting size and position on the screen by using the geometry method. When using this method, note that it only provides the window with the size and … Continue reading "How Do I Change the Size and Position of the Main Window in Tkinter and Python 3" ...

Add the Current Date to a Sheet When Data Is Added So That The Date Does Not Change(Static) – Google Sheets (Updated January 2022)

Last Updated on 2022-01-07 by Jarret Hooper 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() … Continue reading "Add the Current Date to a Sheet When Data Is Added So That The Date Does Not Change(Static) – Google Sheets (Updated January 2022)" ...

How to Get Something from Google Sheets and Display it in the Sidebar in Google Apps Script (Updated January 2022)

Last Updated on 2022-01-07 by Jarret Hooper Google Apps Script: SpreadsheetApp, getUI, HTMLservice,  What if you want to get a value or a range from Google Sheets and show it in your sidebar using Google Apps Script? First, you will need to get the value or range by using Google’s server-side script. Then you will … Continue reading "How to Get Something from Google Sheets and Display it in the Sidebar in Google Apps Script (Updated January 2022)" ...

Add the Current Date When Data is Added to a Cell(Dynamic) – Google Sheets

Last Updated on 2021-10-25 by Jarret Hooper Google Apps Script: NOW, TODAY A colleague of mine had recently asked me if there was a way to automatically display the day’s date when a cell has data in it without having to use Google Apps Script. My answer: “Why yes, there is Jim.” There are two … Continue reading "Add the Current Date When Data is Added to a Cell(Dynamic) – Google Sheets" ...

Google Apps Script – Iterating Through Ranges in Sheets the Right and Wrong Way

Last Updated on 2021-10-25 by Jarret Hooper Google Apps Script: SpreadsheetApp, getRange, getLastRow, getDataRange I was trying to rush out some Google Apps Script code to deal with a task on Google Sheets recently. Basically, I had to search through a heap of data and find certain values and do something too them. My column … Continue reading "Google Apps Script – Iterating Through Ranges in Sheets the Right and Wrong Way" ...

Skewed Random Range in Google Sheets (RANDBETWEEN)

Last Updated on 2021-10-25 by Jarret Hooper Google Sheets: RANDBETWEEN The Standard RANDBETWEEN We can achieve a random range of numbers in Google Sheets by using the RANDBETWEEN formula. The formula is quite simple to use. Just add your start range and your end range: =RANDBETWEEN(start range, end range) For example, if I want to … Continue reading "Skewed Random Range in Google Sheets (RANDBETWEEN)" ...
Combinations non repeating

How do I Randomly Assign People to a Group Without Doubling Up on the Same Group in Google Sheets

Last Updated on 2021-10-25 by Jarret Hooper Google Sheets: TRANSPOSE, arrays, Randomize Ranges Random Combinations Without Repetition Using Arrays, TRANSPOSE and Randomize Ranges Say you have a list of  30 people and a 30 list of groups. After every half hour for six hours the people must change to another group, but they cannot go … Continue reading "How do I Randomly Assign People to a Group Without Doubling Up on the Same Group in Google Sheets" ...

VLOOKUP Left in Google Sheets with INDEX and MATCH

Last Updated on 2021-10-25 by Jarret Hooper Google Sheets: INDEX, MATCH, VLOOKUP Ever been faced with an issue where you can’t move your column to the far left in order to Vlookup? I just came across this issue recently. The Problem – Vlookup I needed to add teachers to a list of students who had … Continue reading "VLOOKUP Left in Google Sheets with INDEX and MATCH" ...

Copy and paste ranges in excel with OpenPyXl and Python 3

Last Updated on 2021-10-24 by Jarret Hooper OpenPyXl is a Python open library that allows you to read and write Microsoft Excel files. Specifically, the ‘*.xlsx’ file extension. It helps you to create programs to create and modify files and automate your processes in excel. NOTE: This post requires that you have some knowledge of … Continue reading "Copy and paste ranges in excel with OpenPyXl and Python 3" ...
Google Add-On, Spacer e.g.

Spacer – Free Google Add-On That Spaces Rows by Column Category (Updated January 2022)

Last Updated on 2022-01-06 by Jarret Hooper What is Spacer? Spacer is a Free Google Add-On that creates row spaces by column categories. I use it as an academic administrator to create a space or two at the end of each class section to create a visual break between sections. This makes it easier for my … Continue reading "Spacer – Free Google Add-On That Spaces Rows by Column Category (Updated January 2022)" ...

Can I modify Google Sheets with code? [updated January 2022]

Last Updated on 2022-01-05 by Yagi Google Apps Script and the Google Suite Updated January 2022 You sure can. Google has a fully supported script editor that you integrate with your Sheets, Docs, Forms, Slide, Gmail, Calendar and pretty much every aspect of the Google Suite. Its name: Google Apps Script. Google Apps Script allows … Continue reading "Can I modify Google Sheets with code? " ...

How do I lock certain cells in a formula in Google Sheets?

Last Updated on 2021-10-25 by Jarret Hooper You know, I’m pretty confident in my use of Google Sheets. I use a lot of its advanced features and formulas in my daily work. But one thing was bugging me. Every time I created a formula and then grabbed and dragged (or double-clicked) that little box in … Continue reading "How do I lock certain cells in a formula in Google Sheets?" ...

No post found