I’m working on a Google Apps Script that transfers data from one sheet to another. My script copies information from range B3:E5 and pastes it to the next available row in my target sheet.
The issue I’m facing is that my destination sheet has formulas in column F that extend down the entire column. When my script runs, it treats these formula cells as occupied rows, so my data gets pasted way down at the bottom instead of the first truly empty row.
Is there a way to make the script only consider rows with actual data and ignore columns that contain formulas?
function TransferRecords() {
var workbook = SpreadsheetApp.getActive();
var sourceSheet = workbook.getSheetByName("Orders");
var dataRange = sourceSheet.getRange("B3:E5");
var recordData = dataRange.getValues();
var targetSheet = workbook.getSheetByName("Database");
targetSheet.getRange(targetSheet.getLastRow() + 1, 2, recordData.length, recordData[0].length).setValues(recordData);
};
yea, u can try using getLastRow on a specific column that doesn’t have formulas. for instance, targetSheet.getRange('B:B').getLastRow() if column B has ur actual data. this should help avoid those formula cells in col F.
I’ve hit this exact issue before. Skip getLastRow() on the whole sheet - it’s grabbing data from column F that you don’t want. Instead, check for the last row with actual data in your specific columns. Use targetSheet.getRange('B:E').getValues() to pull values from just those columns. Then loop backwards from the bottom to find the first non-empty row, or filter out the empty ones. Your script will ignore column F completely and only look at the columns that actually matter.
Here’s what I do instead: define a specific range for checking the last row rather than scanning the entire sheet. I create a helper function that only looks at columns where I actually have data. Try modifying your script to use targetSheet.getRange('B:E').getLastRow() - this only checks rows with content in columns B through E. This approach has saved me tons of headaches when dealing with sheets where formulas or formatting extend way past the actual data. The trick is being explicit about which columns have real data vs. helper formulas.