9543

How do I select the first empty row in google spreadsheets/scripts

Question:

So I should start by saying I'm new at Javascript/programming. I understand a good amount of it but don't really know what I'm doing when it comes to actually writing it.

I'm trying to use google script so that when I open my spreadsheet, the cursor begins in the last row, so that I can use a combination of keyboard maestro and apple script to enter text. Problem is, I can't seem to get anything to work. I've tried to use what they have here: <a href="https://stackoverflow.com/questions/6882104/faster-way-to-find-the-first-empty-row-google-apps-script" rel="nofollow">Faster way to find the first empty row</a> but nothing seems to actually work properly. The function runs no problem, but it never actually does anything. I've used the suggested functions along with some of the developer ones of 'getLastrow" but nothing goes.

I think I have the second part down, in that to get it to work when I open the document I set a trigger 'on open', but I can't actually test it until I get the main function working.

Many thanks,

Answer1:

<a href="https://stackoverflow.com/questions/6882104/faster-way-to-find-the-first-empty-row-google-apps-script" rel="nofollow">The referenced question</a> provides <em>scripts</em> with a reference to the first empty row in a spreadsheet. (...although some of those solutions find the first empty cell in column A, which isn't necessarily an empty <strong>row</strong>. But I digress.)

In a spreadsheet-attached script with access to the User Interface, you can affect the <em>active</em> view of the spreadsheet with several functions:

<ul><li><a href="https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#setActiveRange(Range)" rel="nofollow">Spreadsheet.setActiveRange()</a></li> <li><a href="https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#setActiveSelection(Range)" rel="nofollow">Spreadsheet.setActiveSelection()</a> (multiple flavours)</li> <li><a href="https://developers.google.com/apps-script/reference/spreadsheet/sheet#activate()" rel="nofollow">Sheet.activate()</a></li> <li><a href="https://developers.google.com/apps-script/reference/spreadsheet/range#activate()" rel="nofollow">Range.activate()</a></li> <li>... there are other functions that will impact the concept of "active" as side-effects as well.</li> </ul>

Using whatever technique you wish to identify the "empty row" in the <em>active</em> spreadsheet, this function will move the selection to it, using .setActiveSelection():

<pre class="lang-js prettyprint-override">/** * Place the current user's cursor into the first cell of the first empty row. */ function selectFirstEmptyRow() { var sheet = SpreadsheetApp.getActiveSpreadsheet(); sheet.setActiveSelection(sheet.getRange("A"+getFirstEmptyRowWholeRow())) }

The getFirstEmptyRowWholeRow() function, if you're curious:

<pre class="lang-js prettyprint-override">/** * Mogsdad's "whole row" checker. */ function getFirstEmptyRowWholeRow() { var sheet = SpreadsheetApp.getActiveSheet(); var range = sheet.getDataRange(); var values = range.getValues(); var row = 0; for (var row=0; row<values.length; row++) { if (!values[row].join("")) break; } return (row+1); }

Recommend

  • Random slowness of Google Spreadsheet scripts
  • Send single email with values from all new rows in a spreadsheet (Google Script / GAS)
  • VB.Net: Writing To Excel Workbook as Text
  • Google apps Script won't record submissions
  • Lua: Line breaks in strings
  • Criterion causing memory consumption to explode, no CAFs in sight
  • Is there a way to save the selected text and highlight it again once the page is refreshed?
  • xtable package: Skipping some rows in the output
  • How do I get HTML corresponding to current DOM tree?
  • Jackson Parser: ignore deserializing for type mismatch
  • How to define and use opencv mat of user type
  • JQuery Internet Explorer and ajaxstop
  • JSON response opens as a file, but I can't access it with JavaScript
  • How to use remove-erase idiom for removing empty vectors in a vector?
  • MailKit: The IMAP server replied to the 'EXAMINE' command with a 'BAD' response
  • D3 nodes and links from JSON with nested arrays of children
  • Asynchronous UI Testing in Xcode With Swift
  • If I include Java 8 in my Android app does that affect which devices it will work on?
  • Repeat a vertical line on every page in Report Builder / SSRS
  • Why is an OPTIONS request sent to the server?
  • What is Eclipse's Declaration View used for?
  • output of program is not same as passed argument
  • Does CUDA 5 support STL or THRUST inside the device code?
  • Change an a tag attribute in JavaScript based on screen width
  • Statically linking a C++ library to a C# process using CLI or any other way
  • Jquery - Jquery Wysiwyg return html as a string
  • Why winpcap requires both .lib and .dll to run?
  • SVN: Merging two branches together
  • How to format a variable of double type
  • log4net write single file for each call to log.info
  • python draw pie shapes with colour filled
  • Are Kotlin's Float, Int etc optimised to built-in types in the JVM? [duplicate]
  • Append folder name and increment by 1 using batch script
  • XCode 8, some methods disappeared ? ex: layoutAttributesClass() -> AnyClass
  • Running Map reduces the dimensions of the matrices
  • Reading document lines to the user (python)
  • Binding checkboxes to object values in AngularJs
  • Net Present Value in Excel for Grouped Recurring CF
  • jQuery Masonry / Isotope and fluid images: Momentary overlap on window resize
  • How to load view controller without button in storyboard?