Power Tools and other add-ons for Google Sheets: Release notes

NEW: Compare multiple sheets for duplicates

Remember Compare Columns or Sheets add-on? It compared just two sheets at a time. Well, we enhanced it and now it compares multiple sheets in one go! ;)

As you can see from this scheme, you will also be able to handle the found dupes (or uniques), e.g. color them or remove, in all sheets & not just the main one like it used to.

We've already updated the tutorial for the tool, so feel free to look through so you don't miss anything out.

NEW: Compare two columns on a sheet

Another legacy of the old Compare Columns or Sheets add-on.

Comparing columns from multiple Google sheets is half the job. But comparing two columns within the same Google sheet has proved to be just as important. That's why we singled out this little helper.

Here I'll only tell you that it's a one-step quickie now. So no need to go over the steps to compare just two columns on one Google sheet.

Check out this tutorial to learn what it has to offer.

NEW: Add, remove, extract, replace multiple text substrings at once

We haven't left our text tools behind. These new features have been brewed and polished for months till they're finally ready to meet you.

Substring Batch Toolkit will let you handle multiple substrings at a time. There are 4 tools for different actions:

• Add certain substrings before/after certain words in the selected cells:
  

• Replace multiple substrings. Find certain values in the selected range and replace them with other related records:
  

• Remove multiple substrings. Find & remove certain values from the selected range.
• Extract multiple substrings. Find & extract certain values from the selected cells.

NEW: Delete empty cells & shift data up/left

Power Tools already offers several ways to deal with blanks in your tables. It deletes empty & unused rows & columns, fills blank cells with the values from neighboring cells or with your custom ones.

But this new feature introduces yet another way. It removes those single empty cells from your tables while shifting the other data left or right: it's up to you.

You will find the tool as 2 simple checkboxes in the Clear group:

IMPROVED: Copy the sheet(s) to multiple existing files in a go

As you may know, our Sheets Manager can copy & move multiple sheets to any other spreadsheet in your Drive:

We've improved it just a bit so you could copy those selected tabs to multiple different files at once :)

When you see the window where you are to select the spreadsheet, press and hold Ctrl (for non-adjacent) or Shift (for adjacent) on your keyboard to select multiple files:

IMPROVED: Sort by color & skip empty cells

A new checkbox lets you not just sort all cells in the range by color, but only those with values:

It may seem like a small option but it makes a difference:

IMPROVED: Generate only unique strings

Another small but mighty option was added to the String group of Random Generator: unique values. Make sure to tick it off if you'd rather avoid duplicates generated for your data set:

NEW: Compare sheets cell by cell for differences

Yet another comparison tool you may think… But think twice ;D This add-on doesn't just look for duplicates or uniques. It compares cells in each row between multiple Google sheets and marks all differences.

There are 2 ways to track those differences:

• have them highlighted and even described in a status column in each sheet.
• have everything grouped in one comparison report in another spreadsheet for the whole picture in one place:
  

NEW: Merge multiple lookup sheets in one go

Merge Sheets underwent some major changes as well. First and foremost, it finally lets you merge more than 2 sheets at a time:

You add as many lookup sheets as you need on the 2nd step, and the add-on takes the required data from them one by one to your main sheet:

I'm not going to go over all the details and other new features here since I announced them earlier in this blog post, but here are a few highlights:

1. Insert additional matching rows. For when you also want to pull those extra rows that belong to the same key value:
   
2. Pull notes along with their values from lookup sheets:
   
3. Add sheets from more places of your Drive:
   

NEW: Sum or count ALL colored cells

Function by Color now knows how to sum or count not just one but all colors in the range of interest. It builds one formula that returns the names of all colors with their calculated results:

The settings are almost the same and you'll find them on the additional tab — All colors:

NEW: Split sheet by any number of rows

The upgrade didn't bypass Split Sheet. It features a new way to split your table: by a certain number of rows:

You'll be able to fine-tune the number in 2 ways:

• enter the exact number to split rows by, e.g. by every 10 rows
• or specify the groups of rows you'd like to see in separate sheets, e.g. 1-10, 11-50, 51-500

NEW: Sort by color

Our Power Tools has acquired its own Sort by Color. And it works unlike the Google Sheets built-in tool, so I recommend you check it out anyway:

Our add-on gathers all colors together and sorts them in the order you specify directly in the add-on:

The built-in Google Sheets tool gathers only the rows of the selected color at the beginning of your table:

So based on your task, you may need one tool or another. Another is in our Power Tools ;) These instructions will help you get started.

IMPROVED: Text tools process multiple non-adjacent ranges

The tools designed to process text — Add, Modify, Remove, Split, Extract — all now handle several non-adjacent ranges:

It means that rather than selecting each column individually and processing them one by one you can change all required columns with one tool in one go:

IMPROVED: Easier conditions in Multiple VLOOKUP Matches

Whenever you need to enter multiple words to see if cells contain/equal to/etc. at least one of them (OR condition), you can now simply copy the list of such words and paste it into the add-on. The tool will take them as parts of your condition automatically:

IMPROVED: Convert text in parentheses to negative numbers

