The Blog

*You can access the classic feed here.

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

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

You’ve probably come to this page feeling pretty frustrated that you’ve received a link to a Google Sheet, Google Slide or Google Doc in an email or found a link to one of these documents in a website or even one of my tutorials, but you just can’t edit it. You can’t update the text … Continue reading "Why can’t I edit this Google Sheet that was shared with me??!!! 😠" ...

Google Sheets Beginners: Getting Help (44)

Google Sheets Beginners: Getting Help (44)

One of the biggest revelations that I came across that really changed my whole view of learning was that learning how to ask the right questions or know where to get help completely transforms how you learn. Knowing how to get help quickly and efficiently in Google Sheets is really going to cut away a … Continue reading "Google Sheets Beginners: Getting Help (44)" ...

Google Sheets Beginners: Understanding The Explore Tool (43)

Google Sheets Beginners: Understanding The Explore Tool (43)

The Google Sheets team seem to come out with more and more great tools to help us do more, help us think creatively about how we display our data and improve our efficiency. On the wave of machine learning, it seems that the Google Sheets devs are making our lives just that little bit easier … Continue reading "Google Sheets Beginners: Understanding The Explore Tool (43)" ...

Google Sheets Beginners: Add-ons (42)

Google Sheets Beginners: Add-ons (42)

Sometimes you get to a point in your Google Sheets career where the vast array of tools that Google Sheets is not quite enough. Maybe you want to shortcut a process but don’t have time to learn or write Google Apps Script to do it. Well, the good news is that there is a huge … Continue reading "Google Sheets Beginners: Add-ons (42)" ...

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

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

Carelessly left behind Google Apps Script time triggers can be greedy little blighters that can ultimately end in a whole lot of noggin scratching when your scheduled scripts decide not to run all of a sudden. Then there is a whole lot of house cleaning to remove all those time triggers you couldn’t be bothered … Continue reading "How to programmatically schedule weekly time triggers between two dates in Google Apps Script" ...

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

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

If you have made it to this post you are probably a little frustrates about why your Google Apps Script time triggers are not running on or around the times that you have designated. Of course, you could simply just be a well-oiled machine and want to know how to update the time zone of … Continue reading "Help! My time triggers are not in sync! : How to update your Google Apps Script project time zone." ...

Google Sheets Beginners: Using Themes (41)

Google Sheets Beginners: Using Themes (41)

You don’t have to do all the hard work of creating all the formatting for your Google Sheet data, pivot tables and charts. Google Sheets actually comes with editable set themes that can quickly make your data look coordinated and stylish with a click of a few buttons. Watch this video on YouTube. Contents 00:00 … Continue reading "Google Sheets Beginners: Using Themes (41)" ...

Google Sheets Beginners: Charts (40)

Google Sheets Beginners: Charts (40)

A top data presentation is all about displaying your data in an easy to understand and memorable way. This is why being able to quickly create charts with your Google Sheets data is an essential tool. In this video tutorial, I’ll cover the basics of how to build a number of common chart types in … Continue reading "Google Sheets Beginners: Charts (40)" ...

Get the Occurrence of a Value in a Range as a Percentage in Google Sheets

Get the Occurrence of a Value in a Range as a Percentage in Google Sheets

Finding out how frequently something occurs compared to the rest of the data in a range is a pretty common task in Google Sheets. You will use it to find the frequency of grades in a grade book, the number of sales of an item compared to your total sales or the frequency of choices … Continue reading "Get the Occurrence of a Value in a Range as a Percentage in Google Sheets" ...

Google Sheets Beginners: Filter (38) & Filter View (39)

Google Sheets Beginners: Filter (38) & Filter View (39)

In these video tutorials, we will cover the Filter tool in Google sheets. Filter You can set up a specialised view of your Google Sheet data that will let you sort, or filter out unwanted data in any column with the Filter tool. This tool can pack a punch. You can sort different columns alphabetically, … Continue reading "Google Sheets Beginners: Filter (38) & Filter View (39)" ...

Create custom prefilled Google Forms links in custom emails with Google Apps Script

Create custom prefilled Google Forms links in custom emails with Google Apps Script

Recently I raised a support ticket with a tech company I was subscribed to where we were trying to resolve an integration issue I had with their service. Once we had it all resolved they followed up with a feedback form. That feedback form just happened to be a Google Form. Great, that’s cool. But … Continue reading "Create custom prefilled Google Forms links in custom emails with Google Apps Script" ...

Google Sheets Beginners: Grouping Rows and Columns (36)

Google Sheets Beginners: Grouping Rows and Columns (36)

When your Google Sheet spreadsheet starts to grow in complexity it is often a good idea to group similar parts of your data so you can easily collapse and expand them as you need them. Fortunately, Google Sheets has your back with the Grouping tool. In this tutorial, we will look at how to group … Continue reading "Google Sheets Beginners: Grouping Rows and Columns (36)" ...

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

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

Just when you thought that copy and paste was a no-brainer you start working in Google Sheets spreadsheets and find that there is a little more here than meets the eye. The good news is that Google  Sheets has some great pasting types to help you copy or cut your data and put it in … Continue reading "Google Sheets Beginners: Cutting, Copying and Pasting ( + bonus complete guide) (35)" ...

The Monster Guide to Data Validation in Google Sheets: Free Course

The Monster Guide to Data Validation in Google Sheets: Free Course

In this tutorial, we will cover everything there is to know about Data Validation in Google Sheet. Why am I writing this tutorial? Well, there is a lot to cover for one, but I also find that there are a lot of snags and nuances to Data Validation in Google Sheets that make it easy … Continue reading "The Monster Guide to Data Validation in Google Sheets: Free Course" ...

Google Sheets Beginners: Data Validation (34)

Google Sheets Beginners: Data Validation (34)

Data validation helps you to control what your users enter into your cells. It also allows you to create dropdown menus for your users. In this tutorial, we will cover some basics on using Data Validation. Data validation can really help prevent your user from making errors and guide them towards good practice as well … Continue reading "Google Sheets Beginners: Data Validation (34)" ...

Google Sheets Beginners: Split text to columns (33)

Google Sheets Beginners: Split text to columns (33)

Have a list of names and need to separate the first and last name? Need to put a list of names in separate cells? Need to find a specific Sheet code in a website? Google Sheets has your back with Split text to columns. In this tutorial, we quickly and easily cover how to do … Continue reading "Google Sheets Beginners: Split text to columns (33)" ...

Google Sheets Beginners: Accessing the Keyboard Shortcuts  (32)

Google Sheets Beginners: Accessing the Keyboard Shortcuts (32)

Learning keyboard shortcuts are a must to improve performance when using Google Sheets. Urban myth or not, I remember hearing a story about a manager in the financial industry who took away his new charge’s mouse and made sure the rest of the team didn’t give them one. This shocking bit of tough-love was to … Continue reading "Google Sheets Beginners: Accessing the Keyboard Shortcuts (32)" ...

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

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

Ever wanted your users to simply click a button on your webpage and,💥, a file you saved on Google Drive is automatically downloaded for them? Here have a look at two examples: My Pic! My Folder.zip NOTE! Clicking the links above will automatically download the file described. This quick tool allows you to do just … Continue reading "Automatic download link for non-Google Workspace files in Google Drive" ...

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

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

I am currently working on a larger project at the moment that requires a lot of front-end wrangling. As a part of this project, I needed to create Button Items that are generated by the user from both an HTML select element for one section and, a comma-separated text input in another section. When a … Continue reading "Create Removable Item Buttons Generated From Select or Comma-separated Input elements with HTML, CSS and JS" ...

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

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

You can now work on Excel and Open Document Format (ODS) file right in Google Sheets without converting the file to a Google Sheets file. This makes Googles Sheets a power collaboration tool. In this tutorial, I’ll cover, How to upload an Excel sheet to your Google Drive 00:16 Adding an Excel file from your … Continue reading "Google Sheets Beginners: Editing Excel and ODS format files in Google Sheets (31)" ...

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

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

