Month: March 2019

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:

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 [Updated 08 Apr 2022]

Google Apps Script: UI, Google Sheets [Updated 08 Apr 2022]

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.

  1. Write your function in Google Apps Script editor.
  2. Create an image or upload an image and add it to your Google Sheet.
  3. Right-click the image, select the ellipse and Assign script.
  4. Type in your function name.
  5. 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

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 [Updated 08 Apr 2022]”

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:

  1. Apply Conditional Formatting across a whole range based on a value in one or two columns.
    1. Example 1: Conditional Formatting a Whole Range Based on One Column’s Cell Values.
    2. Example 2: Conditional Formatting a Whole Range Based on Selected Values and Formulas.
    3. Example 3: Conditional Formatting a Whole Range Based on Multiple Column Values.
  2. Apply Conditional Formatting across one column based on values in other columns.
    1. Example 4: Conditional Formatting of a single range Based on Another Column Value.
    2. Example 5: Conditional Format a single column range based on a value in another column – Multiple times.
    3. Example 6: Conditional Formatting a Single Column Based on Two Values.

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”