A super helpful Google Sheets Addon
Recently we published an addon for Google Sheets. This is not just an addon but this is something more and can be customized as your personal Google sheets assistant… more about in the end. But, let me first describe the basic helps that the addon can provide to you.
Many times you might have thought of having some extra Google sheets function, for eg: you need to pick a few items randomly from a list of 100 items, or you need to do a multiple columns VLookup or sum a range based on the color. Well, the addon is built for such people who need some simple way to extend the spreadsheet and make most of it.
Ooo, hoo. I didn’t say the addon name. Its ‘Clippy’. Yea, the same old clippy guy who used to help you in WindowsXP docs and excel. I just copied that name. Thank you Microsoft, I love you.
You can install the addon from the Google Addon store: LINK
Let see some of the function that I felt wonderful in sharing with you.
MVLOOKUP:
Traditional VLOOKUP can accept one column and one search value. MVLOOKUP extends the traditional VLOOKUP, It can accept multiple columns and search values and the returns value from the column that you specify at the end of the formula.
=MVLOOKUP(A:A, 'foo', B:B, 'boo", C:C)
XSUM:
Sums of the range from the cells across all the sheets in a spreadsheet. If you have many sheets in a spreadsheet and need to get the sum of the values from cell A1 in all the sheets then use this formula XSUM(A1). You can also give a range to sum eg: XSUM(A1:A10).
=XSUM(A1)
SUMBYCOLOR
Returns the sum of a range based on color. Give a range and a reference color, the formula returns the sum of the cells that are having the same reference color. Color should be in hex value.
=SUMBYCOLOR(A1:A10, ‘#dadada’)
WORDCOUNT
Returns the count of words in a sentence. When want to know how many words are there in a sentence in the cell. Don’t get confused with the LEN formula. LEN formula returns the total number of characters in a sentence and not the number of words in a sentence.
=WORDCOUNT(A1)
REVERSE
Reverses a range or the content of a cell. Reverses anything that you provide. If you give a cell to reverse, then its content will be reversed. If you give a range to reverse then the entire range will be reversed. Please note that when you give a range the cell content will not be reversed.
=REVERSE(A1:A10)
AGE
Computes age from date of birth. The return value has multiple formats, such as in years, months, days, year and month, year month, and days. The default is Year-Month-Days.
=AGE(A1, ‘Y’)
TOP
Returns top-ranked items from a given list. If you have a huge list of items and need to pick the top-ranked few items then it’s easy now with just one formula.
=TOP(A1:A10, 3)
ANY
Returns random items from a list specified by the count. If you have a list of items and need to pick some random item from the list, use this function.
=ANY(A1:A10, 1)
SHUFFLE
Shuffles the given range. Give a range of content that needs to be shuffled, the formula will return a new set of data that is totally shuffled.
=SHUFFLE(A1:A10)
NUMBERTOWORDS
Converts the given number to words. For eg: 123456789 will be one hundred and twenty-three million four hundred and fifty-six thousand seven hundred and eighty-nine. The function can take really huge numbers, up to 10¹³.
=NUMBERTOWORDS(“123456789”)
ARRAIFY
Returns the given range in an array format. Every row will be a new item group and a cell will be an item in the array.
=ARRAIFY(A1:C10)
And there are few macros as well.
GEO CODE
Converts physical address to latitude and longitude.
Select the range that you need to get geocoded and click on run. A new column will be added with latitude and longitude data.
REVERSE GEO CODE
Converts latitude and longitude to a physical address.
Select the range that you need to get reverse geocoded and click on run. A new column will be added with data from Google’s reverse geocode API.
DELETE BLANK ROWS
Makes it easy to delete blank rows from a sheet. A row will be deleted if there is no content in it. Super easy.
Select a sheet and click on run. Normally delete starts from row 1 to the end row. But you can control the same by selecting a range of rows, then only the blank rows in the selected range will be deleted.
Climax
And now the best part, a couple of days back one of the users who installed the addon came to me with a request that he needs the spreadsheet to be formatted in a specific way related to his business. And after the discussion, we found it's not a generic and I will not be able to able add the macro onto the public use because the public will not find anything useful with that macro. So, we added a very specific macro to the addon and a button that will work only for him and all the users that logs in from his domain. The button will be invisible for all the other users who are not from his domain, Super cool, right? And revolutionary…
Clippy is not just an add-on, it's not an addon built for the public, its an addon built specifically for you, to make your spreadsheet life easy!!! Contact us to discover how Clippy can be modified for your business
addon@elifent.tech 😀