Yeap! That’s right. Google Sheets allows you to set custom page breaks for your Google Sheets tabs. So you can print exactly what you want on each page. What are custom page breaks? 00:00 How custom page breaks work 00:30 Turning custom page breaks off or on 00:58 How to move custom page break lines … Continue reading "Google Sheets Beginners: You Can Create Custom Page Breaks for Printing and Exporting (30)" ...

Google Sheets Beginners:  Printing your Google Sheet (29)

Google Sheets Beginners: Printing your Google Sheet (29)

All the options available to you when printing your Google Sheet can be quite overwhelming. Find out everything you need to know about printing your Google Sheets documents, tabs and selected ranges. In this tutorial, I’ll cover, Ways to access the print button 00:05 Choosing your paper size and orientation 00:30 Using the scale function … Continue reading "Google Sheets Beginners: Printing your Google Sheet (29)" ...

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

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

One of the benefits of using Google Sheets is that it can quickly be downloaded as different file types like Excel files (*.xlsx), Open Document Format **.ods), PDFs for reports or CSV or TSV for uploading onto other software systems. In this tutorial, I’ll cover, Why downloading in different formats can be useful. 00:15 How … Continue reading "Google Sheets Beginners: Exporting your Google Sheet as different file types (28)" ...

2020 – Year in Review

2020 – Year in Review

It’s always nice to a have a bit of a look back at the previous year to see how you went, what you’ve achieved, what you can work on and where you want to go in the future. 2020 was a rough one for everyone and it was no different for this goat 🐐. Some … Continue reading "2020 – Year in Review" ...

Create a custom automated Table of Contents for your Google Sheets tabs with Google Apps Script

Create a custom automated Table of Contents for your Google Sheets tabs with Google Apps Script

In this tutorial, we will cover creating a custom Table of Contents that lists your Google Sheets tabs on its own tab using Google Apps Script. This Table of Contents (TOC) will update whenever you open your sheet or choose to update it with a button. But Yagi! Can’t I just check the list of … Continue reading "Create a custom automated Table of Contents for your Google Sheets tabs with Google Apps Script" ...

Google Sheets Beginners: Trim Whitespace (27)

Google Sheets Beginners: Trim Whitespace (27)

Trim Whitespace removes all spaces in a text string, leaving just a single space between words. Search not working? This may be why. This is a quick and easy tool that you can apply to all of your cells. Watch this video on YouTube. If you enjoy the video and want to learn more about … Continue reading "Google Sheets Beginners: Trim Whitespace (27)" ...

Google Sheets Beginners: Workbook Edit Version History (26)

Google Sheets Beginners: Workbook Edit Version History (26)

Google Sheets keeps a detailed version history of your workbook. You can use this version history to track changes and identify when, where and who made a change. You can make a copy of an old version or revert your current sheet back to an old version. Find out how to check your workbooks changes … Continue reading "Google Sheets Beginners: Workbook Edit Version History (26)" ...

Google Sheets Beginners:  Cell History (25)

Google Sheets Beginners: Cell History (25)

Google Sheets Did you know you can peek in on the history or each one of your Google Sheets cells? via GIPHY Find out how in this short video tutorial: Watch this video on YouTube. If you enjoy the video and want to learn more about how to use Google Sheets, check out the Youtube … Continue reading "Google Sheets Beginners: Cell History (25)" ...

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

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

Your very own Google Sheets Recipe Template For my culture, the Christmas season has come. With it comes all the delightful homemade goodies and snacks you get for over the Christmas season. I do like to cook and over the years I have either inherited or discovered some great recipes. However, one of the problems … Continue reading "Use Google Sheets to store your Recipes to automatically change Batch Sizes and Recipe Amount by Weight" ...

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

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

Google Sheets Google Sheets provides an email notification tool that allows you to get email notifications for changes to a Google Sheet or when a Google Form that is connected to your Google Sheet receives a submission. You can set your email notifications to be received when each change occurs or as a daily summary. … Continue reading "Google Sheets Beginners: Getting Email notifications on changes and form submits (24)" ...

Google Sheets Beginners: Protecting cells, ranges and sheet tabs (23)

Google Sheets Beginners: Protecting cells, ranges and sheet tabs (23)

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. In this tutorial, I’ll cover, Why you may need to protect certain parts of your Google Sheet 00:02 Protecting a range of cells. … Continue reading "Google Sheets Beginners: Protecting cells, ranges and sheet tabs (23)" ...

Google Apps Script: Store a Unique User Key from a User Accessing your WebApp

Google Apps Script: Store a Unique User Key from a User Accessing your WebApp

Google Apps Script: WebApp In this tutorial, we will cover how you can get a unique temporary access key from a user accessing your WebApp that lasts for 30 days. Temporary access keys allow you to track users as they use your WebApp over time while still providing anonymity to the user by providing only … Continue reading "Google Apps Script: Store a Unique User Key from a User Accessing your WebApp" ...

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

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

Google Sheets If you are anything like me, you may find yourself regularly tidying up imported datasets or data from users that contain duplicates that could potentially corrupt your data analysis. Fortunately, Google Sheets has a quick tool to help you to remove duplicates with a simple click of the button. In this tutorial, I’ll … Continue reading "Google Sheets Beginners: Remove Duplicates from a Range (22)" ...

Google Sheets Beginners: Linking (21)

Google Sheets Beginners: Linking (21)

Google Sheets  Find out how to effectively link to websites, your own Google docs and within your very own Google Sheets. In this tutorial, I’ll cover,  Types of linking in Google Sheets 00:00 Simple copy and paste approach 00:11 How the link is displayed and it’s properties 00:20 Add a link to an existing cell … Continue reading "Google Sheets Beginners: Linking (21)" ...

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

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

Google Apps Script: WebApp, HtmlService, LockService; Google Sheets In this tutorial, we are going to create an interactive story chain app that we can embed into a Google Site or your own site like WordPress. What’s a chain story, Yagi?  Maybe you did this in school. Someone wrote the first part of a story. You … Continue reading "Creating an embedded interactive Chain Story app with Google Apps Script and Google Sheets" ...

Google Sheets Beginners: Sharing your Sheet (20)

Google Sheets Beginners: Sharing your Sheet (20)

The ability to share and co-edit documents live is a powerful tool for organisations. In this tutorial, we will dive in deep on sharing a Google Sheet. In this tutorial, I’ll cover, What is sharing? Who can you share your Google Sheet with and what permissions can you give them? 00:00 How to share your … Continue reading "Google Sheets Beginners: Sharing your Sheet (20)" ...

Google Sheets Beginners: Communicating with Comments (19)

Google Sheets Beginners: Communicating with Comments (19)

Google Sheets One of the beauties of Google Sheets is collaboration. In this tutorial, we will cover how to insert comments in Google Sheets so that you can target cells and ranges for discussion with comments. In this tutorial, I’ll cover, Uses of comments in Google Sheets. 00:16  Who can access comments (Permissions)? 01:08 How … Continue reading "Google Sheets Beginners: Communicating with Comments (19)" ...

Google Sheets Beginners – Alternating Colours (17)

Google Sheets Find out how to add alternating colours in your rows in Google Sheets. Adding alternating colours in your spreadsheets can really help your viewers to reference data accurately while also allow your spreadsheet to look quite smart and professional. Google Sheets has a quick and easy alternating colours tool to help you to … Continue reading "Google Sheets Beginners – Alternating Colours (17)" ...

Google Sheets Beginners – Inserting Images (18)

Google Sheets Beginners – Inserting Images (18)

Google Sheets  Find out how to insert images into your Google Sheets. In this tutorial, we will cover the different ways that you can insert images into Google Sheet. Adding images can be useful for styling up your Google Sheet or providing a visual explanation of some of your data. You can also add images … Continue reading "Google Sheets Beginners – Inserting Images (18)" ...

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

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

