How to log data into the last row of the specified column instead of all columns?

Question

Currently, my script is logging values in E based on the position of the last input in columns A,B. Is there a way to prevent these gaps?

 var sss = SpreadsheetApp.openById('sampleID');
 var ss = sss.getSheetByName('Forecast data'); 
 var range = ss.getRange('B126');

const now = new Date();
const data = range.getValues().map(row => row.concat(now));

 var tss = SpreadsheetApp.openById('sampleID2');
 var ts = tss.getSheetByName('Archived Data'); 

 ts.getRange(ts.getLastRow()+1, 5,1,2).setValues(data); 

}

score 2 · Answer 1 · answered May 17 '21 at 19:46

Try something like this:

ts.getRange(getLastRow_(ts, 5) + 1, 5, 1, 2).setValues(data);

Here's a copy of the getLastRow_() function:

/**
* Gets the position of the last row that has visible content in a column of the sheet.
* When column is undefined, returns the last row that has visible content in any column.
*
* @param {Sheet} sheet A sheet in a spreadsheet.
* @param {Number} columnNumber Optional. The 1-indexed position of a column in the sheet.
* @return {Number} The 1-indexed row number of the last row that has visible content.
*/
function getLastRow_(sheet, columnNumber) {
  // version 1.5, written by --Hyde, 4 April 2021
  const values = (
    columnNumber
      ? sheet.getRange(1, columnNumber, sheet.getLastRow() || 1, 1)
      : sheet.getDataRange()
  ).getDisplayValues();
  let row = values.length - 1;
  while (row && !values[row].join('')) row--;
  return row + 1;
}

score 0 · Answer 2 · answered May 17 '21 at 20:32

An alternative way to find it is via filter().

Code:

// Sample data to be iserted
data = [[2.4, '5/5/2021']]

var tss = SpreadsheetApp.openById(sampleID2);
var ts = tss.getSheetByName('Archived Data');

// get values on column E and filter the cells with values and get their length
var column = ts.getRange("E1:E").getValues();
var lastRow = column.filter(String).length;

ts.getRange(lastRow + 1, 5, 1, 2).setValues(data);

Sample data:

Output:

Note:

This approach is good when column has no blank cells in between. When you skip a cell, it will not calculate the lastRow properly and might overwrite data. But as long as you do not have gaps in your column, then this will be good.

Resource:

Determining the last row in a single column