How to find next empty cell using google script

[Carla Cecilia Baffoni]

Hi! i need to know which function or code could i use in order to find the next empty cell in a sheet. Al i've been reading gives me the last empty row, but that doesn't work for me because i have formulas in other columns.
I'm copying a range from Sheet 1 and i need to find the next empty cell in Sheet 2 column B and paste that information i got from sheet 1 into that empty cell.
Do you know how to do it?

Thanks!

This is the function i have so far, being Consolidated the Sheet 2 in my example above.
function EmptyCell() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Consolidated");
 
  var lastRow = sheet.getLastRow();
 
  sheet.getRange(lastRow+1,2).activate();
    };

---

This email and any files transmitted with it contain confidential information and/or privileged or personal advice. This email is intended for the addressee(s) stated above only. If you are not the addressee of the email please do not copy or forward it or otherwise use it or any part of it in any form whatsoever. If you have received this email in error please notify the sender and remove the e-mail from your system. Thank you.

This is an email from the company Just Eat Takeaway.com N.V., a public limited liability company with corporate seat in Amsterdam, the Netherlands, and address at Oosterdoksstraat 80, 1011 DK Amsterdam, registered with the Dutch Chamber of Commerce with number 08142836 and where the context requires, includes its subsidiaries and associated undertakings.

[Jon Couch]

There are probably more elegant methods but here's what I do.

var Bvals  = dest.getRange("B:B").getValues();      // get the data in column B
var destLast = Bvals.filter(String).length + 1;      // get the length of the data and add 1 to get the row number for the first blank cell in Column B

dest.getRange(destLast,2).setValue(copied variable);       // write your copied data to that cell

rinse and repeat

Maybe that will work till something better shows up. BTW: not original with me, copied from somewhere else!

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/0b76b443-f298-4344-b09e-368f06637d96n%40googlegroups.com.

[Derek Halverson]

Hey Guys,

I am not sure how this form works, but I have a similar problem. I use VBA a lot so I am more familiar with that, but I want to write some code in the google script.

I am trying to write code that will look at Column A, find the next empty cell, and then make it the active cell. 

In VBA that code is simply:    Range("A:A").Find("").Select

What is the equivalent in google script? I cannot seem to find anything on this.

Thanks,

[Laurie J. Nason]

Hi Derek,

 I think you want the function setActiveSelection()

Laurie

------ Original Message ------

From "Derek Halverson" <halver...@gmail.com>

To "Google Apps Script Community" <google-apps-sc...@googlegroups.com>

Date 11/24/2022 3:37:01 AM

Subject Re: [Apps-Script] How to find next empty cell using google script

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/aa23c32b-6361-456d-a081-38eda5acf2cdn%40googlegroups.com.

[Michael O'Shaughnessy]

Just adding to the conversation... I find the function "dataRegion" to come in handy when I don't want ALL the values on a sheet or if I am just looking for one column.  I find using "dataRegion" is easier because it will stop at the first "blank" cell.

Here is some code to try:

const test = ()=>{

let ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("namelist2")

let data = ss.getRange("A1").getDataRegion(SpreadsheetApp.Dimension.ROWS).getValues()

//let data = ss.getRange("A1").getDataRegion().getValues()

console.log(`The number of rows is ${data.length} and the number of cols is ${data[0].length}`)

}

This will get all the data in column A up to the first blank cell.  Once you know that length, you just add 1 to get to the empty cell.

Here is a link to the support docs that talks about it: https://developers.google.com/apps-script/reference/spreadsheet/range#getdataregion

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/emf359d2cd-a1ba-4e99-971f-4b1dc0d00927%408c140625.com.