Google Sheets As we increasingly start to work from home and work with clients globally, it is vital that we prepare our Google Sheet for the country that our users and clients are from. In this tutorial, we will cover how to quickly change your current Google Sheet’s timezone, date and currency for the entire … Continue reading "Google Sheets Beginners – Change Your Current Workbook’s Timezone Date & Currency (16)" ...

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

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

Google Apps Script / Javascript I just had a recent email from a reader who asked how to tidy up a user’s inputted name from say, a Google Form so that all the first letters of each work in the name are capitalised in the same way that the Google Sheets Proper function does. I … Continue reading "Google Apps Script – Javascript: Emulate the “Proper” Google Sheets Function" ...

Google Sheets Beginners  – Spell Check(15)

Google Sheets Beginners – Spell Check(15)

Google Sheets Did you know that you can use spell check in Google Sheets? No? Not many people do. Cleaning up your spelling in your Google Sheets can really give your work an edge and save you from any embarrassing moments when you using your data to present. While Google Sheets spreadsheets is quite primitive … Continue reading "Google Sheets Beginners – Spell Check(15)" ...

Google Sheets Beginners – Creating Sheet Tabs (14)

Google Sheets Beginners – Creating Sheet Tabs (14)

Google Sheets Sometimes just one Google Sheets tab is not enough. Maybe you want to tidy up your data, create unique insights in a separate tab or generate a spectacular graphic dashboard or all your daw data, knowing how to create a Google Sheets tab is going to be a vital part of your success … Continue reading "Google Sheets Beginners – Creating Sheet Tabs (14)" ...

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

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

Google Apps Script: Web Apps Did you know that you can easily create an interactive webpage web app that you can embed in your own website or your Google Workspace domain for internal use with your organisation with Google Apps Script? Perhaps you want to create a small online app using resources you have in … Continue reading "Google Apps Script: How to create a basic interactive interface with Web Apps" ...

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

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

Google Sheets Sometimes it is helpful to hide and unhide columns in Google Sheets. You may have some completed tasks that you want to indicate as completed to your users or you may want to hide columns that you have used for working. Whatever, the case hiding rows in columns is a helpful trick to … Continue reading "Google Sheets Beginners – Hide and unhide columns and rows(13)" ...

Google Sheets Beginners: Insert Columns and Rows (12)

Google Sheets Beginners: Insert Columns and Rows (12)

Google Sheets: Insert and Shift Columns and Row Even if you are a veteran Google Sheets users you might find a great little tip in this tutorial. Here we will cover how to insert columns and rows in Google Sheets spreadsheets. Oh, and how to cheat at connect 4. In this tutorial, I’ll cover, Inserting … Continue reading "Google Sheets Beginners: Insert Columns and Rows (12)" ...

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

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

Google Sheets: WEEKNUM, WEEKDAY, FILTER, VLOOKUP, TODAY One really helpful metric to check performance is to compare the current weeks worth of data against the previous weeks. I have had need of this in all sorts of projects such as: Comparing sales performance from previous weeks to current. Comparing hours worked on different tasks from … Continue reading "How to compare current week’s data with previous week’s data in Google Sheets" ...

Google Sheets Beginners: Freeze Rows and Columns(11)

Google Sheets Beginners: Freeze Rows and Columns(11)

Google Sheets: Freeze Have you ever wanted to lock those pesky rows of headers or columns so that when you scroll the titles are still there? In Google Sheets, this is done easily. You can either got to View > Freeze and select your preferred depth of freeze. Or you can do my favourite approach, click and … Continue reading "Google Sheets Beginners: Freeze Rows and Columns(11)" ...

Google Sheets Beginners: Paint Format (10)

Google Sheets Beginners: Paint Format (10)

Google Sheets: Paint Format Want to know a quick way to update a cell or range with an already existing format in your Google Sheets? Paint Format is here to the rescue. This versatile tool will help you to quickly replicate font, alignment, border, rotation and other styling formats in your Google Sheet. In this … Continue reading "Google Sheets Beginners: Paint Format (10)" ...

Creating Unique Ranges from 2D Arrays in Google Apps Script

Creating Unique Ranges from 2D Arrays in Google Apps Script

Google Apps Script, Google Sheets, Javascript  If you have ever done any work in Google Sheets you might have come across the incredibly useful UNIQUE function. This function can take a single row or multiple rows and generate a list of unique cell values. Sometime you might have a use case where you need to … Continue reading "Creating Unique Ranges from 2D Arrays in Google Apps Script" ...

Google Apps Script: How to Add and Remove Editors to a Google Sheet based on Sheet Data

Google Apps Script: How to Add and Remove Editors to a Google Sheet based on Sheet Data

Google Apps Script, Google Sheets, Javascript Have you ever wanted to have a Google Sheet available for only those users who need to edit and then once they are done, take their edit permissions away to maintain the integrity of the sheet? I know that I have come across these conditions a number of times … Continue reading "Google Apps Script: How to Add and Remove Editors to a Google Sheet based on Sheet Data" ...

Google Apps Script: How to Add and Remove Editors to A Google Sheet with Code

Google Apps Script: How to Add and Remove Editors to A Google Sheet with Code

Google Apps Script: SpreadsheetApp,  addEditor/s, removeEditor/s, alert. Javascript: try…catch, forEach, join, push, template literal. Google Sheets In this tutorial, we will go over the basics of adding users as Editors to Google Sheets with Google Apps Script. We’ll go through the process step-by-step, starting with two very basic codes and then progress on to error … Continue reading "Google Apps Script: How to Add and Remove Editors to A Google Sheet with Code" ...

Google Apps Script: 3 Google Sheets Custom Functions to Allocate Items from a Column and Spread Them Out into a New Column N Times

Google Apps Script: 3 Google Sheets Custom Functions to Allocate Items from a Column and Spread Them Out into a New Column N Times

Google Apps Script, Google Sheets, Javascript: Spread operator, fill, new Array, forEach, isArray, flat, every, slice, length, typeOf Imagine if you have a set of data, say, Cat, Dog, Goat and you want to create a new column where each item is repeated 3 times. Essentially what we are doing here is spreading out our original … Continue reading "Google Apps Script: 3 Google Sheets Custom Functions to Allocate Items from a Column and Spread Them Out into a New Column N Times" ...

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

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

Google Apps Script You’ve probably landed on this page because, like most of us, the first time we ran a Google Apps Script we came across a gauntlet of ever-increasing warnings asking us if we truly want to run this script. That’s cool. This is just Google trying to protect users. Unfortunately, it does make … Continue reading "Running Google Apps Script for the First Time: What’s with all the Warnings!" ...

Google Apps Script: Automated PDF Certificated Creator That Is Emailed to the Attendee

Google Apps Script: Automated PDF Certificated Creator That Is Emailed to the Attendee

Google Apps Script: DriveApp, PropertiesService, SpreadsheetApp, GmailApp, SlidesApp. Google Slides, Google Sheets. Have you ever wondered how people create certificates en mass and send them out automatically? You are in the right place. In this tutorial, we are going to: Create Certificates of Attendance for multiple attendees using Google Slides and a list of attendees … Continue reading "Google Apps Script: Automated PDF Certificated Creator That Is Emailed to the Attendee" ...

Google Sheets Beginners: Conditional Formatting (09)

Google Sheets Beginners: Conditional Formatting (09)

Google Sheets: Conditional Formatting Conditional formatting is a powerful tool that helps you to highlight cells that meet a certain value. You can format cells, that contains: date ranges Number ranges or specific numbers Exact text or partial text Once you have identified your condition you can change the cell’s background colour, font-weight or font … Continue reading "Google Sheets Beginners: Conditional Formatting (09)" ...

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

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

