Add value (n+1) in last empty row

Question

I have a problem, I don't know how create button in a Google spreadsheet which will find the last empty row and add in first column next number (n+1) and in second column actual date.

function selectFirstEmptyRow() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  sheet.setActiveSelection(sheet.getRange("A" + getFirstEmptyRowWholeRow()))
  // copy value from row
  var ss = SpreadsheetApp.getActiveSheet();

  ss.insertRowAfter(ss.getLastRow());
  ss.getRange(1, 1).copyTo(ss.getRange(ss.getLastRow() + 1, 1));

  // add 1
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var activeRange = ss.getActiveRange();
  var cell, cellValue, cellFormula;

  // iterate through all cells in the active range
  for (var cellRow = 1; cellRow <= activeRange.getHeight(); cellRow++) {
    for (var cellColumn = 1; cellColumn <= activeRange.getWidth(); cellColumn++) {
      cell = activeRange.getCell(cellRow, cellColumn);
      cellFormula = cell.getFormula();

      if (cellFormula[0] != "=") {
        cellValue = cell.getValue();
        cell.setValue(cellValue + 1);
      }
    }
  }
}

function getFirstEmptyRowWholeRow() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();
  var row = 3;
  for (var row = 3; row < values.length; row++) {
    if (!values[row].join("")) break;
  }
  return (row + 1);
}

Possible duplicate of [How do you add UI inside cells in a google spreadsheet using app script?](http://stackoverflow.com/questions/6876819/how-do-you-add-ui-inside-cells-in-a-google-spreadsheet-using-app-script) — Rubén, Apr 15 '17 at 23:12
Thank you for help, but my main problem, was the formula how add number and date in next empty row. I mistakenly asked the question. Regards. — J.Doe, Apr 16 '17 at 07:34

score 0 · Accepted Answer · answered Apr 15 '17 at 19:59

You can add a custom menu to a Google spreadsheet:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('My menu')
      .addItem('Add new date row', 'insertAtNextEmptyRow')
      .addToUi();
}

function insertAtNextEmptyRow() {
  var firstRow = 3;

  var sheet = SpreadsheetApp.getActiveSheet();
  var row = getNextEmptyRow(firstRow);
  var previousNum = SpreadsheetApp.getActiveSheet().getRange('A' + (row - 1)).getValue()
  if (previousNum !== parseInt(previousNum, 10))
    previousNum = row - firstRow - 1;
  SpreadsheetApp.getActiveSheet().getRange('A' + row).setValue(previousNum + 1);
  var date = Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd");
  SpreadsheetApp.getActiveSheet().getRange('B' + row).setValue(date);
}

function getNextEmptyRow(firstRow) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();

  for (var row = firstRow; row < values.length; row++) {
    if (!values[row].join("")) 
      return (row + 1);
  }
  return null;
}

Here is what it'll look like:

I changed some thing and it works that I want, thank you for help. Regards — J.Doe, Apr 16 '17 at 07:20

Add value (n+1) in last empty row

1 Answers1