0

I need a script to find the first empty row of a google spreadsheet drive , or that since open this spreadsheet in the first empty row.

So, I tried this:

function onOpen(){
var menu = SpreadsheetApp.getUi();
menu.createMenu('Audit PS')
     .addItem('Colar', 'getFirstEmptyRowByColumnArray')
  .addToUi();
}

function getFirstEmptyRowByColumnArray() {
  var spr = SpreadsheetApp.getActiveSpreadsheet();
  var column = spr.getRange('A:A');
  var values = column.getValues(); 
  var ct = 0;
  while ( values[ct] && values[ct][0] != "" ) {
    ct++;
  }
 return (ct+1); 
}

The menu was created and by clicking on it , the script runs . But nothing happens in the spreadsheet.

what is wrong?

Cris Girardi
  • 67
  • 1
  • 9
  • why would anything happen? the code is not changing anything or even showing the result anywhere. – Zig Mandel Jul 21 '15 at 13:28
  • So how do I get this script returns the first empty row? – Cris Girardi Jul 21 '15 at 14:31
  • what do you mean by return? to select it on the sheet? see the spreadsheetApp sheet methods. – Zig Mandel Jul 21 '15 at 14:32
  • I mean how do this script show the first empty row in the spreadsheet? What I need is when I open the spreadsheet , the script point to the first empty row. I have no knowledge of scripts , but I found this on another question. – Cris Girardi Jul 21 '15 at 14:40
  • possible duplicate of [How do I select the first empty row in google spreadsheets/scripts](http://stackoverflow.com/questions/27161792/how-do-i-select-the-first-empty-row-in-google-spreadsheets-scripts) – Zig Mandel Jul 21 '15 at 15:09

1 Answers1

0

It is a bit unclear what you are trying to accomplish, but I will try my best to help based on what I think youre going for.
It sounds like you want to select the last row in a spreadsheet with one column. Not sure if you mean getting the cells object or just setting it to active.
So first off, you are only selecting the spreadsheet, not the sheet, so I would recommend changing getActiveSpreadsheet() to getActiveSheet(). So now lets assume your code looks like this:
var sheet = SpreadsheetApp.getActiveSheet();
To get the range of cells in this sheet that contain data, use the .getDataRange() function on the sheet. You can then use the .getLastRow() function to get the last row containing data. Once you know this, you have all the info you need. If you are trying to get the cell object, your code would look like this"

var sheet = SpreadsheetApp.getActiveSheet();
var lastRow = sheet.getDataRange().getLastRow(); var cell = sheet.getRange(lastRow + 1, 1); // Substituted whatever column for the second argument
You haven't specified what you want to do once you get the cell, so you will have to figure the rest out yourself! Hope this helps.

Jonathan Seed
  • 1,957
  • 16
  • 21
  • Thank you Jonathan! I want to direct the cursor to the first empty cell so that I do not find manually. I try the functions selectFirstEmptyRow() and getFirstEmptyRowByColumnArray(), however, it runs just when the spreadsheet have few rows filled. is there any limitation in this script? how do I fix it? – Cris Girardi Jul 22 '15 at 02:52
  • I am not extremely experienced with sheets, but I believe you could accomplish this using the code I provided above, and using either the .setActiveRange or .setActiveSelection functions and providing the cell variable as an argument. – Jonathan Seed Jul 22 '15 at 14:58