Google Slides – Beginners I always enjoy finding new ways to create documents using the Google Suite. One project I have recently worked on has been to build a Certificate of Attendance template that I can update for non-grades courses or conferences. Why do this using a GSuite package? Well, 1, if you haven’t noticed, … Continue reading "Create a Student Certificate of Attendance with Google Slides and Export it as a PDF or Print it" ...

Google Sheets Beginners: Borders (07 & 08)

Google Sheets Beginners: Borders (07 & 08)

In this next part of our Google Sheets Beginners Youtube course, we take a look at borders. Borders can provide a huge boost to the readability of both your digital Google Sheet and your printed spreadsheets. Not only that, but careful use of border styles, weight and colour can help guide your reader to the … Continue reading "Google Sheets Beginners: Borders (07 & 08)" ...

Google Sheets Beginners: Backgrounds (06)

New to Google Sheets and spreadsheets in general? Find out everything you need to know about inserting a background cell colour efficiently. Changing cell colours in Google Sheets can really make your spreadsheets pop! In this tutorial, I’ll cover, 1. How to insert a background into a single cell. 2. Inserting a background into a … Continue reading "Google Sheets Beginners: Backgrounds (06)" ...

Google Sheets Beginners: Merging Text (5)

Google Sheets Beginners: Merging Text (5)

Google Sheets: Merging Text New to Google Sheets and spreadsheets in general? Find out everything you need to know about merging cells inside a cell efficiently. Merging cells can really increase your design game. In this tutorial, I’ll cover, 1. Merging Across rows. 2. Merging Across columns. 3. Merging mixed ranges. 4. Highlight some things … Continue reading "Google Sheets Beginners: Merging Text (5)" ...

Google Sheets Beginners: Formatting Text Inside Cell (4)

Google Sheets Beginners: Formatting Text Inside Cell (4)

Google Sheets: Text Formatting In this tutorial, I am going to show you some tricks on how to format text inside cells that most spreadsheet users have no idea about. If you are new to Google Sheets or just spreadsheets in general, then this is for you. In this tutorial, I’ll cover: In this tutorial, … Continue reading "Google Sheets Beginners: Formatting Text Inside Cell (4)" ...

Google Sheets Shorts: Text Formatting (3)

Google Sheets Shorts: Text Formatting (3)

Google Sheets: Text Formatting If you a new to Google Sheets or spreadsheets in general, this short video is for you! We’ll go formatting text in Google Sheet. Including: Shorts cuts to access bold, italic, underline and strikethrough. Changing the font type. Changing the font colour. Changing the size of the font. Then I’ll wrap … Continue reading "Google Sheets Shorts: Text Formatting (3)" ...

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

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

Google Apps Script: SpreasheetApp, DocumentApp, DriveApp; Google Sheets, Google Docs If you have ever worked in LibreOffice or Microsoft Excel you will probably be familiar with the mail merge. Traditionally, mail merge is used to create multiple versions of a document and snail-mail them to someone. These days, we don’t often use the snail mail … Continue reading "Google Apps Script: Create multiple versions of a document based on Google Sheet Data and a Google Doc Template (Mail Merge)" ...

Google Sheets Shorts: Align and Wrap Text (2)

Google Sheets Shorts: Align and Wrap Text (2)

Google Sheets: Align and Wrap Text If you a new to Google Sheets or spreadsheets in general, this short video is for you! We’ll go over aligning text in cells vertically and horizontally. Then we’ll show you how to wrap long pieces of text so that they fit neatly in the cell. Lastly, we will … Continue reading "Google Sheets Shorts: Align and Wrap Text (2)" ...

Google Sheets Shorts: Sorting Data (1)

Google Sheets Shorts: Sorting Data (1)

Google Sheets: sorting data.  In this Google Sheet tutorial, we’ll go through the nuances of using sort. You’ll find out how to sort by the sheet tab and by a selected range. Then, we will show you how sort works with text numbers and a mix of the two before diving into how to sort … Continue reading "Google Sheets Shorts: Sorting Data (1)" ...

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

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

Google Sheets: Columns and Rows If you are new to Google Sheets or spreadsheets in general. This is the post for you. In this tutorial, we walk through 3 ways to change the size of Columns and Rows. We also throw in some tips on change rows and columns in bulk to increase your workflow, … Continue reading "Google Sheets Shorts: Change the Column and Row Size (0)" ...

Google Apps Script: Extract Specific Data From a PDF and insert it into a Google Sheet

Google Apps Script: Extract Specific Data From a PDF and insert it into a Google Sheet

Google Apps Script: DriveApp, Advanced Drive Service, SpreadsheetApp,  DocumentApp Javascript: spread operator, map, regex Ah, bureaucracy at its finest.  Have you ever asked for a list from a client, another department or agency and instead of saving into a file that might even be considered marginally useful, they give it to you as a pdf. … Continue reading "Google Apps Script: Extract Specific Data From a PDF and insert it into a Google Sheet" ...

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

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

Google Sheets: DAYS, NETWORKDAYS.INTL, NETWORKDAYS, VALUE A very common spreadsheet task is to get the total count of the days between two dates. We might need this information to: Find the number of days absent of staff or students. Find the total days worked or attending a class. Some times we need to avoid including … Continue reading "Count the Number of Selected Days of the Week in Google Sheets" ...

Google Apps Script: Upload grades into a Google Classroom Coursework Assignment

Google Apps Script: Upload grades into a Google Classroom Coursework Assignment

Google Classroom, Google Sheets, Google Apps Script: Classroom API, SpreadsheetApp, map, forEach At the date of writing this the world is in the midst of a worldwide pandemic – the Corona Virus. For educators and their Igors administrators like me, it has been an incredibly busy couple of months. Many of us have quickly sort … Continue reading "Google Apps Script: Upload grades into a Google Classroom Coursework Assignment" ...

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

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

Google Apps Script: Google Docs, Document App. Javascript: Spread syntax, Set, IndexOf, Substring So here is the scenario, imagine you have a big Google Doc. You want to get a list of information from the document that you have noticed are between two sets of characters. Maybe something like this: You want to grab all … Continue reading "Google Apps Script: Get Google Doc Text Based on Reference Characters" ...

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

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

Google Sheets: ROUND, SPLIT, COUNTA Have you ever woken up in the middle of the night thinking – phawh! I’d really like to know how many words there are in a paragraph – Or stopped mid-chew on some arbitrary item carelessly left in the paddock by the boss and thought – geez! If there was … Continue reading "Google Sheets: Get the Number of Words or Sentences in a Cell (Paragraph)" ...

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

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

Google Apps Script: V8 engine, map, filter, reduce, includes, 2d arrays, matrix Have you ever wanted to delete or add columns in a Google Sheet, based on another set of Sheet data? I know I have. There have been a number of instances where I wanted to insert new columns or removed unused columns in … Continue reading "Google Apps Script: Add and removed Google Sheets columns based on a search array" ...

Google Sheets: How to use OR inside a FILTER

Google Sheets: How to use OR inside a FILTER

Google sheets: FILTER, booleans, NOT, ISBLANK Have you ever had a set of data in Google Sheets where you want to use FILTER to grab rows based on if a condition in one column is met or if a condition is another column is met? Maybe you got frustrated that the OR function did not … Continue reading "Google Sheets: How to use OR inside a FILTER" ...

Google Apps Script Course – Part 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

Google Apps Script, Google Sheets, SpreadsheetApp, 2d arrays <<Part 2                                         <<Intro>> In our last tutorial, we grabbed the Google Sheets count data from a single rating survey question. We then displayed it two ways; horizontally with … Continue reading "Google Apps Script Course – Part 3: 2D Array Data Transformation of Multiple Question Items Data to Total Count of Rating Choices in Google Sheets" ...

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

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

Google Apps Script, Google Sheets, SpreadsheetApp, 2d arrays <<Part 1                                         <<Intro>> In our previous tutorial, we created a 2d array of count values for each item chosen in a survey form in Google Apps Script. In … Continue reading "Google Apps Script Course – Part 2: 2D Array Data Transformation of Single Question Item Data to Total Count of Rating Choices in Google Sheets" ...

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

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

Google Apps Script, Google Sheets, SpreadsheetApp, 2d arrays Note! This course requires a rudimentary understanding of very basic Javascript concepts and of what an array is. Nevertheless, if you want to come in blind you should be able to pick things up with a bit of Googling. Alternatively, if you just want the neat code snippets … Continue reading "Google Apps Script Course – Intro: 2D Array Data Transformation for Google Sheets" ...

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

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

Google Apps Script, Google Sheets Have you ever experienced this warning error when trying to set values or paste a range of values back into your Google Sheet with Google Apps Script? The number of columns in the data does not match the number of columns in the range. The data has 6 but the … Continue reading "Google Apps Script: Filling out your column data to match the number of columns in a range" ...

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

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

Google Sheets, Google Apps Script: SpreadsheetApp, PropertiesService, Binary Search, onOpen, Button Ever had to open a huge data entry spreadsheet an all you want to do is enter your data and move onto another task? Instead, you have to waste precious time navigating all the way down to the bottom of the page to add … Continue reading "How to Automatically Navigate to the First Empty Row in a Google Sheet using Google Apps Script" ...

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

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

Google Forms, Google Sheets, Google Apps Script: SpreadsheetApp, FormApp In this tutorial, we are going to build a simple seat booking Google Form. It will contain a registeree’s name and the session that they wish to attend. Each time a registeree submits a request to book a seat for a session, that seat is taken … Continue reading "Create a seat booking form with Google Forms, Google Sheets and Google Apps Script" ...

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

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

Google Sheets: COUNTIF, ARRAYFOMULA, FILTER, REGEXMATCH, REGULAR EXPRESSIONS (RE2) Okay, wait! Stop!… …I know the byline contained regular expressions, but I promise you I won’t just leave you with the formula for you to figure out your own jumbled mass of jibberish characters or inundate you with a list of commands and leave to attempt … Continue reading "Google Sheets: Counting and Filtering Rows where Cells Contain Particular Values." ...

Google Apps Script: How to force subscribe Google Group Members to a Google Calendar for G Suite

Google Apps Script: How to force subscribe Google Group Members to a Google Calendar for G Suite

Google Apps Script, G Suite Admins, Google Calendar, Calendar API,  AdminDirectory, GroupsApp  Note! This article is for G Suite users and admins. You won’t be able to apply the same code to Consumer accounts.  Google Groups in  G Suite can be a really convenient and clean way to share users to drive folders and file locations, … Continue reading "Google Apps Script: How to force subscribe Google Group Members to a Google Calendar for G Suite" ...

How to stop getting Google document requests to edit from users outside your organisation from publicly viewable files.

How to stop getting Google document requests to edit from users outside your organisation from publicly viewable files.

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 G Suite files like Docs, Sheets and Slides for students to access. For me, this was a pretty logical step. … Continue reading "How to stop getting Google document requests to edit from users outside your organisation from publicly viewable files." ...

Google Apps Script: Google Standard Color Palette Picker for Sidebar and Dialog Boxes in Google Sheets, Google Docs  and Google Slides

Google Apps Script: Google Standard Color Palette Picker for Sidebar and Dialog Boxes in Google Sheets, Google Docs and Google Slides

Google Apps Script: Dev Tools, Color Picker, Side Bar, Custom Prompt, HtmlService, onOpen, Sidebar, Dialog Box I wanted to update one of my free Google Add-on apps that works with colour. What I had is just the standard HTML color input element where the user selects from the palette and that hexadecimal colour code is … Continue reading "Google Apps Script: Google Standard Color Palette Picker for Sidebar and Dialog Boxes in Google Sheets, Google Docs and Google Slides" ...

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

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

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 the text and fill colours. I wanted … Continue reading "Google Apps Script: Hexadecimal Color Codes for Google Docs, Sheets and Slides Standard Palette" ...

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

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

Google Apps Script: isChecked(), switch, filter, map One Checkbox to Rule them All Now that’s a fantasy novel I could be my gums into. Have you ever created a Google Sheet projects where you could really use a select all checkbox (they call them ‘Tick-boxes’ in Google Sheets)? Sure you can copy a range of … Continue reading "Google Apps Script: Create a Select All Checkbox to Tick Checkboxes in Any Desired Range in Google Sheets" ...

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

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

Google Sheets: Intermediate Chart Building, workflow This is Part 3 of this Google Sheets Intermediate Project Course. However, this part of the course can also stand on its own.  If you are looking for part one or two, you can get it here: Part 1 || Part 2 In this tutorial, we will dive into … Continue reading "Google Sheets Intermediate Project: Comparing the Best Daily Currency Exchange Rates Between Two Agencies – XRP/USD Chart Example Part 3" ...

Google Apps Script: How to check if there is a Tick Box (Check Box) in a cell or range

Google Apps Script: checkbox, Google Sheets This is something  I came across today when working on a project. I needed my Google Apps Script to look at a cell or range and determine whether there is a checkbox in that Google Sheet cell. If there is, then my script would do something. Otherwise, I would … Continue reading "Google Apps Script: How to check if there is a Tick Box (Check Box) in a cell or range" ...

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

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

Google Sheets: ARRAYFORMULA, IFERROR, SPARKLINE, Conditional formatting, workflow. This is Part 2 of this Google Sheets Intermediate Project. If you are looking for part one, you can get it here: Part 1 This project compares two currency exchanges rates, USD-XRP, in two different exchange gateways Bitstamp and Gatehub. Contents Recap Stage 2 Highest Exchange Rate … Continue reading "Google Sheets Intermediate Project: Comparing the Best Daily Currency Exchange Rates Between Two Agencies – XRP/USD Example Part 2" ...

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

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

Google Sheets: FILTER, ARRAYFORMULA, UNIQUE, WEEKDAY, LEFT, MIN, MAX, IFERROR, Conditional formatting, workflow. In this tutorial, we are going to tackle some Intermediate level Google Sheets concepts including using functions like: FLITER ARRAYFORMULA MIN and MAX IFERROR SPARKLINE UNIQUE WEEKDAY LEFT We are then going to make our Google Sheet more presentable by doing some … Continue reading "Google Sheets Intermediate Project: Comparing the Best Daily Currency Exchange Rates Between Two Agencies – XRP/USD Example Part 1" ...

My live Google Form response in Google Sheets won’t update in another Sheet Tab that references them. What do I do?

My live Google Form response in Google Sheets won’t update in another Sheet Tab that references them. What do I do?

Google Sheets – Intermediate, Arrays, Form Data Picture this. You have set up your Google Sheet and attached your Google Form data to it so you might get a tab that looks a little something like this: You probably don’t want to mess with this tab because the Form is still live. Instead, you decide … Continue reading "My live Google Form response in Google Sheets won’t update in another Sheet Tab that references them. What do I do?" ...

Google  Apps Script – URL Fetch JSON data from an API and add it to a Google Sheet – Ripple API example

Google Apps Script – URL Fetch JSON data from an API and add it to a Google Sheet – Ripple API example

Google Apps Script – UrlFetchApp, SpreadsheetApp, Ripple API, Time Triggers I have been very fortunate of late to have the patronage of the Ripple XRP cryptocurrency community via XRP Tip Bot and Coil. This is no small part due to the support of user Recreational Rex from Twitter. Full disclosure here, I don’t really know … Continue reading "Google Apps Script – URL Fetch JSON data from an API and add it to a Google Sheet – Ripple API example" ...

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

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

Google Apps Script, SpreadsheetApp, Google Sheet, Iteration A not too uncommon task for Google Sheets users is to delete rows based on a cell’s values in a certain column. Often you don’t want to change the row position of the values so sorting the values and then deleting them in blocks is out. All you … Continue reading "Google Apps Script: Delete Rows Based on a Column’s Cell Value in Google Sheet" ...

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

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

