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:
In the image above, each time the physicist publishes a study they enter their name, the day’s date the Semester and then drag down column’s D and E to get the running totals for overall published papers and total published papers by semester.
Each time the users open the Active Cell Last Row Google Sheet file they would have to scroll all the way down to the bottom to do this. Considering how prolific these authors appear to be, this could take some time to navigate to the bottom to enter their data. Time that could be spent on more SCIENCE!!!
Let’s give these heroes of the cosmos a hand with a simple code snippet.
The Code
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
/* ################################################################### * Jump to first empty cell after last data item in column and makes it active. * * Limitations: Must not have formulas or data in other columns that exceed * the desired empty cell location. * */ function jumpToFirstEmptyBasic() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName("AstroPhizzz"); // << Change the sheet name to your own sheet. var lastRow = sheet.getLastRow(); sheet.getRange(lastRow+1,1).activate(); |
Super simple, right?
Quick Use
To use this on your own project all you have to do is change the sheet name in line 10 to your own sheet tab name. Just make sure it’s in single or double inverted commas. For example:
'Sheet1'
Click here for a beginner’s guide on how to add your code to your Google Sheet.
Unfortunately, this short-sweet code has a number of limitations that we will cover in our second Scenario.
Code Breakdown
When the jumpToFirstEmptyBasic() function is run on line 8, it first grabs the active spreadsheet using Google Apps Scripts SpreadsheetApp Class. In our case, this is the spreadsheet we opened and added our code to in the script editor:
Active Cell Last Row
On line 10, we then grab our desired sheet tab inside our Google Sheet workbook. For our example, the sheet tab we are looking or is “AstroPhizzz”.
It’s this line that you should change to update your own project.
Next, we want to get the last row that contains data in it in our sheet. Google Apps Script has a handy method for this, getLastRow().
In our example, this will give us row 416.
We will want our users to start one row below the last row of data. They will probably want to start by entering their name too.
Our goal is for the user’s Google Sheet to navigate to where they need to enter their data. In other words, we want to make the first empty cell the active cell for the user.
Line 14 achieves this by first grabbing the sheet. It then gets the range within the sheet we want to make active. we do this with the getRange method.
The getRange method can take either A1 notation like, B7 or C2:G10, or it can take a number value. For example for a single cell range it would take the row and then the column:
sheet.getRange(row number, column number)
When numbering the columns simply remember that column A is 1, column B is 2 etc.
Back to our code, and our row will be our lastRow variable plus one to get us to our first empty cell. We want to start in column A, so our column will be 1.
The last part of the code in line 14 makes the cell active with the activate() method. You can tell by the blue emphasis around the cell when you run the code.
sheet.getRange(lastRow+1,1).activate();
That’s all there is to it. Let’s see how we implement it to run when the Google Sheet workbook is opened or via a button.
Running the Code when the Google Sheet Workbook Opens
Google Apps Script has a convenient trigger function that can run code when your Google Sheet workbook opens, onOpen().
To run our jumpToFirstEmptyBasic() function when the Google Sheet file opens simply add it to the onOpen() trigger function like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
function onOpen(){ jumpToFirstEmptyBasic() }; /* ################################################################### * Jump to first empty cell after last data item in column and makes it active. * * Limitations: Must not have formulas or data in other columns that exceed * the desired empty cell location. * */ function jumpToFirstEmptyBasic() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName("AstroPhizzz"); // << Change the sheet name to your own sheet. var lastRow = sheet.getLastRow(); Logger.log(lastRow); sheet.getRange(lastRow+1,1).activate(); }; |
Running the Code When a Button is Clicked
Perhaps you prefer to have a simple button at the top of the Google Sheet, say on row 1, to quickly get you to the bottom. Like this:
You can create a simple button by going to Insert > Drawing. Then using the shapes and text box in the drawing app create a button. Save and close the drawing.
Next, right-click the new button and some eclipses will appear in the top right of the image. Click them and select Assign a script and add your function to it.
You can get more details on this by following this tutorial:
Google Apps Script: How to Connect a Button to a Function in Google Sheets
Scenario 2: The more difficult, but more useable way
Scenario 1 is fine an all, but what if our data input spreadsheet is a little more complicated? What if we decide to automate some of the processes for our users and drag down the Semester Count and Total Submissions. We also want to provide the semester for the user with a formula that is hidden until the date is entered.
The problem is that if we use the code back in scenario 1, it will take us all the way down to the last cell we dragged column C, D and E data to. In our example, it will take me to A:1000, which is not where it is meant to be.
The Code
Note! You can pop out the code so that you can follow along with the quick use guide and code breakdown.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
/* ################################################################### * Jump to first empty cell after last data item in column and makes it active. * * Iterates through an assigned column using a binary search. * Saves the previous last empty cell session in PropertiesService class. * */ function jumpToFirstEmpty(){ var col = "A";//<< Add your selected reference column var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName("AstroPhizzz");//<<Add your sheet data //Save last end location var docProperties = PropertiesService.getDocumentProperties(); var startRow = docProperties.getProperty('LAST_ROW'); //Check if there is a value in the Property Service if(startRow === null){ startRow = "1"; } //Get the values in col A var range = sheet.getRange(col+startRow+":"+col+sheet.getLastRow()); var rangeVals = range.getValues(); //Check if data has been deleted from the bottom. if(rangeVals[0][0] === ""){ docProperties.setProperty('LAST_ROW',"1"); return jumpToFirstEmpty() //Check if array contains all values. If so set to bottom of range. }else if(rangeVals[rangeVals.length-1][0] !== ""){ var row = Number(startRow)+rangeVals.length sheet.getRange(col+row).activate(); docProperties.setProperty('LAST_ROW',row.toString()); }; //Binary Search https://www.geeksforgeeks.org/binary-search-in-javascript/ var start = 0, end = rangeVals.length-1; while(start<=end){ var mid = Math.floor((start + end)/2); if(rangeVals[mid][0] === "" && rangeVals[mid-1][0] !== ""){ sheet.getRange(col+(Number(startRow)+mid)).activate(); docProperties.setProperty('LAST_ROW',(Number(startRow)+mid).toString()); break; } else if(rangeVals[mid][0] ==="" && rangeVals[mid-1][0] ==="") { end = mid - 1; } else{ start = mid + 1; } }; }; |
Quick Use
To set this up in your own project, simply copy and paste in the code into your Google Apps Script Editor.
Next change variable col on line 9 to the column you want to use as your reference. For example:
var col = "D";
Then change the Google Sheet tab name to the Sheet tab you wish to reference on line 12. For example:
var sheet = ss.getSheetByName("YourSheetName");
You can then either connect it to your onOpen trigger function or a button (see above).
Looking to learn more about Google Apps Scripts in a more structured format? Udemy has some great courses that can get your from the basics to a real Google Apps Script pro!
Got a more specific problem you need help with, but don’t have the time to develop the skills? Fiverr’s your best bet to find a skilled Google Apps Script professional to solve your problem quickly and cheaply. *
*The above affiliate links have been carefully researched to get you to what you specifically need. If you decide to click on one of these links it will cost you just the same as going to the site. If you decide to sign up, I just get a little pocket money to help pay for the costs of running this website.
Code Breakdown
Hey! You made it to the fun stuff!
Before we go step-by-step through the code, let me explain a couple of things we needed to consider first.
We are going to iterate through a selected column to find the first empty cell. We also want to do it fast.
To do this we are going to use two tools:
- PropertiesService: We will use this Google Apps Script Class to store the empty row of the previous time we ran our code. This will allow us to reduce the range of rows we need to iterate through each time to find the first empty cell.
- Binary Search: Binary search will exponentially decrease our iteration time. It works by looking at the midpoint of our range of rows. If the primary condition is not met, it will halve the top or bottom half depending on another set of rules. It will then check the primary condition again. It will continue to do this until it finds the primary condition. More on this when we get there.
Getting the Data
On line 9 we first set what column we are going to use as our iterator. For us, this is column A.
var col = "A";//<< Add your selected reference column
Next, on 11 and 12 we first grab our active Google Sheet workbook with the ss variable using the SpreadsheetApp class. We then call our target sheet tab. In our example, this is our “AstroPhizz” Sheet using the getSheetByName method.
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("AstroPhizzz");//<<Add your sheet data
So now we want to be able to save the end location from the last time we ran our search so that we are not iterating through all the rows from the top of the sheet again.
PropertiesService
We can store data, like an object, in key-value pairs (e.g. "GOAT":"Yagi") using Google Apps Script’s PropertiesService class. This means that even if we close our Google Sheet workbook, we will still have a record the row from the last time we ran our code.
On line 15, we get the document properties from the Properties Service. Document properties are available to all users that will access the assigned document.
We then assign the variable startRow to the last row we saved in our document properties. The row value was saved under the key 'LAST_ROW'. We use getProperty to call that value.
Note! All Properties Service values are stored as strings. You will need to convert things like numbers from their string state before use.
var docProperties = PropertiesService.getDocumentProperties();
var startRow = docProperties.getProperty('LAST_ROW');
Next, we want to check to see if we actually have a value in the LAST_ROW key. If it is the first time we run our code, then we won’t have a value and will return null. If this is the case, then we want to assign startRow to 1 so it can start from the top of the page.
if(startRow === null){ startRow = "1"; }getRange method can take a number of different parameters. This time around we are going to use A1 notation as our parameter format. We’ll join our col variable to the startRow variable to get our starting range. We’ll then join that to the col variable again and then get the last row of our sheet.var range = sheet.getRange(col+startRow+":"+col+sheet.getLastRow());var range = sheet.getRange("A400:A1000");var rangeVals = range.getValues();|
1 2 3 4 5 6 7 8 |
[ [...] [David Charbonneau], [Neil deGrasse Tyson], [], [], [...] ] |
Running some verification
Before we get cracking with our binary search it would be beneficial to run some checks on the data first.
Check if data has been deleted
If users have been deleted from our dataset since the last time we ran our function, then the start row saved in the Properties Service will be greater than the first empty row. We need to check for this.
We can do this by seeing if our first value in our rangeVals is blank. If this is true, we can reset our Properties Service property with setProperty to row 1 again. We then call our function again recursively.
Lines 27 to 32 take care of this.
Note that the setproperty method takes the key value, in our case 'LAST_ROW' and then the new value as a string. We will want to set this to '1'.
|
27 28 29 30 31 |
//Check if data has been deleted from the bottom. if(rangeVals[0][0] === ""){ docProperties.setProperty('LAST_ROW',"1"); return jumpToFirstEmpty() |
Check if the range contains all values and no empty cells
Alternatively, if the range only contains cell values in each row of data and there are no empty cells, then the first row will be the sum of the startRow function plus the length of the rangeVals.
If we check the last value of our rangeVals to see if it is not equal to blank, then we know that the entire range contains values (Line 34).
We then get our row by adding the rangeVals to the startRow (Line 36).
Next, we add col to our row and set it to Activate. The sheet will then travel to that cell for the user to add their data.
Finally, we need to update our LAST_ROW value in our Properties Service with our new row.
|
33 34 35 36 37 38 39 |
//Check if array contains all values. If so set to bottom of range. }else if(rangeVals[rangeVals.length-1][0] !== ""){ var row = Number(startRow)+rangeVals.length sheet.getRange(col+row).activate(); docProperties.setProperty('LAST_ROW',row.toString()); }; |
Binary Search
For the binary search, I shamelessly hacked the example from GeeksforGeeks. Of course, it needed quite a bit of modification to get where I wanted.
The row we want to find is the first empty cell below the last entered data. The condition we need to meet then is that if the current row item is equal to empty (“”) and the previous row item is not equal to empty, then this is the first empty cell.
If this condition is not met, then we ask if the current row and previous row are both equally empty then we want to look at the halfway point above the currently assigned middle.
Alternatively, if the current and previous row item both contain values then we want to look at the halfway point below the currently assigned middle.
If the primary condition is not met then the loop continues halving away at the data until it finds it.
Let’s go through the steps.
|
41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
//Binary Search https://www.geeksforgeeks.org/binary-search-in-javascript/ var start = 0, end = rangeVals.length-1; while(start<=end){ var mid = Math.floor((start + end)/2); if(rangeVals[mid][0] === "" && rangeVals[mid-1][0] !== ""){ sheet.getRange(col+(Number(startRow)+mid)).activate(); docProperties.setProperty('LAST_ROW',(Number(startRow)+mid).toString()); break; } else if(rangeVals[mid][0] ==="" && rangeVals[mid-1][0] ==="") { end = mid - 1; } else{ start = mid + 1; } }; |
On line 42, we set the start and end variables. The end variable is the length of the rangeVals-1.
Line 44 commences the while loop continuing the loop if the start is less than or equal to the end.
Next, on line 45, we assign the middle point with the mid variable. To do this, we add the start to the end and divide by two. We then use math.floor to round down. math.floor is used here so as not to exceed the length limit of the array.
We then set our primary condition on line 47. If the current row value is empty and the previous row value is not empty, then we want to activate that cell and update our LAST_ROW value in Properties Service.
On line 49, we get our last empty cell range. We add the col to the sum of the startRow and the mid variable. The cell is then activated.
Line 50, then sets the new LAST_ROW value.
Lastly, we break the loop. Job’s done.
Our first failure condition is when both the current and previous rows value are both equal empty (Line 53). If this is the case, then our desired cell is somewhere above the midpoint. We can then reset our end value to the mid minus one. The loop is then repeated.
Alternatively, if both the current and previous rows contain a value, our desired value is below the midpoint. We then reset our start value to the mid plus one and repeat our loop.
Done the jumpToFirstEmpty() function is complete.
Conclusion
Both the easy scenario 1 function and the more complex scenario functions have their uses. However, jumpttoFirstEmpty() is designed to meet scenario 1’s conditions too. It is certainly more versatile, particularly in more complex data entry Google Sheet environments.
jumpToFirstEmpty does, however, have its limitations. Imagine if your data is a bit patchy and there are a few empty cells somewhere in the middle of your data. In this case, the binary search may display a false positive and activate an empty cell in the middle of your data. How would you resolve this?
Have a look at the tutorial below and see if you can combine what you learnt here with that tutorial to fix this issue.
So what did you use this on? I would love to hear in the comments below. It’s always interesting to see how these code snippets are applied to other projects.
Happy coding!
Need help with Google Workspace development?
My team of experts can help you with all of your needs, from custom app development to integrations and security. We have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.
Schedule a free consultation today to discuss your needs and get started or learn more about our services here.
~Yagi
I found this very helpful – thank you.
Do you know how I would go to the last empty row in column B with a particular value in column A?
For example if column A is Class A, Class B or Class C and column B is student name and I want to jump to the next empty space for a student in Class B.
Thanks
Ger
Hello
Thanks so much for the code and for the in depth explanation.
I have used the code in a sheet. One question I have is, can it be changed so that it can search for the first empty cell in any sheet in the workbook? I need to find the empty cell in two different sheets at different times. This code only works on the sheet I’ve named.
I have attempted to edit, however, am such a novice at script that I can’t seem to get it to work.
Any further help is greatly appreciated.
B
Why can’t there be a keyboard shortcut or like in Excel you can hit End then the down arrow & that moves you down to the next empty cell. All this code seems too much if you ask me
Hi Lynford,
The above tutorial is just in case you need to do something programmatically. There are a lot of use cases where I have an automation that needs to get the last “real” row.
As a user, though, I usually just hit ctrl + down arrow.
You might find the tips below useful:
https://yagisanatode.com/2018/12/05/5-easy-shortcuts-that-will-save-you-heaps-of-time-google-sheets/
~Yagi
This is fantastic! Such a thorough easy to follow explanation. Thank you!
Thanks for the great feedback CJ!
Thank you for this detailed breakdown!!!
Hi Ricardo,
Thanks for the kind words.
~Yagi
Hello, thank you very much, I have worked a code to copy the last active row of a sheet called REGISTRATION, but I would like that row, copy the values from column A to F, and paste the values only, in the first row empty sheet B. Can you help me?
function jumpToFirstEmptyBasic() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(“REGISTRO”); //
var lastRow = sheet.getLastRow();
sheet.getRange(lastRow,1).activate();
}
thank you
This is PERFECT!
Thanks for sharing.
You’re welcome, Wayne.
Happy coding.
~Yagi