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 Script provides us with a great set of enumerators for copying and pasting, the CopyPasteType.

To ensure that we maintain the same column width in our destination that was copied from our source, we need to run another copyTo() method of our sourceDataRange with the CopyPasteType.PASTE_COLUMN_WIDTHS. So all together it might look a little like this:

sourceDataRange.copyTo(destination);
sourceDataRange.copyTo(destinationStart,
                       SpreadsheetApp.CopyPasteType.PASTE_COLUMN_WIDTH);

sourceDataRange.copyTo(destination);

sourceDataRange.copyTo(destinationStart,

SpreadsheetApp.CopyPasteType.PASTE_COLUMN_WIDTH);

Okay, okay, a little extra work, but pretty straight forward. We get our column width in the end.

Now by this stage, you might be thinking, Yagi, why are you even blogging about this. Seems pretty straight forward, ya just gotta do the same with heights!

Nope. This is the point where we just have to remind ourselves that we love Google Apps Script for her flaws and all. Right? Right?!

For, I am sure, a very logical reason for the developers of Google Apps Script, copying and pasting the width has to be done row-by-row.

Here’s how you would do it the long way.

Scroll down to The Shortcut if you just want to copy and paste the functions into your code.

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 image, select the ellipse and Assign script.
Type in your function name.
Click the button to test. You should be up and running.

The Example

Let’s look at a super easy example of connecting a button to a function.

Imagine we want to have a button that, when clicked, reads the selected cell and displays it as an alert.

We’ll start with the code first.

The Code

function button() {
  var ui = SpreadsheetApp.getUi();
  var cell = SpreadsheetApp.getCurrentCell().getValue();
  
  ui.alert("You clicked: "+cell+"!! \n Ya big legend!!!");
}

function button() {

var ui = SpreadsheetApp.getUi();

var cell = SpreadsheetApp.getCurrentCell().getValue();

ui.alert("You clicked: "+cell+"!! \n Ya big legend!!!");

}

Line 1, sets the function, button(). Our first variable, ui, gets the User Interface class (getUi()). The second variable, cell, then calls the SpreadsheetApp service again all for the value of the currently selected cell.

Now that our variables are set, we call the ui class and send an alert on our screen. Inside the alert (Line 5) we add some text plus our cell value.

When the function is run, you will need to accept permissions for the first time. Then, you will see that whatever cell you clicked, the function will read that cell value and display it as an alert on your screen.

With the function complete, we’ll move over to create the button.

Continue reading “Google Apps Script: How to Connect a Button to a Function in Google Sheets”

Google Sheets: Conditional Formatting with Custom Formula

Feature inner image credit: Samuel King Jr.

Google Sheets – Conditional Formatting

Conditional formatting in Google Sheets is a powerful and useful tool to change fonts and backgrounds based on certain rules.

This tutorial assumes that you already have a basic knowledge of Conditional Formatting but would like to uncover the mysteries of the Custom Formula option.

In this post, I will guide you through the steps to build your own custom formulas in oder to:

Apply Conditional Formatting across a whole range based on a value in one or two columns.
Apply Conditional Formatting across one column based on values in other columns.

Throughout the examples, we’ll look at various aspects of using Custom Formula to match, use formula functions and apply multiple conditions.

Before we hit the examples, let’s briefly go over accessing the Custom Formula in Google Sheet’s Conditional Formatting.

Continue reading “Google Sheets: Conditional Formatting with Custom Formula”

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 advantage of the Great Chicken Transformation back in, oh, 2019, I believe.

Folk kept turning into chickens, while other folk were wanting eggs. It just so happened that I had the farm to make it all happen.

First, though, I needed to keep a tab of every person I knew and if they turned into a chicken. If they did, then they were destined for the pen.

…Note to self: it may be late at night, but dam Yagi, your analogies are tight!

Google Sheets and Google Apps Script to the rescue.

So first off I set up a sheet named: Plague. Here I put all the people I knew, so I could watch em good and propper.

Next, I set up a sheet named Farm. These are for the people who turned into chickens. No harm in profiting from a few newly formed egg layers, right?

Whenever a new transformation occurs, I find the person on the Plague sheet and then select “Yes” to say that they have turned into a chicken and will now be spending their days on the farm. Upon editing (onEdit) this cell to “Yes”, the row is copied and pasted to the Farm sheet.

Just like this:

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, 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 taking advantage of JavaScript’s Date object. It was a mere 41 lines of code. You can take a look at it here:

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

Then curiosity got the better of me, and I wanted to know how to create a custom ID generator. My requirements were simple:

It needs to be able to take any size number format. For example, from 1003 to 100000000000003.
It needs to handle leading zeroes. For example 0000653.
It needs to be able to take optional leading and trailing letters. For example, AR1340203, 3000484030BSTN, ENT900848933IRE.
It needs to generate a unique ID based on the pattern provided by at least one ID in the ID Column of the Sheet.
It needs to be able to look through all the unsorted IDs in the ID column and find the next largest number that will become the new ID.
It needs to be able to create IDs on multiple sheets.
It needs to be customisable and easily reusable in other projects.

For this project, the code would not handle the following:

It won’t handle letters between numbers. For example, ERC1299374ER3900TT.
It won’t handle non-alphanumeric characters. For example, 1394~~8%$&)~~
ID’s must be of the same length and the newly created ID must have the same length of characters as the previous ones. For example, A99C >> ~~A100C~~.

The Results

After an afternoon tinkering, the results turned out pretty good. Take a look at the demo below:

The Code

Continue reading “Google Apps Script – Create Custom Unique IDs in Google Sheets”

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

Google Sheets – TEXT, TODAY, WEEKDAY

If you are a busy admin nerd like me and have created a Google Doc or Sheet on the fly to meet your company’s demands for something or another, you probably also have a few Sheets lying around that are not 100% right. That obsessive-compulsive nature in you is niggling the back of your mind saying, “You can do this better!”

But the day-to-day race to get things done takes over and you move on to more pressing matter.

Until the next time you have to look at that Google Sheet and it starts bugging you again. Well, until you have a moment to fix it.

The Problem

For me each week I had a sign-up sheet for a makeup test for students. The coordinators who would add students to the sheet required that in the header rows, the date of the next Makeup Test be added for each week.

The makeup test was always at the same time each week: Monday at 15:30. I would then need to prefix this with the day and month. So it would look something like this:

Monday 28 Jan at 15:30

My original approach then was to open the sheet and change the date manually each week. Okay, I admit that on not just one occasion I forgot to change the date much to the glee of the most persnickety of the coordinators who could happily call me out on my failure. Grrr.

I knew I could make this more efficient. I knew I could probably automate this process. Fortunately, the day finally came where I found myself with 15 minute s of free time and this issue in my mind.

This post is the solution to the problem.

The Solution

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

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

Google Apps Script, onEdit, Date, Google Sheets

Here is the scenario. You have a small business and you want to store your customers, products and sales information on separate Google sheets.

You’re probably going to have someone from your staff enter new customers, products are sales transactions.

We know if we get them to enter unique ID’s in manually that mistakes are going to get made. So how about we automate this process with unique ID’s based on date-timestamps.

Why use a date-timestamp to create a unique ID?

Every year is unique. Every day, hour, minute, second and millisecond of that year creates a unique number. This mean that a new unique id will be create every millisecond for us.

Wow! Wow! Wow! Yagi! Just hold it one damn minute! You could have multiple results each millisecond that would each be the same number!

Well, true if we were running a loop generating and publishnig our date-timestamp, we could have multiple numbers. However, we are generating this unique ID as an onEdit function when the user adds some information in Google Sheets to a cell and an adjacent cell returns our unique number.

The user’s input and then the calls to and from the server to the Google Sheet will be sufficiently slow enough not to have a number generated multiple times a millisecond, so we are safe there.

We have the main idea of what we are planning, let’s move onto the example.

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

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 practice workflow and learn about Google Forms, Sheets, WordPress integration and a little HTML5 and Javascript. You can access the beginning of the project here:

Creating a Short Google Form Survey and Embedding it into a WordPress Post – Part 1 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

Where We Left Off

After creating and embedding a Google Form into our WordPress post, in our last tutorial we added a graph of all the results from the survey that updates every 30 seconds.

In this tutorial we are going to add a country selection to our form and then embed a country heat map into our post.

Creating a Country Drop-Down List in Google Forms

The Countries List

Getting The Country Data

First off, we need to find a list of countries. I’m going grab that from https://www.listofcountriesoftheworld.com. I could probably just copy the list and paste them in but I might want to use the list of countries again as a reference for other calculations in my sheet so I am going to go to my Google Sheet that is connected to my form and create a new Sheet tab named Countries.

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 practice workflow and learn about Google Forms, Sheets, WordPress integration and a little HTML5 and Javascript. You can access the beginning of the project here:

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

Where We Left Off

In our previous post I showed you how to create a Google Form and embed it into a WordPress post. The end result looked like this.

Feel free and complete the survey for fun and the unadulterated joy of surveys!

In this tutorial we will add a live Google pie chart of our results that updates every 30 seconds so that our viewer, …erh…you, can see the results as they come in.

Here is what our Chart will look like.

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”

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 the Spreadsheet world. It can rapidly cure all sorts or issue with a simple set of commands. It is seriously amazing stuff.

Yeah! Yeah! We all know about Find and Replace.

I know! I thought so too, but then I started to really use and identify how I could use it to quickly:

Change template sheets.
Fix bulk errors in formulas.
Change parts of cells.
Replace values in the whole spreadsheet, one sheet or a selected range.

Before we get started, you need to know the short cut for the Find and Replace tool. This will come in handy in all sorts of programs and apps.

PC – Ctrl + H
Mac – CMD (⌘) + H

I’m going to go ahead and continue the examples using PC, because, you know, Mac.

Continue reading “Multiple Cell Data and Formula Reference With Find and Replace – Google Sheets”