Google Apps Script: Time Triggers, ClockTriggerBuilder, ScriptApp One of the most powerful things about Google Apps Script is the ability to automatically complete tasks with time-driven triggers. You can set up your code to run any one of your functions in your script by the minute, hour, day, week, or month. You can even set … Continue reading "Google Apps Scripts: Create Time Triggers to automatically send email task reminders from a Google Sheets check list" ...

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

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

Google Apps Script: SpreadsheetApp, Utilities, MailApp, UrlFetchApp, HtmlService, onOpen Ever wanted a quick way to send reminders to staff, letting them know that their task is overdue? I often have to provide Google Sheet checklists that administration team managers can crosscheck to see if their team members have complete a certain task. Reminding staff to … Continue reading "Google Apps Script: Create an HTML email reminder service from Google Sheet data." ...

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

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

Gsuite: Groups for Business, Gmail Note: This post is for those who have Gsuite admin permissions.  GSuite groups are a great bulk email tool. They can be used: To send announcements to a group. As a collaborative email for teams to communicate with each other. As an internal or public facing support email. To send … Continue reading "GSuite 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.

Google Sheets: How to create a task completion check sheet.

Google Sheet: IMPORTRANGE, IF, TODAY, conditional formatting, tick box, alternating colors, protect sheet and ranges, share sheet  Sometimes you have teams, staff or students who all need to complete reports and tasks by a certain date, but you need a way to keep track of when the reports are complete so that you can compile … Continue reading "Google Sheets: How to create a task completion check sheet." ...

Google Apps Script: Get the last row of a data range when other columns have content like hidden formulas and check boxes

Google Apps Script: Get the last row of a data range when other columns have content like hidden formulas and check boxes

Google Apps Script: getRange, getLastRow, getDataRange, spreadsheetApp – requires a basic understanding of GAS Finding the last row of a data range in Google Sheets using Google Apps Script is a pretty common task. We often do this to find the next available free row to insert new data or to copy an entire data … Continue reading "Google Apps Script: Get the last row of a data range when other columns have content like hidden formulas and check boxes" ...

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

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

Google Sheets, IFERROR, ISBLANK, IF Have you ever set up a Google Sheet with formulas that you drag all the way down to the bottom? See all those messy error values littering your otherwise immaculate spreadsheet when those formulas don’t reference a value? It sucks, right? What if I told you that you could hide … Continue reading "Google Sheets: How to hide formula error warnings where there is no data or the data divides by zero" ...

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

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

Google Sheets, Google Apps Script, GmailApp Ever wanted a quick way to give edit permissions to a user and then send them an email based on values in your Google Sheet? This task crosses my table several times a week. Whether it is sending reminders to complete a Google Sheets task or share a Google … Continue reading "Google Apps Script: Send Bulk Emails and Share a Google Sheet with One Button Click" ...

Google Apps Script: Basic Beginners Guide to Using Strings in Code

Google Apps Script: Basic Beginners Guide to Using Strings in Code

Google Apps Script, Javascript, strings Hey there, Yagi here, you’ve probably stumbled across this page from a link from one of my other in-depth tutorials. This is just a quick primer on Javascript Strings in Google Apps Script for the non-coder. Here’s a bare-bones example of how a string of text might come together in … Continue reading "Google Apps Script: Basic Beginners Guide to Using Strings in Code" ...

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

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

Google Apps Script, Google Sheets, getRangeList, 3D arrays In a number of situations in my day-to-day work, I need to grab data from only Selected Columns in Google Sheets. Then using a selected column as a key, remove any duplicates in a similar way that a pivot table does. I might want to use this … Continue reading "Google Apps Script: Getting Selected Google Sheets Columns and Reducing them to a Unique Value Based on a Key Column" ...

Working with Google Apps Script in Visual Studio Code using clasp

Working with Google Apps Script in Visual Studio Code using clasp

Google Apps Script, clasp, Visual Studio Code, Node.js, npm, Windows 10 I don’t mind the Google Apps Script built-in online editor. It provides word completion, error handling, and pretty formatting, making it ideal to hash out a small automation project pretty quickly. However, one of its major limitations is that after 1,000 or so lines … Continue reading "Working with Google Apps Script in Visual Studio Code using clasp" ...

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

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

Google Apps Script: getRowHeight, setRowHeight Copying and pasting data while maintaining row heights and widths can be a frustrating business in Google Apps Script. You see, there is a difference in how to set column width and set row heights. Let’s say we have already copied our data over with something like: sourceDataRange.copyTo(destination) Google Apps … Continue reading "Google Apps Script: Maintain Row Heights When Copying and Pasting Data in Google Sheets" ...

Google Apps Script: How to Connect a Button to a Function in Google Sheets

Google Apps Script: UI, Google Sheets Ever wanted to use a button in Google Sheets to execute a function in Google Apps Script? Well, guess what? The process is super easy. Write your function in Google Apps Script editor. Create an image or upload an image and add it to your Google Sheet. Right-click the … Continue reading "Google Apps Script: How to Connect a Button to a Function in Google Sheets" ...

Google Sheets: Conditional Formatting with Custom Formula

Google Sheets: Conditional Formatting with Custom Formula

Feature inner image credit: Samuel King Jr.  Google Sheets – Conditional Formatting Conditional formatting in Google Sheets is a powerful and useful tool to change fonts and backgrounds based on certain rules. This tutorial assumes that you already have a basic knowledge of Conditional Formatting but would like to uncover the mysteries of the Custom … Continue reading "Google Sheets: Conditional Formatting with Custom Formula" ...

Google Apps Script – Why isn’t my add-on showing up in the add-on menu when I test it?

Google Apps Script – Why isn’t my add-on showing up in the add-on menu when I test it?

Google Apps Script – AuthMode I mean, when I run it normally, it works just fine! This is an interesting problem I came across recently when reviewing one of my Google add-ons. The Problem The issue was that when I ran my Google Apps Script add-on code normally – in my case, connected to a … Continue reading "Google Apps Script – Why isn’t my add-on showing up in the add-on menu when I test it?" ...

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

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

Gmail, cPanel, web host like GreenGeeks or HostGator One of the most frustrating things about using a domain email like imbatman@yagisanatode.com is that every time you want to check your email you need to log into your websites cPanel, navigate to the Email section, click ‘Email Accounts’ find the Access a Webmail provider like Roundcube … Continue reading "How do I Forward and send Emails for my Website Email in my Personal Gmail account?" ...

Google Apps Script – When I add a value to a cell in a selected column, I want it to move to another Google Sheet

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: onEdit, Google Sheets Sometimes you want to be able to automatically move a row from one sheet to another based on the value of a certain cell. The Example One of the first things that come to mind, and I am sure it does for you dear reader, is when I took … Continue reading "Google Apps Script – When I add a value to a cell in a selected column, I want it to move to another Google Sheet" ...

Google Apps Script – Create Custom Unique IDs in Google Sheets

Google Apps Script – Create Custom Unique IDs in Google Sheets

Google Apps Script, Google Sheet Sometimes something on the surface that sounds downright easy turns out to be quite a complex procedure. My recent experience in creating a Custom Unique ID in Google Sheets using Google Apps Script was a case in point. In a previous version, I created a simple unique ID generator by … Continue reading "Google Apps Script – Create Custom Unique IDs in Google Sheets" ...

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

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

Google Sheets – TEXT, TODAY, WEEKDAY If you are a busy admin nerd like me and have created a Google Doc or Sheet on the fly to meet your company’s demands for something or another, you probably also have a few Sheets lying around that are not 100% right. That obsessive-compulsive nature in you is … Continue reading "How to display a date for one day in a week that automatically changes weekly on Google Sheets" ...

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

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

Google Apps Script, onEdit, Date, Google Sheets Here is the scenario. You have a small business and you want to store your customers, products and sales information on separate Google sheets. You’re probably going to have someone from your staff enter new customers, products are sales transactions. We know if we get them to enter … Continue reading "Google Apps Script – Adding a Unique ID in a Cell on Edit of an Adjacent Cell Using Time" ...

Creating a Google Sheet Geo Map From Form Data and Posting it to WordPress – Part 3 of Google Forms in WordPress with Live Chart Project

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

Google Forms, Google Sheets (IMPORTXML), XML Path, WordPress Note: This is part 3 of a larger project. Each part of the project is self-contained should you wish to reference just one particular aspect. Alternatively, you can follow along with the project to practise workflow and learn about Google Forms, Sheets, WordPress integration and a little … Continue reading "Creating a Google Sheet Geo Map From Form Data and Posting it to WordPress – Part 3 of Google Forms in WordPress with Live Chart Project" ...

Embedding a Live Google Sheet Graph that Updates Every 30 Seconds into a WordPress Post – Part 2 of Google Forms in WordPress with Live Chart Project

Google Sheets, Forms, WordPress,  HTML5, a touch of Javascript Note: This is part 2 of a larger project. Each part of the project is self-contained if you wish to reference just one particular aspect. Alternatively, you can follow along with the project to practise workflow and learn about Google Forms, Sheets, WordPress integration and a … Continue reading "Embedding a Live Google Sheet Graph that Updates Every 30 Seconds into a WordPress Post – Part 2 of Google Forms in WordPress with Live Chart Project" ...

Creating a Short Google Form Survey and Embedding it into a WordPress Post – Part 1 of Google Forms in WordPress with Live Chart Project

Google Forms, WordPress,  HTML5 Note: This is the beginning of a larger project. Each part of the project is self-contained if you wish to reference just one particular aspect. Alternatively, you can follow along with the project to practise workflow and learn about Google Forms, Sheets, WordPress integration and a little HTML5 and Javascript. Intro … Continue reading "Creating a Short Google Form Survey and Embedding it into a WordPress Post – Part 1 of Google Forms in WordPress with Live Chart Project" ...

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

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

One of my biggest regrets when I first got started with Google Sheets and spreadsheets in general, was not taking full advantage of Find and Replace. Find and Replace is the penicillin of the Spreadsheet world. It can rapidly cure all sorts or issue with a simple set of commands. It is seriously amazing stuff. … Continue reading "Multiple Cell Data and Formula Reference With Find and Replace – Google Sheets" ...

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

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

It’s time to get evangelical, peeps! Release yourself from the yolk of the menu bar and its insidious demands on your coordination and time! Break free from the right-click menu! For is it not anything more than a proxy menu bar?! A veritable wolf in sheep’s clothing with no other design, but to bring you … Continue reading "5 Easy Shortcuts That Will Save you Heaps of Time – Google Sheets" ...

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

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

left, right, find, length Every academic quarter I receive a list of students by their full name in one cell that I need to split into a cell for the first name and then a cell for the middle and last names combined.  This fairly simple process can be achieved with the Google Sheets formulas left, … Continue reading "Google Sheets – How to Separate the First Name from a Full Name Cell" ...

Google Apps Script – How to Get the Sheet Name and Spreadsheet Name and add to a Cell on Google Sheets with a Custom Function

Google Apps Script – How to Get the Sheet Name and Spreadsheet Name and add to a Cell on Google Sheets with a Custom Function

Google Apps Script, Custom Functions Boy, are these titles getting longer.  But that’s pretty much the gist of it. In this post we will look at creating a Google Apps Script Custom Function that allows you to do Three things in Google Sheets:  Get the current sheet name.  That’s the same sheet name as the … Continue reading "Google Apps Script – How to Get the Sheet Name and Spreadsheet Name and add to a Cell on Google Sheets with a Custom Function" ...

Google Apps Script – Copy Selected Columns in One Sheet and Add Them To The Bottom of Different Selected Columns in Another

Google Apps Script – Copy Selected Columns in One Sheet and Add Them To The Bottom of Different Selected Columns in Another

I recently had a legend (a.k.a reader) ask about how to copy selected columns from one sheet and append them to the bottom of different selected columns in another sheet.  To me, this seemed like a pretty common task that one might face, so in response, I decided to create a template function to easily … Continue reading "Google Apps Script – Copy Selected Columns in One Sheet and Add Them To The Bottom of Different Selected Columns in Another" ...

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

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

Google Apps Script, DriveApp I had an unexpected need to change some non-native file names – in my case, MS Word docs – to something else to appease my masters. Being the resident Igor, I delighted in the task.  The Problem I had a folder of MS Word documents on my Google Drive that needed … Continue reading "Google Apps Script – How to Change the File Name of Non-Native Files like MS Word Docs, PDF’s and Excel files." ...

Google Apps Script: Get File By Name – With Optional Parent Folder Crosscheck

Google Apps Script:  DriveApp One of my recent projects in Google Apps Script required me to search for a file by name and get its ID. This can be problematic in Google Drive because you can have multiple files of the same name in multiple locations. My solution was to also check the file’s parent … Continue reading "Google Apps Script: Get File By Name – With Optional Parent Folder Crosscheck" ...

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

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

Javascript, WordPress, Your Affiliate Program’s Campaign Link The Story When I created Yagisanatode.com my goal was to provide a resource for myself and others to reference on all the coding projects I work on. Since it’s beginnings in October 2017 I have seen a huge rise in my readership and am so pleased to see … Continue reading "Simple Code for AB Testing Affiliate Recommendations in a WordPress Sidebar HTML Widget" ...

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

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

Google Sheets – MIN, FILTER, INDEX, MATCH, SUM, COUNTIF In the region of the world that I work in, it is a pretty common occurrence for university courses to run weekly assessment. At the end of the course, all the weekly assessment is then added together minus the lowest piece of assessment. For lecturers with … Continue reading "Google Sheets – Remove The Lowest Grade for Each Student on a Course" ...

Google Sheets – Get the Top 2 Grades for Each Group in a Spreadsheet using RANK and FILTER

Google Sheets – Get the Top 2 Grades for Each Group in a Spreadsheet using RANK and FILTER

Google Sheets: FILTER, RANK This week I was asked to provide a list of the top two student grades in each of the 100 classes in our university program.  Normally, this would be a pretty easy task if there were only a half dozen or so classes. Simply sort by group (class) and then Grade. … Continue reading "Google Sheets – Get the Top 2 Grades for Each Group in a Spreadsheet using RANK and FILTER" ...

Google Apps Script – How to make a Custom Function to Use in Google Sheets

Google Apps Script – How to make a Custom Function to Use in Google Sheets

Google Apps Script: Custom Functions, Google Sheets Google Sheets has a vast library of functions (or formulas) you can use to get your Spreadsheet tasks done. However, there are some instances when you want a specific function that is not available in Google Sheets or wants to make a simplified version of some combined function … Continue reading "Google Apps Script – How to make a Custom Function to Use in Google Sheets" ...

Google Sheets – Random Alphabetic, Random Alphanumeric and Random Alphanumeric + Character Custom Functions

Google Sheets – Random Alphabetic, Random Alphanumeric and Random Alphanumeric + Character Custom Functions

Google Sheets, Google Apps Script I was working on a Spreadsheet in Google Sheets a few days ago and needed to generate some random codes for my users. To do this, I just used the RANDBETWEEN(start val, end val) function built into Google Sheets. It goes a little like this. If I want to build … Continue reading "Google Sheets – Random Alphabetic, Random Alphanumeric and Random Alphanumeric + Character Custom Functions" ...

Google Apps Script – How to Find the Folder ID of a Non-Unique Folder Using File Path Names

