Excluding formula columns when finding last row in Google Sheets script

Other Questions Google Sheets
1

Hey everyone, I’m stuck with a Google Sheets problem. I’ve got a script that’s supposed to copy data from A2:D4 to the last row of another sheet. But there’s a catch! The destination sheet has a formula in column E for the whole column. This messes things up, and my script ends up pasting everything at the very bottom.

I’m wondering if there’s a way to make my script ignore that formula column when figuring out where the last row is. Here’s what my current code looks like:

function MoveStuff() {
  let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  let sourceSheet = spreadsheet.getSheetByName('Invoices');
  let dataRange = sourceSheet.getRange('A2:D4');
  let dataValues = dataRange.getValues();
  let targetSheet = spreadsheet.getSheetByName('List');
  targetSheet.getRange(targetSheet.getLastRow() + 1, 1, dataValues.length, dataValues[0].length).setValues(dataValues);
}

Any ideas on how to tweak this so it doesn’t get thrown off by that formula column? Thanks in advance!

2

I’ve encountered this issue before, and there’s a simple workaround. Instead of using getLastRow(), you can use getRange(‘A:D’).getLastRow() to find the last row within your data columns. This ignores the formula in column E. Here’s the modified script:

function MoveStuff() {
  let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  let sourceSheet = spreadsheet.getSheetByName('Invoices');
  let dataRange = sourceSheet.getRange('A2:D4');
  let dataValues = dataRange.getValues();
  let targetSheet = spreadsheet.getSheetByName('List');
  let lastRow = targetSheet.getRange('A:D').getLastRow();
  targetSheet.getRange(lastRow + 1, 1, dataValues.length, dataValues[0].length).setValues(dataValues);
}

This solution should only consider the relevant data columns when determining the last row, ignoring any formulas in other columns. Feel free to reach out if you need further clarification.

3

I’ve dealt with this exact issue before, and I can share a solution that worked for me. Instead of using getLastRow(), which considers all columns, you can find the last row of a specific column that doesn’t have formulas. Here’s how I modified the script:

function MoveStuff() {
  let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  let sourceSheet = spreadsheet.getSheetByName('Invoices');
  let dataRange = sourceSheet.getRange('A2:D4');
  let dataValues = dataRange.getValues();
  let targetSheet = spreadsheet.getSheetByName('List');
  
  // Find last row of column A (or whichever column doesn't have formulas)
  let lastRow = targetSheet.getRange('A:A').getValues().filter(String).length;
  
  targetSheet.getRange(lastRow + 1, 1, dataValues.length, dataValues[0].length).setValues(dataValues);
}

This approach checks only column A for the last row with data, effectively ignoring the formula in column E. It’s been reliable for me across various sheets and datasets. Hope this helps!

4

hey sofiag, i had the same issue. try using getRange(‘A:D’).getLastRow() instead of just getLastRow(). this way it only looks at columns A to D for the last row, ignoring that pesky formula in E. worked like a charm for me. good luck!