How to Automatically Navigate to the First Empty Row in a Google Sheet using Google Apps Script

Google Sheets, Google Apps Script: SpreadsheetApp, PropertiesService, Binary Search, onOpen, Button

Ever had to open a huge data entry spreadsheet an all you want to do is enter your data and move onto another task? Instead, you have to waste precious time navigating all the way down to the bottom of the page to add your data.

Boo!!!

Wouldn’t it be cool to just jump down to the first empty row and get clicky-clacking away?

Behold, dear biped! This friendly coding goat has a little gift for you.

In this tutorial, we are going to tackle two ways to automatically move down to the first available empty cell. One will be super easy and the other slightly more tricky, fun and versatile (You can thank one of the Google Apps Script outreach gurus for the inspiration for this one).

We’ll show you how to set up this little code so it can be run via a button at the top of your page or when you open the Google Sheet workbook each time.

Note! As always feel free to read the entire tutorial or take what you need. There is a quick start guide for those people who just want to copy and paste into their own projects and a deep dive for those who want to understand the code. 

Contents

Scenario 1: The Super Easy Way

Let’s say we have a simple data set that the user needs to enter a new row of data each time the open the Google Sheet workbook.

Something like this:

Sample File - Google Sheets first empty cell at end of data

Continue reading “How to Automatically Navigate to the First Empty Row in a Google Sheet using Google Apps Script”

Google Apps Script: Delete Rows Based on a Column’s Cell Value in Google Sheet

Google Apps Script, SpreadsheetApp, Google Sheet, Iteration

A not too uncommon task for Google Sheets users is to delete rows based on a cell’s values in a certain column. Often you don’t want to change the row position of the values so sorting the values and then deleting them in blocks is out.

All you want to do is iterate through the rows in the Sheet searching your selected column for the text or value that will trigger your code to remove the assigned row. Then delete the row.

Rinse and repeat until all rows have been checked.

This task can be automated with Google Apps Script in a number of ways depending on the size of your data set.

Small Data Sets

The most logical approach to delete rows in a small data set might be like this:

In the Globals section, lines 5-7 get the Spreadsheet, Sheet and Range Data. We use the SpreadsheetApp Class to get this information. For your own use, you would change the SS and SHEET variables to suit your project. The RANGE variable is the full range of data

Next, we have the parameters we will use to decide what rows we will delete. The DELETE_VAL variable is the text value in the row you want to delete. When the code sees this, it will delete the row.

The COL_TO_SEARCH variable tells the code which column to search. In your Google Sheet, you will have a letter across the top to determine the columns. We want to change this to a number. In coding, numbers start at zero. So, if your column to search is, say column “D”, then you would count:

0:A, 1:B, 2:C, 3:D

deleteEachRow()

Continue reading “Google Apps Script: Delete Rows Based on a Column’s Cell Value in Google Sheet”