Google Apps Script – DriveApp Well, that title is such a mouthful. So I think it needs some explaining… I’ve recently come across a task that requires me to access a particular folder in a directory based on the information in a Google Sheet. This means: I don’t have the folder or the sub-folder ID. … Continue reading "Google Apps Script – How to Find the Folder ID of a Non-Unique Folder Using File Path Names" ...

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

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

Google Apps Script – DriveApp Well, it’s a rainy day here travelling in Romania, so time for a post. When creating a Google Apps Script’s I often find I am creating new folders and files in specific locations on Google Drive after, say, generating a report or something. A Note on Folders in Google Drive … Continue reading "Google Apps Script – How to Create Folders in Directories with DriveApp" ...

Google Apps Script – Getting Input Data From a Dialog Box in Google Sheets

Google Apps Script, Jquery, Javascript, HTML One of the weekly tasks in my day job as an academic administrator is to look at a number of ‘modules’ or courses that our college is running and complete some analysis and crosschecking. Each week the students complete a ‘unit’. However, during some quarters, not all modules are doing the … Continue reading "Google Apps Script – Getting Input Data From a Dialog Box in Google Sheets" ...

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

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

Google Apps Script, Google Sheets On a recent board post, a Google Sheets user wanted to change a four-digit number (for example, 1230) to a time, like 12:30, in the same cell that the item was entered. Unfortunately, the user was not in a position to change the starting values, so they were left with … Continue reading "Google Apps Script – How to Automatically Generate a Time From a Four Digit 24 hour Time in Google Sheets." ...

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

Google Sheets, Google Apps Script, onEdit Sometimes, when you are working on a shared Google Sheet you might want to hide a row based on a cell value. For example, perhaps we don’t want to see row information of orders that have been paid for. Take a look at my D&D miniatures wholesale orders sheet (image … Continue reading "How to Hide a Row based on a Cell Value in Google Sheets with Filter or Google Apps Script." ...

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

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

What is Sections to Sheets? Sections to Sheets is a free Google Sheets Add-on that separates rows by column sections and puts them in individual pages or sheets in a spreadsheet in preparation export and printing. You can find Sections to Sheets at the Google Chrome Webstore. Example 1 Let’s say you want to print out a list of … Continue reading "Sections to Sheets – Free Google Add-on That Separates Column Sections Into Individual Sheets for Page-by-Page Export or Printing" ...

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

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

Google Apps Script: SpreadsheetApp, copyTo Have you ever copied and pasted something in Google Sheets only to be frustrated with the fact that it stubbornly refuses to paste the column widths? I mean, everything else is perfect, the formatting, the formulas the comments, they all were pasted across just fine. However, that dastardly column width … Continue reading "Google Apps Script – Copy and Paste Cells While Retaining Column Width in Google Sheets." ...

Google Apps Script – Disable Enable Submit Button in Sidebar

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 and that “Submit” Button was ripe to … 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

Google Apps Script-templates, Javascript, CSS So, I started working on a larger Google Sheet Sidebar project in Google Apps Script recently and I quickly realised that it was going to be a mess if I didn’t separate my Javascript, CSS and even some of my HTML into separate files. However, if you have ever noticed … Continue reading "Google Apps Script – How to create Javascript and CSS files for a Sidebar Project in Google Apps Script" ...

Google Apps Script – How to Alternate Colors in an Ordered List by Column Category. 

Google Apps Script and Google Sheets Imagine that you have a Google Sheet that you have sorted by a certain column. You might be sorting by the surname of your sales team, class sections or regions. To make the sheet easier to read for your team, you want to alternate the background colours after each … Continue reading "Google Apps Script – How to Alternate Colors in an Ordered List by Column Category. " ...

Google Apps Script – Get the Start Row and Length of Each Category in an Ordered Column in Google Sheets

Google Apps Script and Google Sheets Quite often I will need to get the range of each category in an item and do something with it in Google Sheets. For example, I work in education, I will often have rows of students that are categorized by class sections. I will then be asked to do … Continue reading "Google Apps Script – Get the Start Row and Length of Each Category in an Ordered Column in Google Sheets" ...

Set the Paper Size and Orientation in a Doc Using Google Apps Script

Google Apps Script: DocumentApp setPageWidth, setPageHeight Sometimes you need to prepare a Google Doc’s paper size and orientation programmatically using Google Apps Script. Unfortunately, you can’t just call for say, A4 in Landscape. Okay, not until now (see my code below). Google Apps Script does provide a way to set the dimensions of your page  … Continue reading "Set the Paper Size and Orientation in a Doc Using Google Apps Script" ...

How to make multiple copies of a file with Python 3 and a file name list from an Excel sheet

Python 3, openPyXl, os,  shutil on Windows 10 The Problem As an academic administrator, I have to prepare 70 empty grade report spreadsheets templates at the end of each academic quarter: one for each of my teachers. Each copy of the template sheet needs to be named with the teacher’s name and class number. Then … Continue reading "How to make multiple copies of a file with Python 3 and a file name list from an Excel sheet" ...

How to Create a Simple YouTube Download Program with a Progress Indicator in Python 3 with pytube

Python 3, pytube, os in Windows 10 Some of my friends live in an area that really struggles to get decent internet speeds in the afternoons and evenings. So much so that they can barely watch a YouTube video at 144p some days, and that is not particularly useful if they are trying to watch … Continue reading "How to Create a Simple YouTube Download Program with a Progress Indicator in Python 3 with pytube" ...

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

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:\Users\yagisanatode\ Because if another user on another computer tries to use your … 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?

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: To download a video from YouTube you don’t really have to do much. So much so that you … 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?

Google Sheets: SORT, INDEX, ROWS Sometimes you have a need to reverse a list quickly in Google Sheets. That’s pretty easy to do if the list is sorted alphabetically. Just go on into the <Data> menu and choose from one of the sort functions. But what if the data you want to flip is not … Continue reading "How do I reverse the Rows in A Column in Google Sheets?" ...

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

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 a Tkinter Entry field and when they … 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

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 at the centre of the page or … 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

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 position when it is initialized. This means … 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

Google Sheets, Google Apps Script: onEdit You’ve probably come across the problem where you need to know when a piece of data has been added to your spreadsheet. You probably have been equally frustrated that there is no out-of-the-box function that will do just this. You’ve tried TODAY() and NOW(), but they change dynamically. What … Continue reading "Add the Current Date to a Sheet When Data Is Added So That The Date Does Not Change(Static) – Google Sheets" ...

How to Get Something from Google Sheets and Display it in the Sidebar in Google Apps Script

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 Googles server-side script. Then you will have to display it client-side in your … Continue reading "How to Get Something from Google Sheets and Display it in the Sidebar in Google Apps Script" ...

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

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 really cool tools that you can use … 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

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 was reaching across the page to something … Continue reading "Google Apps Script – Iterating Through Ranges in Sheets the Right and Wrong Way" ...

Skewed Random Range in Google Sheets (RANDBETWEEN)

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 get a random number between zero(o) and … Continue reading "Skewed Random Range in Google Sheets (RANDBETWEEN)" ...

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

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

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 back to a group they have already … 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

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 to move into their class for two … Continue reading "VLOOKUP Left in Google Sheets with INDEX and MATCH" ...

Copy and paste ranges in excel with OpenPyXl and Python 3

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 Python and the OpenPyXl library. The library … Continue reading "Copy and paste ranges in excel with OpenPyXl and Python 3" ...

Spacer – Free Google Add-On That Spaces Row by Column Category

Spacer – Free Google Add-On That Spaces Row by Column Category

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 staff to quickly identify class sections. You … Continue reading "Spacer – Free Google Add-On That Spaces Row by Column Category" ...

Can I modify Google Sheets with code?

Google Apps Script and the Google Suite 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 you to do all sorts of things like building … Continue reading "Can I modify Google Sheets with code?" ...

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

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 the bottom right of the cell to … Continue reading "How do I lock certain cells in a formula in Google Sheets?" ...