How to find next empty cell using google script

68 views
Skip to first unread message

Carla Cecilia Baffoni

unread,
Jan 25, 2022, 11:17:58 AMJan 25
to Google Apps Script Community
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

unread,
Jan 25, 2022, 11:30:09 AMJan 25
to google-apps-sc...@googlegroups.com
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.
Reply all
Reply to author
Forward
0 new messages