Category Archives: Google Apps

How to Insert and Delete Cells from Google Spreadsheets

[update on 9/24/2014:  This has now been published as a Google Add-on.  Please see this page.  To install the Add-on, just search for “insert cells” when browsing for Add-ons from Google Sheets]

I haven’t been able to find a way in Google Spreadsheets to insert a cell (or cells) in the middle of a spreadsheet and tell the existing content to shift down or to the right.  Similarly, I’d like to be able to select cells to delete, and have them fill in by content shifting up or from the right.

So, I created a Google Apps script to add “Insert Cells” and “Delete Cells” menus to Google Spreadsheets.  Right now, you need to copy the code from github and paste it into the Script Editor in Google Spreadsheets (see the Tools menu).  Hopefully I’ll get the code cleaned up enough to have Google approve it as an Add-on soon!

Watch the video for a demonstration.

Google Apps Script to monitor an email box and send notifications

googlemail-128

Here’s the scenario: you have a second gmail account that gets email infrequently, but you need to monitor it.  For whatever reason (company policy, in my case), you can’t forward the email to your main account.

I discovered that I could log into the second Google account and attach a Google Apps Script to a spreadsheet.  The script monitors the gmail account for unread threads.  If it finds any, it sends a message to my main account.  The script has a time-driven trigger that can be set to run as frequently or infrequently as desired.

To use the script, make a copy of this spreadsheet.  Open the spreadsheet and edit the cells that contain the email message parameters.  Then go to Tools – Script editor.  Choose Resources – Current project’s triggers.  Add a time-driven trigger that runs the script as often as you’d like.

Email Notifier trigger

Google Apps Script to find duplicates in a Google spreadsheet

Sometimes you want to find duplicate rows in a Google spreadsheet, but don’t want to examine every column — maybe you want to exclude a timestamp or other columns.

This Google Apps Script takes a list of columns that you care about.  Then it temporarily copies those columns to a new sheet, looks for duplicates, highlights the duplicate rows in red on the main sheet, and removes the temporary sheet.

The script is attached to this Google Spreadsheet. Make a copy of this sheet, or just copy the script.  You can do that by going to Tools – Script Editor and copying the script to the script editor of your own spreadsheet. Then edit the line that defines the important columns. After you re-open your spreadsheet, the “My Scripts” menu will show up. The first time you run the script, it will bring up the permissions dialog. You’ll need to run it again to make it work.

Here’s a video of it in action: