Tag: iteration

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
  • The Code
    • Quick Use
    • Code Breakdown
  • Running the Code when the Google Sheet Workbook Opens
  • Running the Code When a Button is Clicked
• Scenario 2: The more difficult, but more useable way
  • The Code
    • Quick Use
    • Code Breakdown
      • Getting the Data
        • PropertiesService
      • Running some verification
        • Check if data has been deleted
        • Check if the range contains all values and no empty cells
      • Binary Search
• Conclusion

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:

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