The Convert tool now turns all text written in parentheses into negative numbers, just like Google Sheets does when you change the Number format:

And this is apart from the fact that Convert also recognizes more formats in general, including custom ones!

IMPROVED: Add favorite spreadsheets from your Drive

All add-ons that let you add files from Drive (Merge Sheets, Compare Sheets, etc.) will see your starred items as well. This is super useful if you want to instantly add some files of interest to handle it with the tool:

NEW: Compare multiple sheets for duplicates

This one is an enhanced version of our long-existing Compare Columns or Sheets. It used to compare just two columns or Google sheets. Now it will deal with several sheets at a time :)

NEW: Compare 2 columns within a sheet

Since Compare Two Sheets or Columns has been changed to support multiple sheets, we had to make this separate quickie: Compare Two Columns.

It's for when you must compare two columns on the same Google sheet. Many of you asked to keep this ability, so here it is :)

It's now a one-step add-on with just a few settings to tweak:

1. the range containing your columns
2. two actual compared columns
3. whatever you want to find: dupes or uniques
4. the action

Easy-peasy, right? This tutorial digs deeper into the options if you'd still like to explore them :)

NEW: Merge multiple lookup sheets in one go

Merging 2 sheets is good but merging multiple sheets is way better!

With this major upgrade, you can now add numerous files from your Drive right to the add-on and select as many lookup sheets from them as you need:

The add-on will go through each lookup sheet one by one (in the order you add them to the add-on and they appear in the tree view), matching them with your main sheet and merging the necessary information.

Example 1. Update records & add non-matching rows

Example 2. Update records, mark the changes in the status columns

Example 3. Update only empty cells in the main sheet

Example 4. Update records, add non-matching rows & new columns

Note. Introducing multiple lookup sheets made us put our thinking caps on. What will happen if there are identically named columns in several lookup sheets and you choose to add them to the end of your main sheet?

Should they update one another or what? Learn how the add-on handles such columns in this part of the Merge Sheets help page. Forewarned – forearmed, right? ;)

NEW: Insert additional matching rows

Make Merge Sheets not just update your main sheet with the 1st value, but also take all extra matching rows belonging to the same key value with it:

You can also personalize this merge a bit and put all those additional matching rows at the end of your main table (rather than right after the row with the same key value):

This new setting resides on the 5th step:

IMPROVED: Enhanced user experience

All this talk about the new options and multiple lookup sheets would be pointless if we didn't review the add-on completely. We significantly improved the algorithm so you could have a faster and more reliable Merge Sheets at hand.

Instead of loading arrows, there is now a percent count and the process name for your convenience:

It's a game changer for handling large amounts of data because you will understand how much longer (at least approximately) it's going to work.

Even if Merge Sheets catches some problem, it'll show you a gentle notification with the issue description & its possible solution.

IMPROVED: 10 million cells available in your main spreadsheet

Speaking of enhancements! The limit for total cells in a spreadsheet is finally expanded to 10 million. This allowed us to include more lookup sheets & pull those additional matching rows to your main sheet. Yay!

IMPROVED: Add sheets from more places of your Drive

In addition to your personal and shared Drives, you can now search for the necessary main or lookup files among your starred ones:

IMPROVED: No more missing notes from the lookup sheets

If your lookup sheets have notes, the add-on doesn't leave them behind anymore. It pulls them to the main sheet along with their corresponding values:

NEW: New style sets

The first thing that will catch your eye — lots of new style patterns! We designed multiple new style sets featuring various shades and colors. Some of them may even remind your favorite movie or a video game ;)

All in all, you have 52 built-in styles ready to be used as is, tailored to your needs and saved as your custom sets.

All patterns are grouped into 6 types based on their color themes: practical, monochrome, light, juicy, contrasting and even dark.

We believe even the most demanding Google Sheets users will find something to their liking. Go ahead and prove us wrong ;)

NEW: More formatting tools

You can finally format borders:

• change their colors using the built-in color palette.
• change their styles: solid, dotted, dashed, or double.

There's also an addition to the horizontal alignment: the automatic type.

Use it and the values in your Google table will be automatically aligned based on their format just as they normally do in spreadsheets, e.g. numbers to the right and text to the left.

NEW: Style parts of the tables

However neat the whole style is, you probably don't always want to apply it to your entire table.

The new version makes it possible to style only particular table element(s) from the chosen style set: font, its size/formatting/color, fill colors, borders, horizontal/vertical alignment, and text wrapping.

IMPROVED: Fresh looks

We redesigned the add-on completely! It now keeps up with the updated Google Sheets design. But what's more important — you get access to any setting from just one window — without any extra steps. Enjoy! :)

IMPROVED: Edit multiple table elements at once

We get it — some parts of your table may share formatting, for instance, the font color in the left and right columns. And we agree — applying the same formatting to each part is unreasonable.

That's why you need the 2.0 version. It lets you select all necessary elements and format them all at once. Awesome!

IMPROVED: More fonts available

The updated add-on has more fancy, simple and funny fonts so you could personalize your styles. Let us know if there are still some you're missing ;)